1.MySQL Replication常用架构

MySQL Replication技术在实际应用中有多种实现架构，常见的有：

• 一主一从，即一个Master服务器和一个Slave服务器。这是最常见的架构。

• 一主多从，即一个Master服务器和两个或两个以上Slave服务器。经常用在写操作不频繁、查询量比较大的业务环境中。

• 主主互备，又称双主互备，即两个MySQL Server互相将对方作为自己的Master，自己又同时作为对方的Slave来进行复制。主要用于对MySQL写操作要求比较高的环境中，避免了MySQL单点故障。

• 双主多从，其实就是双主互备，然后再加上多个Slave服务器。主要用于对MySQL写操作要求比较高，同时查询量比较大的环境中。

2.必须遵守的规则

在进行Mysql Replication各种部署之前，有一些必须遵守的规则：

• 同一时刻只能有一个Master服务器进行写操作。
• 一个Master服务器可以有多个Slave服务器。
• 无论是Master服务器还是Slave服务器，都要确保各自的Server ID唯一，不然双主互备就会出问题。
• 一个Slave服务器可以将其从Master服务器获得的更新信息传递给其他的Slave服务器。依此类推。

3.MYSQL双主架构环境准备

3.1双主架构图

如下图，前端暂时放置一个应用服务器，后端数据库采用双主架构：

3.2数据库服务器基础信息规划

配置环境如下：

4.安装数据库

此过程略，可参考《Python运维实践》公众号发布的文章《【数据库专题1】：MYSQL数据库主流分支、存储引擎及二进制安装》。

5.MYSQL主主互备配置

5.1修改mysql配置文件

主要操作是：配置服务器编号，开启bin-log日志功能。

首先在MYDB01主机上修改my.cnf文件，在[mysqld]字段中原基础上增加以下内容：

[root@MYDB01 mysql]# vim /etc/my.cnf
[mysqld]
#
server-id = 1
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

然后，同样在MYDB02主机上修改my.cnf文件，在[mysqld]字段中原基础上增加以下内容：

[root@MYDB02 mysql]# vim /etc/my.cnf
[mysqld]
#
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
log-error=/usr/local/mysql/mysqld.log
pid-file=/usr/local/mysql/logs/mysqld.pid

server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

其中：

• server-id是节点标识，主、从节点不能相同，必须全局唯一。
• log-bin表示开启MySQL的binlog日志功能。“mysql-bin”表示日志文件的命名格式，会生成文件名为mysql-bin.000001、mysql-bin.000002等的日志文件。
• relay-log用来定义relay-log日志文件的命名格式。

replicate-wild-ignore-table是个复制过滤选项，可以过滤掉不需要复制的数据库或表，例如mysql.%表示不复制mysql库下的所有对象，其他依此类推。与此对应的是replicate_wild_do_table选项，用来指定需要复制的数据库或表。

这里需要注意的是，不要在主库上使用binlog-do-db或binlog-ignore-db选项，也不要在从库上使用replicate-do-db或replicate-ignore-db选项，因为这样可能产生跨库更新失败的问题。

推荐在从库上使用replicate_wild_do_table和replicate-wild-ignore-table两个选项来解决复制过滤问题。

5.2数据库数据同步

两种情况，一种是没有数据，一种是有数据的情况：

1）没有数据：

直接清空两台主机：

mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql>

2）有数据情况的同步：

如果MYDB01上已经有mysql数据，那么在执行主主互备之前，需要将MYDB01和MYDB02上两个mysql的数据保持同步。

MYDB01:

首先在MYDB01上备份mysql数据，执行如下SQL语句：

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)

mysql>

然后，再开一个终端，完成数据的备份工作：

进入data目录，有下列文件：

