MyCat入门篇-使用案例1：读写分离(part3)

最新推荐文章于 2021-05-12 22:09:40 发布
最新推荐文章于 2021-05-12 22:09:40 发布
阅读量152 收藏
点赞数
分类专栏： MyCat 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/javaanddonet/article/details/109274622
版权

前置文章请参考：MyCat入门篇-使用案例1：读写分离(part2)

文章目录

验证主从复制

注意：在验证主从复制的时候，所有的写入操作都需要在master节点上进行操作，从节点上是不允许有写的操作的，只能有读的操作。也就是我们只能在master1、master2、master3这三个主节点上执行写的操作。不能再slave1、slave2、slave3上面执行写操作。

这也是为什么在启动MySQL容器的时候，每一个slave从节点的配置文件中都增加了一个“readonly=true”参数的根本原因，但是这个参数只对非root用户有效，对root用户来说，登录到从节点之后，也是可以执行写数据的操作。

如果在从节点上面写入的数据，就很有可能导致主节点写入的数据无法同步到从节点上面。
因为从节点上面如果直接写入数据后，就把一些自增主键的值给占用了，导致在主节点写入的数据行的无法同步到从节点，从而导致主从复制断开。

M1->S1，M1->M2，M2->S2的验证
  1. 在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>
  1. 然后再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>
  1. 在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>
  1. 在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的验证我们不在贴出具体验证过程。下面只是描述一下具体的验证过程。

  1. 在M2上创建一个名称为”db_created_by_m2“数据库，然后去S2上看下这个数据库是否存在，同时再去M1、S1上看是否存在。
    按照我们的预期，答案应该都是yes。
  2. 然后再M2上的“db_created_by_m2”数据库下面创建一个表“tab_created_by_m2”，然后去S2上看下这个表是否存在，然后再去M1、S1上看是否存在。
    按照我们的预期，答案应该都是yes。
  3. 在M2上的新建的表“db_created_by_m2.tab_created_by_m2”中，插入几行数据，看下这几行数据是否可以在S2上面的看到，然后再去M1、S1上看下是否也存在。
    与此同事，观察M2、S2、M1、S1上面的几行测试数据的自增主键是否是遵从2、4、6、8、10偶数作为自增主键的规律。
    按照我们的预期，答案应该都是yes。
  4. 在M1上的“db_created_by_m2.tab_created_by_m2”表中插入几行数据，看下这几行数据是否可以在S1上面的看到，然后再去M2、S2上看下是否存在。
    与此同事，观察在M1上插入的数据的自增主键是否是遵从1、3、5、7、9奇数作为自增主键的规律。
    按照我们的预期，答案应该都是yes。
M3->S3的验证

M3->S3的验证我们不在贴出具体验证过程。下面只是描述一下具体的验证过程。

  1. 在M3上创建一个名称为：“db_created_by_m3”数据库，然后去S3上看下这个数据库是否存在。
  2. 在M3上的“db_created_by_m3”数据库下面面创建一个表“tab_created_by_m3”，然后看下这个表是否在S3上面可以看到。
  3. 在M3上面的“db_created_by_m3.tab_created_by_m3”上，插入几行数据，看下这几行数据是否可以在S3上面的看到，与此同事，观察在M3上插入的数据的自增主键是否是遵从1、3、5、7、9奇数作为自增主键的规律。

以上就是验证MySQL双主双从，主主互为主从集群的步骤。
上述验证通过后，代表整个MySQL集群主从复制是OK的。

微信搜索“coder-home”或扫一扫下面的二维码，
关注公众号，第一时间了解更多干货分享，还有各类视频教程资源。扫描它，带走我。
在这里插入图片描述

