mysql主从复制docker,使用Docker快速搭建MySQL主从复制环境

MySQL常见复制模式

MySQL的常见复制模式(假设有DB1,DB2,DB3三个MySQL实例):

主从复制 DB1 -> DB2

主主复制 DB1 DB2

链式复制 DB1 -> DB2 -> DB3

环形复制 DB1 -> DB2 -> DB3 -> DB1

生产环境常见主从复制，这是最稳健的一种方式；为了切换方便也可选择主主模式，但要注意主主复制必须确保在任何时刻只有一个数据库是master写入状态，否则可能导致数据异常。

链式和环形复制在生产环境很少使用，主要的缺点是随着节点的增加整个复制系统的稳健性会下降。

对于各种复制模式，主从复制是基础。本文记录自己使用Docker快速搭建MySQL主从复制环境的过程，用于平时的学习和试验。

主从配置文件

master配置文件/home/mysql/etc/master/master.cnf

123456789101112131415161718192021

[mysqld]

server_id = 1

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

default-storage-engine=INNODB

#Optimize omit

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin = /var/lib/mysql/binlog

log_bin_trust_function_creators=1

binlog_format = ROW

expire_logs_days = 99

sync_binlog = 0

slow-query-log=1

slow-query-log-file=/var/log/mysql/slow-queries.log

long_query_time = 3

log-queries-not-using-indexes

slave配置文件/home/mysql/etc/slave/slave.cnf

12345678910111213141516171819202122232425

[mysqld]

server_id = 2

character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

default-storage-engine=INNODB

#Optimize omit

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin = /var/lib/mysql/binlog

log_bin_trust_function_creators=1

binlog_format = ROW

expire_logs_days = 99

sync_binlog = 0

relay_log=slave-relay-bin

log-slave-updates=1

slave-skip-errors=all

slow-query-log=1

slow-query-log-file=/var/log/mysql/slow-queries.log

long_query_time = 3

log-queries-not-using-indexes

启动容器,配置主从复制

创建数据目录：

12345

mkdir -p /home/mysql/data/master

mkdir -p /home/mysql/data/slave

MASTER_DIR=/home/mysql/data/master

SLAVE_DIR=/home/mysql/data/slave

启动MySQL容器：

1234567891011121314

docker run --name mysql-master \

-v /home/mysql/etc/master:/etc/mysql/conf.d \

-v /home/mysql/data/master:/var/lib/mysql \

-e MYSQL_ROOT_PASSWORD=root \

-d \

mysql:5.7

docker run --name mysql-slave \

-v /home/mysql/etc/slave:/etc/mysql/conf.d \

-v /home/mysql/data/slave:/var/lib/mysql \

-e MYSQL_ROOT_PASSWORD=root \

--link mysql-master:master \

-d mysql:5.7

Master上创建复制用户：

1234

docker exec -it mysql-master mysql -p

CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

查看Master节点 Binlog状态：

12345678

show master status\G

*************************** 1. row ***************************

File: binlog.000008

Position: 595

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

在Slave节点上配置复制：

123456789101112

docker exec -it mysql-slave mysql -p

CHANGE MASTER TO \

MASTER_HOST='master',\

MASTER_PORT=3306,\

MASTER_USER='repl',\

MASTER_PASSWORD='repl',\

MASTER_LOG_FILE='binlog.000008',\

MASTER_LOG_POS=595;

start slave;

查看Salve状态：

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960

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: binlog.000008

Read_Master_Log_Pos: 595

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 317

Relay_Master_Log_File: binlog.000008

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: 595

Relay_Log_Space: 524

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: 7a5a452f-fca9-11e6-be18-0242ac110002

Master_Info_File: /var/lib/mysql/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:

1 row in set (0.00 sec)