show slave status

造成延迟的可能原因

这部分是我总结现有的我知道的原因：

• 大事物延迟 延迟略为2*执行时间 状态为:reading event from the relay log
• 大表DDL延迟 延迟略为1*执行时间 状态为:altering table
• 长期未提交的事物延迟，会造成延迟的瞬时增加
• 表上没有主键或者唯一键 状态为:system lock 或者 reading event from the relay log
• innodb层锁造成延迟 状态为:system lock 或者 reading event from the relay log
• 从库参数设置如sync_binlog，sync_relay_log，innodb_flush_log_at_trx_commit等参数

这些原因都是我遇到过的。接下来我想分析一下从库system lock形成的原因。

状态报告中要检查的关键字段是：

Slave_IO_State：从库的当前状态。有关更多信息，请参见第8.14.4节“复制从属I / O线程状态”和 第8.14.5节“复制从属SQL线程状态”。

Slave_IO_Running：用于表示读取主库二进制日志的IO线程是否正在运行。通常为Yes，除非尚未开始复制或使用明确停止了复制，即执行了stop slave。

Slave_SQL_Running：用于表示执行中继日志事件的SQL线程是否正在运行。与IO线程一样，通常应为Yes。

Last_IO_Error， Last_SQL_Error：处理中继日志时IO和SQL线程注册的最后错误。理想情况下，这些应该为空白，表示没有错误。

Seconds_Behind_Master：从属SQL线程落后于处理主二进制日志的秒数。较高的数字（或增加的数字）可能表明从服务器无法及时处理来自主机的事件。Seconds_Behind_Master通常将0的值，解释为从库已跟上了主库，但是在某些情况下，这并非完全正确。例如，如果主服务器和从服务器之间的网络连接断开但从服务器的IO线程尚未注意到这一点（即slave_net_timeout尚未过去），则可能发生这种情况。瞬态值的Seconds_Behind_Master也可能无法准确反映情况。当从属SQL线程追上IO时，Seconds_Behind_Master显示0；否则显示0。但是当从属IO线程仍在排队一个新事件时，Seconds_Behind_Master可能会显示一个较大的值，直到SQL线程完成执行新事件为止。当事件具有旧时间戳时，这种情况尤其可能发生；在这种情况下，如果您SHOW SLAVE STATUS在较短的时间内执行了几次，则可能会看到此值在0到较大的值之间来回反复变化。

复制详细进度

几对字段提供有关从库同步主库的二进制日志读取事件并在中继日志中处理事件的进度信息：

（Master_Log_file， Read_Master_Log_Pos）：主库二进制日志中的坐标，指示从IO线程已从主库二进制日志读取事件的距离。仅仅表示复制主库二进制日志的位置，通常IO线程不会出现性能瓶颈。

（Relay_Master_Log_File， Exec_Master_Log_Pos）：主库二进制日志中的坐标。指示从SQL线程已执行接受到的二进制日志事件的距离。表示从库真正同步到主库的位置。通常出现性能瓶颈。

（Relay_Log_File， Relay_Log_Pos）：从属中继日志中的坐标，指示从属SQL线程已执行中继日志的距离。这些对应于前面的坐标，但是以从属中继日志坐标而不是主二进制日志坐标表示。仅仅表示从库执行中继日志的位置。该位置对应于主库的（Relay_Master_Log_File， Exec_Master_Log_Pos）

一旦建立好主从复制关系后就不要在从库上执行任何dml和ddl操作，包括创建用户也不行。

那么万一在从库上执行了dml或者ddl操作了，会有何影响，以及如何恢复？

