提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

误删数据恢复 start slave sql_thread until

SQL thread start 数据恢复 slave 误删 until
2023-09-27 14:21:15 时间
 

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

 

 

 

1，针对传统复制：start slave 可以指定sql线程的 end log file and position的：

START SLAVE UNTIL sql_thread master_log_file='binlog.000002',master_log_pos=829090187; 

START SLAVE [SQL_THREAD] UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos 

START SLAVE [SQL_THREAD] UNTIL RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos 

thread_type: IO_THREAD | SQL_THREAD 

2，针对gtid复制：start slave 可以指定sql线程的sql_before_gtids的：

start slave sql_thread until sql_before_gtids='9e912d2b-92f6-11e8-9747-0050568be82c:9791090';

不过UNTIL语法不可以用在IO_THREAD上。

 

 

 # 当获取到一个全新的备份后，进行如下操作：

 stop slave;

 reset slave all;

 reset master;

 change master to master_host='10.10.10.10',master_port=3306,master_user='mysqlsync',master_password='123456', master_log_file='mysql-bin.009044',master_log_pos=517587984;

 start slave io_thread;

 start slave sql_thread until master_log_file='mysql-bin.009161',master_log_pos=224619836;

 

 

案例如下：

 

root@10.10.10.10((none)) > stop slave;reset slave all;reset master;

root@10.10.10.10((none)) >change master to master_host='10.10.10.20',master_port=3306,master_user='mysqlsync',master_password='123456', master_log_file='mysql-bin.000241',master_log_pos=669674886;

root@10.10.10.10((none)) >start slave io_thread;

root@10.10.10.10((none)) >start slave sql_thread until master_log_file='mysql-bin.000246',master_log_pos=228466573;

root@10.10.10.10((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.10.10.20
                  Master_User: mysqlsync
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000250
          Read_Master_Log_Pos: 161937975
               Relay_Log_File: relay-bin.000016
                Relay_Log_Pos: 228466778
        Relay_Master_Log_File: mysql-bin.000246
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 228466573
              Relay_Log_Space: 4479380022
              Until_Condition: Master
               Until_Log_File: mysql-bin.000246
                Until_Log_Pos: 228466573
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
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: 123456
                  Master_UUID: c2c56743-b3ba-11eb-adf3-e4434bdfe564
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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.01 sec)

Wed Aug  4 12:04:30 2021

 

如上：通过shwo slave status\G;观察这5个指标：

# 表示同步完成的一个标志，为yes表示正在同步，为no表示同步完成

Slave_SQL_Running: No


# 这两个指标表示同步主库的io线程同步主库binblog文件到哪个binlog文件了，至少保证Master_Log_File文件的编号大于Until_Log_File文件的编号

Until_Log_File: mysql-bin.000246

Master_Log_File: mysql-bin.000250


# 这两个值要相等，相等表示同步完成的标志

Until_Log_Pos: 228466573

Exec_Master_Log_Pos: 228466573

 

 

 

 

 

 

 

 

 

 

work@hostname((none)) > reset slave all;reset master;SET @@GLOBAL.GTID_PURGED='1751c709-9e64-11ea-855e-e4434b034ad8:1-4301373';
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.13 sec)

Wed Nov  4 15:20:08 2020
work@hostname((none)) > change master to master_host='10.10.10.10',master_port=3306,master_user='sync',master_password='123456',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

Wed Nov  4 15:20:55 2020
work@hostname((none)) > start slave io_thread;
Query OK, 0 rows affected (0.01 sec)

Wed Nov  4 15:21:08 2020
work@hostname((none)) > start slave sql_thread until sql_before_gtids='1751c709-9e64-11ea-855e-e4434b034ad8:4315229';
Query OK, 0 rows affected (0.00 sec)


work@hostname((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.10
                  Master_User: mysqlsync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 442173408
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 4401708
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              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: 441864511
              Relay_Log_Space: 4710798
              Until_Condition: SQL_BEFORE_GTIDS
               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: NULL
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: 177103670
                  Master_UUID: 1751c709-9e64-11ea-855e-e4434b034ad8
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 1751c709-9e64-11ea-855e-e4434b034ad8:4301374-4316055
            Executed_Gtid_Set: 1751c709-9e64-11ea-855e-e4434b034ad8:1-4315228,
eddc13e2-1e6d-11eb-9b37-246e9691b550:1-10
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Wed Nov  4 15:25:18 2020
autopilot@c4-using-glc-db05.bj((none)) >

 

 

 

 

 

 ###########

 

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

相关文章