Posts tagged ‘show slave status’

MySQL 5.6 GTID模式下手工删除日志导致备库数据丢失

我们在测试 5.6 GTID 的时候,发现了一个导致主备数据丢失的场景。特别提醒一下使用 MySQL 5.6 并启用了 GTID 的各位,以避免这种情况发生。同时也简单介绍了 GTID 的实现原理。

场景描述

有一台 MySQL 实例 A ,启用了 GTID 。由于 MySQL 服务器空间吃紧,我们手工将主库的所有的 binlog 以及 binlog 的 index 文件都删除掉,并启动了这个 MySQL 实例。此后,本来连在该 MySQL 实例上的备库虽然 show slave status 状态都是正常的,但是却无法获得主库上的任何更新了。备库上的 show slave status 如下:

GTID 是什么?

首先,简单描述一下 GTID 。

GTID 是 MySQL 5.6 新引入了的概念,它是由 UUID 和事务 ID 组成。这样全球所有的服务器的事务都可以用 GTID 来表示。下面就是一个 GTID :

9300dd57-51da-11e3-989d-3cd92bee36a8 表示的是一台服务器 A 的 UUID ,这个是全球唯一的。 1 表示这个服务器 A 的第一个事务。
引入 GTID 以后, MySQL 就更灵活了:

  • Slave 在搭建复制的时候,简单的设置 auto_position=1 ,不用去找 MASTER_LOG_FILE 和 MASTER_LOG_POS 这种坑爹的“二进制文件的字节偏移位置”了。
  • 在环形复制或者一主多从的架构下,节点一旦损坏,其他节点之间复制的架构重新搭建起来能够非常简单的完成。

GTID 实现原理

我们这里也简单描述一下 GTID 的实现原理。
MySQL 在主库上会把它自己执行过的所有事务的 GTID 都记录下来: gtid_executed 。 gtid_executed 是一个 GTID 的集合,不管是客户自己提交的还是 SQL 线程执行过的数据变更事务,都会被记录在这里。你可以通过 show global variables 查看:

然后,当备库需要搭建复制的时候,有两种方式:

  • auto_position=0 ,需要指定 MASTER_LOG_FILE 和 MASTER_LOG_POS 。
  • auto_position=1 。

auto_position=0 时,很简单根据指定的 binlog 文件和偏移量, Master 将 binlog 中的各个数据变更事务发送给备库就好了。备库接收到对应的事务,判断是否本机发起的 ( 通过数据变更事务记录的 server_id 来判断 ) ,如果不是本机发起的,就直接执行。

在 auto_position=1 时,备库将自己的 gtid_executed 的事务集合传给 Master , Master 找到第一个包含有非备库 gtid_executed 事务集中数据变更的 binlog ,将 binlog 中的各个 event 发送给备库。这样备库首先判断是否本机发起的,然后判断发送过来的各个数据变更事务是否在本机执行过。没有执行过的事务都需要在本地执行一遍。

问题分析

我们还是看备库上的 slave 状态:

我们注意到 Retrieved_Gtid_Set 比 Executed_Gtid_Set 还要少。 Retrieved_Gtid_Set 记录的是 IO thread 从主库拿到的事务集合, Executed_Gtid_Set 记录的是本机已经执行的事务集合。这里有一个很奇怪的想象,本机已经执行的事务集合比 IO thread 从主库拿到的事务集合还要大,而且它只是备库。
回想一下,我们之前做的操作:手工删除了 binlog 日志和 binlog index 文件,然后启动了 MySQL 。这个操作在没有启动 GTID 的 MySQL 上,我们也做过类似的事情,备库并没有复制丢失的问题。

这里之所以数据丢失的原因就很清晰了:

我们手工删除了 binlog 以后,数据库会自动生成 binlog 。但是在 Master 中,它重新从 9300dd57-51da-11e3-989d-3cd92bee36a8:1 第一个事务开始计数。从另外一个侧面来说,已经执行完的 GTID 集合, MySQL 并没有单独保存,而是通过 Binlog 来获得的。

备库已经执行完了 9300dd57-51da-11e3-989d-3cd92bee36a8 :1 - 28123 这些事务。所以当 SQL thread 拿到从主库复制过来的变更事务时,发现这些事务都是它已经执行过的事务,那么它就不会再重复执行,从而导致对应的这些事务都会被丢失掉。

