Docker实现mysql主从复制

主从复制原理

依据binlog文件，主节点将数据库变更记录在该文件中，从节点的IO线程读取该文件并记录在自己的Relay文件中，从节点中的SQL线程执行该文件实现和主节点数据一致。

部署过程

1.主节点配置

1.1跑一个mysql主节点

sudo docker run --privileged=true  -p 3303:3306 --name mysql-master  -v /mydata/mysql/master/log:/var/log/mysql  -v /mydata/mysql/master/data:/var/lib/mysql  -v /mydata/mysql/master/conf:/etc/mysql/conf.d  -e MYSQL_ROOT_PASSWORD=root  -d mysql

1.2修改主节点配置文件内容如下

/mydata/mysql/master/conf/my.cnf

server_id=1

replicate-wild-ignore-table=mysql.*

log-bin=mysql-bin

log_slave_updates=1

binlog_format=mixed

1.3在主节点创建用户并授予复制权限

CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'root@123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

1.4在主节点查看binlog文件名称及下标指针

show master status;

2.从节点配置

2.1跑一个mysql从节点

sudo docker run --privileged=true  -p 3307:3306 --name mysql-slave  -v /mydata/mysql/slave/log:/var/log/mysql  -v /mydata/mysql/slave/data:/var/lib/mysql  -v /mydata/mysql/slave/conf:/etc/mysql/conf.d  -e MYSQL_ROOT_PASSWORD=root  -d mysql

2.2修改从节点配置文件内容如下

/mydata/mysql/slave/conf/my.cnf

[mysqld]
server_id=2

replicate-wild-ignore-table=mysql.*
#从节点设置为只读，实现读写分离
read_only=1

CREATE USER 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'root@123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

2.3.从节点配置开启复制

change master to master_host='192.168.xxx.xxx',master_user='repl',master_password='root@123',master_log_file='mysql-bin.000001',master_log_pos=1693,master_port=3306;
start slave;

注意master_log_file和master_log_pos采用1.4步获得的

2.4检查复制状态

show slave status \G;

留意Slave_IO_Running和Slave_SQL_Running的状态是否都为yes