Mysql主备复制架构

# 环境：

mysql版本：8.0.18

# 步骤

# 主MySQL

编辑主MySQL的配置文件master.conf

[mysqld]
log-bin=mysql-bin    //[必须]启用二进制日志
server-id=1          //[必须]服务器唯一ID，默认是1，一般取IP最后一段，这里看情况分配

1
2
3

启动容器

docker run --name mysql-master --privileged=true --restart=always -v /ocean/docker/mysql/master/mysql:/var/lib/mysql -v /ocean/docker/mysql/master.conf:/etc/mysql/my.cnf -v /ocean/docker/mysql/master/mysql-files:/var/lib/mysql-files/ -p 3406:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql

配置MySQL

mysql> USE mysql;
# 创建slave账号
mysql> CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123';
# 授予slave REPLICATION权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
# 刷新授权表信息
mysql> FLUSH PRIVILEGES;
# 查看master情况
mysql> SHOW MASTER STATUS\G;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      842 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 从MySQL

编辑从MySQL的配置文件slave.conf

[mysqld]
log-bin=mysql-bin    //[必须]启用二进制日志
server-id=2          //[必须]服务器唯一ID，默认是1，一般取IP最后一段，这里看情况分配

1
2
3

启动容器

docker run --name mysql-slave --privileged=true --restart=always --link mysql-master:master -v /ocean/docker/mysql/slave/mysql:/var/lib/mysql -v /ocean/docker/mysql/slave.conf:/etc/mysql/my.cnf -v /ocean/docker/mysql/slave/mysql-files:/var/lib/mysql-files/ -p 3407:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql

配置MySQL

# 配置连接主MySQL
mysql> change master to master_host='master', master_user='slave', master_password='123', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=842, master_connect_retry=30;
# 启动slave
mysql> start slave;
# 查看slave状态，Slave_IO_Running和Slave_SQL_Running都为true，则表示复制是正常进行的
mysql> show slave status\G;
# 否则停止slave，重新设置
mysql> stop slave;

1
2
3
4
5
6
7
8