mysql5.7 主主、主从同步配置以及碰到的问题分析
一、环境准备
1.1、相关知识储备
1.1.1、主从复制介绍
Replication enables data from one MySQL database server (the source) to be copied to one or more MySQL database servers (the replicas). Replication is asynchronous by default; replicas do not need to be connected permanently to receive updates from the source. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.
复制使数据可以从一台MySQL数据库服务器（源）复制到一台或多台MySQL数据库服务器（副本）。默认情况下，复制是异步的；副本无需永久连接即可从源接收更新。根据配置，您可以复制数据库中的所有数据库，选定的数据库甚至选定的表。
应用场景：
- 读写分离，通过中间件或者代码方式做负载均衡。由于从服务器可以水平扩展，因此可以将数据库写入动作放在主（源）数据库进行，而读取动作放到一个或者多个从服务器上进行，从而提升数据读取速度（此场景适用于多数的读远大于写的系统中，对于读写较为均衡或者写大于读的系统不适用）。
- 数据安全，由于副本已经有主数据库的近实时备份数据，当主数据库crash时从服务器可以顶上，提升系统的健壮性（一主一从算常态，对于数据安全比较高的的业务跨机房一主N从也不少）。
- 数据分析，大数据量的抽取分析一般都会对数据库性能产生较大的影响，那么我们可以把这个分析动作放到专门的从数据库上面进行操作，从而避免影响主数据库的性能。
- 数据分发，按照不同的业务对源数据进行拆分，例如需要进行数据抽取的数据分析的库或表放到一台从数据库，给供应商或者客户访问的（不涉及写入）数据库单独一台从数据库。这样既避免影响主数据库性能，也提升了数据安全性（所以第三点是第四点的子集）。
1.1.2、为何要使用主从复制
基于实际业务，我们的使用场景有以下方面：
- 读写分离
- 离线计算
- 数据冗余备份
1.1.3、参考文档
一般来说，我觉得除了官方文档，网上任何的文章图表都只能作为灵感来源，而不能作为决定依据。因此我们的参考文档肯定就是mysql的官方文档啦，纯英文看起来比较费劲,搭配谷歌浏览器的翻译食用风味更佳（官方文档上有更为详细的介绍，这里只做抛砖引玉，引入基本的概念和操作，更为详细的使用指南请务必阅读官方文档，你想要的都有）。
翻译后的文档大概长这样：
1.2、环境配置
1.2.1、服务器数据库环境搭建
关于如何在centos7.5上安装mysql请看这篇：Centos7.5安装mysql5.7.23-二进制包方式安装-完美安装,一步到位
1.2.2、搭建好的环境一览
环境：
| 软件环境 | |
|---|---|
| 虚拟机 | VMware Workstation Pro |
| 操作系统 | centos7.5 |
| 数据库 | mysql 5.7.23 |
虚拟化后的各服务器配置以及承担角色：
| 服务器名称 | IP地址 | 配置 | 角色 |
|---|---|---|---|
| master1 | 192.168.3.9 | 操作系统:centos7.5,CPU*1,内存:512M | 源数据库 1，与源数据库2互为主备 |
| master2 | 192.168.3.10 | 操作系统:centos7.5,CPU*1,内存:512M | 源数据库 2，与源数据库1互为主备 |
| slave1 | 192.168.3.11 | 操作系统:centos7.5,CPU*1,内存:512M | 从数据库1，为源数据库1的从数据库 |
| slave2 | 192.168.3.12 | 操作系统:centos7.5,CPU*1,内存:512M | 从数据库2，为源数据库2的从数据库 |
1.2.2、目标数据库结构
我们将搭建一套双主双从的数据库同步结构，完成并测试mysql的主主复制，主从复制，级联复制等功能。数据库结构图：
二、主数据库准备
2.1 master1（192.168.3.9）准备
- 配置service_id
主从网络中每个数据库服务器都必须有其唯一的服务ID（官方文档-service_id），这个ID必须是大于1且小于(2^32)-1的正整数。
# 可以在sql窗口输入
SET GLOBAL server_id = 1;
# 也可以在my.cnf(windows系统为my.ini)文件的[mysqld]栏下配置
[mysqld]
server-id=1
- 打开二进制文件记录bin_log
必须打开二进制日志记录才能正常使用mysql的同步功能。二进制文件具体作用，有哪些配置参数请参考官方文档-innodb参数配置。这里我们使用以下配置：
[mysqld]
server-id=1
#二进制日志文件名前缀
log-bin=mysql-bin
#需要记录日志的数据库，
binlog-do-db=test_db
# 如果需要记录多个，则配置多次本属性
# binlog-do-db=test_db1
# 不记录日志的数据库，同步时不会同步这些表的改动。
binlog-ignore-db=mysql
# 同理，如果需要记录多个，则配置多次本属性
binlog-ignore-db=sys
# 控制二进制文件刷盘的频率和时机
sync_binlog=1
innodb_flush_log_at_trx_commit=1
- 确保在配置文件中没有开启skip_networking
开启skip_networking会导致网络连接失败。 - 创建专门用于同步的用户
建议新建专门的同步账号供从数据库同步数据使用。可以为每个不同的从数据库创建不同的同步账号，也可以使用同一个同步账号给全部的从数据库。这里我们采用创建一个同步账号的方式。
CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
- 确定主数据库的二进制文件名称和最新一条日志位置
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 12987 | test_db | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#把File下面的mysql-bin.000004和Position 下的12987记录下来备用（以实际显示为准）。
- 创建当前需要同步的数据库数据备份并同步至各从数据库服务器
# 创建指定数据库test_db的数据备份
mysqldump -uroot -p --databases test_db --master-data > /home/test_db.sql
# 用scp远程拷贝至从服务器
scp /home/test_db.sql root@192.168.13.10:/home
scp /home/test_db.sql root@192.168.13.11:/home
scp /home/test_db.sql root@192.168.13.12:/home
2.2 master2（192.168.3.10）准备
- 修改配置文件
由于master2和master1互为主备，因此我们master2的作为主数据的相关配置同master1一样，只是将server-id改为2。 - 数据备份
master2不需要进行数据库备份（直接恢复master1的数据即可）。使用命令从备份SQL中恢复数据至master2:
[root@vm ~]# mysql -uroot -p </home/test_db.sql
- 确定主数据库的二进制文件名称和最新一条日志位置
mysql> SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 723798548 | test_db | mysql | |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)
#把File下面的mysql-bin.000002 和Position 下的723798548 记录下来备用（以实际显示为准）。
三、从数据库准备
master1和master2关于主数据库的准备做完之后，我们就有两台mysql主服务器，分别是192.168.3.9(server-id=1)和192.168.3.10(server-id=2)，现在我们开始配置从数据库。从数据库的配置除了server-id以及对应的主服务器的Ip不一样，其它基本一致。
3.1 master1作为master2的从数据库
- 配置service_id
master1的service_id在配置主数据库的时候已经配置过了，这里保持不变即可。
[mysqld]
server-id=1
- 配置同步数据日志记录功能
由于master1既作为master2和slave1主数据库，又作为master2的从数据库，因此我们希望master2的更改也能同步至slave1，这就需要打开mysql的同步数据记录至binlog的功能（默认是不记录的）
log_slave_updates=1。
#这里贴上master1完整的my.cnf配置文件，master2的配置文件只需要修改一下server-id即可。
[mysqld]
port=3306
# ---------------------数据同步相关配置开始
server-id=1
#二进制日志文件名前缀
log-bin=mysql-bin
#需要记录日志的数据库，
binlog-do-db=test_db
# 如果需要记录多个，则配置多次本属性
# binlog-do-db=test_db1
# 不记录日志的数据库，同步时不会同步这些表的改动。
binlog-ignore-db=mysql
# 同理，如果需要记录多个，则配置多次本属性
binlog-ignore-db=sys
# 控制二进制文件刷盘的频率和时机
sync_binlog=1
innodb_flush_log_at_trx_commit=1
#打开同步数据日志记录
log_slave_updates=1
# ---------------------数据同步相关配置结束
character-set-server=utf8
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
#innodb_buffer_pool_size=8M
[mysqld_safe]
log-error=/usr/local/mysql/data/error.log
pid-file=/usr/local/mysql/data/mysql.pid
tmpdir = /tmp
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
- 配置主服务器地址和账户信息
# master1的主服务为master2，因此
# MASTER_HOST='192.168.13.10'，
# MASTER_LOG_FILE为master2的File：mysql-bin.000002,
# MASTER_LOG_POS为master2的Position：723798548
CHANGE MASTER TO MASTER_HOST='192.168.13.10', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=723798548;
- 启动复制线程
mysql> START SLAVE;
- 查看同步状态
Slave_IO_Running和Slave_SQL_Running都为YES的时候说明slave启动成功。
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event #说明IO线程已启动并且等待主数据库发送文件
Master_Host: 192.168.13.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 723798548
Relay_Log_File: vm-relay-bin.000002
Relay_Log_Pos: 591
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes #说明IO线程已启动
Slave_SQL_Running: Yes #说明SQL执行线程已启动
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: 723798548
Relay_Log_Space: 795
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
# 最新的失败日志，IO和SQL执行的都会显示出来
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
#注意这个UUID，有些数据库无法同步就是因为是直接完整复制的源数据库文件，导致UUID没有更改。
# 如果出现UUID相同的情况，删除从数据库的auto.cnf文件再重启数据库即可。
Master_UUID: ad85daaa-dd26-11ea-93e5-000c29c3c030
Master_Info_File: /usr/local/mysql/data/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.02 sec)
3.2 master2作为master1的从数据库
- 配置service_id
master2的service_id在配置主数据库的时候已经配置过了，这里保持不变即可。
[mysqld]
server-id=2
- 配置同步数据日志记录功能
由于master2既作为master1和slave2主数据库，又作为master1的从数据库，因此我们希望master1的更改也能同步至slave2，这就需要打开mysql的同步数据记录至binlog的功能（默认是不记录的）
log_slave_updates=1。
-
数据恢复
master2在作为主数据库的时候已经从master1恢复了数据。 -
配置主服务器地址和账户信息
# master2的主服务为master1，因此
# MASTER_HOST='192.168.13.9'，
# MASTER_LOG_FILE为master2的File：mysql-bin.000004,
# MASTER_LOG_POS为master2的Position：12987
CHANGE MASTER TO MASTER_HOST='192.168.13.9', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=12987 ;
- 启动复制线程
mysql> START SLAVE;
- 查看同步状态
Slave_IO_Running和Slave_SQL_Running都为YES的时候说明slave启动成功。
3.3 slave1作为master1的从数据库
- 配置service_id
#这里贴上slave1完整的my.cnf配置文件，slave2的配置文件只需要修改一下server-id即可。
[mysqld]
port=3306
# ---------------------数据同步相关配置开始
server-id=3
# ---------------------数据同步相关配置结束
character-set-server=utf8
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
#innodb_buffer_pool_size=8M
[mysqld_safe]
log-error=/usr/local/mysql/data/error.log
pid-file=/usr/local/mysql/data/mysql.pid
tmpdir = /tmp
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
- 数据恢复
使用命令从备份SQL中恢复数据至slave1:
[root@vm ~]# mysql -uroot -p </home/test_db.sql
- 配置主服务器地址和账户信息
# slave1的主服务为master1，因此
# MASTER_HOST='192.168.13.9'，
# MASTER_LOG_FILE为master2的File：mysql-bin.000004,
# MASTER_LOG_POS为master2的Position：12987
CHANGE MASTER TO MASTER_HOST='192.168.13.9', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=12987;
- 启动复制线程
mysql> START SLAVE;
- 查看同步状态
Slave_IO_Running和Slave_SQL_Running都为YES的时候说明slave启动成功。
3.1 slave2作为master2的从数据库
- 配置service_id
[mysqld]
server-id=4
- 数据恢复
使用命令从备份SQL中恢复数据至slave2:
[root@vm ~]# mysql -uroot -p </home/test_db.sql
- 配置主服务器地址和账户信息
# slave2的主服务为master2，因此
# MASTER_HOST='192.168.13.10'，
# MASTER_LOG_FILE为master2的File：mysql-bin.000002,
# MASTER_LOG_POS为master2的Position：723798548
CHANGE MASTER TO MASTER_HOST='192.168.13.10', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=723798548;
- 启动复制线程
mysql> START SLAVE;
- 查看同步状态
Slave_IO_Running和Slave_SQL_Running都为YES的时候说明slave启动成功。
四、同步测试
- 单向复制测试
通过在master1的test_db中新建表，进行增删改数据，测试master2、slave1的数据状态，从而可以看出mysql的单向复制效果。 - 级联复制测试
通过在master1的test_db中新建表，进行增删改数据，测试master2、slave2的数据状态，由于数据流向为:master1->master2->slave2,因此可以看出mysql的级联复制效果。 - 双向复制测试
通过在master1的test_db中新建表，进行增删改数据，测试master2的数据状态。在master2的test_db中新建表，进行增删改数据，测试master1的数据状态，进而测试mysql双向复制效果。 - 环状复制测试
双向复制是环状复制的低配版，从双向复制就能体现出mysql的多主多从机制的复制效果了。
五、碰到的问题及解决
- UUID重复
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
这个错误的大概意思就是IO线程停止运行，因为主数据库和从数据库的UUID相同，mysql的主从数据库的UUID必须不相同才能执行同步任务。
那么，UUID是个啥玩意呢？为啥我们明明配置了不同的server-id，咋还整了个UUID相同的异常出来了呢？详情请见：官方文档-service_id。
那么如何解决这个问题呢？如官方文档所描述的，
- Attempt to read and use the UUID written in the file data_dir/auto.cnf (where data_dir is the server’s data directory).
- If data_dir/auto.cnf is not found, generate a new UUID and save it to this file, creating the file if necessary.
UUID是自动生成的，写在data_dir/auto.cnf这个文件里面的，并且我们不能尝试去修改或者写入这个文件。但是当文件不存在的时候，mysql会生成一个新的UUID并将其保存在这个文件中。所以，我们可以删除这个文件，对，就是数据目录下的auto.cnf，删掉重启就完事了。
我们本次的数据目录是：/usr/local/mysql/data/
[root@vm ~]# ls /usr/local/mysql/data/
auto.cnf ib_buffer_pool ib_logfile0 ibtmp1 mysql mysql-bin.000002 mysql.pid relay-log.info sys vm-relay-bin.000002
error.log ibdata1 ib_logfile1 master.info mysql-bin.000001 mysql-bin.index performance_schema ry_city vm-relay-bin.000001 vm-relay-bin.index
删掉auto.cnf，然后重启。
- No query specified
执行 SHOW SLAVE STATUS\G查看同步状态的时候会在状态信息下面出现这个ERROR。
mysql> SHOW SLAVE STATUS\G;
......
......
ERROR:
No query specified
原因是在执行命令：SHOW SLAVE STATUS\G;的时候，多加了一个“;”分号。执行：SHOW SLAVE STATUS\G 就没事了。
- CREATE USER FAILED || grant FAILED || RENAME USER failed 等一系列操作用户授权的失败操作
Error 'Operation RENAME USER failed for 'test13'@'%'' on query. Default database: 'test_db'. Query: 'RENAME USER `test13`@`%` TO `test11`@`%`'
Error 'Operation CREATE USER failed for 'test333'@'%'' on query. Default database: 'test_db'. Query: 'CREATE USER 'test333'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9''
参考: 多源复制遇到CREATE USER FAILED错误 ，同内容文章很多，貌似大家转载引用的时候都不写来源的，所以不确定哪一篇是原创哈，先引一篇看起来像的。
文章大意为：
在多于源数据同步的时候，从数据库上报出了创建用户失败的日志。
MySQL Multi-Source Replication enables a replication slave to receive transactions from multiple sources simultaneously. Multi-source replication does not implement any conflict detection or resolution when applying the transactions, and those tasks are left to the application if required.
在应用事务时，多源复制不会实现任何冲突检测或解决方案，并且如果需要，这些任务将留给应用程序。
首先怀疑是配置了binlog_format=‘STATEMENT’，因为如果是基于行复制（row）的时候从数据库会检查数据所属数据库，如果不在二进制日志所配置的库列表中（通过binlog-do-db、
binlog-ignore-db、eplicate_db和replicate_ignore_db进行配置），则会忽略更改（5.7版本默认是基于行复制）。但是经测试，基于行的复制一样会出现上述错误信息。
Only DML statements can be logged using the row format. DDL statements are always logged as statements, even when binlog_format=ROW. All DDL statements are therefore always filtered according to the rules for statement-based replication. This means that you must select the default database explicitly with a USE statement in order for a DDL statement to be applied.
只能使用行格式记录DML语句。DDL语句始终作为语句记录，即使在binlog_format=ROW时也是如此。因此，所有DDL语句都会通过过滤规则。这意味着您必须使用use语句显式选择默认数据库，才能应用DDL语句。
也就是说明，当我们操作DDL/DCL语句时，必须显示的指定数据库。例如 use mysql，如果不显示地指定数据库，则语句会被同步，一旦从数据库中有重名用户或者其它情况事务执行不成功，就会出现上述错误，并且同步失败。至于什么时候需要指定，哪些语句行复制规则不会过滤，请看：基于语句复制和基于行复制的优缺点
终于有空写写文章了，可以把以前的笔记都整理整理发出来。下次我们有空讨论下主从数据一致性问题？
6088
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
