CentOS7 下 MySQL 主从复制/同步配置操作指导
原文：https://blog.csdn.net/u011415782/article/details/112257192
背景
本文整理了 MySQL 的 Replication 集群部署操作（一主多从 + 多主多从）
可对比 PXC 集群部署，以提高 MySQL 集群配置能力 —— 【CentOS7 下 PXC 集群部署操作指导】
在此整理一番 MySQL 主从配置的操作
感觉每次配置总是参考别人的，有时对问题的描述不够详细，
还是，把自己的操作过程记录下来比较好
也方便后期的补充扩展， 同时也欢迎道友们参考、指摘 …
准备工作：
确保主库的 mysql能够给 外网访问，也就是使用 Navicat 等软件能够连接的上才行（使用宝塔要到端口那里 放行 3306 端口）
主从数据库版本 【最好一致】，或者【从库】版本略大于【主库】
操作环境参考
我使用 VMware 创建了两台虚拟机，作为主从分离的宿主
主库IP： 192.168.80.221
从库IP： 192.168.80.222
linux 系统： centos7.9
数据库版本： mySQL 5.7.32
1
2
3
4
5
6
主从分离原理
【总结】：
> 主服务器把操作记录到 binary log
> 从服务器 执行 "I/O线程"，将 binary log 中的数据同步到 relay log（中继日志中）
> 从服务器 执行 "SQL线程"，读取 relay log ，进行数据同步操作
1
2
3
对于原理的解释，可参考 —— 【Mysql 主从同步的原理】
主从分离配置步骤
一、【Master主库】配置操作 [192.168.80.221]
①. 编辑【主库】配置文件
找到 my.cnf 文件，比如使用宝塔安装，一般会在目录 "/etc/my.conf"
vi /etc/my.cnf
1
启用二进制日志文件
补充或更新如下三个主要配置项
[mysqld]
server-id = 1 #设置 server-id
log-bin=master-bin #开启二进制日志
log-bin-index=master-bin.index #打开二进制日志文件索引
1
2
3
4
【注意】：
当前的操作，一般都是认为在 主从数据要全部一致的情况下操作！
不过，一般实际业务中，可能会涉及到 【指定同步的数据库】，可以考虑配置参数 ："eplicate-wild-do-table"
此处不做赘述，百度好多，也可参考后面步骤： 【三、进行主从数据同步配置-指定同步数据库的一点建议】
②. 重启 mysql 加载配置文件
执行命令：service mysqld restart
重启后，可以在 mysql 的安装目录下看到：
生成了以在配置文件中定义 "log_bin=master-bin" 为开头的文件
连接 mysql (当然，也可是使用 Navicat for MySQL 等数据库连接工具)
mysql -uroot -p password
1
③. 查看日志信息
如果前面配置没问题，这一步其实也可以跳过，只是为了确认信息
查看二进制日志是否开启 : show global variables like '%log%';
查看主节点二进制日志列表: SHOW MASTER LOGS;
查看主节点的 server id: SHOW GLOBAL VARIABLES LIKE '%server%';
④. 在主节点上创建有复制权限的用户
注意参考截图中注释信息 ，可自定义，如 (用户名：repl_moTzxx，密码：201107070）
GRANT REPLICATION SLAVE ON *.* TO 'repl_moTzxx'@'192.168.80.222' IDENTIFIED BY '201107070';
⑤. 刷新权限
flush privileges;
此时，如果条件允许
为了同步前数据一致，建议锁定一下表 : flush tables with read lock;
然后，查看 【Master(主库)】 状态：SHOW MASTER STATUS;
记录二进制文件名 (masterl-bin.000001) 和位置 (601)
(如果有多个二进制文件名，取最后一个的名称和位置！！)
至此，主服务器配置完成！
二、【Slave从库】配置操作 [192.168.80.222]
①. 编辑从节点配置文件
同前面【主库】操作： vim /etc/my.cnf
开启中继日志 relay-log
[mysqld]
server-id=2 #配置 server id
relay-log=slave-relay-log #打开从服务器中继日志文件
relay-log-index=slave-relay-log.index #打开从服务器中继日志文件索引
1
2
3
4
②. 修改完配置文件后，启动或者重启 mysqld 服务
执行命令："service mysqld restart"
③. 打开 mysql 会话，执行同步 SQL语句
首先， 保险起见要再次到【主库】 ,
查看主节点二进制日志列表："SHOW MASTER STATUS;"
然后，在【从库】，
打开 mysql :mysql -uroot -p，执行 同步 SQL 语句
注意： 需要主服务器主机名，登陆凭据，二进制文件(最后一个)的名称和位置
SQL语句：CHANGE MASTER TO MASTER_HOST='192.168.80.221', MASTER_USER='repl_moTzxx', MASTER_PASSWORD='201107070', MASTER_LOG_FILE='master-bin.000008', MASTER_LOG_POS=333;
④. 启动 【Slave(从库)】 同步进程 :
start slave;
⑤. 查看 slave 状态
show slave status\G;
【提示】：
如果，出现问题，那就先停止 【Slave】, 阅读日志，解决后再开启【Slave】
停止语句：STOP SLAVE；
开启语句：START SLAVE；
查看语句：SHOW SLAVE STATUS;
再次开启 Slave 时，如果指定的 Master 没变，可以只运行如下语句：
CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=154;
至此，主从同步配置完成
【拓展】:
如果，当前想停止 主从配置，那就在 【从库】中执行 sql 语句："STOP SLAVE;"
三、进行主从数据同步配置
现在，要进行最重要的数据同步配置了，一切的目的都是为了 数据同步
█▶ 根据实际业务情况，进行配置
【注】: 在此需要分情况操作：
1. 如果此时，【主库】中没有需要同步的数据库 db 时
根据默认配置信息，基本是满足后续操作的；
当在【主库】上创建新的 数据库 db、数据表 table 时，【从库】也会同步创建
2. 如果此时已进行了部分业务，那么【主库】中，是有需要操作的数据库 db 的
此时，要求 【从库】要把 【主库】中的 db 复制过来
方便的话也可以使用 Navicat 等客户端操作
1
2
3
4
5
6
7
以鄙人的操作为例，
当下我需要对 【主库】中的一个数据库 [tp5_pro]做实际的业务处理
所以，需要等待我在【从库】中创建数据库[tp5_pro]，并导入其中的数据后 …
【注意！注意！】:
如果前面对 【主库】做了锁表操作，此时需要： 【 对 Master 解除 table（表）的锁定： "unlock tables;" 】
这时，在【主库】中，对数据库 [tp5_pro]中的数据变动后，到【从库】就会发现，数据已同步操作！
█▶ 指定同步数据库的一点小建议
本人建议，指定所要同步数据库 【只需要在主或者从，其中一个配置就可以了】,可参考 >>> 这篇文章
所以，如果要指定数据库，建议在 【主库】中进行配置
# 不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = sys
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
# 只同步哪些数据库，除此之外，其他不同步 (注意跟上面的配置只使用一种方式即可)
# binlog-do-db = tp5_pro
1
2
3
4
5
6
7
8
9
☞. 【一主多从】 模式 配置
以上内容中，只是介绍了 【一主一从】，最基础的配置指导
而在实际业务处理中，如果想继续配置多台从库，根据上面的操作，进行扩展就好
【重点】 ：
主节点上创建具有复制权限的用户 （注意ip的指定）
从节点注意对应配置项的改动
从节点执行同步 SQL 语句 （注意ip的指定）
注意对【主库】的锁表、解锁操作 ...
配置完成后，正确的参考结果是：更改【主库】中的数据，所有【从库】都会变动！
不过，一主多从基本不受青睐的，毕竟一旦主机挂掉，直接无法写入数据了！建议至少也要配置个 【两主两从】 …
☞. 【多主多从】 模式 配置
既然已经忙活到了这一步，在此再补充一下 —— 【多主多从】模式的配置吧
♦ 需求分析
相对而言，【多主多从】模式是更为合理的 高可用分布式设计模式
毕竟在【一主多从】中，我们的从数据库在数据安全性上并没有发挥到最好，只是为了提供读写分离和查询负载均衡。
当主数据库服务器挂掉了，那么就无法进行写入数据，整个数据库就无法正常工作了
所以，这时就需要【多主多从】的出场了
此处，以最简单的 【两主两从】配置操作为例
♦ 角色分配
在此，我以四台 CentOS7 虚拟机作为 MySQL 数据库的宿主机
主机名 ip 地址 角色
Master1 192.168.80.221 主库 M1，和 M2 互为主备
Slave1 192.168.80.222 从库 S1，是 M1 的从库
Master2 192.168.80.223 主库 M2，和 M1 互为主备
Slave2 192.168.80.224 从库 S2，是 M2 的从库
关系图设计如下：
- 这种双主双从的模型，
- 只要我们对 M1 或者 M2 任意一个主数据库插入数据，其他3个数据库也会作相应的改动。
- 因为 M1 和 M2 是互为主从数据库，所以两个数据库是互相同步的，
- 另外两个数据库是他们的从数据库，写入操作也会更新从数据库。
- 当 M1 挂掉了，可以启动 M2 作为该数据库的主数据库，保证网站的正确运行。
- 同时在正常情况下，M2、S1、S2 都参与查询的负载均衡 ...
1
2
3
4
5
6
此处只介绍 同步配置，对于读写分离的高可用集群部署，请参考 —— 【CentOS7 下实现 MyCat 部署读写分离】
♦ Master/Slave 配置信息
如果是 Master 数据库，注意添加如下两条信息
auto_increment_increment=2 #每次自增2个
auto_increment_offset=1
#起始值为1 如果是Master1,则为1;如果是Master2,则起始值变为2
#这样两个mysql同样是递增2则id不会冲突
1
2
3
4
【说明】：
1). 有没有 "log-bin=mysql-bin" 是服务器作为 Master 还是 Slave 的关键内容
2). "server-id" 是每个 mysql 的唯一 id，四个不同服务器的需要配置成不同的数字
3). "auto_increment_offset=1" #起始值为1 如果是另外一个 master 则起始值变为2,这样两个 mysql 同样是递增 2 ,则 id 不会冲突
如果是 Slave 数据库，则配置文件注意 "server-id" 必须唯一、尽量关闭"log_bin" 参数
按照文章前面介绍的 【主从分离配置步骤】，分别将 M1->S1 ，M2->S2 配置为主从关系
♦ M1/M2 互为主备，配置操作
到达这一步，M1->S1 ，M2->S2 已配置为主从关系，但是 M1 和 M2 之间还是没有关系的
首先，在 M1和 M2 的数据库配置文件 "my.cnf"中，添加参数：log-slave-updates
#在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates
1
2
对于参数"log-slave-updates"的解释，请阅读 —— 【MySQL 配置参数 -- logs-slave-updates】
重启 M1、M2 的 mysql 服务，使得配置文件生效：service mysqld restart
此处展示一下，我对各个数据库的配置参数：
▶ 进行 M1 为主，M2 和 S1 为从的配置操作
按照前面的步骤，进行一下 以 M1为主，M2 为从的主从配置操作
首先，是在 M1 中的执行语句操作如下：
mysql> show master status;
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
| master-bin.000003 | 154 | | mysql,test,sys,information_schema,performance_schema | |
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_moTzxx'@'192.168.80.223' IDENTIFIED BY '201107070';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
然后，在 M2 中的操作，执行语句如下：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.80.221', MASTER_USER='repl_moTzxx', MASTER_PASSWORD='201107070', MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.221
Master_User: repl_moTzxx
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 611
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 778
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
注意，此时我并没有对已配好的 Slave1 进行变动，正常情况下，查看信息依然是同步 Master1 的状态！
▶ 进行 M2 为主，M1和S2 为从的配置操作
首先，是在 M2 中的执行语句操作如下：
mysql> show master status;
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
| master-bin.000003 | 611 | | mysql,test,sys,information_schema,performance_schema | |
+-------------------+----------+--------------+------------------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_moTzxx'@'192.168.80.221' IDENTIFIED BY '201107070';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
然后，在 M1 中的操作，执行语句如下：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.80.223', MASTER_USER='repl_moTzxx', MASTER_PASSWORD='201107070', MASTER_LOG_FILE='master-bin.000003', MASTER_LOG_POS=611;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.223
Master_User: repl_moTzxx
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 1068
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
注意，此时我并没有对已配好的 Slave2 变动，正常情况下，查看信息依然是同步 Master2 的状态！
♦ 测试配置成果
测试之前，我建议确认下 M1、S1、M2、S2 的 slave 状态是否都为 Yes，否则，需要回溯问题所在，正确配置！
mysql> show slave status\G;
*************************** 1. row ***************************
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1
2
3
4
5
分析可知，此时对 M1 和 M2 中任意一个的数据进行了操作变化，其他三个的数据应该都会同步！
在进行数据库同步操作时，如果前面对【主库】做了锁定，记得要将【主库】解锁！"unlock tables;"
此时
方便起见，直接使用 Navicat for MySQL 工具，进行连接操作即可
对 M1，进行插入、更新数据后，会发现：M2、S1、S2 都会同步变化
当然， 对 M2，进行插入、更新数据后会发现：M1、S1、S2 都会同步变化
只有对 S1和S2 进行数据操作时，
会发现，其他主库不会同步
毕竟它们只是作为 从库的存在！
【提示1】：在真实的项目中，不应该对从数据库（slave）做写入操作，这样会破坏数据的一致性！（测试而已）
【拓展】：
最近，涉猎知识中，根据有经验的道友提醒，
可以通过 mycat 来实现读写分离
"mycat 连接多个数据库，数据源只需要连接 mycat"
毕竟，对于开发人员而言:
不需要根据不同业务来选择不同的库（不便于动态变化）
此处，提供一下我在 mycat中的配置演示
配置后的效果便是：
如果是读操作，会从 M2/S1/S2 中读取
如果是写操作，会写入 M1，当 M1宕机时，会转到 M2 中进行写操作
【提示2】： 有此需求的建议移步姊妹篇 —— 【CentOS7 下使用 MyCat 实现 MySQL 读写分离/主从切换】
综上，即为 最简单的 【双主双从】配置步骤，进行扩展配置即可形成【多主多从】 …
☺•☹ MySQL 主从分离实际应用
首先声明下，毕竟鄙人只是一个 PHPer，不可能有DBA那样充足的见识！吼吼吼 ~~~
▶. ThinkPHP5.1 中的分布式配置
因为我常用的开发框架为 ThinkPHP5.1，那么一般需要的配置信息可参考如下：
//------------- 主从分离配置参考----------------------------------
'hostname' => '192.168.80.221,192.168.80.222',
'database' => 'tp5_pro',
'username' => 'root',
'password' => ['MT123456','TM123456'],
'prefix' => 'tp5_',//前缀而已,不要太在意
'hostport' => '',
'deploy' => 1,
'rw_separate' => true,
//-------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
▶. 数据库多节点集群部署
毕竟，"Replication 主从分离" 的集群思维，难以绕过 "【数据同步延迟】" 的问题
对于重要数据，比如订单、购物券、支付信息这里数据
此时，建议考虑使用 PXC 集群部署
正在摸索中 …
附录
①. 操作建议
在进行数据库同步操作时，尽量先将【主库】锁定，配置【从库】完毕，记得再将【主库】解锁！
②. MySQL 用户操作指令
查看 MySQL 数据库中所有用户
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
查看数据库中具体某个用户的权限
show grants for 'repl_moTzxx'@'192.168.80.224'
删除一个用户 drop user 'repl_moTzxx'@'192.168.80.224'
③. Slave_IO_Running: No 的一种情况
一旦出现这种问题，最好要去查看 mysql 日志进行排查
我操作过程中遇到一种情况
因为，我使用是虚拟机直接克隆而来，
就会有 "server-uuid" 相同而导致无法操作主从分离配置
网友有的建议直接更改一下
但是我测试是不可以的，
还是直接卸载重装了一下 MySQL ，一路顺利 YES !
④. 有时需要关闭 slave 线程
实际操作过程中，有时会有如下提示信息：
"ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first."
这说明当前正在运行着一个 Slave I/O 线程，最直接的办法就是先关闭 ：stop slave;
参考文章
【mysql复制--主从复制配置】
【mysql 主从复制–启动操作start slave, stop slave】
【Mycat中间件实现一主一从和双主双从的读写分离】
————————————————
版权声明：本文为CSDN博主「moTzxx」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u011415782/article/details/112257192
