mysql5.7 pxc
pxc优点总结：
可以达到时时同步，无延迟现象发生
完全兼容MySQL
对于集群中新节点的加入，维护起来很简单
数据的强一致性
不足之处总结：
只支持Innodb存储引擎
存在多节点update更新问题，也就是写放大问题
在线DDL语句，锁表问题
sst针对新节点加入的传输代价过高的问题
关于写放大的原理图
下面从上次组复制转pxc
https://www.cnblogs.com/omsql/p/9277047.html
pxc架构图如下
卸载mysql
卸载前记得备份my.cnf
yum remove mysql-community-*
安装pxc
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm
yum install http://rpmfind.net/linux/dag/redhat/el6/en/x86_64/dag/RPMS/socat-1.7.2.4-1.el6.rf.x86_64.rpm
yum install Percona-XtraDB-Cluster-57
修改my.cnf
#pxc
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.20.201,192.168.20.202,192.168.20.203
wsrep_slave_threads= 8
innodb_autoinc_lock_mode=2
wsrep_node_address=192.168.20.201
wsrep_cluster_name=ht-pxc-cluster
wsrep_node_name=pxc01
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sst:ocm123
第一个节点启动
/etc/init.d/mysqld bootstrap-pxc
创建用户
SHOW VARIABLES LIKE 'validate_password%';
set global validate_password_policy=LOW;
set global validate_password_length=4;
create user 'sst'@'localhost' identified by 'ocm123';
grant all privileges on *.* to 'sst'@'localhost' identified by 'ocm123';
flush privileges;
第二个节点启动
/etc/init.d/mysql start
第三个节点启动
/etc/init.d/mysql start
添加第三个节点redis03的时候redis02日志输出如下
1 redis02 2 2018-07-09T15:01:19.004758Z 0 [Note] WSREP: Member 2.0 (pxc03) requested state transfer from '*any*'. Selected 0.0 (pxc02)(SYNCED) as donor. 3 2018-07-09T15:01:19.004800Z 0 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 7054) 4 2018-07-09T15:01:19.004867Z 5 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification. 5 2018-07-09T15:01:19.005039Z 0 [Note] WSREP: Initiating SST/IST transfer on DONOR side (wsrep_sst_xtrabackup-v2 --role 'donor' --address '192.168.20.203:4444/xtrabackup_sst//1' --socket '/data/mysql/mysql.sock' --datadir '/data/mysql/' --defaults-file '/etc/my.cnf' --defaults-group-suffix '' --binlog 'mysql-bin' --gtid '62e9006b-8379-11e8-bc64-874d49a77c07:7054') 6 2018-07-09T15:01:19.087469Z 5 [Note] WSREP: DONOR thread signaled with 0
redis02被选为donar，使用xtrabackup备份整个库传到redis03下，同时查看redis03 /data/mysql
[root@redis03 mysql]# ls
galera.cache grastate.dat gvwstate.dat sst_in_progress
恢复完后如下
1 [root@redis03 mysql]# ls 2 auto.cnf grastate.dat ib_logfile1 mysql-bin.000007 public_key.pem server-cert.pem xtrabackup_info 3 ca-key.pem gvwstate.dat ibtmp1 mysql-bin.index pxc server-key.pem xtrabackup_master_key_id 4 ca.pem ht innobackup.move.log mysql.sock redis03-relay-bin-group_replication_applier.000001 sys 5 client-cert.pem ib_buffer_pool innobackup.prepare.log mysql.sock.lock redis03-relay-bin-group_replication_applier.index xb_doublewrite 6 client-key.pem ibdata1 mysql performance_schema redis03-relay-bin-group_replication_recovery.000001 xtrabackup_binlog_pos_innodb 7 galera.cache ib_logfile0 mysql-bin.000006 private_key.pem redis03-relay-bin-group_replication_recovery.index xtrabackup_galera_info
上面输出可以看出sst针对新节点加入的传输代价过高。
查看集群状态
root@redis03> show status like 'wsrep%'; +----------------------------------+-------------------------------------------------------------+ | Variable_name | Value | +----------------------------------+-------------------------------------------------------------+ | wsrep_local_state_uuid | 62e9006b-8379-11e8-bc64-874d49a77c07 | | wsrep_protocol_version | 8 | | wsrep_last_applied | 7054 | | wsrep_last_committed | 7054 | | wsrep_replicated | 0 | | wsrep_replicated_bytes | 0 | | wsrep_repl_keys | 0 | | wsrep_repl_keys_bytes | 0 | | wsrep_repl_data_bytes | 0 | | wsrep_repl_other_bytes | 0 | | wsrep_received | 2 | | wsrep_received_bytes | 285 | | wsrep_local_commits | 0 | | wsrep_local_cert_failures | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_max | 1 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_recv_queue_avg | 0.000000 | | wsrep_local_cached_downto | 0 | | wsrep_flow_control_paused_ns | 0 | | wsrep_flow_control_paused | 0.000000 | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | wsrep_flow_control_interval | [ 173, 173 ] | | wsrep_flow_control_interval_low | 173 | | wsrep_flow_control_interval_high | 173 | | wsrep_flow_control_status | OFF | | wsrep_cert_deps_distance | 0.000000 | | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 0.000000 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 0.000000 | | wsrep_local_state | 4 | | wsrep_local_state_comment | Synced | | wsrep_cert_index_size | 0 | | wsrep_cert_bucket_count | 22 | | wsrep_gcache_pool_size | 1320 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_ist_receive_status | | | wsrep_ist_receive_seqno_start | 0 | | wsrep_ist_receive_seqno_current | 0 | | wsrep_ist_receive_seqno_end | 0 | | wsrep_incoming_addresses | 192.168.20.202:3306,192.168.20.201:3306,192.168.20.203:3306 | | wsrep_desync_count | 0 | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0/0/0/0/0 | | wsrep_evs_state | OPERATIONAL | | wsrep_gcomm_uuid | ee9c63ef-8388-11e8-af36-8bf4f2f07974 | | wsrep_cluster_conf_id | 5 | | wsrep_cluster_size | 3 | | wsrep_cluster_state_uuid | 62e9006b-8379-11e8-bc64-874d49a77c07 | | wsrep_cluster_status | Primary | | wsrep_connected | ON | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 2 | | wsrep_provider_name | Galera | | wsrep_provider_vendor | Codership Oy <info@codership.com> | | wsrep_provider_version | 3.26(rac090bc) | | wsrep_ready | ON | +----------------------------------+-------------------------------------------------------------+