MySQL主从复制
获取MySQL镜像
搜索mysql，前两个是mysql官方制作的镜像
1
2
3
4
5
[root@msr ~]# docker search mysql
NAME DESCRIPTION STARS OFFICIAL AUTOMATED
mysql MySQL is a widely used, open-source relation… 9686 [OK]
mariadb MariaDB is a community-developed fork of MyS… 3523 [OK]
mysql/mysql-server Optimized MySQL Server Docker images. Create… 706 [OK]
Copy 拉取mysql镜像到本地，因为mysql镜像有很多版本，对应mysql的版本：mysql:tag
1
[root@msr ~]# docker pull mysql:5.7
Copy 运行MySQL镜像生成容器
运行mysql镜像，生成容器。用户名root，密码123456
1
2
3
[ root @ msr ~ ] # docker run -d --name mysql5.7 -p 3306:3306 -v /opt/workspace/mysql/data:/var/lib/mysql -v /opt/workspace/mysql/conf/my.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=123456 master
[ root @ slave ~ ] # docker run -d --name mysql5.7 -p 3306:3306 -v /opt/workspace/mysql/data:/var/lib/mysql -v /opt/workspace/mysql/conf/my.cnf:/etc/my.cnf -e MYSQL_ROOT_PASSWORD=123456 slave
Copy 至于mysql的配置文件my.cnf，可以把自己准备，也可以通过一下命令获取
1
[ root@msr ~] # docker run -i --rm mysql5.7 cat /etc/my.cnf > my.cnf
Copy MySQL主从配置
修改mysql的配置文件
master：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[ mysqld ]
user = mysql
default - storage - engine = INNODB
character - set - server = utf8
#开启log-bin
log - bin = / var / lib / mysql / mysql - bin
#设置服务id，主从不能一致
server ‐ id = 1
#设置需要同步的数据库
#binlog‐do‐db=user_db
#屏蔽系统库同步
binlog ‐ ignore ‐ db = mysql
binlog ‐ ignore ‐ db = information_schema
binlog ‐ ignore ‐ db = performance_schema
expire - logs - days = 14
max - binlog - size = 500 M
default - time - zone = '+08:00'
[ client ]
default - character - set = utf8
[ mysql ]
default - character - set = utf8
Copy 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
[ mysqld ]
user = mysql
default - storage - engine = INNODB
character - set - server = utf8
#开启log-bin
log - bin = / var / lib / mysql / mysql - bin
expire - logs - days = 14
max - binlog - size = 500 M
server - id = 2
#relay_log的位置
relay_log = mysql - relay - bin
log - slave - updates = 1
#设置需要同步的数据库
#replicate_wild_do_table=user_db.%
#屏蔽系统库同步
replicate_wild_ignore_table = mysql . %
replicate_wild_ignore_table = information_schema . %
replicate_wild_ignore_table = performance_schema . %
#只读
read - only = 1
default - time - zone = '+08:00'
[ client ]
default - character - set = utf8
[ mysql ]
default - character - set = utf8
Copy 重启master和slave
1
2
[root@msr ~]# docker restart master
[root@msr ~]# docker restart slave
Copy 下一步在Master数据库创建数据同步用户，授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据。首先要进入到docker容器内，也可以通过向Navicat等工具执行。
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
[root@msr ~]# docker exec -it master /bin/bash
root@207f37f4c64a:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1633
Server version: 5.7.30-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
Copy 重启数据库
1
2
[root@msr ~]# docker restart master
[root@msr ~]# docker restart slave
Copy 在salve中执行
1
change master to master_host='192.168.74.130', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000004', master_log_pos= 154, master_connect_retry=30;
Copy master_host ：Master的地址，指的是容器的独立ip,可以通过docker inspect –format=’{% raw %} {{.NetworkSettings.IPAddress}} {% endraw %}‘容器名称|容器id`查询容器的ip，这个命令查看的是容器的Host，同服务器下可以使用。不同服务器下就要使用服务的真实IP地址。
master_port ：Master的端口号，指的是容器的端口号
master_user ：用于数据同步的用户
master_password ：用于同步的用户的密码
master_log_file ：指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos ：从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry ：如果连接失败，重试的时间间隔，单位是秒，默认是60秒
查看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
show slave status \\G ;
*************************** 1. row ***************************
Slave_IO_State :
Master_Host : 192.168 . 74.130
Master_User : slave
Master_Port : 3306
Connect_Retry : 30
Master_Log_File : mysql - bin . 000004
Read_Master_Log_Pos : 154
Relay_Log_File : edu - mysql - relay - bin . 000001
Relay_Log_Pos : 4
Relay_Master_Log_File : mysql - 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 : 154
Relay_Log_Space : 154
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 : 1045
Last_IO_Error : error connecting to master 'slave@47.96.175.101:3306' - retry - time : 60 retries : 10
Last_SQL_Errno : 0
Last_SQL_Error :
Replicate_Ignore_Server_Ids :
Master_Server_Id : 0
Master_UUID :
Master_Info_File : / var / lib / mysql / master . info
SQL_Delay : 0
SQL_Remaining_Delay : NULL
Slave_SQL_Running_State :
Master_Retry_Count : 86400
Master_Bind :
Last_IO_Error_Timestamp : 200628 09 : 03 : 36
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 )
Copy 正常情况下Slave_IO_Running: No和Slave_SQL_Running: No。因为我们还没有开启主从复制过程。使用start slave开启主从复制过程，然后再次查询主从同步状态show slave status \G;
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
*************************** 1. row ***************************
Slave_IO_State : Waiting for master to send event
Master_Host : 192.168 . 74.130
Master_User : slave
Master_Port : 3306
Connect_Retry : 30
Master_Log_File : mysql - bin . 000004
Read_Master_Log_Pos : 154
Relay_Log_File : edu - mysql - relay - bin . 000002
Relay_Log_Pos : 320
Relay_Master_Log_File : mysql - 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 : 154
Relay_Log_Space : 531
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 : 0994 ae5c - b83a - 11 ea - 9769 - 0242 ac110003
Master_Info_File : / var / lib / mysql / 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 )
Copy Slave_IO_State: Waiting for master to send event，说明正在等待master的数据。Slave_IO_Running: Yes和Slave_SQL_Running: Yes说明主从复制已经开启。此时可以测试数据同步是否成功。
配置过程中可能出现的错误
Slave_IO_State: Connecting to master或Slave_IO_Running:Connectiog。说明从库一直在尝试连接主库，所以可能是：
主库的Host地址不对
端口是否不对
用于连接主库的用户和密码是否不对
master_log_file和master_log_pos是否和主库的不一致
