MySQL集群PXC的搭建 最近公司某客戶要求我們的資料庫搭建PXC集群以保證他們的系統高性能和搞穩定性 以後花費了一些時間去搭建和測試，也踩過一些坑，準備分享出來 系統：centos6.6PXC：5.6.26 建議關閉iptables， 4個埠 3306,4444,4567,4568pxc環境所 ...
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 http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes socat nc -y
2.安裝xtrabackup（PXC同步數據需要用到）
yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm 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.gz tar zxvf Percona-XtraDB-Cluster-5.6.26-rel74.0-25.12.1.Linux.x86_64.tar.gz useradd -M -s /sbin/nologin mysql mkdir -p /usr/local/pxc mkdir -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 = 3306 socket = /data/mysql/mysql3306/tmp/mysql.sock # The MySQL server [mysqld] #########Basic################## explicit_defaults_for_timestamp=true port = 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 = 65535 back_log = 500 #event_scheduler = ON #lower_case_table_names=1 skip-external-locking skip_name_resolve = 1 default-storage-engine = InnoDB #timeout wait_timeout=1000 interactive_timeout=1000 connect_timeout = 20 server-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 = 1 long_query_time=1 slow_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 = 7 slave-net-timeout=30 log_bin_trust_function_creators = 1 log-slave-updates = 1 skip-slave-start = 1 #read_only =1 #從上設置,5.7是super_read_only #GTID gtid-mode = on binlog_gtid_simple_recovery=1 enforce_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 = 1 sync_relay_log_info = 1 sync_relay_log = 1 relay_log_recovery = 1 master_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=1100 max_user_connections=1000 max_connect_errors=1000 #myisam_recover key_buffer_size = 64M max_allowed_packet = 16M #table_cache = 3096 table_open_cache = 6144 table_definition_cache = 4096 read_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 = 1 myisam_sort_buffer_size = 32M tmp_table_size = 32M max_heap_table_size = 64M query_cache_type=0 query_cache_size = 0 bulk_insert_buffer_size = 32M thread_cache_size = 64 #thread_concurrency = 32 thread_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 = 3 innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 innodb_lock_wait_timeout = 10 innodb_sync_spin_loops = 40 innodb_max_dirty_pages_pct = 80 innodb_support_xa = 1 innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 500 innodb_concurrency_tickets = 1000 innodb_flush_method = O_DIRECT innodb_file_per_table = 1 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity = 800 #機械盤800 ssd 2000 innodb_flush_neighbors = 1 innodb_file_format = Barracuda innodb_purge_threads=1 #5.6只能有一個，5.7可以設置多個 innodb_purge_batch_size = 32 innodb_old_blocks_pct=75 innodb_change_buffering=all innodb_stats_on_metadata=OFF innodb_print_all_deadlocks = 1 #innodb_status_output=1 #innodb_status_output_locks=1 performance_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.130 wsrep_slave_threads=2 wsrep_sst_auth=sst:dongzheng wsrep_sst_method=xtrabackup-v2 wsrep_provider_options="debug=1" [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 = 3306 socket = /data/mysql/mysql3306/tmp/mysql.sock # The MySQL server [mysqld] #########Basic################## explicit_defaults_for_timestamp=true port = 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 = 65535 back_log = 500 #event_scheduler = ON #lower_case_table_names=1 skip-external-locking skip_name_resolve = 1 default-storage-engine = InnoDB #timeout wait_timeout=1000 interactive_timeout=1000 connect_timeout = 20 server-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 = 1 long_query_time=1 slow_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 = 7 slave-net-timeout=30 log_bin_trust_function_creators = 1 log-slave-updates = 1 skip-slave-start = 1 #read_only =1 #從上設置,5.7是super_read_only #GTID gtid-mode = on binlog_gtid_simple_recovery=1 enforce_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 = 1 sync_relay_log_info = 1 sync_relay_log = 1 relay_log_recovery = 1 master_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=1100 max_user_connections=1000 max_connect_errors=1000 #myisam_recover key_buffer_size = 64M max_allowed_packet = 16M #table_cache = 3096 table_open_cache = 6144 table_definition_cache = 4096 read_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 = 1 myisam_sort_buffer_size = 32M tmp_table_size = 32M max_heap_table_size = 64M query_cache_type=0 query_cache_size = 0 bulk_insert_buffer_size = 32M thread_cache_size = 64 #thread_concurrency = 32 thread_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 = 3 innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 innodb_lock_wait_timeout = 10 innodb_sync_spin_loops = 40 innodb_max_dirty_pages_pct = 80 innodb_support_xa = 1 innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 500 innodb_concurrency_tickets = 1000 innodb_flush_method = O_DIRECT innodb_file_per_table = 1 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity = 800 #機械盤800 ssd 2000 innodb_flush_neighbors = 1 innodb_file_format = Barracuda innodb_purge_threads=1 #5.6只能有一個，5.7可以設置多個 innodb_purge_batch_size = 32 innodb_old_blocks_pct=75 innodb_change_buffering=all innodb_stats_on_metadata=OFF innodb_print_all_deadlocks = 1 #innodb_status_output=1 #innodb_status_output_locks=1 performance_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 [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.soView Code
128
[client] port = 3306 socket = /data/mysql/mysql3306/tmp/mysql.sock # The MySQL server [mysqld] #########Basic################## explicit_defaults_for_timestamp=true port = 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 = 65535 back_log = 500 #event_scheduler = ON #lower_case_table_names=1 skip-external-locking skip_name_resolve = 1 default-storage-engine = InnoDB #timeout wait_timeout=1000 interactive_timeout=1000 connect_timeout = 20 server-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 = 1 long_query_time=1 slow_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 = 7 slave-net-timeout=30 log_bin_trust_function_creators = 1 log-slave-updates = 1 skip-slave-start = 1 #read_only =1 #從上設置,5.7是super_read_only #GTID gtid-mode = on binlog_gtid_simple_recovery=1 enforce_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 = 1 sync_relay_log_info = 1 sync_relay_log = 1 relay_log_recovery = 1 master_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=1100 max_user_connections=1000 max_connect_errors=1000 #myisam_recover key_buffer_size = 64M max_allowed_packet = 16M #table_cache = 3096 table_open_cache = 6144 table_definition_cache = 4096 read_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 = 1 myisam_sort_buffer_size = 32M tmp_table_size = 32M max_heap_table_size = 64M query_cache_type=0 query_cache_size = 0 bulk_insert_buffer_size = 32M thread_cache_size = 64 #thread_concurrency = 32 thread_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 = 3 innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 innodb_lock_wait_timeout = 10 innodb_sync_spin_loops = 40 innodb_max_dirty_pages_pct = 80 innodb_support_xa = 1 innodb_thread_concurrency = 0 innodb_thread_sleep_delay = 500 innodb_concurrency_tickets = 1000 innodb_flush_method = O_DIRECT innodb_file_per_table = 1 innodb_read_io_threads = 16 innodb_write_io_threads = 16 innodb_io_capacity = 800 #機械盤800 ssd 2000 innodb_flush_neighbors = 1 innodb_file_format = Barracuda innodb_purge_threads=1 #5.6只能有一個，5.7可以設置多個 innodb_purge_batch_size = 32 innodb_old_blocks_pct=75 innodb_change_buffering=all innodb_stats_on_metadata=OFF innodb_print_all_deadlocks = 1 #innodb_status_output=1 #innodb_status_output_locks=1 performance_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;socket.checksum=1" [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.soView Code
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 ]
到這裡搭建就基本結束了。3個節點同時支持read/write操作。
在130上執行
show global status like 'wsrep%'\G; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 6 Current database: *** NONE *** *************************** 1. row *************************** Variable_name: wsrep_local_state_uuid Value: f4cb2250-30ac-11e7-8315-17918c780c9e *************************** 2. row *************************** Variable_name: wsrep_protocol_version Value: 7 *************************** 3. row *************************** Variable_name: wsrep_last_committed Value: 4 *************************** 4. row *************************** Variable_name: wsrep_replicated Value: 0 *************************** 5. row *************************** Variable_name: wsrep_replicated_bytes Value: 0 *************************** 6. row *************************** Variable_name: wsrep_repl_keys Value: 0 *************************** 7. row *************************** Variable_name: wsrep_repl_keys_bytes Value: 0 *************************** 8. row *************************** Variable_name: wsrep_repl_data_bytes Value: 0 *************************** 9. row *************************** Variable_name: wsrep_repl_other_bytes Value: 0 *************************** 10. row *************************** Variable_name: wsrep_received Value: 7 *************************** 11. row *************************** Variable_name: wsrep_received_bytes Value: 797 *************************** 12. row *************************** Variable_name: wsrep_local_commits Value: 0 *************************** 13. row *************************** Variable_name: wsrep_local_cert_failures Value: <