slave同步状态中出现Slave_SQL_Running: No ，即slave不同步！
解决办法：
第一种方法：忽略错误后，继续同步。
该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况（下面均为在slave机器上的操作）
mysql> stop slave;
mysql> set global sql_slave_skip_counter =1;   //表示跳过一步错误，后面的数字可变；或者在my.cnf里添加slave-skip-errors = all（上面已在配置中添加）
mysql> start slave;
mysql> show slave statusG      //查看：
第二种方法：重新做主从，完全同步
该方法适用于主从库数据相差较大，或者要求数据完全统一的情况
1--master主库上操作
mysql> flush tables with read lock;  //进行锁表，防止数据写入。注意该处是锁定为只读状态，语句不区分大小写
#mysqldump --lock-all-tables --all-databases --flush-logs --master-data=2 > /root/allsql.sql   //主库完全备份(如果是指定库同步，就备份指定库)，注意数据库备份一定要定期进行，确保数据万无一失
mysql> show master status;      //查看master状态，注意log file和pos节点，slave同步会用到
# scp mysql.bak.sql root@192.168.1.102:/tmp/        //把备份文件传到slave从库机器，进行数据恢复
2--slave从库操作
mysql> stop slave;
mysql> source /tmp/mysql.bak.sql
mysql> change master to master_host = '192.168.1.101', master_user = 'slave', master_port=3306.......；
mysql> start slave;
mysql> show slave statusG 
.......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
此种方法中最为关键主要有两步：
①主服务器上锁表做完全备份，并滚动日志；
②从服务器上进行半道恢复.

5）slave中继日志relay-log损坏？
什么是中继日志？
relay-log存放在从服务器上，从服务器将主服务器的二进制日志文件拷贝到自己的主机上放在中继日志中，然后调用SQL线程按照拷中继日志文件中的二进制日志文件执行以便就可达到数据的同步 。
如何中继日志避免：
mysql 5.6版本后，在my.cnf文件中开启relay_log_recover=1即可避免。

6）slave连接超时且重新连接频繁
若有多少slave，且没有设置server_id或两个slave设置相同的server_id，将有可能会出现服务器的ID冲突。这种情况下，其中一台slave可能会频繁超时或丢失后重新连接序列。
所以一定要确保每台slave及master在my.cnf中都要设置不一样的server_id。

7）主库与从库使用不同的存储引擎造成不同步

8）从库同步时，提示表不存在
错误：Last_Error: Error executing row event: 'Table 'test.t1' doesn't exist'
解决方法：在从库重建这张表。

9）max_allowed_packet设置过小导致slave报错
max_allowed_packet默认是16M，主从库的max_allowed_packet值和备库上的不匹配。
在这情况下，主库可能会记录一个备库认为过大的包。当备库获取到该二进制日志事件时，可能会碰到各种问题，如无限报错和重试、中继日志损坏等。
具体表现：
从库的Slave_IO_Thread死掉了，查看后，出现以下错误提示：
Got a packet bigger than 'max_allowed_packet' bytes
很明显是由于max_allowed_packet的设置太小导致的，然后查检主从库上的设置，主库的设置大于从库，因为max_allowed_packet是动态参数，先调整从库上的max_allowed_packet 与主库相同，重新单独启动I/O线程就正常了。
原理说明：binlog的事件以RBR格式记录，且当前的事件长度大于了从库的max_allowed_packet, 导致无法Slave IO不能正常读取master binlog event.

10）在master上删除一条记录时出现的故障
在master上删除一条记录后，slave上因找不到这条记录而报错。
解决方法：
由于主库上已经对这条语句进行了删除操作，故可以跳过。
在这种情况下，说明主从同步可能数据会有不一致的情况发生，所以需要使用pt-table-checksum进行数据库一致性比对。
（参考：mysql主从同步(3)-percona-toolkit工具（数据一致性监测、延迟监控）使用梳理）

11）在master更新一条记录，而slave却找不到。
主从数据不致时，master有某条记录，但在salve上没有这条记录，若在master上进行更新这条记录，则在slave中可能报错。
解决方法：
   1--根据从库发生异常的位置，查主库上的二进制日志。
   2--根据主库二进制日志信息，找到更新后的整条记录。
   3--在从库上执行在主库上找到的记录信息，进行insert操作。
   4--跳过这条语句，再同步slave。
   5--使用pt-table-checksum查看主从库表数据否一致

##################################################

 

