本文使用docker实现mysql的一主一从架构
主从同步原理:
- 在主库上把数据更改记录到二进制日志中(Binary Log)中,这些记录称为二进制日志事件。
- 从库通过IO线程将主库上的日志复制到自己的中继日志(Relay Log)中。
- 从库通过SQL线程读取中继日志中的事件,将其重放到自己数据上。
运行mysql容器
如:
# 具体映射地址根据自己实际情况进行调整
docker run -p 3306:3306 --name mysql-master -v d:\Docker\mysql\conf.d:/etc/mysql/conf.d -v d:\Docker\logs:/var/log -v d:\Docker\mysql\data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6
docker run -p 3307:3306 --name mysql-slave -v d:\Docker\mysql\conf.d:/etc/mysql/conf.d -v d:\Docker\logs:/var/log -v d:\Docker\mysql\data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6
配置同步
在主数据库上配置如下:
[mysqld]
server-id=100
log-bin=mysql-bin
确保server-id
全局唯一,配置完后记得重启主数据库服务。
接着在主库创建用于同步数据的账号:
mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在从数据库上配置如下:
[mysqld]
server_id=101
log-bin=mysql-slave-bin
relay_log=edu-mysql-relay-bin
replicate-ignore-db=test_db2
replicate-ignore-db=test_db1
确保server-id
全局唯一,replicate-ignore-db
表示无需从mater同步到slave的数据库,多个数据库可写多行,配置完后记得重启slave数据库。
mysql> show slave status;
Empty set (0.00 sec)
链接主从数据库:
进入从数据库,然后执行:
change master to master_host='172.18.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos=120, master_connect_retry=30;
master_host
为主数据库的IP,可通过docker inspect mysql-master
查看;master_user
和master_password
为前面创建的用于主从同步数据的账号和密码;master_log_file
要与主数据库中通过执行show master status;
显示的File
字段一致;master_log_pos
要与主数据库中通过执行show master status;
显示的Position
字段一致;master_connect_retry
如果连接失败,重试的时间间隔,单位是秒,默认是60秒
此时,在slave数据库执行,如下命令:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.18.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: edu-mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
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: 120
Relay_Log_Space: 120
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: NULL
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: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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
1 row in set (0.00 sec)
正常情况下,Slave_IO_Running
和 Slave_SQL_Running
都是No,因为还没开启主从复制。
使用start slave
开启主从复制过程,然后再次查询主从同步状态show slave status \G;
。
mysql> start slave;
Query OK, 0 rows affected (0.08 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 120
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000002
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: 120
Relay_Log_Space: 460
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: 100
Master_UUID: 78d1589a-7981-11ea-8082-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 the slave I/O thread to update it
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
1 row in set (0.00 sec)
Slave_IO_Running
和 Slave_SQL_Running
都是Yes,说明已经配置正确了,可以进行主从同步了。
出现Slave_IO_Running
或Slave_SQL_Running
不为Yes的情况,需要确认下两个容器间是否连通(在slave数据库ping一下即可测试是否连通),确认下master_log_file
和master_log_pos
等参数是否配置正确。
对于在开启主从同步前,主数据库已经存在的数据,是不会自动同步到从库的,需要手动备份到从库