• Post author:
  • Post category:mysql
  • Post comments:0评论

一、简介

  主从复制可以将一台主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

发表回复

验证码: + 31 = 37