本文详细介绍了如何在Docker环境下搭建MySQL主备模式，包括创建主从数据库节点、配置主从同步、数据迁移以及宕机处理方案。通过设置server-id、binlog日志、权限和复制配置，确保了数据的实时同步。此外，还提及了使用nginx实现主备切换的策略。
mysql主备搭建
作者：周云玉 日期：2022年2月18日 上午
背景：基于线上项目PXC经常出现问题，切换成主备模式
主备原理，如图
一、创建主数据库节点
1、创建容器
注意：如果是线上单节点改成主备，这一步省略
docker run -d -p 3306:3306 --name mysql-master \
-e MYSQL_ROOT_PASSWORD='iws123456' \
-v /data/iws/mysql-master/conf/my.cnf:/etc/mysql/my.cnf \
-v /data/iws/mysql-master/data:/var/lib/mysql \
-v /etc/localtime:/etc/localtime:ro \
-v /etc/timezone:/etc/timezone:ro \
--privileged=true \
--restart=always mysql:5.7
2、导入测试数据
注意：如果是线上单节点改成主备，这一步省略。自行创建表及数据即可
3、创建slave备份账号和权限
#进入master数据库容器
$ docker exec -it mysql-master /bin/bash
#进入数据库
$ mysql -u root -p
#创建slave备份用的账号和权限
> GRANT REPLICATION SLAVE ON *.* to 'slave'@'%' identified by '123456';
> FLUSH PRIVILEGES;
> exit;
注解：这里表示配置从机登录用户名为 slave，密码为 123456，并且必须从指定地址登录，登录成功之后可以操作任意库中的任意表。其中，如果不需要限制登录地址，可以将 IP 地址更换为一个 %
4、开启bin-log
[mysqld]
log-bin=/var/lib/mysql/binlog
server-id=128
binlog-do-db=master
log-bin：同步的日志路径及文件名，一定注意这个目录要是 mysql 有权限写入的
binlog-do-db：要同步的数据库名，当从机连上主机后，只有这里配置的数据库才会被同步，其他的不会被同步
server-id: mysql在主从环境下的唯一标志符，给个任意数字，注意不能和从机重复
备注：配置完后需要重启mysql节点
5、查看二进制日志名和偏移量
>show master status;
从上可以看出：master_log_file=‘binlog.000001’,master_log_pos=154 后续从节点配置要用到
二、创建从数据库节点
1、创建容器
docker run -d -p 3307:3306 --name mysql-slave \
-e MYSQL_ROOT_PASSWORD='iws123456' \
-v /data/iws/mysql-slave/conf/my.cnf:/etc/mysql/my.cnf \
-v /data/iws/mysql-slave/data:/var/lib/mysql \
-v /etc/localtime:/etc/localtime:ro \
-v /etc/timezone:/etc/timezone:ro \
--privileged=true \
--restart=always mysql:5.7
2、同步主节点已有的数据
安装mydumper
#上传镜像文件
$ docker load -i mydumper.tar
#创建备份脚本目标
$ mkdir /home/mysqldump
$ chmod 0777 /home/mysqldump
#启动mydumper容器
$ docker run -it --network=host -v "/home/mysqldump:/dumpdir:rw" -d --name mydumper cacciald/mydumper
同步数据步骤一：拷贝主节点数据到目标文件夹
#进入容器内部
$ docker exec -it mydumper /bin/bash
#备份指定的数据库到dumpdir（宿主机目录/data/iws/mysqldump）目录下
$ mydumper --outputdir=/dumpdir --database=${源数据库} --host=${源数据库IP地址} --port=${源数据库端口} --user=${源数据库账号} --password=${源数据库密码} --compress --lock-all-tables --build-empty-files --threads=4 --compress-protocol --verbose=3 --skip-tz-utc --logfile=/dumpdir/log.log --triggers --events --routines
#threads 可根据服务器机器核数进行调整，最好不要超过CPU核数
示例：
mydumper --outputdir=/dumpdir --database=master --host=192.168.230.188 --port=3306 --user=root --password=iws123456 --compress --lock-all-tables --build-empty-files --threads=4 --compress-protocol --verbose=3 --skip-tz-utc --logfile=/dumpdir/log.log --triggers --events --routines
#查看dumpdir目录
root@localhost:/# cd dumpdir/
root@localhost:/dumpdir# ll
total 4032
drwxr-xr-x 2 root root 45056 Feb 18 07:12 ./
drwxr-xr-x 1 root root 33 Feb 18 03:30 ../
-rw-r--r-- 1 root root 97350 Feb 18 07:12 log.log
-rw-r--r-- 1 root root 88 Feb 18 07:12 master-schema-create.sql.gz
-rw-r--r-- 1 root root 293 Feb 18 07:12 master.ACTIVEMQ_ACKS-schema.sql.gz
-rw-r--r-- 1 root root 20 Feb 18 07:12 master.ACTIVEMQ_ACKS.sql.gz
-rw-r--r-- 1 root root 209 Feb 18 07:12 master.ACTIVEMQ_LOCK-schema.sql.gz
-rw-r--r-- 1 root root 125 Feb 18 07:12 master.ACTIVEMQ_LOCK.sql.gz
-rw-r--r-- 1 root root 301 Feb 18 07:12 master.ACTIVEMQ_MSGS-schema.sql.gz
-rw-r--r-- 1 root root 20 Feb 18 07:12 master.ACTIVEMQ_MSGS.sql.gz
-rw-r--r-- 1 root root 506 Feb 18 07:12 master.ADVERSE_EVENT-schema.sql.gz
-rw-r--r-- 1 root root 20 Feb 18 07:12 master.ADVERSE_EVENT.sql.gz
-rw-r--r-- 1 root root 765 Feb 18 07:12 master.ADVICE-schema.sql.gz
同步数据步骤二：把数据从文件夹导入数据库
#进入容器内部
$ docker exec -it mydumper /bin/bash
#导入数据库到从节点数据库中
$ myloader --database=${目标数据库} --user=${目标数据库账号} --password=${目标数据库密码} --host=${目标数据库IP地址} --port=${目标数据库端口} --directory=/dumpdir --queries-per-transaction=50000 --threads=10 --compress-protocol --verbose=3 -e -o
示例：
myloader --database=master --user=root --password=iws123456 --host=192.168.230.188 --port=3307 --directory=/dumpdir --queries-per-transaction=50000 --threads=10 --compress-protocol --verbose=3 -e -o
#查看部分执行日志
** Message: 10 threads created
** Message: Creating database `master`
** Message: Dropping table or view (if exists) `master`.`ACTIVEMQ_ACKS`
** Message: Creating table `master`.`ACTIVEMQ_ACKS`
** Message: Dropping table or view (if exists) `master`.`ACTIVEMQ_LOCK`
** Message: Creating table `master`.`ACTIVEMQ_LOCK`
** Message: Dropping table or view (if exists) `master`.`ACTIVEMQ_MSGS`
** Message: Creating table `master`.`ACTIVEMQ_MSGS`
** Message: Dropping table or view (if exists) `master`.`ADVERSE_EVENT`
** Message: Creating table `master`.`ADVERSE_EVENT`
** Message: Dropping table or view (if exists) `master`.`ADVICE`
** Message: Creating table `master`.`ADVICE`
** Message: Dropping table or view (if exists) `master`.`ADVICE_EXECUTE`
** Message: Creating table `master`.`ADVICE_EXECUTE`
** Message: Dropping table or view (if exists) `master`.`ADVICE_TEMPLATE`
** Message: Creating table `master`.`ADVICE_TEMPLATE`
** Message: Dropping table or view (if exists) `master`.`APPLICATION_INFO`
注意：一般是跨服务器拷贝，所以需要分步执行拷贝。
最后用客户端查看从节点数据是否已经拷贝过来
3、配置server-id
[mysqld]
server-id=129
注意：从节点只需配server-id即可，配置完需要重启节点
4、使用命令来配置从节点
#进入master数据库容器
$ docker exec -it mysql-slave /bin/bash
#进入数据库
$ mysql -u root -p
#执行以下命令
>change master to master_host='192.168.230.188',master_port=3306,master_user='slave',master_password='123456',master_log_file='binlog.000001',master_log_pos=154;
#启动 slave 进程
>start slave;
#启动之后查看从机状态
>show slave status\G;
===============================================
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.230.188
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 154
Relay_Log_File: 5f99499e0a94-relay-bin.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: binlog.000001
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: 128
Master_UUID: 713eb6e8-907c-11ec-ba71-0242ac110002
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.01 sec)
主要是下面两项值都要为为 YES，则表示配置正确：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
至此，配置完成，主机创建库，添加数据，从机会自动同步
三、搭建nginx主备模式
upstream mysql_server {
server 192.168.230.188:3306; #主用服务器
server 192.168.230.188:3307 backup; #主服务器宕机，自动切换备用服务器
}
四、主机宕机处理方案
主库宕机，备机接管服务
$ docker exec -it mysql-slave /bin/bash
#进入slave数据库
$ mysql -uroot -p
>stop slave;
其他步骤同主备模式方案一致，删除宕机的主库，然后使用当前的备库作为主库，建立主备模式即可
五、缺点
1、同步线上已有的数据，需要停服务
2、当主机宕机，无法及时发现并处理，如果出现二次宕机，很难处理
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
