MySQL搭建双主双从

weixin_47354082

已于 2022-03-27 14:29:06 修改

阅读量1.5k

点赞数 3

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-03-27 14:22:06 首次发布

本文链接：https://blog.csdn.net/weixin_47354082/article/details/123769840

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

原理

将主结点中binlog日志内容，实时传送到相邻结点relay log中，再在各主从数据库进行SQL重放，实现数据一致，主主之间互为主从，两主结点表中数据会同时有写操作，确保不产生唯一性约束冲突；

主要问题

解决唯一性约束冲突，数据表定义自增长字段，一主结点奇数增长，一主结点偶数增长，也可应用程序算法实现，或引入第三方软件实现，只要能解决两主结点写操作不产生唯一性约束冲突；

规划

架构

环境

主机	IP	类型	server id
mysql8_1	172.17.0.2	主1	1
mysql8_2	172.17.0.3	主2	2
mysql8_3	172.17.0.4	从1	3
mysql8_4	172.17.0.5	从2	4

注意：如下操作不说明操作主机，默认4台都执行；

安装MySQL

rpm -ivh https://repo.mysql.com//mysql80-community-release-el7-5.noarch.rpm
yum install -y mysql-community-server-8.0.28

修改MySQL配置

修改mysql.cnf，在[mysqld]下加入如下内容

主1：

server-id = 1
auto-increment-increment = 2    
auto-increment-offset = 1

从1：

server-id = 3

主2：

server-id = 2
auto-increment-increment = 2    
auto-increment-offset = 2

从1：

server-id = 4

启动数据库

systemctl start mysql

创建同步账号并授权

创建主主同步账号repl_user和主从同步账号slave_sync_user。

主1、主2：

mysql> CREATE USER 'repl_user'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE USER 'slave_sync_user'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_sync_user'@'%';
Query OK, 0 rows affected (0.00 sec)

配置主主同步

配置主1->主2

主1：

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |     1215 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

主2：

mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.2', MASTER_USER='repl_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1215;
Query OK, 0 rows affected, 8 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Checking source version
                  Master_Host: 172.17.0.2
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1215
               Relay_Log_File: mysql8_2-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:

配置主2->主1

主2：

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |     1195 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

主1：

mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.3', MASTER_USER='repl_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1195;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Checking source version
                  Master_Host: 172.17.0.3
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1195
               Relay_Log_File: mysql8_1-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB:

配置主从同步

配置主1->从1

主1：

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |     1215 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

从1：

mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.2', MASTER_USER='slave_sync_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1215;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Checking source version
                  Master_Host: 172.17.0.2
                  Master_User: slave_sync_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1215
               Relay_Log_File: mysql8_3-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB:

配置主2->从2

主2：

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |     1195 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从2：

mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.3', MASTER_USER='slave_sync_user', MASTER_PASSWORD='123456', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1195;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Queueing source event to the relay log
                  Master_Host: 172.17.0.3
                  Master_User: slave_sync_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1195
               Relay_Log_File: mysql8_4-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB:

验证双主双从同步效果

任意主结点操作数据，所有结点到得相同数据；

验证前检查，发现四个结点均只有4个自带数据库。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.07 sec)

验证主1写入

主1：

mysql> create database libs;
Query OK, 1 row affected (0.02 sec)

mysql> use libs;
Database changed
mysql> create table books(id int primary key auto_increment, name varchar(50));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into books(name) values("MySQL 8");
Query OK, 1 row affected (0.02 sec)

mysql> insert into books(name) values("Java");
Query OK, 1 row affected (0.00 sec)

mysql> insert into books(name) values("Shell");
Query OK, 1 row affected (0.00 sec)

mysql> select * from books;
+----+---------+
| id | name    |
+----+---------+
|  1 | MySQL 8 |
|  3 | Java    |
|  5 | Shell   |
+----+---------+
3 rows in set (0.00 sec)

主2、从1、从2

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| libs               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use libs
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_libs |
+----------------+
| books          |
+----------------+
1 row in set (0.00 sec)

mysql> select * from books;
+----+---------+
| id | name    |
+----+---------+
|  1 | MySQL 8 |
|  3 | Java    |
|  5 | Shell   |
+----+---------+
3 rows in set (0.00 sec)

验证主2写入

主2：

mysql> insert into books(name) values("Linux"),("UNIX"),("Windows"),("iOS");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from books;
+----+---------+
| id | name    |
+----+---------+
|  1 | MySQL 8 |
|  3 | Java    |
|  5 | Shell   |
| 10 | Linux   |
| 12 | UNIX    |
| 14 | Windows |
| 16 | iOS     |
+----+---------+
7 rows in set (0.00 sec)

mysql> delete from books where id=14;
Query OK, 1 row affected (0.01 sec)

mysql> select * from books;
+----+---------+
| id | name    |
+----+---------+
|  1 | MySQL 8 |
|  3 | Java    |
|  5 | Shell   |
| 10 | Linux   |
| 12 | UNIX    |
| 16 | iOS     |
+----+---------+
6 rows in set (0.00 sec)

主1、从1、从2：

mysql> select * from books;
+----+---------+
| id | name    |
+----+---------+
|  1 | MySQL 8 |
|  3 | Java    |
|  5 | Shell   |
| 10 | Linux   |
| 12 | UNIX    |
| 16 | iOS     |
+----+---------+
6 rows in set (0.01 sec)

weixin_47354082

关注

3
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
MySQL搭建双主双从

原理将主结点中binlog日志内容，实时传送到相邻结点relay log中，再在各主从数据库进行SQL重放，实现数据一致，主主之间互为主从，两主结点表中数据会同时有写操作，确保不产生唯一性约束冲突；主要问题解决唯一性约束冲突，数据表定义自增长字段，一主结点奇数增长，一主结点偶数增长，也可应用程序算法实现，或引入第三方软件实现，只要能解决两主结点写操作不产生唯一性约束冲突；规划架构环境主机 IP 类型 server id mysql8_1
复制链接

扫一扫