MySQL 双主复制搭建和双主热备搭建

摘要:MySQL 双主复制搭建和双主热备搭建,目前配置双主复制都是双主热备模式,指的是两台机器都在运行,但并不是两台机器都同时在提供服务。当提供服务的一台出现故障的时候,另外一台主库会马上自动接管并且提供服务,而且切换的时间非常短。

相关文章

MySQL 复制(主从复制)相关概述和复制原理剖析

MySQL 主从复制搭建和为搭建好的主从复制增加备库

MySQL 双主复制搭建和双主热备搭建(本文)

MySQL 主从复制延迟和主从复制不一致


双主复制概述

目前的双主复制都是双主热备模式,指的是两台机器都在运行,但并不是两台机器都同时在提供服务。当提供服务的一台出现故障的时候,另外一台主库会马上自动接管并且提供服务,而且切换的时间非常短。

其实如果因为多个节点多有写入而配置双主,那么还是建议用 MySQL Group Replication 的多主模式。这才是长远的方案。或者说如果想利用多主库来分担写操作的压力,其实做好分库分表也是一个明智的选择。


双主复制缺点

如果两个库都提供写服务,虽然可以利用 auto_increment_increment 和 auto_increment_offset 两个参数解决双写主键不冲突问题。但是这个做法是有前提条件的,比如:主键必须是自增列,必须只有 insert 操作,如果一个事务中有 insert 和 update 操作肯定不行。还有就是两个库都更新一个 id 会造成冲突。

如果一个库担任写服务,另一个库作为一个备选主库,等真主库宕机后,该主库成为新的主库,那么此时这个备主库就是一种资源的浪费。也可以说这种资源的浪费纯粹是为备库节省了一次 change master to 操作。


工作原理

双主复制模式下两个数据库之间互为主备关系,比如主机 A 和主机 B,B 既是 A 的备库,也是 A 的主库,那么 A 既是 B 的备库,也是 B 的备库。双主复制原理其实和主从复制原理相似,主从复制工作结构中,主库只担任写 binlog 日志操作,并不担任复制操作。备库从主库拉取 binlog 日志然后写入到备库 relaylog 日志中,再由备库 sql 线程将数据同步到备库中。

既然两个库互为主库和备库,所以两个库既要担任写 binlog 日志操作,也要担任拉取对方的 binlog 日志写入到自己的 relaylog 中,然后再由 sql 线程同步数据。

双主复制配置中有两个重要的参数 auto_increment_increment 和 auto_increment_offset,这两个参数是为了控制自增列 AUTO_INCREMENT 的行为,用于 MASTER-MASTER 之间的复制,防止出现重复值。因为在双主复制模式中,两个数据库都可以进行写操作。

为了更好了解双主复制模式工作原理,可以先了解下一主多从复制如何搭建:MySQL 主从复制搭建步骤和主库增加备库操作。 下面进行双主复制的搭建。


Master 主库 A 配置

1、my.cnf 配置

配置 my.cnf 配置并保存退出。

[root@localhost ~]# vim /etc/my.cnf
...
[mysqld]
log_bin=mysql-bin
server_id=1 
binlog_format=mixed
auto_increment_increment=2
auto_increment_offset=1
log_slave_updates=1
...

[root@localhost ~]# service mysql restart

- log_bin = mysql-bin 开启 binlog 日志

- server_id = 1 设置主库 server id

- binlog_format = mixed 此处的复制模式设置成混合模式即可

- auto_increment_increment = 2 自增主键步长,通常有几个主库就写几,避免主键冲突

- auto_increment_offset = 1 设置自增主键起始值,第一个主库为1,第二个主库为2,以此类推,取值范围都是 1 - 65535

- log_slave_updates = 1 表示备库执行 relay log 后生成 binlog


2、创建复制账号

该复制账号是为了让 master_2 服务器使用。

mysql> grant replication slave, replication client on *.* to 'master_1'@'192.168.136.130' identified by 'Slave123_';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


Master 主库 B 配置

1、my.cnf 配置

配置 my.cnf 配置并保存退出。

