Docker + MySQL搭建主从

1. 配置master.cnf，放到 /software/docker/mysql/master/conf/

[mysqld]
server_id=100 #唯一id
log-bin=mysql-bin #启用二进制日志
#binlog-do-db=xxx #指定哪个数据库可以打开注释，xxx替换成数据库名字
read-only=0 #1只读，0读写

#忽略系统自带的数据库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

2. 配置slave.cnf，放到 /software/docker/mysql/slave/conf/

[mysqld]
server_id=200 #唯一id
log-bin=mysql-bin #启用二进制日志
#binlog-do-db=xxx #指定哪个数据库可以打开注释，xxx替换成数据库名字
read-only=1 #1只读，0读写

#忽略系统自带的数据库
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

3. 运行MySQL, master节点

docker run --name mysql-master -p 3036:3306 \
-v /software/docker/mysql/master/conf:/etc/mysql/conf.d \
-v /software/docker/mysql/master/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:latest \
--character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

4. 运行MySQL, slave节点

docker run --name mysql-slave-01 -p 3037:3306 \
-v /software/docker/mysql/slave/conf:/etc/mysql/conf.d \
-v /software/docker/mysql/slave/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:latest \
--character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

5. 进入master容器，创建账号

docker exec -it mysql-master bash
mysql -uroot -proot
#<username> 用户名
#<salveip>可修改为%，代表任意的主机
#<reppassword>用户的认证密码
CREATE USER '<username>'@'<salveip>' IDENTIFIED WITH mysql_native_password BY '<reppassword>';

6. 获取记录，执行show master status
   

   Snipaste_2024-03-09_19-08-26.jpg

7. 配置slave

docker exec -it mysql-slave bash
mysql -uroot -proot
#<master_ip> 主节点ip
#<username> 主节点用户名
#<reppassword> 密码

CHANGE MASTER TO 
MASTER_HOST='<master_ip>',MASTER_PORT=3036,MASTER_USER='<username>',MASTER_PASSWORD='<reppassword>',MASTER_LOG_FILE='<File>',MASTER_LOG_POS=<Position>;

#开启同步
start slave 开启
stop slave 关闭

show slave status\G;

Snipaste_2024-03-09_19-24-21.jpg

只有这两项都是YES就成功了