mysql集群,基于 binlog 日志的主从配置
MySQL 集群的同步操作通过 3 个线程实现,其基本步骤如下:
主服务器 将数据的更新记录到 二进制日志(Binary log)中,用于记录二进制日志事件,这一步由 主库线程 完成;
从库 将 主库 的 二进制日志 复制到本地的 中继日志(Relay log),这一步由 从库 I/O 线程 完成;
从库 读取 中继日志 中的 事件,将其重放到数据中,这一步由 从库 SQL 线程 完成。
下面分别用Dockfile与docker-compose配置mysql集群的主从复制环境。
Dockerfile配置
从docker远程仓库直接获取mysql:8.0的镜像,也可以先pull下来。
docker pull mysql:8.0
1.my.cnf,配置log-bin 与 server-id, my.cnf配置,server-id需要保持不一致
log-bin = /var/run/mysqld/mysql-bin
server-id = 2
2.DockerFile:
FROM mysql:8.0
MAINTAINER syLucky
WORKDIR /root
COPY config/* /tmp/
RUN mv /tmp/my.cnf /etc/mysql/my.cnf && \
mv /tmp/my.cnf.master /etc/mysql/my.cnf.master && \
mv /tmp/my.cnf.slave1 /etc/mysql/my.cnf.slave1 && \
mv /tmp/my.cnf.slave2 /etc/mysql/my.cnf.slave2
RUN chmod +x ~/start-mysql.sh && \
chmod +x ~/stop-mysql.sh
CMD [ "sh", "-c", "service ssh start; bash"]
3.启动mysql主库容器:
docker run -itd \
-v "/home/docker/data/dbdata-master":/var/lib/mysql \
-v "/home/docker/data":/usr/local/data \
--net=bridge \
-p 3306:3306 \
--name mysql-master \
--hostname mysql-master \
-e MYSQL_ROOT_PASSWORD=Difficult_password1234 \
-d mysql:8.0 \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_unicode_ci \
--restart always
4.从库容器,注意改从库的映射端口要改变
docker run -itd \
-v "/home/docker/data/dbdata-slave1":/var/lib/mysql \
-v "/home/docker/data":/usr/local/data \
--net=bridge \
-p 3307:3306 \
--name mysql-slave1 \
--hostname mysql-slave1 \
-e MYSQL_ROOT_PASSWORD=Difficult_password1234 \
-d mysql:8.0 \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_unicode_ci \
--restart always
5.改了my.cnf,需要重启容器生效
docker stop mysql-master
docker stop mysql-slave1
docker stop mysql-slave2
docker start mysql-master
docker start mysql-slave1
docker start mysql-slave2
6.主库建立复制用户,分配slave权限
CREATE USER 'repl'@'%' IDENTIFIED BY 'Difficult_password1234';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
7.锁定主库,只读模式,取得log-bin与快照
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
# 两种方法选一种,一种是覆盖数据文件data,一种是导入模式
# 选择导入模式,主库备份数据
docker exec mysql-master mysqldump -u root '-pDifficult_password1234' --all-databases --master-data > /home/docker/data/dbdump.db
8.从库导入数据
# 从库导入数据
docker exec mysql-slave1 sh -c "mysql -u root '-pDifficult_password1234' < /usr /local/data/dbdump.db"
docker exec mysql-slave2 sh -c "mysql -u root '-pDifficult_password1234' < /usr /local/data/dbdump.db"
9.检查从库用repl用户登录主库
mysql -h mysql-master -urepl '-pDifficult_password1234'
root@mysql-slave1:/# mysql -hmysql-master -uroot '-pDifficult_password1234'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 8.0.15 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
10.从库change master配置
CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='repl',
MASTER_PASSWORD='Difficult_password1234',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=1169;
MASTER_LOG_FILE填写图中File的值
MASTER_LOG_POS填写图中Position的值
11.从库start slave 需要等待从库成功连接主库
START SLAVE;
SHOW SLAVE STATUS\G
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-master
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1169
Relay_Log_File: mysql-slave1-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1169
Relay_Log_Space: 537
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: c7402f5d-3d08-11e9-9a24-0242ac130002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)
mysql>
12.主库写,观察从库是否同步,需检查从库状况为 Waiting for master to send event。 不要忘记关闭主库锁
主库写:
UNLOCK TABLES;
mysql> create database testdb111;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| replicas_db |
| sys |
| testdb |
| testdb111 |
+--------------------+
7 rows in set (0.00 sec)
mysql>
从库读:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| replicas_db |
| sys |
| testdb |
| testdb111 |
+--------------------+
7 rows in set (0.00 sec)
mysql>
docker-compose.yml配置
mysql主服务器:
version: '2.3'
services:
mysql-master:
image: mysql:8.0
container_name: mysql-master
environment:
- "MYSQL_ROOT_PASSWORD=Difficult_password1234"
- "MYSQL_DATABASE=replicas_db"
links:
- mysql-slave1
- mysql-slave2
ports:
- "3306:3306"
restart: always
hostname: mysql-master
volumes:
- /home/docker/data/dbdata-master:/var/lib/mysql
- /home/docker/data:/usr/local/data
mysql从服务器:
mysql-slave1:
image: mysql:8.0
container_name: mysql-slave1
environment:
- "MYSQL_ROOT_PASSWORD=Difficult_password1234"
- "MYSQL_DATABASE=replicas_db"
ports:
- "3307:3306"
restart: always
hostname: mysql-slave1
volumes:
- /home/docker/data/dbdata-slave1:/var/lib/mysql
- /home/docker/data:/usr/local/data
mysql-slave2:
image: mysql:8.0
container_name: mysql-slave2
environment:
- "MYSQL_ROOT_PASSWORD=Difficult_password1234"
- "MYSQL_DATABASE=replicas_db"
ports:
- "3308:3306"
restart: always
hostname: mysql-slave2
volumes:
- /home/docker/data/dbdata-slave1:/var/lib/mysql
- /home/docker/data:/usr/local/data
修改my.cnf和主从复制,同上,接着第5步继续往下做即可。
另外附上docker-compose的官方文档Compose file version 2 referencedocs.docker.com
简书上有篇中文的写的不错Docker Compose 配置文件详解www.jianshu.com