Docker初识：mysql8主从复制（双主双从）

Linux系统：CentOS Linux release 7.4.1708 (Core) 
Docker版本： 17.03.0-ce, build 3a232c8

mysql ：8

一、准备以及搭建

1、创建主从服务器配置文件以及文件

在opt下创建mysqlCluster22文件夹，以及其相关的目录结构。

1.1 主服务器目录结构以及配置文件

[root@VM_0_6_centos mysqlCluster22]# ls
master1  master2  slave1  slave2
[root@VM_0_6_centos mysqlCluster22]# cd master1/
[root@VM_0_6_centos master1]# ls
cnf
[root@VM_0_6_centos master1]# cd cnf
[root@VM_0_6_centos cnf]# ls
my.cnf
[root@VM_0_6_centos cnf]# cd ../../master2
[root@VM_0_6_centos master2]# ls
cnf
[root@VM_0_6_centos master2]# cd cnf/
[root@VM_0_6_centos cnf]# ls
my.cnf
[root@VM_0_6_centos cnf]# cd ../../slave1/
[root@VM_0_6_centos slave1]# ls
cnf  data
[root@VM_0_6_centos slave1]# cd cnf
[root@VM_0_6_centos cnf]# ls
my.cnf
[root@VM_0_6_centos cnf]# cd ../../slave2/cnf/
[root@VM_0_6_centos cnf]# ls
my.cnf
[root@VM_0_6_centos cnf]# cd ../../
[root@VM_0_6_centos mysqlCluster22]# pwd
/opt/mysqlCluster22

1.2 主从服务器相关IP以及端口配置表

容器名	IP&端口（内网）	用户	密码
master3316	172.17.0.6:3316	root	master123
master3317	172.17.0.6:3317	root	master123
slave3318	172.17.0.6:3318	root	slave123
slave3319	172.17.0.6:3319	root	slave123

 

 

 

 

 
 

1.3 主从服务器配置文件

master3316

[mysqld]

log-bin = mysql-bin
server-id = 1

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

expire_logs_days=7
max_binlog_size=1G
binlog_format=ROW
log-slave-updates=1
auto-increment-offset=1
auto-increment-increment=2

binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys


