对比PXC集群与主从架构在一致性上的区别
在上一篇文章中，已经完成了三节点PXC集群的搭建，现在搭建node3的从库node4，并且有如下测试：
搭建node3的从库，代号node4
测试1：
先断开node3节点，然后在node1构造数据，再连接上node3，查看同步情况。发现node1、node2、node3、node4所有节点数据一致。
测试2：
先断开node4节点，然后在node1构造数据，再连接上node4，查看同步情况。发现node1、node2、node3数据一致，node4缺失断开期间的数据。
结论：
pxc架构会始终在各节点间同步数据，最终使得各节点间的所有数据保持一致。主从结构，只有保持主从结构时，从库会同步主库的数据，断开主从结构后，从库忽略断开后主库的数据。
操作记录
|
node1 |
node2 |
node3 |
node4 |
|
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) |
|
mysql> create database a; |
|
|
|
|
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | a | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | a | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | a | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | a | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) |
|
|
|
# systemctl stop mysqld |
|
|
|
mysql> create database b; |
|
|
|
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | a | | b | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | a | | b | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) |
|
mysql> show databases; +--------------------+ | Database | +--------------------+ | a | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) |
|
|
|
# systemctl start mysqld
|
|
|
|
|
mysql> show databases; +--------------------+ | Database | +--------------------+ | a | | b | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) |
mysql> show databases; +--------------------+ | Database | +--------------------+ | a | | b | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) |
|
|
|
#在这里测试1，完成。发现只要节点连接到PXC集群中，就会自动与其他节点同步数据 |
|
|
|
|
|
# mysqladmin -uroot -p123456 shutdown |
|
mysql> create database c; |
|
|
|
|
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | a | | b | | c | | mysql | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec) |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | a | | b | | c | | mysql | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec) |
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | a | | b | | c | | mysql | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec) |
|
|
|
|
|
# nohup /mysqldb/base/bin/mysqld_safe --defaults-file=/mysqldb/config/my.cnf --user=mysql >/dev/null 2>&1 & |
|
|
|
|
mysql> show databases; +--------------------+ | Database | +--------------------+ | a | | b | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.01 sec) |
|
|
|
|
mysql> show slave status\G; Slave_IO_Running: No Slave_SQL_Running: No
#此时node4虽然启动，但主从状态已经断开，需要重建主从关系， |
|
|
|
show master status\G; ******* 1. row ******* File: host-192-168-164-124-bin.000016 Position: 344 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: d54ad7f4-3b89-ee17-4ffd-b062a2fc55ab:1-9 |
|
|
|
|
|
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.164.124', -> MASTER_USER='repl', -> MASTER_PASSWORD='123456', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='host-192-168-164-124-bin.000016', -> MASTER_LOG_POS=344, -> MASTER_CONNECT_RETRY=10; |
|
|
|
|
mysql> start slave; |
|
|
|
|
mysql> show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes |
|
|
|
|
mysql> show databases; +--------------------+ | Database | +--------------------+ | a | | b | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) |
|
|
|
|
#虽然手动完成主从重建，但依然丢失了部分数据，解决方法也简单，去BINLOG找到对应POS点，从POS点开始重建主从，从库就能rely这部分数据。 |
