docker配置mysql集群_Docker安装与配置mysql集群主从复制环境（七）

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 reference​docs.docker.com

简书上有篇中文的写的不错Docker Compose 配置文件详解​www.jianshu.com