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]
|
拉取mysql镜像到本地，因为mysql镜像有很多版本，对应mysql的版本：mysql:tag
1
|
[root@msr ~]# docker pull mysql:5.7
|
运行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
|
至于mysql的配置文件my.cnf，可以把自己准备，也可以通过一下命令获取
1
|
[root@msr ~]# docker run -i --rm mysql5.7 cat /etc/my.cnf > my.cnf
|
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 = 500M
default-time-zone='+08:00'
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
|
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 = 500M
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
|
重启master和slave
1
2
|
[root@msr ~]# docker restart master
[root@msr ~]# docker restart slave
|
下一步在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)
|
重启数据库
1
2
|
[root@msr ~]# docker restart master
[root@msr ~]# docker restart slave
|
在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;
|
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)
|
正常情况下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: 0994ae5c-b83a-11ea-9769-0242ac110003
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)
|
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是否和主库的不一致