[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

master3317

[mysqld]
log-bin = mysql-bin
server-id=3

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

expire_logs_days=7
max_binlog_size=1G
binlog_format=ROW
log-slave-updates=1
auto-increment-offset=2
auto-increment-increment=2

binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys


[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

slave3318

[mysqld]
log-bin = mysql-bin
server-id = 2

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

relay-log=mysql-relay

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

slave3319

[mysqld]
log-bin = mysql-bin
server-id = 4

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

relay-log=mysql-relay

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

关于配置文件属性的几点说明：

[mysqld]

#主服务器唯一ID。（与 Master1，Master3，...... MasterN 的不同点）
server-id=1
#启用二进制日志
log-bin=mysql-bin

#从库的中继日志，主库日志写到中继日志，中继日志再重做到从库
relay-log=myslql-relay-bin

# binlog保留时间7天
expire_logs_days=7

# binlog 文件的大小
max_binlog_size=1G

#设置logbin格式。取值： STATEMENT （默认），ROW，MIXED
binlog_format=ROW

# 双主模式中，log-slave-updates必须配置。因为作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates=1

# 该服务器自增列的初始值。取值范围是1 .. 65535
auto-increment-offset=2

# 该服务器自增列增量。（与 Master1，Master 3， ...... MasterN 的不同点）
auto-increment-increment=2

# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys

#设置需要复制的数据库（可选）
# 如果要配置了此项，则 mysql 只复制下面指定的数据库。
# 如果不配置此项，则 mysql 默认复制所有的数据库（不包含 binlog-ignore-db 的数据库）
binlog-do-db=需要复制的主数据库1
binlog-do-db=需要复制的主数据库2

1.4 运行mysql容器

docker run -itd -p 3316:3306 --name master3316 -v /opt/mysqlCluster22/master1/cnf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=master123 mysql

docker run -itd -p 3317:3306 --name master3317 -v /opt/mysqlCluster22/master2/cnf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=master123 mysql

docker run -itd -p 3318:3306 --name slave3318 -v /opt/mysqlCluster22/slave1/cnf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=slave123 mysql

docker run -itd -p 3319:3306 --name slave3319 -v /opt/mysqlCluster22/slave2/cnf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=slave123 mysql

运行结果

[root@VM_0_6_centos mysqlCluster22]# docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                      PORTS                               NAMES
4bb427ad7301        mysql                       "docker-entrypoint.s…"   23 hours ago        Up 23 hours                 33060/tcp, 0.0.0.0:3319->3306/tcp   slave3319
70c0be987cda        mysql                       "docker-entrypoint.s…"   23 hours ago        Up 4 hours                  33060/tcp, 0.0.0.0:3318->3306/tcp   slave3318
bac7e409424a        mysql                       "docker-entrypoint.s…"   23 hours ago        Up 23 hours                 33060/tcp, 0.0.0.0:3317->3306/tcp   master3317
2a266a63da4a        mysql                       "docker-entrypoint.s…"   23 hours ago        Up 4 hours                  33060/tcp, 0.0.0.0:3316->3306/tcp   master3316

2、参考《Docker初识：mysql8主从复制（单向）》分别进行单向主从配置。

参考地址：https://blog.csdn.net/wngpenghao/article/details/103710262

主从服务器关系

主服务器	从服务器
master3316	slave3318
master3317	slave3319

 

 

 

 

配置好后，查看主从服务器的状态。

master3316

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000005 |     1010 |              | information_schema,mysql,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

master3317

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000003 |     2173 |              | information_schema,mysql,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+

注意：分别记录下 File 和 Position 的值 ，执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。

slave3318

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.6
                  Master_User: replicate1
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1010
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 1178
        Relay_Master_Log_File: mysql-bin.000005
             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: 1010
              Relay_Log_Space: 1383
              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: 6ec9ec10-0eb5-11eb-a852-0242ac120002
             Master_Info_File: mysql.slave_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:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

slave3319

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.6
                  Master_User: replicate1
                  Master_Port: 3317
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2173
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 1149
        Relay_Master_Log_File: mysql-bin.000003
             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: 2173
              Relay_Log_Space: 1354
              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: 3
                  Master_UUID: bd138994-0eb5-11eb-84aa-0242ac120003
             Master_Info_File: mysql.slave_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:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

3、两个master配置可以进行相互复制。

因为是双主双从，所以要配置 Master3317 复制 Master3316， Master3316复制 Master3317 。

首先，在Master3317上执行的命令，以Master3317作为从机，复制主机Master3316上的数据。

mysql> change master to master_host='172.17.0.6',master_port=3316,master_user='replicate1',master_password='replicate123',master_log_file='mysql-bin.000005',master_log_pos=1010;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.6
                  Master_User: replicate1
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 156
               Relay_Log_File: bac7e409424a-relay-bin.000003
                Relay_Log_Pos: 371
        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:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 156
              Relay_Log_Space: 587
              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: 6ec9ec10-0eb5-11eb-a852-0242ac120002
             Master_Info_File: mysql.slave_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:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

其次，在Master3316上执行的命令，以Master3316作为从机，复制主机Master3317上的数据。

mysql> change master to master_host='172.17.0.6',master_port=3317,master_user='replicate1',master_password='replicate123',master_log_file='mysql-bin.000003',master_log_pos=2173;
Query OK, 0 rows affected, 2 warnings (0.09 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.6
                  Master_User: replicate1
                  Master_Port: 3317
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2173
               Relay_Log_File: 2a266a63da4a-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000003
             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: 2173
              Relay_Log_Space: 540
              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: 3
                  Master_UUID: bd138994-0eb5-11eb-84aa-0242ac120003
             Master_Info_File: mysql.slave_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:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

当 Slave_IO_Running 、Slave_SQL_Running 两个参数都是Yes，则说明主从配置成功！

注意：当master存在有变化后，可以使用reset slave重置slave，参考问题第一个。

4、双主双从测试

在master3316或maser3317上创建数据库tr1，并且建表tr1，结果如下：

二、问题

1、从库报这个错误：：Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 172.17.0.6
                  Master_User: replicate1
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1010
               Relay_Log_File: bac7e409424a-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: No
            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: 1010
              Relay_Log_Space: 156
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 13114
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 6ec9ec10-0eb5-11eb-a852-0242ac120002
             Master_Info_File: mysql.slave_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: 201016 08:09:31
     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:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

解决方案：

在从库上执行。

stop slave;

reset slave;

start slave;

双主双从基本配置完成。