根据前面的描述,我们尽量不要在数据库之外去做一些事情。比如:手工删除 binlog ,我们其实可以用更好的办法:“ purge master logs ”。就算我们确实需要在手工删除,并启动数据库,需要同时将 auto.cnf 清理掉。这样主库会生成新的 UUID ,备库发现是新的事务就会执行这个变更了。

mysql show slave status

 



1. 概览

做了一个列表,简单注释了一下各个字段的意思,如下:

字段名 注释 可能取值 取值示例
Slave_IO_State slave状态的文字描述 slave线程状态 Waiting for master to send event
Master_Host master的IP或主机名 172.20.164.67
Master_User 连接master使用的用户名 repl
Master_Port 连接master使用的密码 3306
Connect_Retry 重新连接MySQL的重试间隔时间,单位秒 60
Master_Log_File IO thread读取到的binlog日志文件 Master_Log_File,Read_Master_Log_Pos;Relay_Log_File,Relay_Log_Pos;Relay_Master_Log_File,Exec_Master_Log_Pos的具体位置 mysql-bin.001896
Read_Master_Log_Pos IO thread读取到的binlog日志文件位置 226029100
Relay_Log_File slave 在本地缓存的relay 日志的文件名 mysql-relay-bin.002014
Relay_Log_Pos slave 在本地缓存的relay 日志的文件位置 87341525
Relay_Master_Log_File SQL thread 执行到master的binlog文件名 mysql-bin.001896
Slave_IO_Running IO thread是否正常运行 Yes
Slave_SQL_Running SQL thread是否正常运行 Yes
Replicate_Do_DB slave上需要执行的schema
Replicate_Ignore_DB slave上需要忽略的schema
Replicate_Do_Table slave上需要执行的table
Replicate_Ignore_Table slave上需要忽略的table
Replicate_Wild_Do_Table slave上需要执行的table正则表达式
Replicate_Wild_Ignore_Table slave上需要忽略的table正则表达式
Last_Errno 上一次出错的错误号 0
Last_Error 上一次出错的错误信息
Skip_Counter 还剩下的忽略event次数 0
Exec_Master_Log_Pos SQL thread 执行到master的binlog文件位置 226029100
Relay_Log_Space relay log占用的空间大小 87341723
Until_Condition 复制until条件,在stop slave,start slave(不带until)或server重启的时候会自动重置 None
Until_Log_File 复制停止的文件名
Until_Log_Pos 复制停止的文件位置 0
Master_SSL_Allowed 是否使用SSL连接master No
Master_SSL_CA_File ssl agent文件ca-cert.pem的文件名 /etc/mysql/newcerts/ca-cert.pem
Master_SSL_CA_Path ssl agent文件ca-cert.pem的路径名
Master_SSL_Cert ssl 授权文件 /etc/mysql/newcerts/client-cert.pem
Master_SSL_Cipher ssl 加密算法
Master_SSL_Key ssl 密钥文件 /etc/mysql/newcerts/client-key.pem
Seconds_Behind_Master SQL thread相对master的延迟时间 0
Master_SSL_Verify_Server_Cert 是否检查master的授权文件 No
Last_IO_Errno IO thread的上一次出错的错误号 0
Last_IO_Error IO thread的上一次出错的错误信息
Last_SQL_Errno SQL thread的上一次出错的错误号 0
Last_SQL_Error SQL thread的上一次出错的错误信息

  • IO thread, SQL thread状态变更
  • Master_Log_File,Read_Master_Log_Pos;Relay_Log_File,Relay_Log_Pos;Relay_Master_Log_File,Exec_Master_Log_Pos的具体位置
  • Replicate_Do, Replicate_Ignore 讨论
  • mysql ssl连接
  • replication文件 master.info, relay.info和连接信息解析
  • Seconds_Behind_Master 精确性
  • replication until和复制延迟控制
  • skip counter忽略event计数和精确性本文只涉及第一和第二个议题

2. IO thread, SQL thread状态变更

  1. IO线程状态变更,对应show slave status的Slave_IO_State字段
