docker配置mysql主从

1、下载MySQL镜像

docker pull mysql:5.7
docker images

2、启动MySQL容器

#主库
docker run -p 3316:3306 --name main_mysql -e MYSQL_ROOT_PASSWORD=pwd@123 -d mysql:5.7

#从库
docker run -p 3326:3306 --name slave_mysql -e MYSQL_ROOT_PASSWORd=pwd@123 -d mysql:5.7

3、查看容器状态

docker ps -a
CONTAINER ID   IMAGE       COMMAND                  CREATED          STATUS                      PORTS                               NAMES
1b9cc22c327b   mysql:5.7   "docker-entrypoint.s…"   47 minutes ago   Up 22 minutes               33060/tcp, 0.0.0.0:3326->3306/tcp   slave_mysql
d4bc65eb3708   mysql:5.7   "docker-entrypoint.s…"   48 minutes ago   Up 22 minutes               33060/tcp, 0.0.0.0:3316->3306/tcp   main_mysql


#启动完成后
#使用Navicat工具测试连接两个连接，分别为master和slave

4、修改主MySQL容器配置

docker exec -it 容器Id /bin/bash
cd /etc/mysql/mysql.conf.d

#安装vim命令：
apt-get update
apt-get install vim

vim mysqld.cnf

[mysqld]

## 同一局域网内注意要唯一
server-id=100
## 开启二进制日志功能，可以随便取（关键）
log-bin=mysql-bin

5、修改从MySQL容器配置

docker exec -it 容器Id /bin/bash
cd /etc/mysql/mysql.conf.d

#安装vim命令：
apt-get update
apt-get install vim

[mysqld]
## 设置server_id,注意要唯一
server-id=101  
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin   
## relay_log配置中继日志
relay_log=edu-mysql-relay-bin 

6、重启MySQL主从容器

docker restart 主容器Id
docker restart 从容器Id

7、创建主MySQL的授权访问用户

docker exec -it 容器Id /bin/bash
mysql -uroot -ppwd@123

#创建用户并授权,允许从库服务连接主库的服务
CREATE USER 'slave'@'%' IDENTIFIED BY 'pwd@123';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

#生效 刷新一下，
flush privileges;

8、查看主库的binlog文件

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      769 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

9、查看主MySQL容器的host

docker inspect --format='{{.NetworkSettings.IPAddress}}' main_mysql
172.17.0.2

10、配置从MySQL容器的读取设置

docker exec -it 从容器Id /bin/bash
mysql -uroot -ppwd@123

change master to master_host='172.17.0.2', master_user='slave', master_password='pwd@123', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 769, master_connect_retry=30;

#生效 刷新一下，
flush privileges;

11、开启事务

#查看从库的状态
show slave status \G 
             Slave_IO_Running: No
            Slave_SQL_Running: No
            
#开启事务
start slave;
show slave status \G 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            
            

12、测试

#在主库建设一个库 进行测试
#主库
docker exec -it main_mysql sh
mysql -uroot -ppwd@123
create database test;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+


#从库
docker exec -it slave_mysql sh
mysql -uroot -ppwd@123
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)