欢迎您光临本站,如有问题请及时联系我们。

MySQL主从延迟复制实践及生产故障案例恢复实践

  1.MySQL主从延迟复制介绍


  从MySQL5.6开始支持了主从延迟复制,这个功能主要解决的问题是,当主库有逻辑的数据删除或错误更新后,所有的从库都会进行错误的更新,从而导致所有的数据库数据异常,即使有定时的备份数据可以用于数据恢复,特别是数据库数据量很大时,恢复时间会很长,再恢复期间数据库数据被删或错误数据影响正常的访问体验。而延迟复制就可以较好的解决这个问题。例如,可以设定某一个从库和主库的更新延迟1小时,这样主库数据出问题以后,1个小时以内发现,可以对这个从库进行无害恢复处理,使之依然是正确的完整的数据,省去了数据恢复占用的时间,用户体验有所增加。


  2.MySQL主从延迟复制配置实践


  MySQL5.6版本的延迟复制配置,是通过在Slave上执行以下命令实现的:


  CHANGE MASTER TO MASTER_DELAY = N;


  #读者可在配置延迟从库Change Master时直接加上MASTER_DELAY选项。


  该语句设置Slave数据库延时N秒后,再与主数据库进行数据复制,具体操作为登录到Slave数据库服务器(本文是52) ,然后执行如下命令。


  mysql> stop slave;


  Query OK, 0 rows affected (0.45 sec)


  mysql> CHANGE MASTER TO MASTER_DELAY = 20;


  #这是延迟的核心命令。


  Query OK, 0 rows affected (0.22 sec)


  mysql> start slave;


  Query OK, 0 rows affected (0.15 sec)


  mysql> show slave status\G


  *************************** 1. row ***************************


  …省略若干…


  Slave_IO_Running: Yes


  Slave_SQL_Running: Yes


  …省略若干…


  SQL_Delay: 20


  #这里的数字就是设置的延迟20秒后进行复制。


  SQL_Remaining_Delay: NULL


  #还剩多少秒执行复制。


  Slave_SQL_Running_State: Slavexx to update it


  #SQL线程的状态。


  …省略若干…


  1 row in set (0.09 sec)


  复制状态里常用的三个状态参数为SQL_Delay、SQL_Remaining_Delay、Slave_SQL_Running_State,说明上面已分别注释了。主库插入数据:


  mysql> create database lanlan;


  Query OK, 1 row affected (0.00 sec)


  主库插入完数据1秒以后,从库执行show databases;查看数据是否及时同步了,结果如下:


  mysql> show databases;


  +——————–+


  | Database |


  +——————–+


  | information_schema |


  | alex_python |


  | mysql |


  | performance_schema |


  +——————–+


  在从库上并没有看到在主库上创建的数据库lanlan,此时执行间歇性的执行show slave status\G查看延迟的参数状态如下输出。


  mysql> show slave status\G


  …省略若干…


  SQL_Delay: 20


  SQL_Remaining_Delay: 13


  #剩于13秒执行复制。


  Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event


  …省略若干…


  1 row in set (0.00 sec)


  mysql> show slave status\G


  …省略若干…


  SQL_Delay: 20


  SQL_Remaining_Delay: 9


  #剩于9秒执行复制。


  Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event


  …省略若干…


  1 row in set (0.00 sec)


  mysql> show slave status\G


  …省略若干…


  SQL_Delay: 20


  SQL_Remaining_Delay: NULL


  #复制完成后,没有新数据更新的状态。


  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it


  …省略若干…


  1 row in set (0.00 sec)


  在从库没有更新数据处于延迟复制没到时间期间,查看从库的中继日志。


  [root@db02 data]# pwd


  /application/mysql/data


  [root@db02 data]# mysqlbinlog db02-relay-bin.000002


  SET @@session.lc_time_names=0/*!*/;


  SET @@session.collation_database=DEFAULT/*!*/;


  create database lanlan


  #中继日志确已经有了创建的语句,说明IO线程还是实时在工作的。


  3.MySQL延迟复制原理解析


  MySQL的延迟复制实际上影响的只是SQL线程将数据应用到从数据库,而I/O线程早已经把主库更新的数据写入到了从库的中继日志中,因此,在延迟复制期间即使主库宕机了,从库到了延迟复制的时间,依然会把数据更新到和主库宕机时一致。


  特别提示:其实MySQL的延迟复制的功能早在几年前,老男孩老师就已经用思想实现了这个功能, 并应用于企业生产备份和恢复中了,方法如下:


  1)15.2节已经介绍过的,执行mysql> stop slave sql_thread;把SQL线程停掉,然后进行备份,备份期间主库宕机,但是主库的Binlog依然会及时发到从库,最终从库依然可以恢复到和主库宕机前的状态。


  2)写一个脚本,利用定时任务控制sql_thread的停止和运行,进而库就可以控制实现简单的从库延迟复制功能了,这就是思想的重要性。当然了5.6版本就用软件提供的功能吧,5.6以前的数据库要想实现延迟复制,可以思考下老男孩曾经用过的延迟备份以及延迟复制的思路。


  4.MySQL延迟复制恢复案例实践


  在企业中,我们要根据业务需求给延迟复制指定一个时间段,例如1个小时后进行该从库复制,那么在这一个小时内,如果主库误更新了数据,那么其他的从库也都傻傻地误更新了数据,如何将这个延迟的从库恢复正常没有误更新数据前的完整状态呢?且看下文的实践。


  (1)将从库延迟调整为1小时模拟环境,将从库延迟调整为3600秒;


  mysql> stop slave;


  Query OK, 0 rows affected (0.00 sec)


  mysql> CHANGE MASTER TO MASTER_DELAY = 3600;


  Query OK, 0 rows affected (0.03 sec)


  mysql> start slave;


  Query OK, 0 rows affected (0.08 sec)


  mysql> show slave status\G


  …省略若干…


  SQL_Delay: 3600


  SQL_Remaining_Delay: 2414


  Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event


  …省略若干…


  1 row in set (0.00 sec)


  (2)模拟在主库写入数据每隔5秒写入1个库,就当模拟用户写入数据了。


  [root@db01 ~]# for n in {1..5}


  > do


  > mysql -e “create database oldboy$n”


  > sleep 5


  > done


  提示:Shell脚本知识可参考《跟老男孩学习Linux运维:Shell编程实战》一书。


  (3)模拟人为破坏数据模拟人为破坏数据也可以是不带where的update语句。


  mysql> drop database oldboy5;


  #删除oldboy5数据库,后面就是把这个数据恢复回来,别的数据还得有。


  Query OK, 0 rows affected (0.00 sec)


  mysql> show databases like ‘oldboy%’;


  +——————–+


  | Database (oldboy%) |


  +——————–+


  | oldboy1 |


  | oldboy2 |


  | oldboy3 |


  | oldboy4 |


  +——————–+


  4 rows in set (0.00 sec)


  #此时,所有的从库都已经是坏数据了,只有延迟从库是好的,但是是一小时前的数据。


  (4)停止写库恢复数据当数据库出现误删数据情况时,要想完整恢复数据(特别是update不加条件破坏数据),最好选择对外停止访问措施,需要牺牲用户体验,除非业务可以忍受数据不一致,并且不被二次破坏。从库可以适当继续开放给用户读访问,但是也可能会导致用户读到的数据是坏的数据,需要读者去衡量数据一致性和用户体验的问题。本例使用iptables封堵用户对主库的所有访问。


  [root@db01 ~]# iptables -I INPUT -p tcp –dport 3306 ! -s 172.16.1.51 -j DROP


  #非172.16.1.51禁止访问数据库3306端口,51是主库IP。


  (5)检查Binlog发送和接收是否完成登录主库执行show processlist;对Binlog是否全部发送到该延迟从库进行确认,当然了,也可以登录延迟从库执行show processlist;对从库IO线程是否接收完全部Binlog进行状态查询确认。


  mysql> show processlist;


  +—-+—————————————+—————+


  | 12 | rep | 172.16.1.52:39043 | NULL | Binlog Dump | 709 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |


  +—-+————————————+——————+


  2 rows in set (0.00 sec)


  #上述提示表示主库已经发送完所有Binlog日志到从库了。


  (6)从库暂停主从复制,并检查数据从库上执行stop slave;暂停主从复制,并查看数据库是否同步过来。


  mysql> stop slave;


  mysql> show databases;


  +——————–+


  | Database |


  +——————–+


  | information_schema |


  | alex_python |


  | mysql |


  | performance_schema |


  +——————–+


  4 rows in set (0.00 sec)


  #提示:因为延迟时间还未到,因此数据不会同步到该延迟从库。


  (7)定位恢复数据对应中继日志 根据relay-log.info记录的SQL线程读取relay-log的的位置解析未应用到从库的relay-bin日志。


  [root@db02 data]# pwd


  #进入到中继日志所在的目录。


  /application/mysql/data


  [root@db02 data]# ls -l *relay*


  #查看中继日志相关信息。


  -rw-rw—-. 1 mysql mysql 172 9月 13 17:32 db02-relay-bin.000001


  -rw-rw—-. 1 mysql mysql 993 9月 13 17:37 db02-relay-bin.000002


  -rw-rw—-. 1 mysql mysql 48 9月 13 17:32 db02-relay-bin.index


  -rw-rw—-. 1 mysql mysql 61 9月 13 17:32 relay-log.info


  #SQL线程读取中继日志位置信息。


  [root@db02 data]# cat relay-log.info


  #查看中继日志应用的位置信息。


  7


  ./db02-relay-bin.000002 #SQL线程读取中继日志的文件名信息。


  284 #SQL线程读取中继日志位置点信息。


  oldboy-bin.000024


  309


  3600


  0


  1


  (8)解析需要的中继日志解析SQL线程未解析的全部剩余relay-bin中继日志数据,由于模拟数据量不够大,因此本例里只有db02-relay-bin.000002一个中继日志,实际工作中可能有多个,一并解析到一个指定文件或者分不同的文件存放也可。


  [root@db02 data]# mysqlbinlog –start-position=284 db02-relay-bin.000002 >relay.sql


  #根据上述的relay-log.info的中级日志文件和位置信息进行解析中继日志,此命令的用法前面章节已经讲解过了,此不累述。


  (9)从解析文件中删除问题SQL将破坏数据库数据的SQL语句找到并从已解析的SQL语句中删除,这里就是“drop database oldboy5”。


  [root@db02 data]# egrep “drop database oldboy5” relay.sql


  #检查是否存在误删的SQL语句。


  drop database oldboy5


  [root@db02 data]# sed -i ‘/drop database oldboy5/d’ relay.sql


  #删除,注意别删多了。


  [root@db02 data]# egrep “^drop database oldboy5” relay.sql


  #检查删除结果。


  (10)将处理好的SQL文件恢复到数据库将解析后并处理好的relay.sql数据文件恢复到延迟从库。


  [root@db02 data]# mysql<relay.sql


  #这步就是从停止slave复制开始,根据relay-log.info位置手工将剩下的所有日志数据恢复到数据库中,需要注意就是提前要清理破坏数据库的语句,在恢复。


  [root@db02 data]# mysql -e “show databases like ‘oldboy%’;”


  +——————–+


  | Database (oldboy%) |


  +——————–+


  | oldboy1 |


  | oldboy2 |


  | oldboy3 |


  | oldboy4 |


  | oldboy5 | #被删除的oldboy5数据库已经找回。


  +——————–+


  到此,利用延迟数据库恢复数据完毕,将此库提升为主库(见手工实现主从角色切换章节内容),将VIP指向该“延迟从库”,即新主库提供用户访问,然后,在对其他的破坏的主从数据库进行修复。


来源:本文由E8运维原创撰写,欢迎分享本文,转载请保留出处和链接!