在MySQL的主从环境中，我们可以通过在slave上执行show slave status来查看slave的一些状态信息，其中有一个比较重要的参数Seconds_Behind_Master。那么你是否明白它的真正含义以及它是怎么计算的呢？

 

        在之前我一直误以为Seconds_Behind_Master是表示slave比master落后多少，如果这个值为0的表示主从已经处于一致了（在非同步模式下，现在官方最多也只在5.5中增加了半同步复制）。但是最近我终于认识到之前的错误理解。首先我们需要明白的一点：Seconds_Behind_Master表示slave上SQL thread与IO thread之间的延迟，我们都知道在MySQL的复制环境中，slave先从master上将binlog拉取到本地（通过IO thread），然后通过SQL thread将binlog重放，而Seconds_Behind_Master表示本地relaylog中未被执行完的那部分的差值。手册上的定义：

 

In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

 

所以如果slave拉取到本地的relaylog（实际上就是binlog，只是在slave上习惯称呼relaylog而已）都执行完，此时通过show slave status看到的会是0，那么Seconds_Behind_Master的值为0是否表示主从已经处于一致了呢?答案几乎是否定的！为什么几乎是否定的？因为绝大部分的情况下复制都是异步的，异步就意味着master上的binlog不是实时的发送到slave上，所以即使Seconds_Behind_Master的值为0依然不能肯定主从处于一致，这也是我之前强调非同步复制的原因（现在已经有公司在做同步复制了，比如网易自己实现了VSR（Virtual Synchronized Replication 由于同步复制性能较差，所以网易再实现同步复制的同时还打了group commit的补丁）。所以如果我们要以这个参数来估计主从延迟多久的话至少得在一个比较好的网络环境中，这样才能保证几乎master上的binlog都已经发送到slave上。

 

        上面解释了Seconds_Behind_Master这个值的真正含义，那么它的值到底是怎么计算出来的呢？实际上在binlog中每个binlog events都会附上执行时的timestamp，所以在在确定Seconds_Behind_Master的值时MySQL是通过比较当前系统的时间戳与当前SQL thread正在执行的binlog event的上的时间戳做比较，这个差值就是Seconds_Behind_Master的值。

 

[root@yayun mysql]# mysqlbinlog mysql-bin.000005 | tail -n 20
COMMIT/*!*/;
# at 58936
#140413 21:25:53 server id 1  end_log_pos 59004         Query   thread_id=84    exec_time=0     error_code=0
SET TIMESTAMP=1397395553/*!*/;
BEGIN
/*!*/;
# at 59004
#140413 21:25:53 server id 1  end_log_pos 59098         Query   thread_id=84    exec_time=0     error_code=0
SET TIMESTAMP=1397395553/*!*/;
delete from yayun.t1 where id=5
/*!*/;
# at 59098
#140413 21:25:53 server id 1  end_log_pos 59125         Xid = 1756
COMMIT/*!*/;
# at 59125
#140413 21:50:26 server id 1  end_log_pos 59144         Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@yayun mysql]# 

 

也许你会有疑问那要是两台服务器之间的时钟不一致怎么办？确实会存在这种情况，那么此时这个值的可靠性就不大了，手册上对此也进行了说明：

 

This time difference computation works even if the master and slave do not have identical clock times, provided that the difference,
computed when the slave I/O thread starts, remains constant from then on. Any changes—including NTP updates—can lead to clock
skews that can make calculation of Seconds_Behind_Master less reliable

 

Seconds_Behind_Master的值除了是非负数之外还可能是NULL，它是由如下几种情况导致的：SQL thread没运行/IO thread没运行/slave没有连接到master。

 

接下来再简单介绍一下异步复制/半同步复制之间的区别。

 

异步复制：   master上的操作记录binlog的同时不关心binlog是否已经被slave接收。

 

半同步复制：master上的操作记录binlog的同时会关心binlog是否被slave接受。但是由于它的处理逻辑问题可能丢一个事务，如下图所示：

 

 

这样的处理流程存在一个问题，当存储引擎提交（storage commit）后，此时如果master挂了那么会存在主从不一致，对于这个问题orczhou好像自己对源码进行了修改更改storage commit的顺序来达到一个增强的半同步复制。

 

对Seconds_Behind_Master就解释到这里，希望对大家有一点帮助。