程序猿集锦
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mycat配置访问oracle_数据库中间键mycat 实现oracle数据库读写分离
weixin_42577243的博客
12-30 398
01/1416:47:08.956INFO[WrapperSimpleAppMain](MycatServer.java:195)-===============================================01/1416:47:08.957INFO[WrapperSimpleAppMain](MycatServer.java:196)-MyCati...
新版mysql修改初始密码语句_MySql增加用户、授权、修改密码等语句
weixin_28887297的博客
02-12 1234
MySql增加用户、授权、修改密码等语句数据库top_develop登录数据库1：新增用户[mysql@lcamdb ~]$ mysql -u root -pEnter password: *****Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>use mysqlmysq...
读写分离_Demo之Mycat读写分离
weixin_39930557的博客
11-28 100
前言数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能；对于MySQL来说，标准的读写分离是主从模式，一个写节点Master后面跟着多个读节点，其中包含两个步骤，其一是数据源的主从同步，其二是sql的读写分发；而Mycat不负责任何数据的同步，具体的数据同步还是依赖Mysql数据库自身的功能。Mysql主从复制准备两台主机，安装相同版本的Mysql数据库，下面准备配...
python + MySql 的基础使用
weixin_44500833的博客
06-28 1084
MySql 登陆 mysql -u 账户名 -p 我的MySql版本是8.0.19 \[a-zA-Z!?#.]式的命令列表 Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement. 输入help、\h、查看下面这个命令列表，输入\c清空当前输入的陈述。 ? (\?) Synonym for `help'. clear (\c) Clear the current input sta
Mysql 数据备份与恢复
syztoo
05-16 479
一、相关概念 1.备份目的 防止硬件故障、软件故障、自然灾害、黑客攻击、误操作、测试场景等导致的数据丢失； 2.注意要点 最多能容忍丢失多少数据； 恢复数据需要在多长时间内完成； 需要恢复哪些数据； 3.还原要点 做还原测试，用于测试备份的可用性； 4.备份类型 1）完全备份，部分备份 完全备份：备份整个数据集； 部分备份：只备份数据子集，如部分库或表； 2）完全备份、增...
Windows下安装MyCat：Mycat-server-1.6-RELEASE-20161028204710-win
12-10
- 下载：首先从官方网站或者可靠的源获取适合Windows环境的Mycat版本，例如“Mycat-server-1.6-RELEASE-20161028204710-win”。 - 解压：将下载的压缩包解压到本地目录，通常包含Mycat的服务器端程序。 - 配置：...
mycat2 install-template（mycat2-install-template-1.20.zip）
06-10
1. **bin** 目录：包含了MyCat的启动脚本（如`mycat-start.sh`、`mycat-stop.sh`）、日志清理脚本等。 2. **conf** 目录：存放MyCat的配置文件，如`schema.xml`（定义数据节点和表的分片策略）、`server.xml`...
mycat2 install-template（mycat2-install-template-1.21.zip）
06-10
【标题】"mycat2 install-template（mycat2-install-template-1.21.zip）" 指的是MyCat 2的一个安装模板文件，版本为1.21，通常包含了安装MyCat 2所需的所有配置文件和脚本，用于简化部署过程。 【描述】虽然描述...
mycat-example：mycat读写分离分库分表示例
02-14
**标题解析：** "mycat-example：mycat读写分离分库分表示例" 这个标题表明我们关注的是一个关于Mycat的实例，它主要用于实现数据库的读写分离和分库分表功能。Mycat是一款开源的分布式数据库中间件，常用于解决大...
Mycat-server-1.6.6.1 linux版本
07-20
《Mycat-server-1.6.6.1在Linux环境下的部署与应用》 Mycat是一款开源的分布式数据库中间件，它主要用于解决大数据环境下单机数据库的性能瓶颈问题，实现了数据的分布式存储和处理。Mycat-server-1.6.6.1是其在2018...
MySql 01（安装，删除，简介，常用命令，简单查询，条件查询，排序，单行处理函数、多行处理函数、分组查询）
windyjcy的博客
05-12 335
MySql 01 1、什么是数据库？什么是数据库管理系统？什么是SQL？关系是什么？ 序列化就是最原始的数据库 数据库： 英文单词DataBase，简称DB。按照一定格式存储数据的一些文件的组合。 顾名思义：存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。 数据库管理系统： DataBaseManagement，简称DBMS。 数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的数据进行增删改查。 常见的数据库管理系统： MySQL、Orac
MySQL root密码重置报错：mysqladmin: connect to server at 'localhost' failed的解决方案！
haoyuxuan的博客
04-28 3379
- ==================================================================== --  mysqladmin: connect to server at 'localhost' failed的解决方案！ -- ==============================================================
mysql基础
whell_scl的博客
07-12 1944
mysql基础 mysql安装 mysql安装方式有三种： 源代码：编译安装 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用 程序包管理器管理的程序包： rpm：有两种 1. OS Vendor：操作系统发行商提供的 2.项目官方提供的 //配置mysql的yum源 [root@100 src]# wget http://dev.mysql.com/get/mysql57-com...
menisa mysql_MySQL数据库基本操作三
weixin_39983383的博客
01-27 132
环境：msyql版本5.0，windows操作系统mysqladmin命令修改密码034039->123456C:\Users\ASUS>mysqladmin-uroot-p034039password123456C:\Users\ASUS>mysql-uroot-p123456WelcometotheMySQLmonitor.Commandsend...
MySQL5.6.12 Waiting for commit lock导致从库hang住的问题剖析
热门推荐
csdn's blog
07-12 1万+
nagios报警，线上一台从库检测不到slave状态，于是远程上去查看问题： 1，show slave status\G卡住： Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show slave status\G show slave status卡住了，动弹不...
proxysql实现MySQL读写分离
wanfjiayue的博客
08-15 192
1. mysql实现读写分离的方式 mysql 实现读写分离的方式有以下几种： 　　程序修改mysql操作，直接和数据库通信，简单快捷的读写分离和随机的方式实现的负载均衡，权限独立分配，需要开发人员协助。 　　amoeba，直接实现读写分离和负载均衡，不用修改代码，有很灵活的数据解决方案，自己分配账户，和后端数据库权限管理独立，权限处理不够灵活。 　　mysql-proxy，直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号，效率低 mycat中间件 　　proxysql中间件
MySQL执行命令警告_MYSQL命令行输入密码警告问题"mysql: [Warning]"解决
weixin_42138376的博客
01-21 3393
[[emailprotected] ~]# mysql -uroot -p1234567mysql: [Warning] Using a password on the command line interface can be insecure.1.2 解决方法1.2.1 方法一1、将密码写入到主配置文件中[[emailprotected] ~]# vim /etc/my.cnf[mysql...
mysql自定义加锁_mysql密码重置
weixin_36011546的博客
01-28 122
mysql的登录：[root@lianxi~]#/usr/local/mysql/bin/mysql-uroot//登录mysql，WelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis152Serverversion:5.1.40-logMySQL...
Hive 环境的安装部署
努力的凹凸曼的博客
05-14 1877
Hive在客户端上的安装部署 一、客户端准备：   到这我相信大家都已经搭过三节点集群了，如果是的话则可以跳过一，直接进入二。如果不是则按流程来一遍！ 1、克隆虚拟机，见我的博客：虚拟机克隆及网络配置。 2、 实现客户端和集群的连接（该步骤为多节点集群搭建，详情见我博客： 节点Hadoop集群搭建 有多节点集群的请跳到二） （1）配置时钟同步：保证客户端和集群的时间是同步的，具体操...
Mycat集群搭建指南：读写分离与高可用实践
"Mycat集群环境搭建手顺，包括读写分离、分库分表以及HA高可用的实现步骤。这份资料详细介绍了Mycat作为数据库中间件的作用、发展历程和与其他类似中间件的对比，同时提供了Mycat的官方网站链接。" 在数据库管理...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值