主从架构_对比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 statusG;

Slave_IO_Running: No

Slave_SQL_Running: No

#此时node4虽然启动，但主从状态已经断开，需要重建主从关系，

show master statusG;

******* 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 statusG;

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这部分数据。