[root@MYDB01 data]# pwd
/usr/local/mysql/data
[root@MYDB01 data]# ll -sh
总用量 121M
4.0K -rw-r-----. 1 mysql mysql   56 9月  14 11:32 auto.cnf
4.0K -rw-------. 1 mysql mysql 1.7K 9月  14 11:32 ca-key.pem
4.0K -rw-r--r--. 1 mysql mysql 1.1K 9月  14 11:32 ca.pem
4.0K -rw-r--r--. 1 mysql mysql 1.1K 9月  14 11:32 client-cert.pem
4.0K -rw-------. 1 mysql mysql 1.7K 9月  14 11:32 client-key.pem
4.0K -rw-r-----. 1 mysql mysql  436 9月  14 11:32 ib_buffer_pool
 12M -rw-r-----. 1 mysql mysql  12M 9月  14 13:05 ibdata1
 48M -rw-r-----. 1 mysql mysql  48M 9月  14 13:05 ib_logfile0
 48M -rw-r-----. 1 mysql mysql  48M 9月  14 11:31 ib_logfile1
 12M -rw-r-----. 1 mysql mysql  12M 9月  14 11:38 ibtmp1
4.0K drwxr-x---. 2 mysql mysql 4.0K 9月  14 11:32 mysql
   0 drwxr-x---. 2 mysql mysql   58 9月  14 13:03 odoodb
 12K drwxr-x---. 2 mysql mysql 8.0K 9月  14 11:32 performance_schema
4.0K -rw-------. 1 mysql mysql 1.7K 9月  14 11:32 private_key.pem
4.0K -rw-r--r--. 1 mysql mysql  452 9月  14 11:32 public_key.pem
4.0K -rw-r--r--. 1 mysql mysql 1.1K 9月  14 11:32 server-cert.pem
4.0K -rw-------. 1 mysql mysql 1.7K 9月  14 11:32 server-key.pem
 12K drwxr-x---. 2 mysql mysql 8.0K 9月  14 11:32 sys
[root@MYDB01 data]# 

然后对data文件夹打包：

[root@MYDB01 mysql]# tar zcvf data.tar.gz data
[root@MYDB01 mysql]# 

然后将文件复制到MYDB02的/usr/local/mysql目录下：

[root@MYDB01 mysql]# scp data.tar.gz root@192.168.250.194:/usr/local/mysql/
The authenticity of host '192.168.250.194 (192.168.250.194)' can't be established.
ECDSA key fingerprint is SHA256:vThEoRhUOECeD5jhE+m8TZA2+6OoElIoNOQ3XqtopZw.
ECDSA key fingerprint is MD5:97:40:b2:35:6e:07:5a:61:1f:73:f1:b2:6e:54:5b:7d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.250.194' (ECDSA) to the list of known hosts.
root@192.168.250.194's password: 
data.tar.gz                                                                                                                                                                    100% 1424KB  65.9MB/s   00:00    
[root@MYDB01 mysql]# 

MYDB02:

上面操作完成后，进入MYDB02主机，停掉mysql服务：

[root@MYDB02 mysql]# systemctl stop mysqld
[root@MYDB02 mysql]# 

然后备份原data文件夹：

[root@MYDB02 mysql]# systemctl stop mysqld
[root@MYDB02 mysql]# mv data data.bak.ori
[root@MYDB02 mysql]# ll
总用量 1708
drwxr-xr-x.  2 mysql mysql    4096 9月  14 11:24 bin
drwxr-xr-x.  5 mysql mysql    4096 9月  14 11:39 data.bak.ori
-rw-r--r--.  1 root  root  1458366 9月  14 13:45 data.tar.gz
drwxr-xr-x.  2 mysql mysql      55 9月  14 11:24 docs
drwxr-xr-x.  3 mysql mysql    4096 9月  14 11:24 include
drwxr-xr-x.  5 mysql mysql     230 9月  14 11:24 lib
-rw-r--r--.  1 mysql mysql  259200 6月   7 2021 LICENSE
drwxr-xr-x.  2 mysql mysql      24 9月  14 11:39 logs
drwxr-xr-x.  4 mysql mysql      30 9月  14 11:24 man
-rw-r-----.  1 mysql mysql    1108 9月  14 11:32 mysqld.log
-rw-r--r--.  1 mysql mysql     566 6月   7 2021 README
drwxr-xr-x. 28 mysql mysql    4096 9月  14 11:24 share
drwxr-xr-x.  2 mysql mysql      90 9月  14 11:24 support-files
[root@MYDB02 mysql]#

