一、环境介绍

使用docker部署mysql实例
数据库：MySQL 5.7.x 
操作系统：CentOS 7.x
容器：Docker version 20.10.17, build 100c701
镜像：mysql:5.7
主库：IP=172.168.10.149; PORT=3306; server-id=100; database=test; table=t1
从库：IP=172.168.50.151; PORT=3306; server-id=110; database=test; table=t1

注意事项：
主从库必须保证网络畅通可访问
主库必须开启binlog日志
主从库的server-id必须不同

[root@localhost data]# docker images
REPOSITORY   TAG        IMAGE ID       CREATED       SIZE
mysql        5.7        eef0fab001e8   10 days ago   495MB
[root@localhost conf]# docker ps -a
CONTAINER ID   IMAGE        COMMAND                  CREATED        STATUS                    PORTS                                                  NAMES
15b8f6e717ad   mysql:5.7    "docker-entrypoint.s…"   14 hours ago   Up 57 minutes             0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql
[root@localhost ~]# docker --version
Docker version 20.10.17, build 100c701

如果是系统运行了一段时间后才开始做主从同步，那么首先需要将这部分数据复制到从库。我这里使用 xtrabackup 工具来完成这一步工作，当然如果从节点是一个全新的环境，mysql版本一致的话，可以直接复制主库数据文件到从库。

首先安装xtrabackup 工具，如下所示：

cat <>/etc/yum.repos.d/percona.repo
[percona]
name = Percona
baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/\$releasever/RPMS/\$basearch
enabled = 1
gpgcheck = 0[epel]
name=epelrepo
baseurl=https://mirrors.aliyun.com/epel/\$releasever/\$basearch
gpgcheck=0
enable=1
eof[root@node1 ~]# yum list all| grep xtraback -i
Repository epel is listed more than once in the configuration
holland-xtrabackup.noarch                      1.0.14-3.el6                 epel
percona-xtrabackup.x86_64                      2.3.10-1.el6                 percona
percona-xtrabackup-20.x86_64                   2.0.8-587.rhel6              percona
percona-xtrabackup-20-debuginfo.x86_64         2.0.8-587.rhel6              percona
percona-xtrabackup-20-test.x86_64              2.0.8-587.rhel6              percona
percona-xtrabackup-21.x86_64                   2.1.9-746.rhel6              percona
percona-xtrabackup-21-debuginfo.x86_64         2.1.9-746.rhel6              percona
percona-xtrabackup-22.x86_64                   2.2.13-1.el6                 percona
percona-xtrabackup-22-debuginfo.x86_64         2.2.13-1.el6                 percona
percona-xtrabackup-24.x86_64                   2.4.11-1.el6                 percona
percona-xtrabackup-24-debuginfo.x86_64         2.4.11-1.el6                 percona
percona-xtrabackup-debuginfo.x86_64            2.3.10-1.el6                 percona
percona-xtrabackup-test.x86_64                 2.3.10-1.el6                 percona
percona-xtrabackup-test-21.x86_64              2.1.9-746.rhel6              percona
percona-xtrabackup-test-22.x86_64              2.2.13-1.el6                 percona
percona-xtrabackup-test-24.x86_64              2.4.11-1.el6                 percona[root@node1 ~]# yum -y install percona-xtrabackup-24

在主库执行备份

innobackupex --datadir=/etc/mysql/data --user=root --password=123456 --host=127.0.0.1 --databases=test /tmp/mysql_data/

--datadir 参数的路径为mysql数据库文件路径，--databases 表示要设备的库，/tmp/mysql_data/ 备份文件路径

查看备份文件，如下所示： 

[root@localhost mysql_data]# ls /tmp/mysql_data/
2022-11-15_23-42-40  2022-11-15_23-44-19
[root@localhost mysql_data]# ls /tmp/mysql_data/2022-11-15_23-42-40/
backup-my.cnf  ib_buffer_pool  ibdata1  test  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile
[root@localhost mysql_data]# ls /tmp/mysql_data/2022-11-15_23-44-19/
backup-my.cnf  ib_buffer_pool  ibdata1  mysql  performance_schema  sys  test  xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info  xtrabackup_logfile

在从库执行还原，需要将在主库备份的文件复制到从库

innobackupex --datadir=/etc/mysql/data --user=root --password=123456 --host=127.0.0.1 --port=3306  --copy-back /tmp/mysql_data/2022-11-15_23-44-19

详细介绍可以参考如下：
https://www.cnblogs.com/f-ck-need-u/p/9018716.html#auto_id_1
https://www.cnblogs.com/zhoujinyi/p/4088866.html
命令参数介绍
https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html#cmdoption-innobackupex--parallel

二、环境搭建

