Comparison of consistency between PXC cluster and master-slave architecture

In the previous article, we have completed the construction of the three-node PXC cluster and now build the Node3 node4 from the library and have the following tests:

Build Node3 from the library, code NODE4

Test 1:

Disconnect the NODE3 node first, then construct the data in Node1, and then connect the Node3 to view the synchronization situation. Found Node1, Node2, Node3, node4 all node data consistent.

Test 2:

Disconnect the NODE4 node first, then construct the data in Node1, and then connect the node4 to view the synchronization situation. Node1, Node2, node3 data are found to be consistent, node4 missing data during disconnection.

Conclusion:

The PXC architecture always synchronizes data between nodes, eventually keeping all data across the nodes consistent. Master-Slave structure, only maintain the master-slave structure, from the library will synchronize the main library data, after disconnecting the master-slave structure, from the library to ignore the primary library to disconnect data.

Operation Record

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)	+--------------------+ | Database           | +--------------------+ | a                  | | b                  | | information_schema | | MySQL              | | performance_schema | | sys                | +--------------------+ 6 rows in Set (0.00 sec)
		# Test 1here, complete. Discovers that data is automatically synchronized with other nodes as long as the node is connected to the PXC cluster
			# 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 # at this time node4 Although the boot, but the master and slave state has been disconnected, need to rebuild Master-slave relations,
		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)
			# Although manually completed master-slave reconstruction, but still lost some of the data, the solution is also simple, go to BINLOG to find the corresponding pos Point, from the pos Point to start rebuilding the master and slave, from the library can rely This part of the data.

Comparison of consistency between PXC cluster and master-slave architecture