解压从MYDB01主机传过来的文件data.tar.gz ：

[root@MYDB02 mysql]# tar -zxvf data.tar.gz 

解压完成后，删除data目录下的auto.cnf文件，否则，后面Slave_IO_Running: No这样显示。

以上步骤完成后，分别重启MYDB01和MYDB02数据库。

[root@MYDB01 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL..                                      [  确定  ]
Starting MySQL..                                           [  确定  ]
[root@MYDB01 mysql]# 

MYDB02上也重启：

[root@MYDB02 mysql]# /etc/init.d/mysqld start
Starting MySQL..                                           [  确定  ]
[root@MYDB02 mysql]# 

查看数据库odoodb，已经存在。

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

mysql> 

5.3 创建复制用户并授权

第一步：

先在MYDB01主机（192.168.250.193）上创建mysql复制用户：

mysql> grant replication slave  on *.* to 'repl_user'@'192.168.250.194' identified by 'repl_passwd';
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> 

接着上面，查看bin文件和位置

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      456 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

然后，在MYDB02主机（192.168.250.194）上操作，在MYDB02的mysql库中将MYDB01设为自己的主服务器：

mysql> change master to master_host='192.168.250.193',master_user='repl_user',master_password='repl_passwd',master_log_file='mysql-bin.000001',master_log_pos=456;
Query OK, 0 rows affected, 2 warnings (0.26 sec)

mysql> 

接着就可以在MYDB02主机上启动slave服务，执行如下SQL命令：

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

mysql> 

在MYDB02上查看slave的运行状态，如果看出Slave_IO_Running和Slave_SQL_Running都是Yes状态，表明MYDB02上复制服务运行正常，到这里位置，从MYDB01到MYDB02的mysql主从复制已经完成了。

mysql> show slave statusG;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4
Current database: *** NONE ***

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.250.193
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 456
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             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: mysql.%,test.%,information_schema.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 456
              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: cae5a58a-33dd-11ed-bff1-5254000cf752
             Master_Info_File: /usr/local/mysql/data/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)

ERROR: 
No query specified

mysql>

接下来开始配置从MYDB02到MYDB01的mysql主从复制，这个配置过程与上面完全一样。

第二步：

MYDB02:

接着在MYDB02主机（192.168.250.194）上的mysql库中创建复制用户：

mysql> grant replication slave  on *.* to 'repl_user'@'192.168.250.193' identified by 'repl_passwd';
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> 

接着查看bin-log名称和位置信息：

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

mysql>

MYDB01：

然后回到MYDB01主机，在MYDB01的mysql库中将MYDB02设为自己的主服务器：

mysql> change master to master_host='192.168.250.194',master_user='repl_user',master_password='repl_passwd',master_log_file='mysql-bin.000002',master_log_pos=456;
Query OK, 0 rows affected, 2 warnings (0.26 sec)

mysql>

还是接着在MYDB01主机上启动slave服务：

mysql> start slave;
Query OK, 0 rows affected (0.07 sec)

mysql>

接着在MYDB01上查看slave运行状态：

mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.250.194
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 456
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             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: mysql.%,test.%,information_schema.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 456
              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: 67dfb966-33f8-11ed-b116-525400bb2b75
             Master_Info_File: /usr/local/mysql/data/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)

ERROR: 
No query specified

mysql>

上面显示：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

的状态为Yes，表明MYDB01上复制服务运行正常。至此，mysql双主模式的主从复制已经配置完毕了。