一、简介
主从复制可以将一台主MySQL数据库服务器的数据实时自动复制到一台或多台从MySQL数据库服务器,实际上默认情况下主从复制是异步的。根据配置,可以复制数据库中的所有数据库、选定的数据库甚至选定的表。
MySQL 5.7支持不同的复制方法。传统方法基于复制主库二进制日志中的事件,从库需要主库的二进制日志文件及其中的位置点。而新方法基于全局事务标识符(GTID)是事务性的,从库并不需要去找主库的二进制日志文件及其中的位置点,从而大大简化了许多常见的复制任务。使用GTID进行复制可以确保源和副本之间的一致性,在主库上提交的所有事务都会自动应用到从库上。
官方文档:https://dev.mysql.com/doc/refman/5.7/en/replication.html
以下以传统方法为主,关于基于GTID的主从复制会在后面文章介绍到。以下内容基于MySQL5.7.28。
二、主从复制配置
我们先来简单配置一下主从复制,再来细说原理,这样更加容易理解明白。
主库操作:
1、开启binlog日志
[root@db01 ~]# vim/etc/my.cnf # 在mysqld下添加以下配置
server_id=1
log_bin=/service/data/binlog/mysql-bin
2、主库创建专门用于复制的用户
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '000000';
3、获取主库当前的binlog文件和位置点
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 414 | | | |
+------------------+----------+--------------+------------------+-------------------+
不建议从主数据库的当前状态开始同步,因为可能会造成主从数据不一致,从而导致主从复制错误。
可以从File:mysql-bin.000001;Position:1,即从最开始开始复制。首先得保证binlog日志文件保存着数据库从头到尾的事件,如果数据量大同步耗时久。
建议采用:备份主库恢复至从库,再从备份文件中获取binlog日志文件和位置点。
这里简单演示下,注意简单,因为数据库并没有什么数据:
[root@db01 backup]# mysqldump -uroot -p -A --master-data=2 >/backup/full.sql
从库操作:主从时间要同步
1、修改配置文件
[root@db02 ~]# vim/etc/my.cnf # 在mysqld下添加以下配置
server_id=2 // 注意主库和从库不能相同,还有server_uuid也不能相同
2、恢复数据,获取主库的binlog日志文件和位置点
mysql> source /backup/full.sql;
[root@db02 ~]# grep '\-\- CHANGE MASTER TO' full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=414;
3、配置从库
mysql> change master to
master_host='10.0.0.51',
master_user='repl',
master_password='000000',
master_port=3306,
master_log_file='mysql-bin.000002',
master_log_pos=414;
4、启动从库的复制功能
mysql> start slave;
5、检查从库复制功能状态
mysql> show slave status \G
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
三、主从复制原理
简单的说,主从复制的基础是二进制日志文件(binary log file)。一台MySQL数据库一旦启用二进制日志后,其作为主库 master,它的数据库中所有操作都会以“事件”的方式记录到二进制日志中。
其他数据库作为从库 slave 通过一个 IO 线程与主库的 binlog_dump 线程保持通信,binlog_dump 线程一直监控着binlog 日志文件的状态,有新的日志就返回给从库的 IO 线程。
从库的IO线程接收到主库 binlog 日志文件发生的变化,则会把变化复制到自己的中继日志 relay log 中。
然后从库的一个 SQL 线程会从 relay log 里面读取相关的“事件”执行到自己的数据库中,从而实现从库和主库数据的一致性,也就实现了主从复制。
具体流程如下:
1、从库执行 change master to , 所有信息会被保存到master.info文件中。
2、从库执行 start slave,启动IO和SQL线程。
3、从库IO线程工作,获取master.info文件信息,生成指针(mi)。
4、从库IO线程,连接主库。
5、主库连接层,接收请求,验证用户、权限,并生成binlog_dump线程。
6、从库IO线程和主库binlog_dump线程交互,验证server_id、server_uuid、时间等,从库正式注册到主库中。
7、主库binlog_dump线程一直监控着binlog状态,有新的日志就返回给从库IO线程。
8、从库IO线程通过MI指针中的binlog位置点,向binlog_dump线程请求最新日志。
9、从库IO线程接收主库binlog_dump线程发送的新的日志,mi指针自动更新,并写入master.info中。
10、从库IO线程线程最终会将接收到的binlog信息,写入到relay log中继日志中。
11、从库SQL线程,读取relay-log.info信息,获取到上次已经应用过的relay log的位置点信息,生成一个rli指针,与relay log中继日志中的pos进行对比。
12、如果有新的中继日志生成,就执行到数据库中,执行完成更新rli指针,并写入到relay-log.info中。
注:
master.info:用来存储主库相关的信息,默认位于数据目录内。
relay-log.info:用来记录上次已经应用过的relay log的位置点信息。
relay log:存储接收到的binlog日志,默认位于数据目录内,默认文件名一般为:主机名称-relay-bin.xxxxxx。
四、主从复制状态信息查看
主库:
1、查看线程
mysql> show processlist;
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 10 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 11 | repl | 10.0.0.52:36712 | NULL | Binlog Dump | 1307 | Master has sent all binlog to slave; waiting for more updates | NULL |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
2、查看已经注册的从库
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 2 | | 3306 | 1 | 846602a3-232d-11eb-ad14-000c29e0d787 |
+-----------+------+------+-----------+--------------------------------------+
从库:
mysql> show slave status \G
1、主库相关信息(来自于master_info)
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 704
2、从库relay-log相关信息(relay_info)
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 610
3、relaylog和binlog的对应关系
Relay_Master_Log_File: mysql-bin.000002
Exec_Master_Log_Pos: 704
4、线程状态有关的信息
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
5、过滤复制相关信息
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
6、主从复制的时延(从库同步主库数据的延时)
Seconds_Behind_Master: 0
7、延时从库状态信息
SQL_Delay: 0
SQL_Remaining_Delay: NULL
8、GTID复制相关
Retrieved_Gtid_Set:
Executed_Gtid_Set:
五、主从复制常见故障
有时候我们会遇到IO线程或者SQL线程状态异常,下面来介绍一下遇到这些异常状态的原因和解决办法。
一般通过关注以下信息去获知线程状态有关的信息。
mysql> show slave status \G
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
......
1、IO线程
(1)connecting(建立连接)状态
外部原因: 网络不通、防火墙
内部原因: 用户、密码错误、port、IP错误、主库连接数满了
对于change master to时主库信息填写有误可以使用以下方法:
mysql> stop slave; // 停从库
mysql> reset slave all; // 重置从库配置信息,会忘记已经记录的主库binlog相关信息,还会删除任何现有的relay log并开始一个新的relay log。
mysql> change master to ...... //重新构建主从
mysql> start slave; // 启动从库
(2)NO状态
可能原因:主从之间的server_id和server_uuid重复、搭建时位置点写错了、主库的日志损坏。
解决方案:
第一个:修改server_id和server_uuid使之不重复即可。
第二个:重置从库配置,重新构建主从。
第三个:修复主库日志,重新构建主从。
2、SQL线程
NO状态,可能原因:
(1)relay log损坏
(2)接收到SQL语句无法执行,导致原因:
• 版本差异,参数设定不同,比如:数据类型的差异,SQL_MODE影响
• 要创建的数据库对象,已经存在
• 要删除或修改的对象不存在
• DML语句不符合表定义及约束时.
解决方案:
对于第一点,把硬件配置、版本、参数和SQL_MODE配置一致即可。
因为对象的存在性或者约束冲突导致SQL语句无法执行,原因在于,从库发生写入了,或者经历过宕机导致主从数据不一致。
防范方案:
• 从库设置只读,例如只读只会影响到普通用户,对管理员用户无效,所以不影响主从复制。
方法:set global read_only=1;
• 使用读写分离中间件。
• 高可用结构、半同步、MGR等。
当出现此类问题的解决思路:
1) PT工具校验主从一致性(pt-table-checksum)
2) 通过校验信息进行同步数据 (pt-table-sync)
3) 跳过错误
跳过错误方法:
mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1; // 将同步指针向下移动一个,如果多次不同步,可以重复操作。
mysql> start slave;
参考文章:https://www.jianshu.com/p/6ed2cc292077