JZTXT

随笔（二十三）『docker MySql 主从复制』

发布时间 2023-07-14 10:01:22作者: 小昕昕

1、准备好带docker的虚拟机

2、拉取mysql5.7镜像

docker pull mysql:5.7

3、创建主服务的映射

mkdir -p /mydata/mysql_master/log
mkdir -p /mydata/mysql_master/data
mkdir -p /mydata/mysql_master/conf
touch /mydata/mysql_master/conf/my.cnf

4、创建从服务的映射

mkdir -p /mydata/mysql_slave/log
mkdir -p /mydata/mysql_slave/data
mkdir -p /mydata/mysql_slave/conf
touch /mydata/mysql_slave/conf/my.cnf

5、设置主服务mysql_master配置

cd /mydata/mysql_master/conf
vim my.cnf
[client]
default_character_set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation_server = utf8_general_ci
character_set_server = utf8
skip-character-set-client-handshake
skip-name-resolve

server_id=10010
binlog-ignore-db=mysql
log-bin=mysql_master-bin
expire_logs_days=3

6、设置从服务mysql_slave配置

cd /mydata/mysql_slave/conf
vim my.cnf
[client]
default_character_set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation_server = utf8_general_ci
character_set_server = utf8
skip-character-set-client-handshake
skip-name-resolve

server_id=10086
binlog-ignore-db=mysql
log-bin=mysql_slave-bin
expire_logs_days=3
relay_log=mysql-relay-bin
log_slave_updates=1
read_only=1

7、创建主服务mysql_master实例并启动

docker run -p 10010: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 \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7

8、进入主服务容器，登录msql

docker exec -it mysql_master /bin/bash

mysql -u root -p

密码：root

9、创建从服务mysql_slave用户并设置密码

CREATE USER 'mysql_slave'@'%' IDENTIFIED BY '123456';

10、给从服务mysql_slave赋权及刷新权限

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'mysql_slave'@'%';

FLUSH PRIVILEGES;

11、查看主服务mysql_master状态

show master status;

image

12、创建主服务mysql_slave实例并启动

docker run -p 10086: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 \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7

13、进入从服务容器，登录msql

docker exec -it mysql_slave /bin/bash

mysql -u root -p

密码：root

14、在从服务mysql_slave配置主服务mysql_master信息

change master to master_host='172.17.0.1',master_port=10010,master_user='mysql_slave',master_password='123456',master_log_file='mysql_master-bin.000003',master_log_pos=781;

15、从服务mysql_slave查看主从状态

show slave status \G;

image

16、从服务mysql_slave开启主从同步

start slave;

17、再次从服务mysql_slave查看主从状态

show slave status \G;

image