在上一篇文章中,已经完成了三节点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这部分数据。