Docker mysql 主从
一、独立容器部署mysql主从
# 主从 my.cnf加上 [mysqld] server-id = XXX log-bin = mysql-bin log-bin-index = log-bin.index
（1）master
docker run -d \ --name mysql_master \ -p 3310:3306 \ -v /data/conf/master-slave/master.cnf:/etc/mysql/my.cnf \ -v /data/conf/master-slave/localtime:/etc/localtime \ -v /data/storage/cluster_data/master_data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=Password4Root \ mysql
（2）slave
docker run -d \ --name mysql_slave \ -p 3311:3306 \ -v /data/conf/master-slave/slave.cnf:/etc/mysql/my.cnf \ -v /data/conf/master-slave/localtime:/etc/localtime \ -v /data/storage/cluster_data/slave_data:/var/lib/mysql \ -e MYSQL_ROOT_PASSWORD=Password4Root \ --link mysql_master:master \ mysql
（3）查看
docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES dc5f46aa135b mysql "docker-entrypoint.s…" About a minute ago Up About a minute 0.0.0.0:3311->3306/tcp mysql_slave be3e1af663cd mysql "docker-entrypoint.s…" About a minute ago Up About a minute 0.0.0.0:3310->3306/tcp mysql_master
（4）主库Replication设置
# 登入主库
docker exec -it mysql_master mysql -uroot -pPassword4Root
mysql> grant all privileges on *.* to 'repl'@'%' identified by 'repl_password';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 434
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
（5）从库Replication设置
# 登入从库
docker exec -it mysql_slave mysql -uroot -pPassword4Root
# 设置主从
mysql> change master to
-> master_host='master',
-> master_user='repl',
-> master_password='repl_password',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=434;
# 启动slave
mysql> start slave;
# 查看状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 434
Relay_Log_File: 5925cc301b6b-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
（6）测试
主库新建数据库
从库查看
尝试重启mysql_master容器，slave状态变为connecting
重启完成后，状态恢复正常
二、Docker Swarm mysql主从
（1）创建secret文件
# echo "Password4Root" | docker secret create root_cluster_password - w4wv4askqam5znua5032gm8vt
（2）使用swarm配置管理
master.cnf和slave.cnf要存在
# 创建主从配置文件 [root@manager master-slave]# docker config create master.cnf master.cnf t1rjlq7djtulxzl7gppf3rrum [root@manager master-slave]# docker config create slave.cnf slave.cnf u229zquvbevbq50k92ylqcgqn
（3）创建overlay网络
# docker network create -d overlay mysql-cluster --subnet 10.10.2.0/24 zf4n8waxc283uprq8eppx2gzp
（4）创建服务
# master [root@manager ~]# docker service create \ > --name mysql_master \ > --replicas 1 \ > --network mysql-cluster \ > --secret root_cluster_password \ > --config src=master.cnf,target=/etc/mysql/my.cnf \ > -e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/root_cluster_password \ > --mount type=volume,src=master_data,dst=/var/lib/mysql \ > --publish 3320:3306 \ > mysql nuthmf6fdpmjmew1pzhdninmg overall progress: 1 out of 1 tasks 1/1: running [==================================================>] verify: Service converged # slave [root@manager ~]# docker service create \ > --name mysql_slave \ > --replicas 1 \ > --network mysql-cluster \ > --secret root_cluster_password \ > --config src=slave.cnf,target=/etc/mysql/my.cnf \ > --mount type=volume,src=slave_data,dst=/var/lib/mysql \ > -e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/root_cluster_password \ > --publish 3321:3306 \ > mysql pyed0j0jlijy1upbyicuayvko overall progress: 1 out of 1 tasks 1/1: running [==================================================>] verify: Service converged
[root@manager ~]# docker service ls ID NAME MODE REPLICAS IMAGE PORTS nuthmf6fdpmj mysql_master replicated 1/1 mysql:latest *:3320->3306/tcp pyed0j0jlijy mysql_slave replicated 1/1 mysql:latest *:3321->3306/tcp
（5）主从配置
# 登入master所在节点的主库
mysql> grant all privileges on *.* to 'repl'@'%' identified by 'repl_password';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 434
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
# 登入slave所在节点从库
mysql> change master to
-> master_host='mysql_master',
-> master_user='repl',
-> master_password='repl_password',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=434;
Query OK, 0 rows affected, 2 warnings (0.29 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec
查看状态
（6）测试验证
主库创建数据库
查看从库
stop 主库（在swarm集群中，关停的容器默认会自动重启）
重启完成后恢复正常
三、服务编排部署mysql主从
（1）mysql_replications.yml
version: "3.3"
services:
master:
image: mysql:latest
networks:
- rep_net
ports:
- "3330:3306"
volumes:
- type: volume
source: master-data
target: /var/lib/mysql
volume:
nocopy: true
configs:
- source: localtime
target: /etc/localtime
- source: master.cnf
target: /etc/mysql/my.cnf
deploy:
replicas: 1
restart_policy:
condition: on-failure
resources:
limits:
cpus: '0.8'
memory: 500M
reservations:
cpus: '0.25'
memory: 200M
secrets:
- root_password
environment:
- MYSQL_ROOT_PASSWORD_FILE=/run/secrets/root_password
slave:
image: mysql:latest
networks:
- rep_net
ports:
- "3331:3306"
volumes:
- type: volume
source: slave-data
target: /var/lib/mysql
volume:
nocopy: true
configs:
- source: localtime
target: /etc/localtime
- source: slave.cnf
target: /etc/mysql/my.cnf
deploy:
replicas: 1
restart_policy:
condition: on-failure
resources:
limits:
cpus: '0.8'
memory: 500M
reservations:
cpus: '0.25'
memory: 200M
secrets:
- root_password
environment:
- MYSQL_ROOT_PASSWORD_FILE=/run/secrets/root_password
secrets:
root_password:
external: true
configs:
localtime:
external: true
master.cnf:
external: true
slave.cnf:
external: true
volumes:
master-data:
slave-data:
networks:
rep_net:
driver: overlay
（2）部署服务
# docker stack deploy -c mysql_replications.yml repl Creating network repl_rep_net Creating service repl_master Creating service repl_slave
（3）查看服务
[root@manager stack_compose]# docker service ls ID NAME MODE REPLICAS IMAGE khfpkq81op7b repl_master replicated 1/1 mysql:latest *:3330->3306/tcp u7k4enpy1eru repl_slave replicated 1/1 mysql:latest *:3331->3306/tcp
（4）配置主从
# master
# docker exec -it fb85153f27ac mysql -uroot -pPassword4Root
mysql> grant all privileges on *.* to 'repl'@'%' identified by 'repl_password';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 434
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
# slave
mysql> change master to
-> master_host='master',
-> master_user='repl',
-> master_password='repl_password',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=434;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 434
Relay_Log_File: 0ddecac560e1-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
.....
.....
主从正常