名称 状态值 解释
wait_master Waiting for master update
connect_master Connecting to master
check_master Checking master version
register_slave Registering slave on master
request_binlog Requesting binlog dump
request_wait_reconnect Waiting to reconnect after a failed binlog dump request
request_reconnecting Reconnecting after a failed binlog dump request
wait_event Waiting for master to send event
queue_to_relay_log Queueing master event to the relay log
read_wait_reconnect Waiting to reconnect after a failed master event read
read_reconnecting Reconnecting after a failed master event read
wait_relay_space Waiting for the slave SQL thread to free enough relay log space
wait_slave_mutex Waiting for slave mutex on exit

状态变更图如下:
io_thread 状态变更
代码请参考sql/slave.cc的handle_slave_io函数

  1. SQL线程状态变更,对应服务器上SQL线程的State字段,通过show processlist查看
名称 状态值 解释
wait_relay_event Waiting for the next event in relay log
read_relay Reading event from the relay log
wait_io_thread Has read all relay log; waiting for the slave I/O thread to update it
make_temp_file Making temp file
wait_slave_mutex Waiting for slave mutex on exit

状态变更图如下:

sql_thread 状态变更

代码请参考sql/slave.cc的handle_slave_sql函数

3. Master_Log_File,Read_Master_Log_Pos;Relay_Log_File,Relay_Log_Pos;Relay_Master_Log_File,Exec_Master_Log_Pos的具体位置

  1. 定义
  • Master_Log_File,Read_Master_Log_Pos 记录了IO thread读到的当前master binlog文件和位置,对应master的binlog文件和位置。
  • Relay_Log_File,Relay_Log_Pos记录了SQL thread执行到relay log的那个文件和位置,对应的是slave上的relay log文件和位置。
  • Relay_Master_Log_File,Exec_Master_Log_Pos记录的是SQL thread执行到master binlog的文件和位置,对应的master上binlog的文件和位置。
  1. 日志文件介绍需要明确这几个值的意思,我们首先需要了解binlog日志文件和relay log日志文件的具体结构。binlog的文件格式可以参考 Mats Kindah和Lars Thalman的binlog API文档。binlog的event可以参考我之前的blog和binlog events 介绍
    • binlog数据文件示例binlog文件和relay log文件都可以用mysqlbinlg工具来打开。下图是一个mysqlbinlog解析普通的binlog文件出来的文本文件:我们这里主要是row方式的binlog。

      注意:binlog的event语句开始位置就是二进制binlog文件的字节偏移位置。而且根据上一个event的end_log_pos可以找到下一个event开始的位置,如上图所示。

  • relay log数据文件示例binlog文件和relay log文件都可以用mysqlbinlg工具来打开。下图是一个mysqlbinlog解析普通的relay log文件出来的文本文件:relay log和binlog记录方式基本相同,最大的不同就是end_log_pos记录的是master的binlog文件中event的位置,而不是relay log自己event的位置。如图所示,上一个event的end_log_pos和下一个relay log event开始的位置不一样。

为什么需要这样设置?原因很简单,就是为了方便找到master binlog的位置,在slave上,记录relay log 下一个event的开始偏移意义不大,但是如果记录了master binlog的偏移量,我们就可以在SQL thread中明确我们执行到master的某个binlog的哪个位置了。那么是哪个binlog列。我们找到relay log的最前面。

如图所示,每个relay log开头都有这么一个rotate event,也就是当前master的binlog文件名。引用一下Automated master failover的一页PPT如下。

这里列出了Read_Master_Log_Pos,Relay_Log_Pos,Exec_Master_Log_Pos 的具体位置。

  • IO thread 把所有从master读到的binlog记录到本地的binlog中,所以relay log的最后一个event的end log_pos就是Read_Master_Log_Pos
  • SQL thread 按照transaction来执行,所以Exec_Master_Log_Pos对应relay log中最后一个事务event的end_log_pos,这个位置对应的是master的binlog的位置。
  • Relay_Log_Pos 记录的是SQL thread执行的event在relay log中结束位置,这个才是relay log的偏移量。

4. 参考资料

mysql reference IO 线程状态

mysql reference SQL 线程状态

mysql reference show slave status

binlog events

binlog API

Automated master failover