前置文章请参考:MyCat入门篇-使用案例1:读写分离(part2)
验证主从复制
注意:在验证主从复制的时候,所有的写入操作都需要在master节点上进行操作,从节点上是不允许有写的操作的,只能有读的操作。也就是我们只能在master1、master2、master3这三个主节点上执行写的操作。不能再slave1、slave2、slave3上面执行写操作。
这也是为什么在启动MySQL容器的时候,每一个slave从节点的配置文件中都增加了一个“readonly=true”参数的根本原因,但是这个参数只对非root用户有效,对root用户来说,登录到从节点之后,也是可以执行写数据的操作。
如果在从节点上面写入的数据,就很有可能导致主节点写入的数据无法同步到从节点上面。
因为从节点上面如果直接写入数据后,就把一些自增主键的值给占用了,导致在主节点写入的数据行的无法同步到从节点,从而导致主从复制断开。
M1->S1,M1->M2,M2->S2的验证
- 在M1上创建一个名称为”db_created_by_m1“数据库,然后去S1上看下这个数据库是否存在,同时再去M2、S2上看是否存在。
按照我们的预期,答案应该都是yes。下面贴出具体的验证过程和结果:
- 在M1上创建一个名称为”db_created_by_m1“的数据库:
➜ docker_mysql_conf mysql -uroot -proot -P3311 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> create database db_created_by_m1 default charset utf8mb4;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> select @@hostname;
+---------------+
| @@hostname |
+---------------+
| master1.mysql |
+---------------+
1 row in set (0.01 sec)
mysql>
- 验证在S1上是否存在在M1上刚创建的数据库”db_created_by_m1“:
Last login: Fri Oct 23 15:09:50 on ttys005
➜ ~ mysql -uroot -proot -P3312 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> select @@hostname;
+--------------+
| @@hostname |
+--------------+
| slave1.mysql |
+--------------+
1 row in set (0.01 sec)
mysql>
- 验证在M2上是否存在在M1上刚创建的数据库”db_created_by_m1“:
➜ ~ mysql -uroot -proot -P3321 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> select @@hostname;
+---------------+
| @@hostname |
+---------------+
| master2.mysql |
+---------------+
1 row in set (0.01 sec)
mysql>
- 验证在S2上是否存在在M1上刚创建的数据库”db_created_by_m1“:
➜ ~ mysql -uroot -proot -P3322 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> select @@hostname;
+--------------+
| @@hostname |
+--------------+
| slave2.mysql |
+--------------+
1 row in set (0.01 sec)
mysql>
- 然后再M1上的“db_created_by_m1”数据库下面创建一个表“tab_created_by_m1”,然后去S1上看下这个表是否存在,然后再去M2、Sc上看是否存在。
按照我们的预期,答案应该都是yes。下面贴出具体的验证过程和结果:
- 在M1的“db_created_by_m1”数据库下面,创建表“tab_created_by_m1”:
➜ docker_mysql_conf mysql -uroot -proot -P3311 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db_created_by_m1;
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> create table tab_created_by_m1 (id int primary key auto_increment, hostname varchar(32), create_timestamp timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> show create table tab_created_by_m1 \G
*************************** 1. row ***************************
Table: tab_created_by_m1
Create Table: CREATE TABLE `tab_created_by_m1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hostname` varchar(32) DEFAULT NULL,
`create_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> desc tab_created_by_m1;
+------------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hostname | varchar(32) | YES | | NULL | |
| create_timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------------+-------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.01 sec)
mysql> select database();
+------------------+
| database() |
+------------------+
| db_created_by_m1 |
+------------------+
1 row in set (0.00 sec)
mysql> select @@hostname;
+---------------+
| @@hostname |
+---------------+
| master1.mysql |
+---------------+
1 row in set (0.00 sec)
mysql>
- 在S1上的“db_created_by_m1”下面查看是否存在表“tab_created_by_m1”:
➜ ~ mysql -uroot -proot -P3312 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use db_created_by_m1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_db_created_by_m1 |
+----------------------------+
| tab_created_by_m1 |
+----------------------------+
1 row in set (0.01 sec)
mysql> desc tab_created_by_m1;
+------------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hostname | varchar(32) | YES | | NULL | |
| create_timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------------+-------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.01 sec)
mysql> select @@hostname;
+--------------+
| @@hostname |
+--------------+
| slave1.mysql |
+--------------+
1 row in set (0.01 sec)
mysql>
- 在M2上的“db_created_by_m1”下面查看是否存在表“tab_created_by_m1”:
➜ ~ mysql -uroot -proot -P3321 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db_created_by_m1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_db_created_by_m1 |
+----------------------------+
| tab_created_by_m1 |
+----------------------------+
1 row in set (0.01 sec)
mysql> desc tab_created_by_m1;
+------------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hostname | varchar(32) | YES | | NULL | |
| create_timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------------+-------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.01 sec)
mysql> select @@hostname;
+---------------+
| @@hostname |
+---------------+
| master2.mysql |
+---------------+
1 row in set (0.00 sec)
mysql>
- 在S2上的“db_created_by_m1”下面查看是否存在表“tab_created_by_m1”:
➜ ~ mysql -uroot -proot -P3322 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use db_created_by_m1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_db_created_by_m1 |
+----------------------------+
| tab_created_by_m1 |
+----------------------------+
1 row in set (0.01 sec)
mysql> desc tab_created_by_m1;
+------------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hostname | varchar(32) | YES | | NULL | |
| create_timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------------+-------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.01 sec)
mysql> select @@hostname;
+--------------+
| @@hostname |
+--------------+
| slave2.mysql |
+--------------+
1 row in set (0.01 sec)
mysql>
- 在M1上的新建的表“db_created_by_m1.tab_created_by_m1”中,插入几行数据,看下这几行数据是否可以在S1上面的看到,然后再去M2、S2上看下是否也存在。
与此同事,观察M1、S1、M2、S2上面的几行测试数据的自增主键是否是遵从1、3、5、7、9奇数作为自增主键的规律。
按照我们的预期,答案应该都是yes。下面贴出具体的验证过程和结果:
- 在M1的“db_created_by_m1.tab_created_by_m1”表中插入测试数据,同时观察主键自增的规律是否是奇数作为主键。
➜ docker_mysql_conf mysql -uroot -proot -P3311 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use db_created_by_m1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_db_created_by_m1 |
+----------------------------+
| tab_created_by_m1 |
+----------------------------+
1 row in set (0.00 sec)
mysql>
mysql> desc tab_created_by_m1;
+------------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hostname | varchar(32) | YES | | NULL | |
| create_timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------------+-------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.01 sec)
mysql> insert into tab_created_by_m1(hostname, create_timestamp) values(@@hostname, now());
Query OK, 1 row affected (0.02 sec)
mysql> insert into tab_created_by_m1(hostname, create_timestamp) values(@@hostname, now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into tab_created_by_m1(hostname, create_timestamp) values(@@hostname, now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into tab_created_by_m1(hostname, create_timestamp) values(@@hostname, now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab_created_by_m1;
+----+---------------+---------------------+
| id | hostname | create_timestamp |
+----+---------------+---------------------+
| 1 | master1.mysql | 2020-10-25 13:42:40 |
| 3 | master1.mysql | 2020-10-25 13:42:43 |
| 5 | master1.mysql | 2020-10-25 13:42:43 |
| 7 | master1.mysql | 2020-10-25 13:42:44 |
+----+---------------+---------------------+
4 rows in set (0.00 sec)
mysql> select @@hostname;
+---------------+
| @@hostname |
+---------------+
| master1.mysql |
+---------------+
1 row in set (0.01 sec)
mysql>
- 在S1的“db_created_by_m1.tab_created_by_m1”表中查看数据是否存在,同时观察主键自增的规律是否是奇数作为主键。
➜ ~ mysql -uroot -proot -P3312 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db_created_by_m1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_db_created_by_m1 |
+----------------------------+
| tab_created_by_m1 |
+----------------------------+
1 row in set (0.01 sec)
mysql> select * from tab_created_by_m1;
+----+---------------+---------------------+
| id | hostname | create_timestamp |
+----+---------------+---------------------+
| 1 | master1.mysql | 2020-10-25 13:42:40 |
| 3 | master1.mysql | 2020-10-25 13:42:43 |
| 5 | master1.mysql | 2020-10-25 13:42:43 |
| 7 | master1.mysql | 2020-10-25 13:42:44 |
+----+---------------+---------------------+
4 rows in set (0.01 sec)
mysql> select @@hostname;
+--------------+
| @@hostname |
+--------------+
| slave1.mysql |
+--------------+
1 row in set (0.01 sec)
mysql>
- 在M2的“db_created_by_m1.tab_created_by_m1”表中查看数据是否存在,同时观察主键自增的规律是否是奇数作为主键。
➜ ~ mysql -uroot -proot -P3321 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db_created_by_m1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_db_created_by_m1 |
+----------------------------+
| tab_created_by_m1 |
+----------------------------+
1 row in set (0.01 sec)
mysql> select * from tab_created_by_m1;
+----+---------------+---------------------+
| id | hostname | create_timestamp |
+----+---------------+---------------------+
| 1 | master1.mysql | 2020-10-25 13:42:40 |
| 3 | master1.mysql | 2020-10-25 13:42:43 |
| 5 | master1.mysql | 2020-10-25 13:42:43 |
| 7 | master1.mysql | 2020-10-25 13:42:44 |
+----+---------------+---------------------+
4 rows in set (0.01 sec)
mysql> select @@hostname;
+---------------+
| @@hostname |
+---------------+
| master2.mysql |
+---------------+
1 row in set (0.01 sec)
mysql>
- 在S2的“db_created_by_m1.tab_created_by_m1”表中查看数据是否存在,同时观察主键自增的规律是否是奇数作为主键。
➜ ~ mysql -uroot -proot -P3322 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db_created_by_m1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_db_created_by_m1 |
+----------------------------+
| tab_created_by_m1 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select * from tab_created_by_m1;
+----+---------------+---------------------+
| id | hostname | create_timestamp |
+----+---------------+---------------------+
| 1 | master1.mysql | 2020-10-25 13:42:40 |
| 3 | master1.mysql | 2020-10-25 13:42:43 |
| 5 | master1.mysql | 2020-10-25 13:42:43 |
| 7 | master1.mysql | 2020-10-25 13:42:44 |
+----+---------------+---------------------+
4 rows in set (0.01 sec)
mysql> select @@hostname;
+--------------+
| @@hostname |
+--------------+
| slave2.mysql |
+--------------+
1 row in set (0.00 sec)
mysql>
- 在M2上的“db_created_by_m1.tab_created_by_m1”表中插入几行数据,看下这几行数据是否可以在S2上面的看到,然后再去M1、S1上看下是否存在。
与此同事,观察在M2上插入的数据的自增主键是否是遵从2、4、6、8、10偶数作为自增主键的规律。
按照我们的预期,答案应该都是yes。下面贴出具体的验证过程和结果:
- 在M2上“db_created_by_m1.tab_created_by_m1”表中插入测试数据,观察从M2上插入的数据的自增主键是否是偶数自增:
➜ ~ mysql -uroot -proot -P3321 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db_created_by_m1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_db_created_by_m1 |
+----------------------------+
| tab_created_by_m1 |
+----------------------------+
1 row in set (0.00 sec)
mysql>
mysql> desc tab_created_by_m1;
+------------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| hostname | varchar(32) | YES | | NULL | |
| create_timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------------+-------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.01 sec)
mysql> insert into tab_created_by_m1(hostname, create_timestamp) values(@@hostname, now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into tab_created_by_m1(hostname, create_timestamp) values(@@hostname, now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into tab_created_by_m1(hostname, create_timestamp) values(@@hostname, now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from tab_created_by_m1;
+----+---------------+---------------------+
| id | hostname | create_timestamp |
+----+---------------+---------------------+
| 1 | master1.mysql | 2020-10-25 13:42:40 |
| 3 | master1.mysql | 2020-10-25 13:42:43 |
| 5 | master1.mysql | 2020-10-25 13:42:43 |
| 7 | master1.mysql | 2020-10-25 13:42:44 |
| 8 | master2.mysql | 2020-10-25 13:54:28 |
| 10 | master2.mysql | 2020-10-25 13:54:30 |
| 12 | master2.mysql | 2020-10-25 13:54:31 |
+----+---------------+---------------------+
7 rows in set (0.00 sec)
mysql> select @@hostname;
+---------------+
| @@hostname |
+---------------+
| master2.mysql |
+---------------+
1 row in set (0.01 sec)
mysql>
- 在S2的“db_created_by_m1.tab_created_by_m1”表中查看从M2上插入的数据是否存在:
➜ ~ mysql -uroot -proot -P3322 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> use db_created_by_m1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_db_created_by_m1 |
+----------------------------+
| tab_created_by_m1 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select * from tab_created_by_m1;
+----+---------------+---------------------+
| id | hostname | create_timestamp |
+----+---------------+---------------------+
| 1 | master1.mysql | 2020-10-25 13:42:40 |
| 3 | master1.mysql | 2020-10-25 13:42:43 |
| 5 | master1.mysql | 2020-10-25 13:42:43 |
| 7 | master1.mysql | 2020-10-25 13:42:44 |
| 8 | master2.mysql | 2020-10-25 13:54:28 |
| 10 | master2.mysql | 2020-10-25 13:54:30 |
| 12 | master2.mysql | 2020-10-25 13:54:31 |
+----+---------------+---------------------+
7 rows in set (0.00 sec)
mysql> select @@hostname;
+--------------+
| @@hostname |
+--------------+
| slave2.mysql |
+--------------+
1 row in set (0.00 sec)
mysql>
- 在M1的“db_created_by_m1.tab_created_by_m1”表中查看从M2上插入的数据是否存在:
➜ docker_mysql_conf mysql -uroot -proot -P3311 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db_created_by_m1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_db_created_by_m1 |
+----------------------------+
| tab_created_by_m1 |
+----------------------------+
1 row in set (0.01 sec)
mysql> select * from tab_created_by_m1;
+----+---------------+---------------------+
| id | hostname | create_timestamp |
+----+---------------+---------------------+
| 1 | master1.mysql | 2020-10-25 13:42:40 |
| 3 | master1.mysql | 2020-10-25 13:42:43 |
| 5 | master1.mysql | 2020-10-25 13:42:43 |
| 7 | master1.mysql | 2020-10-25 13:42:44 |
| 8 | master2.mysql | 2020-10-25 13:54:28 |
| 10 | master2.mysql | 2020-10-25 13:54:30 |
| 12 | master2.mysql | 2020-10-25 13:54:31 |
+----+---------------+---------------------+
7 rows in set (0.01 sec)
mysql> select @@hostname;
+---------------+
| @@hostname |
+---------------+
| master1.mysql |
+---------------+
1 row in set (0.01 sec)
mysql>
- 在S1的“db_created_by_m1.tab_created_by_m1”表中查看从M2上插入的数据是否存在:
➜ ~ mysql -uroot -proot -P3312 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_created_by_m1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use db_created_by_m1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_db_created_by_m1 |
+----------------------------+
| tab_created_by_m1 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select * from tab_created_by_m1;
+----+---------------+---------------------+
| id | hostname | create_timestamp |
+----+---------------+---------------------+
| 1 | master1.mysql | 2020-10-25 13:42:40 |
| 3 | master1.mysql | 2020-10-25 13:42:43 |
| 5 | master1.mysql | 2020-10-25 13:42:43 |
| 7 | master1.mysql | 2020-10-25 13:42:44 |
| 8 | master2.mysql | 2020-10-25 13:54:28 |
| 10 | master2.mysql | 2020-10-25 13:54:30 |
| 12 | master2.mysql | 2020-10-25 13:54:31 |
+----+---------------+---------------------+
7 rows in set (0.01 sec)
mysql> select @@hostname;
+--------------+
| @@hostname |
+--------------+
| slave1.mysql |
+--------------+
1 row in set (0.00 sec)
mysql>
M2->S2,M2->M1,M1->S1的验证
前面我们逐步验证了数据流向:M1->S1,M1->M2,M2->S2的同步情况。
对于数据流向:M2->S2,M2->M1,M1->S1的验证我们不在贴出具体验证过程。下面只是描述一下具体的验证过程。
- 在M2上创建一个名称为”db_created_by_m2“数据库,然后去S2上看下这个数据库是否存在,同时再去M1、S1上看是否存在。
按照我们的预期,答案应该都是yes。 - 然后再M2上的“db_created_by_m2”数据库下面创建一个表“tab_created_by_m2”,然后去S2上看下这个表是否存在,然后再去M1、S1上看是否存在。
按照我们的预期,答案应该都是yes。 - 在M2上的新建的表“db_created_by_m2.tab_created_by_m2”中,插入几行数据,看下这几行数据是否可以在S2上面的看到,然后再去M1、S1上看下是否也存在。
与此同事,观察M2、S2、M1、S1上面的几行测试数据的自增主键是否是遵从2、4、6、8、10偶数作为自增主键的规律。
按照我们的预期,答案应该都是yes。 - 在M1上的“db_created_by_m2.tab_created_by_m2”表中插入几行数据,看下这几行数据是否可以在S1上面的看到,然后再去M2、S2上看下是否存在。
与此同事,观察在M1上插入的数据的自增主键是否是遵从1、3、5、7、9奇数作为自增主键的规律。
按照我们的预期,答案应该都是yes。
M3->S3的验证
M3->S3的验证我们不在贴出具体验证过程。下面只是描述一下具体的验证过程。
- 在M3上创建一个名称为:“db_created_by_m3”数据库,然后去S3上看下这个数据库是否存在。
- 在M3上的“db_created_by_m3”数据库下面面创建一个表“tab_created_by_m3”,然后看下这个表是否在S3上面可以看到。
- 在M3上面的“db_created_by_m3.tab_created_by_m3”上,插入几行数据,看下这几行数据是否可以在S3上面的看到,与此同事,观察在M3上插入的数据的自增主键是否是遵从1、3、5、7、9奇数作为自增主键的规律。
以上就是验证MySQL双主双从,主主互为主从集群的步骤。
上述验证通过后,代表整个MySQL集群主从复制是OK的。
微信搜索“coder-home”或扫一扫下面的二维码,
关注公众号,第一时间了解更多干货分享,还有各类视频教程资源。扫描它,带走我。