docker搭建mysql主备

1、设置配置文件

设置主库配置文件
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id = 1   //主库ID
log_bin = mysql-bin //主库binglog文件名称


设置备库配置文件
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id = 2  
log_bin = mysql-bin
relay-log = mysql-relay-log //中继日志名称
read-only = 1   //备库只读
log_slave_updates = 1 //更新的数据写进二进制日志中

2、启动docker
我这里使用了自定义ip
自定义ip设置 https://blog.csdn.net/gjzf313/article/details/103513485

启动主库
docker run --rm --name mysqlMaster -v $PWD/localtime:/etc/localtime -v $PWD/data:/var/lib/mysql -v $PWD/mastercnf:/etc/mysql --network mysqlnet --ip 192.168.13.101 -e MYSQL_ROOT_PASSWORD=123456 -p 3307:3306 -d mysql:5.7

-v $PWD/localtime:/etc/localtime  //挂载本地的时区文件到容器
-v $PWD/data:/var/lib/mysql   //挂载一个目录到容器用于保存mysql的数据
-v $PWD/mastercnf:/etc/mysql  //挂载配置文件
--network mysqlnet --ip 192.168.13.101 //指定容器IP
-e MYSQL_ROOT_PASSWORD=123456  //mysql的root密码

启动备库
docker run --rm --name mysqlSlave -v $PWD/localtime:/etc/localtime  -v $PWD/data:/var/lib/mysql -v $PWD/slavecnf:/etc/mysql --network mysqlnet --ip 192.168.13.102 -e MYSQL_ROOT_PASSWORD=123456 -p 3308:3306 -d mysql:5.

3、在主库上创建同步账号

docker exec -it mysqlMaster /bin/bash
mysql -h 127.0.0.1 -p123456
grant replication slave, replication client on *.* to slave@'192.168.13.*' identified by '123456';

查看主库状态
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |  3063576 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4、在备库上设置

change master to master_host='192.168.13.101',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0;

master_log_file='mysql-bin.000001',master_log_pos=3063576 

最后启动备库线程
start slave;


查看备库状态
show slave status;



GTID模式
配置文件中
gtid_mode = on
enforce_gtid_consistency = true

备库设置
change master to master_host="192.168.13.101",master_port=3306,master_user="slave",master_password="123456",master_auto_position=1;