1、安装docker

yum install epel-release
yum -y install docker
systemctl start docker.service

2、拉取镜像

docker pull mysql:5.7

如出现如下问题：

[root@k3sn1 ~]# docker pull mysql:5.7
Error response from daemon: Head "https://registry-1.docker.io/v2/library/mysql/manifests/5.7": read tcp 172.168.50.144:44414->3.216.34.172:443: read: connection reset by peer

则需要配置加速器

网易云加速：https://hub-mirror.c.163.com
百度去加速：https://mirror.baidubce.com

vi /etc/docker/daemon.json
{"registry-mirrors": ["https://hub-mirror.c.163.com","https://mirror.baidubce.com"]
}systemctl daemon-reload
systemctl restart docker

3、创建主从节

docker run -id --privileged=true -p 3306:3306 --name mysql \
-v /etc/mysql/conf:/etc/mysql/conf.d \
-v /etc/mysql/logs:/var/log/mysql \
-v /etc/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7

4、创建从节点

docker run -id --privileged=true -p 3306:3306 --name mysql \
-v /etc/mysql/conf:/etc/mysql/conf.d \
-v /etc/mysql/logs:/var/log/mysql \
-v /etc/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7

参数说明：
–name：容器名，此处命名为mysql
-e：配置信息，此处配置mysql的root用户的登陆密码
-p：端口映射，此处映射 主机3306端口 到 容器的3306端口
-d：后台运行容器，保证在退出终端后容器继续运行
-v：主机和容器的目录映射关系，":"前为主机目录，之后为容器目录

５、主库添加配置文件

[root@localhost ~]# cat /etc/mysql/conf/master.cnf
[client]
port = 3306
default-character-set = utf8mb4[mysql]
port = 3306
default-character-set = utf8mb4[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data  #数据存储目录，根据实际情况而定，在docker中是指定其他目录了，这个目录没用使用，但是若不是docker的话则需要指定这个配置##########################
# log bin
##########################
server-id = 100				#必须唯一
log_bin = mysql-bin 		#开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7			#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256Mbinlog-do-db = test 		#要同步的数据库 binlog-ignore-db = mysql 	#不需要同步的数据库 
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

６、从库添加配置文件

[root@localhost conf]# cat slave.cnf 
[client]
port = 3306
default-character-set = utf8mb4[mysql]
port = 3306
default-character-set = utf8mb4[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data 	#数据存储目录，根据实际情况而定，在docker中是指定其他目录了，这个目录没用使用，但是若不是docker的话则需要指定这个配置##########################
# log bin
##########################
server-id = 110##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

三、主节点操作

# 进入容器内部
[root@localhost conf]# docker exec -it mysql bash
# 登录mysql
bash-4.2# mysql -uroot -p123456
# 创建mysql账号
grant replication slave on *.* to 'slave_user'@'%' identified by '123456';
# 刷新权限
flush privileges;
# 查看server_id，这个不能和从节点重复
mysql> show global variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 100   |
+---------------+-------+
1 row in set (0.00 sec)
# 查看是否启用binlog
mysql> show global variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
# 查看master状态，注意：mysql-bin.000004、708这两个参数从库会用到
mysql> show master status;
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                 | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| mysql-bin.000004 |      708 | test         | mysql,information_schema,performation_schema,sys |                   |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
1 row in set (0.00 sec)

四、从节点操作

# 进入容器内部
[root@localhost conf]# docker exec -it mysql bash
# 登录mysql
bash-4.2# mysql -uroot -p123456
# 配置主从同步
change master to master_host='172.168.50.149',master_port=3306,master_user='slave_user',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=708;
# 开启同步
start slave;
# 查看同步状态
show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.168.50.149Master_User: slave_userMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000004Read_Master_Log_Pos: 708Relay_Log_File: 15b8f6e717ad-relay-bin.000003Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000004Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 708Relay_Log_Space: 1254Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 100Master_UUID: ca5eab4c-651e-11ed-b768-0242ac110002Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 
1 row in set (0.00 sec)ERROR: 
No query specified

【Slave_IO_Running】和【Slave_SQL_Running】为Yes，则同步正常。

五、测试数据同步

1、在主库执行如下操作

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)mysql> USE test;
Database changed# 建表
CREATE TABLE t1 (id INT NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
# 添加数据
insert into t1(id) value(11)
# 查看
mysql> select * from t1;
+------+
| id   |
+------+
|   11 |
+------+

2、登录从库验证数据是否同步

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set (0.00 sec)mysql> select * from t1;
+------+
| id   |
+------+
|   11 |
+------+
2 rows in set (0.00 sec)

参考：MySQL5.7主从同步配置 - 哈喽哈喽111111 - 博客园