MySQL主从复制配置

环境

操作系统:CentOS-7

MySQL:mysql-8.0.x

一台主镜像和从镜像

端口:15001  master

端口:15002  slave

端口:15003  slave

使用Dokcer创建masterslave

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