MySQL集群PXC的搭建
最近公司某客户要求我们的数据库搭建PXC集群以保证他们的系统高性能和搞稳定性
以后花费了一些时间去搭建和测试，也踩过一些坑，准备分享出来
系统：centos6.6
PXC：5.6.26
建议关闭iptables， 4个端口 3306,4444,4567,4568
pxc环境所涉及的端口：
#mysql实例端口：3306.
#pxc cluster相互通讯的端口：4567
Port for group communication, default 4567. It can be changed by the option:
wsrep_provider_options ="gmcast.listen_addr=tcp://0.0.0.0:4010; "
#用于SST传送的端口：4444
Port for State Transfer, default 4444. It can be changed by the option:
wsrep_sst_receive_address=10.11.12.205:5555
#用于IST传送的端口：4568
Port for Incremental State Transfer, default port for group communication + 1 (4568). It can be changed by the option:
wsrep_provider_options = "ist.recv_addr=10.11.12.206:7777; "
node1 192.168.3.130
node2 192.168.3.129
node3 192.168.3.128
1.安装软件依赖包（添加repl源，再安装依赖，3台server操作一样。）
rpm -ivh yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes socat nc -y
2.安装xtrabackup（PXC同步数据需要用到）
yum install -y yum install -y percona-xtrabackup-24
3.下载安装Percona-XtraDB-Cluster
#安装开发包
yum install -y libaio*yum groupinstall -y 'Development tools'
yum install -y libssl.so.6
ln -sf /usr/lib64/libssl.so.10 /usr/lib64/libssl.so.6
ln -sf /usr/lib64/libcrypto.so.10 /usr/lib64/libcrypto.so.6
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-56/Percona-XtraDB-Cluster-5.6.26-25.12/binary/tarball/Percona-XtraDB
-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gztar zxvf Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz
useradd -M -s /sbin/nologin mysqlmkdir -p /usr/local/pxcmkdir -p /data/pxc/mysql3306/{data,tmp,logs}mv /data/download/Percona-
XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64/* /usr/local/pxc/
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile
chown -R mysql:mysql /data/pxc/mysql3306/
chown -R mysql:mysql /usr/local/pxc
修改my.cnf配置文件，注意在[mysqld]段落添加PXC的参数：
vi /etc/my.cnf
130
[client] port = 3306socket = /data/mysql/mysql3306/tmp/mysql.sock # The MySQL server [mysqld] #########Basic################## explicit_defaults_for_timestamp=trueport = 3306 user = mysql basedir = /usr/local/mysql datadir = /data/mysql/mysql3306/data tmpdir = /data/mysql/mysql3306/tmp pid-file = /data/mysql/mysql3306/tmp/mysql.pid socket = /data/mysql/mysql3306/tmp/mysql.sock #skip-grant-tables #character set character_set_server = utf8 open_files_limit = 65535back_log = 500#event_scheduler = ON #lower_case_table_names=1skip-external-locking skip_name_resolve = 1default-storage-engine = InnoDB #timeout wait_timeout=1000interactive_timeout=1000connect_timeout = 20server-id =1303306 #ip最后一位+端口号= 1003376#percona 的--recursion-method slavehost模式 #report_host = 10.105.9.115#report_port = 3306#plugin plugin-load="semisync_master.so;semisync_slave.so"#########error log#############log-error = /data/mysql/mysql3306/logs/error.log log-warnings = 2 #########general log############# #general_log=1#general_log_file=/data/mysql/mysql3306/logs/mysql.log #########slow log############# slow_query_log = 1long_query_time=1slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow ############# for replication################### log-bin = /data/mysql/mysql3306/logs/mysql-bin binlog_format = row max_binlog_size = 500M binlog_cache_size = 2M max_binlog_cache_size = 2M expire-logs-days = 7slave-net-timeout=30log_bin_trust_function_creators = 1log-slave-updates = 1 skip-slave-start = 1#read_only =1 #从上设置,5.7是super_read_only #GTID gtid-mode = on binlog_gtid_simple_recovery=1enforce_gtid_consistency=1#relay log relay-log = /data/mysql/mysql3306/logs/mysql-relay relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index max-relay-log-size = 500M #replication crash safe sync_master_info = 1sync_relay_log_info = 1sync_relay_log = 1relay_log_recovery = 1master_info_repository = TABLE relay_log_info_repository = TABLE #semisync 动态开启 主从切换的时候用 #rpl_semi_sync_master_enabled = 1#rpl_semi_sync_master_wait_no_slave = 1#rpl_semi_sync_master_timeout = 1000#rpl_semi_sync_slave_enabled = 1#rpl_semi_sync_master_timeout = 100000000 #不要超时,防止切异步,保证主从数据的完全一致性,默认是10000 #rpl_semi_sync_master_wait_point = 'after_sync' #5.7新增参数 after_sync 5.7增强半同步 after_commit5.6普通半同步 #rpl_semi_sync_master_wait_for_slave_count = 2 #5.7新增参数 等待多少个从库接收到binlog #ignore #replicate-ignore-db = 'school','school2'#replicate-do-db = 'school','school2'#Multi-threaded Slave #slave_parallel_workers=16#slave-parallel-type=LOGICAL_CLOCK #slave-parallel-type=DATABASE #replication error #slave-skip-errors=1007,1051,1062#######per_thread_buffers##################### max_connections=1100max_user_connections=1000max_connect_errors=1000#myisam_recover key_buffer_size = 64M max_allowed_packet = 16M #table_cache = 3096table_open_cache = 6144table_definition_cache = 4096read_buffer_size = 1M join_buffer_size = 128K read_rnd_buffer_size = 1M #myisam sort_buffer_size = 128K myisam_max_sort_file_size = 10G myisam_repair_threads = 1myisam_sort_buffer_size = 32M tmp_table_size = 32M max_heap_table_size = 64M query_cache_type=0query_cache_size = 0bulk_insert_buffer_size = 32M thread_cache_size = 64#thread_concurrency = 32thread_stack = 192K ###############InnoDB########################### innodb_data_home_dir = /data/mysql/mysql3306/data innodb_log_group_home_dir = /data/mysql/mysql3306/logs innodb_data_file_path = ibdata1:1000M:autoextend innodb_buffer_pool_size = 1G #根据内存大小设置 innodb_buffer_pool_instances = 8#innodb_additional_mem_pool_size = 16M innodb_log_file_size = 500M innodb_log_buffer_size = 16M innodb_log_files_in_group = 3innodb_flush_log_at_trx_commit = 1sync_binlog = 1innodb_lock_wait_timeout = 10innodb_sync_spin_loops = 40innodb_max_dirty_pages_pct = 80innodb_support_xa = 1innodb_thread_concurrency = 0innodb_thread_sleep_delay = 500innodb_concurrency_tickets = 1000innodb_flush_method = O_DIRECT innodb_file_per_table = 1innodb_read_io_threads = 16innodb_write_io_threads = 16innodb_io_capacity = 800 #机械盘800 ssd 2000innodb_flush_neighbors = 1innodb_file_format = Barracuda innodb_purge_threads=1 #5.6只能有一个，5.7可以设置多个 innodb_purge_batch_size = 32innodb_old_blocks_pct=75innodb_change_buffering=all innodb_stats_on_metadata=OFF innodb_print_all_deadlocks = 1#innodb_status_output=1#innodb_status_output_locks=1performance_schema=0 #是否打开P_S库进行性能收集 transaction_isolation = READ-COMMITTED #innodb_force_recovery=0#innodb_fast_shutdown=1#innodb_status_file = 1 #实时输出show engine innodb status到innodb_status文件 ############# PXC ##################### innodb_autoinc_lock_mode=2 #自增锁的优化 wsrep_cluster_name=pxc-dongzheng #集群名字 wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so #库文件位置 wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129 #节点中所有ip wsrep_node_address=192.168.3.130 #本节点的ip wsrep_slave_threads=2 # 开启的复制线程数，建议cpu核数*2 ，解决apply_cb跟不上问题 wsrep_sst_auth=sst:dongzheng #sst模式需要的用户名和密码 wsrep_sst_method=xtrabackup-v2 #采用什么方式复制数据。还支持mysqldump，rsync #wsrep_provider_options="debug=1;gcache.size=2G;pc.ignore_quorum=true;pc.ignore_sb=true" #打开调试模式 wsrep_provider_options="debug=1;gcache.size=2G;" wsrep_max_ws_rows=131072wsrep_max_ws_size=16000[mysqldump] quick max_allowed_packet = 128M [mysql] no-auto-rehash max_allowed_packet = 128M prompt = '(product)\u@\h:\p [\d]> 'default_character_set = utf8 [myisamchk] key_buffer_size = 64M sort_buffer_size = 512k read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] #malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so
129
[client] port = 3306socket = /data/mysql/mysql3306/tmp/mysql.sock # The MySQL server [mysqld] #########Basic################## explicit_defaults_for_timestamp=trueport = 3306 user = mysql basedir = /usr/local/mysql datadir = /data/mysql/mysql3306/data tmpdir = /data/mysql/mysql3306/tmp pid-file = /data/mysql/mysql3306/tmp/mysql.pid socket = /data/mysql/mysql3306/tmp/mysql.sock #skip-grant-tables #character set character_set_server = utf8 open_files_limit = 65535back_log = 500#event_scheduler = ON #lower_case_table_names=1skip-external-locking skip_name_resolve = 1default-storage-engine = InnoDB #timeout wait_timeout=1000interactive_timeout=1000connect_timeout = 20server-id =1293306 #ip最后一位+端口号= 1003376#percona 的--recursion-method slavehost模式 #report_host = 10.105.9.115#report_port = 3306#plugin plugin-load="semisync_master.so;semisync_slave.so"#########error log#############log-error = /data/mysql/mysql3306/logs/error.log log-warnings = 2 #########general log############# #general_log=1#general_log_file=/data/mysql/mysql3306/logs/mysql.log #########slow log############# slow_query_log = 1long_query_time=1slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow ############# for replication################### log-bin = /data/mysql/mysql3306/logs/mysql-bin binlog_format = row max_binlog_size = 500M binlog_cache_size = 2M max_binlog_cache_size = 2M expire-logs-days = 7slave-net-timeout=30log_bin_trust_function_creators = 1log-slave-updates = 1 skip-slave-start = 1#read_only =1 #从上设置,5.7是super_read_only #GTID gtid-mode = on binlog_gtid_simple_recovery=1enforce_gtid_consistency=1#relay log relay-log = /data/mysql/mysql3306/logs/mysql-relay relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index max-relay-log-size = 500M #replication crash safe sync_master_info = 1sync_relay_log_info = 1sync_relay_log = 1relay_log_recovery = 1master_info_repository = TABLE relay_log_info_repository = TABLE #semisync 动态开启 主从切换的时候用 #rpl_semi_sync_master_enabled = 1#rpl_semi_sync_master_wait_no_slave = 1#rpl_semi_sync_master_timeout = 1000#rpl_semi_sync_slave_enabled = 1#rpl_semi_sync_master_timeout = 100000000 #不要超时,防止切异步,保证主从数据的完全一致性,默认是10000 #rpl_semi_sync_master_wait_point = 'after_sync' #5.7新增参数 after_sync 5.7增强半同步 after_commit5.6普通半同步 #rpl_semi_sync_master_wait_for_slave_count = 2 #5.7新增参数 等待多少个从库接收到binlog #ignore #replicate-ignore-db = 'school','school2'#replicate-do-db = 'school','school2'#Multi-threaded Slave #slave_parallel_workers=16#slave-parallel-type=LOGICAL_CLOCK #slave-parallel-type=DATABASE #replication error #slave-skip-errors=1007,1051,1062#######per_thread_buffers##################### max_connections=1100max_user_connections=1000max_connect_errors=1000#myisam_recover key_buffer_size = 64M max_allowed_packet = 16M #table_cache = 3096table_open_cache = 6144table_definition_cache = 4096read_buffer_size = 1M join_buffer_size = 128K read_rnd_buffer_size = 1M #myisam sort_buffer_size = 128K myisam_max_sort_file_size = 10G myisam_repair_threads = 1myisam_sort_buffer_size = 32M tmp_table_size = 32M max_heap_table_size = 64M query_cache_type=0query_cache_size = 0bulk_insert_buffer_size = 32M thread_cache_size = 64#thread_concurrency = 32thread_stack = 192K ###############InnoDB########################### innodb_data_home_dir = /data/mysql/mysql3306/data innodb_log_group_home_dir = /data/mysql/mysql3306/logs innodb_data_file_path = ibdata1:1000M:autoextend innodb_buffer_pool_size = 1G #根据内存大小设置 innodb_buffer_pool_instances = 8#innodb_additional_mem_pool_size = 16M innodb_log_file_size = 500M innodb_log_buffer_size = 16M innodb_log_files_in_group = 3innodb_flush_log_at_trx_commit = 1sync_binlog = 1innodb_lock_wait_timeout = 10innodb_sync_spin_loops = 40innodb_max_dirty_pages_pct = 80innodb_support_xa = 1innodb_thread_concurrency = 0innodb_thread_sleep_delay = 500innodb_concurrency_tickets = 1000innodb_flush_method = O_DIRECT innodb_file_per_table = 1innodb_read_io_threads = 16innodb_write_io_threads = 16innodb_io_capacity = 800 #机械盘800 ssd 2000innodb_flush_neighbors = 1innodb_file_format = Barracuda innodb_purge_threads=1 #5.6只能有一个，5.7可以设置多个 innodb_purge_batch_size = 32innodb_old_blocks_pct=75innodb_change_buffering=all innodb_stats_on_metadata=OFF innodb_print_all_deadlocks = 1#innodb_status_output=1#innodb_status_output_locks=1performance_schema=0 #是否打开P_S库进行性能收集 transaction_isolation = READ-COMMITTED #innodb_force_recovery=0#innodb_fast_shutdown=1#innodb_status_file = 1 #实时输出show engine innodb status到innodb_status文件 ############# PXC ##################### innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-dongzheng wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129 wsrep_node_address=192.168.3.129 wsrep_slave_threads=2 wsrep_sst_auth=sst:dongzheng wsrep_sst_method=xtrabackup-v2 #wsrep_provider_options="debug=1;gcache.size=2G;pc.ignore_quorum=true;pc.ignore_sb=true" #打开调试模式 wsrep_provider_options="debug=1;gcache.size=2G;" wsrep_max_ws_rows=131072wsrep_max_ws_size=16000 [mysqldump] quick max_allowed_packet = 128M [mysql] no-auto-rehash max_allowed_packet = 128M prompt = '(product)\u@\h:\p [\d]> 'default_character_set = utf8 [myisamchk] key_buffer_size = 64M sort_buffer_size = 512k read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] #malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so
128
[client] port = 3306socket = /data/mysql/mysql3306/tmp/mysql.sock # The MySQL server [mysqld] #########Basic################## explicit_defaults_for_timestamp=trueport = 3306 user = mysql basedir = /usr/local/mysql datadir = /data/mysql/mysql3306/data tmpdir = /data/mysql/mysql3306/tmp pid-file = /data/mysql/mysql3306/tmp/mysql.pid socket = /data/mysql/mysql3306/tmp/mysql.sock #skip-grant-tables #character set character_set_server = utf8 open_files_limit = 65535back_log = 500#event_scheduler = ON #lower_case_table_names=1skip-external-locking skip_name_resolve = 1default-storage-engine = InnoDB #timeout wait_timeout=1000interactive_timeout=1000connect_timeout = 20server-id =1283306 #ip最后一位+端口号= 1003376#percona 的--recursion-method slavehost模式 #report_host = 10.105.9.115#report_port = 3306#plugin plugin-load="semisync_master.so;semisync_slave.so"#########error log#############log-error = /data/mysql/mysql3306/logs/error.log log-warnings = 2 #########general log############# #general_log=1#general_log_file=/data/mysql/mysql3306/logs/mysql.log #########slow log############# slow_query_log = 1long_query_time=1slow_query_log_file = /data/mysql/mysql3306/logs/mysql.slow ############# for replication################### log-bin = /data/mysql/mysql3306/logs/mysql-bin binlog_format = row max_binlog_size = 500M binlog_cache_size = 2M max_binlog_cache_size = 2M expire-logs-days = 7slave-net-timeout=30log_bin_trust_function_creators = 1log-slave-updates = 1 skip-slave-start = 1#read_only =1 #从上设置,5.7是super_read_only #GTID gtid-mode = on binlog_gtid_simple_recovery=1enforce_gtid_consistency=1#relay log relay-log = /data/mysql/mysql3306/logs/mysql-relay relay-log-index=/data/mysql/mysql3306/logs/relay-bin.index max-relay-log-size = 500M #replication crash safe sync_master_info = 1sync_relay_log_info = 1sync_relay_log = 1relay_log_recovery = 1master_info_repository = TABLE relay_log_info_repository = TABLE #semisync 动态开启 主从切换的时候用 #rpl_semi_sync_master_enabled = 1#rpl_semi_sync_master_wait_no_slave = 1#rpl_semi_sync_master_timeout = 1000#rpl_semi_sync_slave_enabled = 1#rpl_semi_sync_master_timeout = 100000000 #不要超时,防止切异步,保证主从数据的完全一致性,默认是10000 #rpl_semi_sync_master_wait_point = 'after_sync' #5.7新增参数 after_sync 5.7增强半同步 after_commit5.6普通半同步 #rpl_semi_sync_master_wait_for_slave_count = 2 #5.7新增参数 等待多少个从库接收到binlog #ignore #replicate-ignore-db = 'school','school2'#replicate-do-db = 'school','school2'#Multi-threaded Slave #slave_parallel_workers=16#slave-parallel-type=LOGICAL_CLOCK #slave-parallel-type=DATABASE #replication error #slave-skip-errors=1007,1051,1062#######per_thread_buffers##################### max_connections=1100max_user_connections=1000max_connect_errors=1000#myisam_recover key_buffer_size = 64M max_allowed_packet = 16M #table_cache = 3096table_open_cache = 6144table_definition_cache = 4096read_buffer_size = 1M join_buffer_size = 128K read_rnd_buffer_size = 1M #myisam sort_buffer_size = 128K myisam_max_sort_file_size = 10G myisam_repair_threads = 1myisam_sort_buffer_size = 32M tmp_table_size = 32M max_heap_table_size = 64M query_cache_type=0query_cache_size = 0bulk_insert_buffer_size = 32M thread_cache_size = 64#thread_concurrency = 32thread_stack = 192K ###############InnoDB########################### innodb_data_home_dir = /data/mysql/mysql3306/data innodb_log_group_home_dir = /data/mysql/mysql3306/logs innodb_data_file_path = ibdata1:1000M:autoextend innodb_buffer_pool_size = 1G #根据内存大小设置 innodb_buffer_pool_instances = 8#innodb_additional_mem_pool_size = 16M innodb_log_file_size = 500M innodb_log_buffer_size = 16M innodb_log_files_in_group = 3innodb_flush_log_at_trx_commit = 1sync_binlog = 1innodb_lock_wait_timeout = 10innodb_sync_spin_loops = 40innodb_max_dirty_pages_pct = 80innodb_support_xa = 1innodb_thread_concurrency = 0innodb_thread_sleep_delay = 500innodb_concurrency_tickets = 1000innodb_flush_method = O_DIRECT innodb_file_per_table = 1innodb_read_io_threads = 16innodb_write_io_threads = 16innodb_io_capacity = 800 #机械盘800 ssd 2000innodb_flush_neighbors = 1innodb_file_format = Barracuda innodb_purge_threads=1 #5.6只能有一个，5.7可以设置多个 innodb_purge_batch_size = 32innodb_old_blocks_pct=75innodb_change_buffering=all innodb_stats_on_metadata=OFF innodb_print_all_deadlocks = 1#innodb_status_output=1#innodb_status_output_locks=1performance_schema=0 #是否打开P_S库进行性能收集 transaction_isolation = READ-COMMITTED #innodb_force_recovery=0#innodb_fast_shutdown=1#innodb_status_file = 1 #实时输出show engine innodb status到innodb_status文件 ############# PXC ##################### innodb_autoinc_lock_mode=2 wsrep_cluster_name=pxc-dongzheng wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.3.130,192.168.3.128,192.168.3.129 wsrep_node_address=192.168.3.128 wsrep_slave_threads=2 wsrep_sst_auth=sst:dongzheng wsrep_sst_method=xtrabackup-v2 #wsrep_provider_options="debug=1;gcache.size=2G;pc.ignore_quorum=true;pc.ignore_sb=true" #打开调试模式 wsrep_provider_options="debug=1;gcache.size=2G;" wsrep_max_ws_rows=131072wsrep_max_ws_size=16000 [mysqldump] quick max_allowed_packet = 128M [mysql] no-auto-rehash max_allowed_packet = 128M prompt = '(product)\u@\h:\p [\d]> 'default_character_set = utf8 [myisamchk] key_buffer_size = 64M sort_buffer_size = 512k read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] #malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.so
cd /usr/local/pxc/ ./scripts/mysql_install_db --defaults-file=/etc/my.cnf
看到两次ok
#修改启动脚本
vi /etc/init.d/mysql
basedir=/usr/local/pxc datadir=/data/pxc/mysql3306/data
4.启动，进行授权操作，对于第一个节点必须以特殊方式启动，如下：
/etc/init.d/mysql --help
Usage: mysql {start|stop|restart|restart-bootstrap|reload|force-reload|status|bootstrap-pxc} [ MySQL (Percona XtraDB Cluster) options ]
第一个节点启动
/etc/init.d/mysql bootstrap-pxc
5.安全加固
连接mysql：mysql -uroot -p -S /data/mysql/mysql3306/tmp/mysql.sock
delete from mysql.user where user!='root' or host!='localhost';
truncate table mysql.db;
drop database test;
进行授权，这里用户名密码是my.cnf里定义的用户名sst 密码dongzheng
GRANT ALL PRIVILEGES ON *.* TO 'sst'@'%' identified by 'dongzheng';
use mysql;
UPDATE user SET password=PASSWORD('123456') WHERE user='root';
flush privileges;
PXC状态确认
show global status like 'wsrep%';
6.netstat进行查看，可以发现启动两个端口
netstat -lntp | grep mysql
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2964/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2964/mysqld
另外两个节点的启动和配置
node2 my.cnf的PXC 配置节：
############# PXC #####################
innodb_autoinc_lock_mode=2 #自增锁的优化
wsrep_cluster_name=pxc-dongzheng #集群名字
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so #库文件
wsrep_cluster_address=gcomm://192.168.2.130,192.168.3.128,192.168.0.129 #节点中所有ip
wsrep_node_address=192.168.0.129 #节点的ip
wsrep_slave_threads=2 #开启的复制线程数，cpu核数*2
wsrep_sst_auth=sst:dongzheng #sst模式需要的用户名和密码
wsrep_sst_method=xtrabackup-v2 #采用什么方式复制数据。还支持mysqldump，rsync
wsrep_provider_options="debug=1;socket.checksum=1" #打开调试模式
node3 my.cnf的PXC 配置节：
############# PXC #####################
innodb_autoinc_lock_mode=2 #自增锁的优化
wsrep_cluster_name=pxc-dongzheng #集群名字
wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so #库文件
wsrep_cluster_address=gcomm://192.168.2.130,192.168.3.128,192.168.0.129 #节点中所有ip
wsrep_node_address=192.168.0.128 #节点的ip
wsrep_slave_threads=2 #开启的复制线程数，cpu核数*2
wsrep_sst_auth=sst:dongzheng #sst模式需要的用户名和密码
wsrep_sst_method=xtrabackup-v2 #采用什么方式复制数据。还支持mysqldump，rsync
wsrep_provider_options="debug=1;socket.checksum=1" #打开调试模式
启动
/etc/init.d/mysql start
启动的时候会把主节点的所有数据传送过从节点包括所有数据库和日志，简单来讲从节点在启动的时候会做SST同步
从节点不需要做安全加固，因为从节点跟主节点的数据库和数据都一样
PXC状态确认
show global status like 'wsrep%';
netstat进行查看，可以发现启动两个端口
netstat -lntp | grep mysql
tcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 2964/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2964/mysqld
###############################################################
集群关闭
所有节点都用
/etc/init.d/mysql stop
节点重启
第一个节点用：/etc/init.d/mysql restart-bootstrap
其他节点用：/etc/init.d/mysql restart
集群关闭之后，再启动，谁做第一个节点谁就执行/etc/init.d/mysql bootstrap-pxc
如果不是所有节点都关闭，那么当启动集群节点的时候不需要执行/etc/init.d/mysql bootstrap-pxc
/etc/init.d/mysql --help
Usage: mysql {start|stop|restart|restart-bootstrap|reload|force-reload|status|bootstrap-pxc} [ MySQL (Percona XtraDB Cluster) options ]
到这里搭建就基本结束了。
随时随地看视频
