MySQL延迟从库

た入场券 2022-03-19 10:37 232阅读 0赞

# 延迟从库 #

控制从库的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;

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

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

还原故障之后的数据

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

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

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

mysql> STOP SLAVE;
    mysql> RESET SLAVE ALL;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70]: /images/20220319/ae88c09a5bba4fd1b605497d0b222e46.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0w4MzUzMTEzMjQ_size_16_color_FFFFFF_t_70 1]: /images/20220319/9063da17cf1542c1a42922f3babaded3.png