前言
环境 : CentOS 7.7 丶Docker 19.03.4丶MySQL 8.0.18
本教程仅用于对
MySQL主从搭建方案进行说明示例,本文只针对通过binlog日志文件进行数据同步的方案,对其他复制方案不做讨论,文中提及相关知识参考自MySQL官方文档 :
环境准备
-
服务器准备
本文使用两台
CentOS 7.7服务器进行测试
| 节点 | hostname | IP |
|---|---|---|
Master |
node1 | 172.16.146.38 |
Slaver |
node2 | 172.16.146.39 |
-
多节点部署
在两个服务器主机分别部署
MySQL,本文仅用于说明主从搭建示例,对MySQL部署在此不过多阐述,详情请参考 :
主从复制
-
修改配置文件
--------------修改 my.cnf 文件---------------- # Master 节点 my.cnf 文件修改 server-id=38 # 保证局域网内唯一,一般为ip后缀 log-bin=mysql-bin # 开启二进制日志功能,可以随便取（关键） ## Slaver 节点 my.cnf 文件修改 server-id=39 # 保证局域网内唯一,一般为ip后缀 log-bin=mysql-slave-bin # 开启二进制日志功能,以备Slave作为其它Slave的Master时使用 relay_log=edu-mysql-relay-bin # relay_log配置中继日志docker restart [容器ID] # 重启MySQL容器 -
创建用户
## 进入 Master 容器 docker exec -it [容器ID] bash ## 连接 MySQL,密码账号需自行确认 mysql -uroot -ppassword ## 创建用户,同步账号尽量采用非root账号 CREATE USER 'slave'@'%' IDENTIFIED BY 'password'; ## 用户授权 GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'; ## 修改密码插件(MySQL 8.0 默认密码插件为 caching_sha2_password,需修改mysql_native_password ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; FLUSH PRIVILEGES;
-
配置连接
## 在 Master节点执行SQL语句 SHOW MASTER STATUS;结果如下 :
+------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 155 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)File和Position用于标识当前日志记录文件及记录位置,所以在此过程中需保证Master节点不做任何操作,否则将会引起状态变化,从而导致File和Position值发生改变## 在 Slaver 节点执行,指定Master节点,配置主从同步 CHANGE MASTER TO MASTER_HOST='[Master节点IP]', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS= 155, MASTER_CONNECT_RETRY=30;命令说明 :
MASTER_HOST : Master 节点IP地址
MASTER_PORT : Master 节点端口号,指定是容器的端口号(这里是3306)
MASTER_USER : 用于同步的用户(这里是上文创建的用户slave)
MASTER_PASSWORD :用于同步的密码
MASTER_LOG_FILE : 指定Slave从哪个日志文件开始复制数据
MASTER_LOG_POS : 指定从哪个位置开始读(这里是上文提及的Position)
MASTER_CONNECT_RETRY : 连接失败时,重试时间间隔,默认60(单位 :
秒) -
开启复制
START SLAVE; -
查看状态
SHOW SLAVE STATUS \G;结果如下 :*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.146.38 Master_User: slave Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 155 Relay_Log_File: edu-mysql-relay-bin.000002 Relay_Log_Pos: 322 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: 155 Relay_Log_Space: 534 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: 3306 Master_UUID: ee2d4bbe-006b-11ea-a27a-0242ac110002 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 Network_Namespace: 1 row in set (0.00 sec) ERROR: No query specified
查看
Slave_IO_Running和Slave_SQL_Running状态是否为Yes,若为No则表示主从复制未开启,若为Connecting请检查Master端口是否可以ping通,排查是否为防火墙未开放指定端口
主主复制
因为这里都为
Master节点,所以这里暂把原来的Master节点叫Master-one,把Slaver节点叫`Master-two
-
修改配置文件
## 配置 Master-one auto_increment_increment=2 # 步进值auto_imcrement,一般有n台主MySQL就填n auto_increment_offset=1 # 起始值,一般填第n台主MySQL,此时为第一台主MySQL ## 配置 Master-two auto_increment_increment=2 # 步进值auto_imcrement,一般有n台主MySQL就填n auto_increment_offset=2 # 起始值,一般填第n台主MySQL,此时为第二台主MySQL配置完成,重启
MySQL容器
-
创建用户
## 进入 Master 容器 docker exec -it [容器ID] bash ## 连接 MySQL,密码账号需自行确认 mysql -uroot -ppassword ## 创建用户,同步账号尽量采用非root账号 CREATE USER 'slave'@'%' IDENTIFIED BY 'password'; ## 用户授权 GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'; ## 修改密码插件(MySQL 8.0 默认密码插件为 caching_sha2_password,需修改mysql_native_password ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; FLUSH PRIVILEGES; -
配置连接
## 在 Master-two 节点执行 SHOW MASTER STATUS;结果如下 :+------------------+----------+--------------+------------------+--------------------+ | File | Position| Binlog_Do_DB| Binlog_Ignore_DB|Executed_Gtid_Set| +------------------+----------+--------------+------------------+-------------------+ | mysql-slave-bin.000002 | 1180 | | | | +------------------+----------+--------------+------------------+--------------------+ 1 row in set (0.00 sec)## 在 Master-one 节点执行,指定Master节点,配置主从同步 CHANGE MASTER TO MASTER_HOST='[Master-two节点IP]', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-slave-bin.000012', MASTER_LOG_POS= 1180, MASTER_CONNECT_RETRY=30;
-
开启复制
## 开启同步,在 Master-one 节点执行 START SLAVE; -
查看状态
SHOW SLAVE STATUS \G;结果如下 :*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.146.39 Master_User: slave Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mysql-slave-bin.000012 Read_Master_Log_Pos: 2347 Relay_Log_File: 7c9e945c6c27-relay-bin.000002 Relay_Log_Pos: 521 Relay_Master_Log_File: mysql-slave-bin.000012 Slave_IO_Running: Yes 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: 1008 Last_Error: Error 'Can't drop database 'test'; database doesn't exist' on query. Default database: 'test'. Query: 'drop database test' Skip_Counter: 0 Exec_Master_Log_Pos: 2161 Relay_Log_Space: 922 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: 1008 Last_SQL_Error: Error 'Can't drop database 'test'; database doesn't exist' on query. Default database: 'test'. Query: 'drop database test' Replicate_Ignore_Server_Ids: Master_Server_Id: 39 Master_UUID: af712e6d-02da-11ea-a2f3-0242ac110002 Master_Info_File: mysql.slave_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: 191111 04:25:15 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 Network_Namespace: 1 row in set (0.00 sec) ERROR: No query specified -
问题排查
## 若出现数据不一致导致同步报错,执行如下操作,必要的时候需要进行锁表操作 ## 锁定主数据库,只允许读取不允许写入加锁,保证主从数据一致 FLUSH TABLES WITH READ LOCK; ## 查看主节点 binlog 读取文件及位置 SHOW MASTER STATUS; ---------以下在从节点执行---------- ## 关闭从节点数据同步 STOP SLAVE; ## 重置主从复制配置 RESET SLAVE; ## 配置连接主节点 CHANGE MASTER TO MASTER_HOST='[Master-two节点IP]', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-slave-bin.000012', MASTER_LOG_POS= 1180, MASTER_CONNECT_RETRY=30; ## 开启主从同步 START SLAVE; ## 查看主从同步状态 SHOW SLAVE STATUS \G; ## 若同步状态OK, 释放锁 UNLOCK TABLES;
双主多从
-
创建账号
## 进入 Master 容器 docker exec -it [容器ID] bash ## 连接 MySQL,密码账号需自行确认 mysql -uroot -ppassword ## 创建用户,同步账号尽量采用非root账号 CREATE USER 'slave'@'%' IDENTIFIED BY 'password'; ## 用户授权 GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'; ## 修改密码插件(MySQL 8.0 默认密码插件为 caching_sha2_password,需修改mysql_native_password ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'password'; FLUSH PRIVILEGES; -
配置连接
## 查看主节点 binlog 读取文件及位置 SHOW MASTER STATUS; ## 配置连接主节点 CHANGE MASTER TO MASTER_HOST='[Slaver节点IP]', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-slave-bin.000012', MASTER_LOG_POS= 1180, MASTER_CONNECT_RETRY=30; -
开启复制
## 开启主从同步 START SLAVE; ## 查看主从同步状态 SHOW SLAVE STATUS \G; -
问题处理
## 在双主多从模式下,从库仅连接了一个主库,在另一个主库进行数据操作时,从库不会同步过来,所以需要在双主节点的 my.cnf 文件中添加如下: log-slave-updates=on ## 保存并重启双主节点 docker restart [容器ID]至此,本教程结束,文中所述三种方式都是基于
binlog日志文件进行数据同步,故数据同步会有延迟,此方式适用于对数据一致性要求不高的数据备份需求场景
