MySQL延迟从库

延迟从库

控制从库的SQL线程执行速度,二进制日志照常去主库取,但是存放到中继日志之后就延迟执行,如果主库被误操作,这时候对中继日志进行处理,就不用根据全备二进制日志恢复,节省了大部分的时间

设置延迟同步

假设这时候2台数据库正在主从,现在设置延迟同步

mysql>  STOP SLAVE;   -- 先停止同步
mysql> CHANGE MASTER TO MASTER_DELAY = 300;  -- 设置延迟300s,一般设置3-6个小时,单位是s
mysql> START SLAVE; -- 启动同步
mysql> SHOW SLAVE STATUS \G;  -- 查看状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.253.129
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 316
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 316
              Relay_Log_Space: 531
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 129
                  Master_UUID: c5792b98-1be7-11e9-86e3-000c2927db4e
             Master_Info_File: /data/master.info
                    SQL_Delay: 300   --延迟的时间
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

模拟故障

主库

mysql> use test1;
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table t2(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t3(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table t4(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t5(id int);
Query OK, 0 rows affected (0.02 sec)

现在模拟故障 删除表t3

mysql> drop table t3;
Query OK, 0 rows affected (0.01 sec)

假设这时候数据库还在跑,还有数据进来,再创建个表模拟一下

mysql> create table t6(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t7(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t8(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t9(id int);
Query OK, 0 rows affected (0.01 sec)

这时候管理员发现问题了,首先先把从库SQL线程停了,也可以都IO和SQL线程都停了

mysql> STOP SLAVE;  -- 都停了
-- mysql> stop slave sql_thread;  --停止SQL线程方法

通过中继日志relay-log.info获取起始点

[root@localhost data]# cat /data/relay-log.info 
7
./localhost-relay-bin.000002
320
mysql-bin.000004
314
300
0
1


然后找误操作语句

mysql>  SHOW RELAYLOG EVENTS IN 'localhost-relay-bin.000002';
+----------------------------+------+----------------+-----------+-------------+--------------------------------------------------------+
| Log_name                   | Pos  | Event_type     | Server_id | End_log_pos | Info                                                   |
+----------------------------+------+----------------+-----------+-------------+--------------------------------------------------------+
| localhost-relay-bin.000002 |    4 | Format_desc    |       128 |         123 | Server ver: 5.7.24-log, Binlog ver: 4                  |
| localhost-relay-bin.000002 |  123 | Previous_gtids |       128 |         154 |                                                        |
| localhost-relay-bin.000002 |  154 | Rotate         |       129 |           0 | mysql-bin.000004;pos=314                               |
| localhost-relay-bin.000002 |  201 | Format_desc    |       129 |           0 | Server ver: 5.7.24-log, Binlog ver: 4                  |
| localhost-relay-bin.000002 |  320 | Anonymous_Gtid |       129 |         379 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| localhost-relay-bin.000002 |  385 | Query          |       129 |         476 | create database test1                                  |
| localhost-relay-bin.000002 |  482 | Anonymous_Gtid |       129 |         541 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| localhost-relay-bin.000002 |  547 | Query          |       129 |         640 | use `test1`; create table t1(id int)                   |
| localhost-relay-bin.000002 |  646 | Anonymous_Gtid |       129 |         705 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| localhost-relay-bin.000002 |  711 | Query          |       129 |         804 | use `test1`; create table t2(id int)                   |
| localhost-relay-bin.000002 |  810 | Anonymous_Gtid |       129 |         869 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| localhost-relay-bin.000002 |  875 | Query          |       129 |         968 | use `test1`; create table t3(id int)                   |
| localhost-relay-bin.000002 |  974 | Anonymous_Gtid |       129 |        1033 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| localhost-relay-bin.000002 | 1039 | Query          |       129 |        1132 | use `test1`; create table t4(id int)                   |
| localhost-relay-bin.000002 | 1138 | Anonymous_Gtid |       129 |        1197 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| localhost-relay-bin.000002 | 1203 | Query          |       129 |        1296 | use `test1`; create table t5(id int)                   |
| localhost-relay-bin.000002 | 1302 | Anonymous_Gtid |       129 |        1361 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| localhost-relay-bin.000002 | 1367 | Query          |       129 |        1478 | use `test1`; DROP TABLE `t3` /* generated by server */ |
| localhost-relay-bin.000002 | 1484 | Anonymous_Gtid |       129 |        1543 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| localhost-relay-bin.000002 | 1549 | Query          |       129 |        1642 | use `test1`; create table t6(id int)                   |
| localhost-relay-bin.000002 | 1648 | Anonymous_Gtid |       129 |        1707 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| localhost-relay-bin.000002 | 1713 | Query          |       129 |        1806 | use `test1`; create table t7(id int)                   |
| localhost-relay-bin.000002 | 1812 | Anonymous_Gtid |       129 |        1871 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| localhost-relay-bin.000002 | 1877 | Query          |       129 |        1970 | use `test1`; create table t8(id int)                   |
| localhost-relay-bin.000002 | 1976 | Anonymous_Gtid |       129 |        2035 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                   |
| localhost-relay-bin.000002 | 2041 | Query          |       129 |        2134 | use `test1`; create table t9(id int)                   |
+----------------------------+------+----------------+-----------+-------------+--------------------------------------------------------+
26 rows in set (0.01 sec)

通过观察可以看到

所以结束的位置是localhost-relay-bin.000002 的1302,
这时候可以利用mysqlbinlog查看中继日志具体信息

[root@localhost data]# mysqlbinlog  --start-position=320 --stop-position=1302  /data/localhost-relay-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190216  8:16:31 server id 128  end_log_pos 123 CRC32 0xc0c00146 	Start: binlog v 4, server v 5.7.24-log created 190216  8:16:31
# This Format_description_event appears in a relay log and was generated by the slave thread.
# at 201
#190119 12:26:59 server id 129  end_log_pos 0 CRC32 0x562f760f 	Start: binlog v 4, server v 5.7.24-log created 190119 12:26:59
BINLOG '
411DXA+BAAAAdwAAAAAAAAAAAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AQ92L1Y=
'/*!*/;
# at 320
#190119 12:33:00 server id 129  end_log_pos 379 CRC32 0x0392727e 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 385
#190119 12:33:00 server id 129  end_log_pos 476 CRC32 0x2aceb78a 	Query	thread_id=9	exec_time=0	error_code=0
SET TIMESTAMP=1547919180/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database test1
/*!*/;
# at 482
#190119 12:33:40 server id 129  end_log_pos 541 CRC32 0x376c0c9f 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 547
#190119 12:33:40 server id 129  end_log_pos 640 CRC32 0xd0f3a51e 	Query	thread_id=9	exec_time=0	error_code=0
use `test1`/*!*/;
SET TIMESTAMP=1547919220/*!*/;
create table t1(id int)
/*!*/;
# at 646
#190119 12:33:45 server id 129  end_log_pos 705 CRC32 0x828399a7 	Anonymous_GTID	last_committed=3	sequence_number=4	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 711
#190119 12:33:45 server id 129  end_log_pos 804 CRC32 0x2de1e82e 	Query	thread_id=9	exec_time=0	error_code=0
SET TIMESTAMP=1547919225/*!*/;
create table t2(id int)
/*!*/;
# at 810
#190119 12:33:49 server id 129  end_log_pos 869 CRC32 0x02d76ff2 	Anonymous_GTID	last_committed=4	sequence_number=5	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 875
#190119 12:33:49 server id 129  end_log_pos 968 CRC32 0x0dfae9a4 	Query	thread_id=9	exec_time=0	error_code=0
SET TIMESTAMP=1547919229/*!*/;
create table t3(id int)
/*!*/;
# at 974
#190119 12:33:52 server id 129  end_log_pos 1033 CRC32 0xb02cbb37 	Anonymous_GTID	last_committed=5	sequence_number=6	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1039
#190119 12:33:52 server id 129  end_log_pos 1132 CRC32 0x769459c6 	Query	thread_id=9	exec_time=0	error_code=0
SET TIMESTAMP=1547919232/*!*/;
create table t4(id int)
/*!*/;
# at 1138
#190119 12:33:56 server id 129  end_log_pos 1197 CRC32 0x8d300a80 	Anonymous_GTID	last_committed=6	sequence_number=7	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1203
#190119 12:33:56 server id 129  end_log_pos 1296 CRC32 0xc013d53d 	Query	thread_id=9	exec_time=0	error_code=0
SET TIMESTAMP=1547919236/*!*/;
create table t5(id int)
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

确认没问题的时候可以导出这些sql语句

mysqlbinlog  --start-position=320 --stop-position=1302  /data/localhost-relay-bin.000002  > /tmp/inc1.sql

先把故障之前的数据还原

mysql> SET sql_log_bin=0;
mysql> source /tmp/inc1.sql;

在这里插入图片描述
跳过故障的语句进行还原

mysqlbinlog  --start-position=1484    /data/localhost-relay-bin.000002   > /tmp/inc2.sql

还原故障之后的数据

mysql> SET sql_log_bin=0;
mysql> source /tmp/inc2.sql;

在这里插入图片描述
可以看到恢复了表3并且补齐了误操作之后的数据。

这时候如果从库要替代主库,需要清空之前的信息

mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值