MySQL主从复制配置

环境

操作系统：CentOS-7

MySQL：mysql-8.0.x

一台主镜像和从镜像

端口：15001　　master

端口：15002　　slave

端口：15003　　slave

使用Dokcer创建master和slave

1
2
3
4
5
6
[root@bogon tmp]# docker run -d -p 15001:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=1230 mysql:latest
253999e7938217a864d07c1ec974129872a91b8b4f1149132bbc71333390f4fd
[root@bogon tmp]# docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                NAMES
253999e79382        mysql:latest        "docker-entrypoint.s…"   37 seconds ago      Up 37 seconds       33060/tcp, 0.0.0.0:15001->3306/tcp   mysql-master
d9fc4f74791a        mysql:latest        "docker-entrypoint.s…"   41 minutes ago      Up 20 minutes       0.0.0.0:3306->3306/tcp, 33060/tcp    mysql-base

先别急着创建镜像，因为后面我们还需要加上一些配置

说明：

-d ： 后台启动

-p 15001:3306 ： 宿主机端口：容器端口(端口映射)

--name yours_name ： 指定容器名

-e MYSQL_ROOT_PASSWORD=1230 mysql:latest ：初始化参数

如果客户端连接时报错，需要修改密码：

使用docker exec -it 容器名 bash进入容器执行bash命令

1
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1230';

将root密码修改为1230，之后再重新连接，就可以了

没有cnf文件的配置

许多配置选项可以作为标志传递给mysqld。这将使您可以灵活地自定义容器而无需cnf文件。例如，如果要更改所有表的默认编码和排序规则以使用UTF-8（utf8mb4），只需运行以下命令：

1
docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

如果您想查看可用选项的完整列表，请运行：

docker run -it --rm mysql:tag --verbose --help

主数据库配置

编辑/etc/my.cnf文件

在[mysqld]下增加如下两行设置：

1
2
3
[mysqld]
log-bin=mysql-bin # 非必需
server-id=1　　　　# 必需

但是在这里我们使用没有cnf文件的配置，通过docker run -it --rm mysql:tag --verbose --help我们查询到：

1
2
3
4
5
6
7
8
Variables (--variable-name=value)
and boolean options {FALSE|TRUE}                             Value (after reading options)
------------------------------------------------------------ -------------
...
log-bin                                                      binlog
...
server-id                                                    1
...

到这里你可以开始创建master了，命令如下：

1
docker run -d -p 15001:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=1230 mysql:latest --server-id=1 --log-bin=master-bin --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

创建用于数据同步的账户

1
2
3
4
5
CREATE USER 'slave'@'172.17.0.%' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'172.17.0.%';

FLUSH PRIVILEGES;

查看master状态

1
2
3
4
5
6
7
8
9
10
11
mysql> show variables like 'log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /var/lib/mysql/master-bin       |
| log_bin_index                   | /var/lib/mysql/master-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
+---------------------------------+---------------------------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000004 |      913 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从数据库配置

编辑/etc/my.cnf文件

设置server-id

1
2
[mysqld]
server-id=2

创建从镜像的命令：

1
docker run -d -p 15002:3306 --name mysql-slave01 -e MYSQL_ROOT_PASSWORD=1230 mysql:latest --server-id=2 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

执行同步语句

同步语句：

1
change master to master_host='172.17.0.2', master_port=3306, master_user='slave', master_password='123456', master_log_file='master-bin.000004', master_log_pos=913,master_connect_retry=30;

参数说明：

参数 描述
master_host Master的地址，指的是容器的独立ip,可以通过总结提供的语句来查询
master_port Master的端口号，指的是容器的端口号
master_user 用于数据同步的用户名
master_password 用于同步的用户的密码
master_log_file 指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos 从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry 如果连接失败，重试的时间间隔，单位是秒，默认是60秒

查询容器的独立IP：

1
2
3
4
[root@bogon ~]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master
172.17.0.2
[root@bogon ~]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-slave01
172.17.0.3

查看slave状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.17.0.2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: master-bin.000004
          Read_Master_Log_Pos: 913
               Relay_Log_File: c755400ecfa5-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000004
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 913
              Relay_Log_Space: 155
              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: 2061
                Last_IO_Error: error connecting to master 'slave@172.17.0.2:3306' - retry-time: 30  r
etries: 2
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 190327 20:52:08
     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
1 row in set (0.00 sec)

ERROR: 
No query specified

其中：

Slave_IO_Running: No

Slave_SQL_Running: No

这是因为我们还没有启动slave：

1
mysql> start slave;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: master-bin.000004
          Read_Master_Log_Pos: 913
               Relay_Log_File: c755400ecfa5-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: master-bin.000004
             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: 913
              Relay_Log_Space: 538
              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: ebaef5cc-50cd-11e9-a01e-0242ac110002
             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
1 row in set (0.00 sec)

ERROR: 
No query specified

如果：Slave_SQL_Running: No

解决：

  1. 程序可能在slave上进行了写操作，也可能是slave机器重启后，事务的回滚造成的：

    1
    2
    mysql> stop slave;
    mysql> set global sql_slave_skip_counter=1;

  2. 重新执行同步语句，但注意这样需要停止master的写操作

另外一个从镜像也是这样设置，只需将端口号更改一下

验证是否同步成功

在主数据库上操作

1
2
mysql> create database test;
Query OK, 1 row affected (0.11 sec)

从库中查看

1
2
3
4
5
6
7
8
9
10
11
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

成功~~~

从库设置只读账户

1
2
3
4
5
6
7
8
mysql> create user 'readonly'@'%' identified with mysql_native_password by '110';
Query OK, 0 rows affected (0.05 sec)

mysql> grant select on test.* to 'readonly'@'%';
Query OK, 0 rows affected (0.01 sec)

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

总结

​ 在执行同步语句时，一定要注意master_host要指定容器的独立ip，独立IP可通过以下命令来查询：

1
docker inspect --format='{{.NetworkSettings.IPAddress}}' 容器名称/容器id