[root@localhost ~]# vim /etc/my.cnf
...
[mysqld]
log_bin=mysql-bin
server_id=2 
binlog_format=mixed
auto_increment_increment=2
auto_increment_offset=2
log_slave_updates=1
...

[root@localhost ~]# service mysql restart

- log_bin = mysql-bin 开启 binlog 日志

- server_id = 2 设置主库 server id

- binlog_format = mixed 此处的复制模式设置成混合模式即可

- auto_increment_increment = 2 自增主键步长,通常有几个主库就写几,避免主键冲突

- auto_increment_offset = 2 设置自增主键起始值,第一个主库为1,第二个主库为2,以此类推

- log_slave_updates = 1 表示备库执行 relay log 后生成 binlog


2、创建复制账号

该复制账号是为了让 master_1 服务器使用。

mysql> grant replication slave, replication client on *.* to 'master_2'@'192.168.136.129' identified by 'Slave123_';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)


开启主备复制

刚才的几步我们已经把两台数据库配置完成,接下来开启复制模式进行测试。

A 库启动复制

查看 B 库的 binlog 日志文件和 master_log_pos 日志偏移量。

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000019
         Position: 637
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

此处 master_log_file 和 master_log_pos 就是上面 B 库 `show master status;` 结果。

mysql> change master to
    -> master_host='192.168.136.130',
    -> master_user='master_1',
    -> master_password='Slave123_',
    -> master_log_file='mysql-bin.000019',
    -> master_log_pos=637;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.136.130
                  Master_User: master_1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 637
               Relay_Log_File: bogon-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000019
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 637
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
                  Master_UUID: 5cc5d560-9213-11eb-a6ea-000c29151e5e
             Master_Info_File: /usr/local/mysql/var/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

此时可以看到复制状态 Slave_IO_Running 和 Slave_SQL_Running 线程都是 YES,证明复制启动成功。


B 库启动复制

查看 A 库的 binlog 日志文件和 master_log_pos 日志偏移量。

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000015
         Position: 888
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

此处 master_log_file 和 master_log_pos 就是上面 A 库 `show master status;` 结果。

mysql> change master to
    -> master_host='192.168.136.129',
    -> master_user='master_2',
    -> master_password='Slave123_',
    -> master_log_file='mysql-bin.000015',
    -> master_log_pos=888;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.136.129
                  Master_User: master_2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 888
               Relay_Log_File: bogon-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 888
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 3702630d-8c8f-11eb-9491-000c2974b699
             Master_Info_File: /usr/local/mysql/var/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

此时可以看到复制状态 Slave_IO_Running 和 Slave_SQL_Running 线程都是 YES,证明复制启动成功。

因为是互为主备模式,所以要为两台数据库服务器都各自开启复制,A 库使用刚才 B 库创建的账号,B 库使用刚才 A 库创建的账号。


双主复制测试

主库

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

备库

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

现在主库和备库数据库都是一样的,此时我们在主库上新建一个数据库,看看备库会不会同步?


主库

mysql> create database demo_mysql;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo_mysql         |
| mysql              |
| performance_schema |
| sys                |
| test_mysql         |
+--------------------+
6 rows in set (0.00 sec)

备库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo_mysql         |
| mysql              |
| performance_schema |
| sys                |
| test_mysql         |
+--------------------+
6 rows in set (0.00 sec)

此时在 A 库新建的 demo_mysql 在 B 库已经显示出来。那么此时我们在 B 库上再进行操作也会同步到 A 库,这里就不做演示了,到此双主模式搭建完成。

上面对于双主模式的弊端我们已经提到了,具体要不要使用双主模式,或者如何使用双主模式这个要看具体业务。双主模式搭建完成后我们可以让 A 库或者 B 库任意一台数据库担任主从复制操作,也就是说和一主多从模式没有上面区别。另外一个备库担任备选主库操作,等真主库发生问题后,备选主库可以自动接替真主库的工作。

我们可以称这种配置叫“mysql 双主高可用”,需要利用 keepalived 中间件进行搭建,具体过程如下。

结束语:感谢您对本网站文章的浏览,欢迎您的分享和转载,但转载请说明文章出处。
Top