恢复误删除表黑科技之relay log大法

**Generate a full Xtrabackup of master.**

    Today I'm gonna test how to rescue a dropped table from binlog server based on a full Xtrabackup backup set.

 

Check the position on master 

Analyze the relay-bin file to find out the until postion before dropping operation.

 

 

*Step 3: Rescue data.**

Restore the backup on zlm3.

 

 1 [root@zlm2 06:28:44 /data/mysql/mysql3306/data]
 2 #innobackupex --default-file=/data/mysql/mysql3306/my.cnf --host=localhost -uroot -pPassw0rd /data/backup
 3 xtrabackup: recognized server arguments: 
 4 xtrabackup: recognized client arguments: 
 5 180731 06:29:03 innobackupex: Starting the backup operation
 6 ... //Omitted.
 7 
 8 180731 06:29:31 Executing UNLOCK TABLES
 9 180731 06:29:31 All tables unlocked
10 180731 06:29:31 [00] Copying ib_buffer_pool to /data/backup/2018-07-31_06-29-03/ib_buffer_pool
11 180731 06:29:31 [00]        ...done
12 180731 06:29:31 Backup created in directory '/data/backup/2018-07-31_06-29-03/'
13 MySQL binlog position: filename 'mysql-bin.000043', position '190', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229'
14 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/backup-my.cnf
15 180731 06:29:31 [00]        ...done
16 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/xtrabackup_info
17 180731 06:29:31 [00]        ...done
18 xtrabackup: Transaction log of lsn (10141400402) to (10141400411) was copied.
19 180731 06:29:31 completed OK!

Restart instance and check table.

 

1 zlm@192.168.56.100:3306 [sysbench]>show master status;
2 +------------------+----------+--------------+------------------+-------------------------------------------------+
3 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
4 +------------------+----------+--------------+------------------+-------------------------------------------------+
5 | mysql-bin.000098 |      363 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
6 +------------------+----------+--------------+------------------+-------------------------------------------------+
7 1 row in set (0.00 sec)

**Copy these relay-bin files to the proper directory.**

 

Preface

Execute "change master to ... " on zlm2.

 

    Eventually,In order to make the gtid more clear,I executed "reset master" on binlog server(zlm3) and then copyed those necessary binlogs which were transfered from original master.Further more,I changed back the uuid of zlm3.Because I found that there's no need to change it at all.

The precondition is that the master has a up-to-date full backup.(Xtrabackup or mysqldump)

 

6. Execute "change master to ... " with "relay_log_file" and "relay_log_pos".

 

    I was stuck again,faint!T_T...

 

 

 


Copy backup set to zlm2.

 

Framework

 

Step 2: Destroy the table.

    Les't see another way to achieve the goal more simply.

 

 

 

 

 1 [root@zlm2 16:38:09 /data/backup]
 2 #ls -l
 3 total 4
 4 drwxr-x--- 7 root root 4096 Jul 29 16:37 2018-07-29_16-32-33
 5 
 6 [root@zlm2 16:38:12 /data/backup]
 7 #innobackupex -v
 8 xtrabackup: recognized server arguments: --datadir=/var/lib/mysql 
 9 innobackupex version 2.4.12 Linux (x86_64) (revision id: 170eb8c)
10 
11 [root@zlm2 16:39:13 /data/backup]
12 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --apply-log /data/backup/2018-07-29_16-32-33/
13 ... //Omitted.
14 
15 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
16 InnoDB: page_cleaner: 1000ms intended loop took 8812ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
17 InnoDB: FTS optimize thread exiting.
18 InnoDB: Starting shutdown...
19 InnoDB: Shutdown completed; log sequence number 1719676456
20 180729 16:39:39 completed OK!
 1 [root@zlm3 04:11:50 /data/mysql/mysql3306/data]
 2 #cd /data/backup/
 3 
 4 [root@zlm3 04:12:17 /data/backup]
 5 #mysqlbinlog --base64-output=decode-rows relay-bin.000043 > 43.log
 6 
 7 [root@zlm3 04:12:47 /data/backup]
 8 #tail -20 43.log
 9 #180731  6:34:54 server id 1013306  end_log_pos 954224     Delete_rows: table id 222 flags: STMT_END_F
10 # at 954224
11 #180731  6:34:54 server id 1013306  end_log_pos 954251     Xid = 58
12 COMMIT/*!*/;
13 # at 954251
14 #180731  6:35:09 server id 1013306  end_log_pos 954312     GTID    last_committed=2    sequence_number=3    rbr_only=no
15 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730232'/*!*/;
16 # at 954312
17 #180731  6:35:09 server id 1013306  end_log_pos 954436     Query    thread_id=13    exec_time=0    error_code=0
18 use `sysbench`/*!*/;
19 SET TIMESTAMP=1533011709/*!*/;
20 DROP TABLE `sbtest5` /* generated by server */ //Here's the dropping operation.Therefore,the util position we need is "954251" which just below the "COMMIT/*!*/;"
21 /*!*/;
22 # at 954436
23 #180731  6:35:29 server id 1013306  end_log_pos 954479     Rotate to mysql-bin.000044  pos: 4
24 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
25 DELIMITER ;
26 # End of log file
27 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
28 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

*Step 4: **Implement a slave with binlog server.***

4. Reconfigure those binlog files with "relay-bin.xxxxxx" format together with relay-bin.index file.

1 set @@global.gtid_purged='27af30ca-6800-11e8-ad7e-080027de0e0e:1,
2 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694,
3 e00ef9f5-6c4b-11e8-8389-080027de0e0e:1';

2. Restore the backup on new instance and check data is restored normally.

 

 

 1 [root@zlm1 16:32:53 ~]
 2 #cd /data/backup/
 3 
 4 [root@zlm1 16:37:19 /data/backup]
 5 #ls -l
 6 total 4
 7 drwxr-x--- 7 root root 4096 Jul 29 16:32 2018-07-29_16-32-33
 8 
 9 [root@zlm1 16:37:24 /data/backup]
10 #scp -r 2018-07-29_16-32-33 zlm2:/data/backup
11 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
12 ibdata1                                                                                                            100%  100MB  50.0MB/s   00:02
13 ... //Omitted.

Execute start slave sql_thread util clause.

 

Check the table on master.

1 set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';

 

1 zlm@192.168.56.101:3306 [sysbench]>change master to 
2     -> master_host='192.168.56.102',
3     -> master_port=3306,
4     -> master_user='repl',
5     -> master_password='repl4slave',
6     -> master_auto_position=1;
7 Query OK, 0 rows affected, 2 warnings (0.01 sec)
1 [root@zlm3 08:34:15 /data/backup/2018-07-31_06-29-03]
2 #cat xtrabackup_binlog_info
3 mysql-bin.000043    190    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229

 

    Even thought the mysqld process is down on master.I still can get the binlog files on it.How about change the master binlog files into relay log files and apply them on zlm3?Let's have a try.

Preface

    In my previous blogs,I've demonstrated several mothods of how to rescue a dropped table(or truncated table as well).

 

**Startup the MySQL instance on zlm3.**

 1 [root@zlm1 16:32:14 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd /data/backup
 3 180729 16:32:20 innobackupex: Error: extra argument found -pPassw0rd
 4 180729 16:32:20 innobackupex: Error: extra argument found /data/backup
 5 
 6 [root@zlm1 16:32:20 ~]
 7 #innobackupex -v
 8 innobackupex version 2.4.4 Linux (x86_64) (revision id: df58cf2)
 9 
10 [root@zlm1 16:32:26 ~]
11 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
12 180729 16:32:33 innobackupex: Starting the backup operation
13 ...
14 
15 180729 16:32:53 Backup created in directory '/data/backup/2018-07-29_16-32-33'
16 MySQL binlog position: filename 'mysql-bin.000100', position '476', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694'
17 180729 16:32:53 [00] Writing backup-my.cnf
18 180729 16:32:53 [00]        ...done
19 180729 16:32:53 [00] Writing xtrabackup_info
20 180729 16:32:53 [00]        ...done
21 xtrabackup: Transaction log of lsn (1719676169) to (1719676178) was copied.
22 180729 16:32:53 completed OK!
 1 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 2000;
 2 Query OK, 2000 rows affected (0.10 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 3000;
 5 Query OK, 3000 rows affected (0.07 sec)
 6 
 7 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5;
 8 +----------+
 9 | count(*) |
10 +----------+
11 |     5000 |
12 +----------+
13 1 row in set (0.00 sec)
14 
15 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest5;
16 Query OK, 0 rows affected (0.01 sec)
17 
18 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
19 Query OK, 0 rows affected (0.02 sec)
20 
21 (zlm@192.168.1.101 3306)[sysbench]>show master status;
22 +------------------+----------+--------------+------------------+------------------------------------------------+
23 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
24 +------------------+----------+--------------+------------------+------------------------------------------------+
25 | mysql-bin.000044 |      190 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730232 |
26 +------------------+----------+--------------+------------------+------------------------------------------------+
27 1 row in set (0.00 sec)
28 
29 [root@zlm2 06:38:14 ~]
30 #pkill mysqld
31 
32 [root@zlm2 06:38:18 ~]
33 #ps aux|grep mysqld
34 root      4050  0.0  0.0 112640   956 pts/0    R+   06:38   0:00 grep --color=auto mysqld

 

Make sure which binlogs we need and copy them to zlm3.

  • Binlog server act as a master in this experiment.It can prevent data loss such as miss opeartion of dropping tables whenever the master crashes.
  • We can implement as many binlog servers as possible to continuously acquire the binlogs on different masters.
  • If the only purpose is to get back the dropping data,replication filter of "replicate_do_table" is not necessary.
  • The modification of "sever-uuid" is also neglectable operation.Each gtid contains a differten uuid is completely acceptable.It won't influence our replication except for a little bit complex in output.

*5. Copy **those relay logs** to the proper datadir and c*hange the ownership and group of them.**

1 [root@zlm3 16:26:27 /data]
2 #ls -l
3 total 12
4 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
5 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
6 -rw-r----- 1 root  root  410 Jul 29 16:27 mysql-bin.000098
7 -rw-r----- 1 root  root  241 Jul 29 16:27 mysql-bin.000099
8 -rw-r----- 1 root  root  194 Jul 29 16:27 mysql-bin.000100

 

Disguise the binlog server is a fake master.

**Scp the Xtrabackup backup to another server zlm3 with newly initialized instance**

 

 

 1 [root@zlm2 17:12:39 /data/backup/2018-07-29_16-32-33]
 2 #cat xtrabackup_binlog_info
 3 mysql-bin.000100    476    2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
 4 
 5 zlm@192.168.56.101:3306 [sysbench]>reset master;
 6 Query OK, 0 rows affected (0.00 sec)
 7 
 8 zlm@192.168.56.101:3306 [sysbench]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';
 9 Query OK, 0 rows affected (0.00 sec)
10 
11 zlm@192.168.56.101:3306 [sysbench]>start slave io_thread;
12 Query OK, 0 rows affected (0.00 sec)
13 
14 zlm@192.168.56.101:3306 [sysbench]>show slave statusG
15 *************************** 1. row ***************************
16                Slave_IO_State: 
17                   Master_Host: 192.168.56.102
18                   Master_User: repl
19                   Master_Port: 3306
20                 Connect_Retry: 60
21               Master_Log_File: 
22           Read_Master_Log_Pos: 4
23                Relay_Log_File: relay-bin.000001
24                 Relay_Log_Pos: 4
25         Relay_Master_Log_File: 
26              Slave_IO_Running: No
27             Slave_SQL_Running: No
28               Replicate_Do_DB: 
29           Replicate_Ignore_DB: 
30            Replicate_Do_Table: 
31        Replicate_Ignore_Table: 
32       Replicate_Wild_Do_Table: 
33   Replicate_Wild_Ignore_Table: 
34                    Last_Errno: 0
35                    Last_Error: 
36                  Skip_Counter: 0
37           Exec_Master_Log_Pos: 0
38               Relay_Log_Space: 154
39               Until_Condition: None
40                Until_Log_File: 
41                 Until_Log_Pos: 0
42            Master_SSL_Allowed: No
43            Master_SSL_CA_File: 
44            Master_SSL_CA_Path: 
45               Master_SSL_Cert: 
46             Master_SSL_Cipher: 
47                Master_SSL_Key: 
48         Seconds_Behind_Master: NULL
49 Master_SSL_Verify_Server_Cert: No
50                 Last_IO_Errno: 1236
51                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
52                Last_SQL_Errno: 0
53                Last_SQL_Error: 
54   Replicate_Ignore_Server_Ids: 
55              Master_Server_Id: 1023306
56                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e
57              Master_Info_File: /data/mysql/mysql3306/data/master.info
58                     SQL_Delay: 0
59           SQL_Remaining_Delay: NULL
60       Slave_SQL_Running_State: 
61            Master_Retry_Count: 86400
62                   Master_Bind: 
63       Last_IO_Error_Timestamp: 180729 18:00:08
64      Last_SQL_Error_Timestamp: 
65                Master_SSL_Crl: 
66            Master_SSL_Crlpath: 
67            Retrieved_Gtid_Set: 
68             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
69                 Auto_Position: 1
70          Replicate_Rewrite_DB: 
71                  Channel_Name: 
72            Master_TLS_Version: 
73 1 row in set (0.00 sec)

 

    The previous fault of 1236 was due to the wrong configuration of variable "gtid_purged".There were two individual gtids generated by the other masters with different uuid as bellow:

  1. Special technique*

Shutdown the mysqld on master(mimic the MySQL instance down).

 

 1 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 2 Query OK, 0 rows affected (0.06 sec)
 3 
 4 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 5 Query OK, 0 rows affected (0.01 sec)
 6 
 7 zlm@192.168.56.100:3306 [sysbench]>show master status;
 8 +------------------+----------+--------------+------------------+-------------------------------------------------+
 9 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
10 +------------------+----------+--------------+------------------+-------------------------------------------------+
11 | mysql-bin.000100 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
12 +------------------+----------+--------------+------------------+-------------------------------------------------+
13 1 row in set (0.00 sec)

 

 

The simply description of rescuing data with relay log method is shown below:

Start IO_Thread on zlm2.

  1. Destroy***

Start mysqld on binlog server.

 1 [root@zlm3 07:48:10 /data/backup]
 2 #cp relay* /data/mysql/mysql3306/data
 3 
 4 [root@zlm3 07:48:27 /data/backup]
 5 #cd /data/mysql/mysql3306/data
 6 
 7 [root@zlm3 07:48:35 /data/mysql/mysql3306/data]
 8 #ls -l|grep relay
 9 -rw-r----- 1 root  root     954479 Jul 31 07:48 relay-bin.000043 //Notice,the owner and group has been changed.
10 -rw-r----- 1 root  root        209 Jul 31 07:48 relay-bin.000044
11 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_applier.000001
12 -rw-r----- 1 mysql mysql        45 Jul 31 06:55 relay-bin-group_replication_applier.index
13 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_recovery.000001
14 -rw-r----- 1 mysql mysql        46 Jul 31 06:55 relay-bin-group_replication_recovery.index
15 -rw-r--r-- 1 root  root         60 Jul 31 07:48 relay-bin.index
16 
17 [root@zlm3 07:48:40 /data/mysql/mysql3306/data]
18 #chown mysql.mysql relay*
19 
20 [root@zlm3 07:49:45 /data/mysql/mysql3306/data]
21 #ls -l|grep relay
22 -rw-r----- 1 mysql mysql    954479 Jul 31 07:48 relay-bin.000043
23 -rw-r----- 1 mysql mysql       209 Jul 31 07:48 relay-bin.000044
24 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_applier.000001
25 -rw-r----- 1 mysql mysql        45 Jul 31 06:55 relay-bin-group_replication_applier.index
26 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_recovery.000001
27 -rw-r----- 1 mysql mysql        46 Jul 31 06:55 relay-bin-group_replication_recovery.index
28 -rw-r--r-- 1 mysql mysql        60 Jul 31 07:48 relay-bin.index

 

Summary

 1 [root@zlm3 16:25:01 /data]
 2 #mysqlbinlog -R --raw -h192.168.56.100 -urepl -prepl4slave -P3306 --stop-never mysql-bin.000098 &
 3 [1] 4375
 4 mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
 5 
 6 [root@zlm3 16:26:24 /data]
 7 #ls -l
 8 total 4
 9 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
10 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
11 -rw-r----- 1 root  root  363 Jul 29 16:26 mysql-bin.000098
 1 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx';
 2 ERROR 1380 (HY000): Failed initializing relay log position: Could not find first log during relay log initialization
 3 (zlm@192.168.1.102 3306)[(none)]>show master status;
 4 +------------------+----------+--------------+------------------+------------------------------------------------+
 5 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
 6 +------------------+----------+--------------+------------------+------------------------------------------------+
 7 | mysql-bin.000004 |      206 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 |
 8 +------------------+----------+--------------+------------------+------------------------------------------------+
 9 1 row in set (0.00 sec)
10 
11 (zlm@192.168.1.102 3306)[(none)]>reset master;
12 Query OK, 0 rows affected (0.02 sec)
13 
14 (zlm@192.168.1.102 3306)[(none)]>show master status;
15 +------------------+----------+--------------+------------------+-------------------+
16 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
17 +------------------+----------+--------------+------------------+-------------------+
18 | mysql-bin.000001 |      150 |              |                  |                   |
19 +------------------+----------+--------------+------------------+-------------------+
20 1 row in set (0.00 sec)
21 
22 (zlm@192.168.1.102 3306)[(none)]>set @@global.gtid_purged='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229';
23 Query OK, 0 rows affected (0.01 sec)
24 
25 (zlm@192.168.1.102 3306)[(none)]>show master status;
26 +------------------+----------+--------------+------------------+------------------------------------------------+
27 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
28 +------------------+----------+--------------+------------------+------------------------------------------------+
29 | mysql-bin.000002 |      150 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 |
30 +------------------+----------+--------------+------------------+------------------------------------------------+
31 1 row in set (0.00 sec)
32 
33 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx';
34 ERROR 1380 (HY000): Failed initializing relay log position: Could not find first log during relay log initialization
35 
36 (zlm@192.168.1.102 3306)[(none)]>show slave statusG
37 *************************** 1. row ***************************
38                Slave_IO_State: 
39                   Master_Host: xxx
40                   Master_User: test
41                   Master_Port: 3306
42                 Connect_Retry: 60
43               Master_Log_File: 
44           Read_Master_Log_Pos: 4
45                Relay_Log_File: relay-bin.000043
46                 Relay_Log_Pos: 190
47         Relay_Master_Log_File: 
48              Slave_IO_Running: No
49             Slave_SQL_Running: No
50               Replicate_Do_DB: 
51           Replicate_Ignore_DB: 
52            Replicate_Do_Table: 
53        Replicate_Ignore_Table: 
54       Replicate_Wild_Do_Table: 
55   Replicate_Wild_Ignore_Table: 
56                    Last_Errno: 0
57                    Last_Error: 
58                  Skip_Counter: 0
59           Exec_Master_Log_Pos: 0
60               Relay_Log_Space: 0
61               Until_Condition: None
62                Until_Log_File: 
63                 Until_Log_Pos: 0
64            Master_SSL_Allowed: No
65            Master_SSL_CA_File: 
66            Master_SSL_CA_Path: 
67               Master_SSL_Cert: 
68             Master_SSL_Cipher: 
69                Master_SSL_Key: 
70         Seconds_Behind_Master: NULL
71 Master_SSL_Verify_Server_Cert: No
72                 Last_IO_Errno: 0
73                 Last_IO_Error: 
74                Last_SQL_Errno: 0
75                Last_SQL_Error: 
76   Replicate_Ignore_Server_Ids: 
77              Master_Server_Id: 0
78                   Master_UUID: 
79              Master_Info_File: mysql.slave_master_info
80                     SQL_Delay: 0
81           SQL_Remaining_Delay: NULL
82       Slave_SQL_Running_State: 
83            Master_Retry_Count: 86400
84                   Master_Bind: 
85       Last_IO_Error_Timestamp: 
86      Last_SQL_Error_Timestamp: 
87                Master_SSL_Crl: 
88            Master_SSL_Crlpath: 
89            Retrieved_Gtid_Set: //No relay logs was retrieved here.
90             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229
91                 Auto_Position: 0
92          Replicate_Rewrite_DB: 
93                  Channel_Name: 
94            Master_TLS_Version: 
95 1 row in set (0.00 sec)

图片 1

 

 

**Continue to executing some dml operations on the target table and then kill the mysqld.**

 1 [root@zlm2 16:44:41 /data/mysql/mysql3306/logs]
 2 #cd ../data
 3 
 4 [root@zlm2 16:47:40 /data/mysql/mysql3306/data]
 5 #chown -R mysql.mysql *
 6 
 7 [root@zlm2 16:47:49 /data/mysql/mysql3306/data]
 8 #sh /root/mysqld.sh
 9 
10 [root@zlm2 16:47:56 /data/mysql/mysql3306/data]
11 #ps aux|grep mysqld
12 mysql     4514  3.2 17.9 1071804 182316 pts/1  Sl   16:47   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
13 root      4547  0.0  0.0 112640   960 pts/1    R+   16:48   0:00 grep --color=auto mysqld
14 
15 [root@zlm2 16:48:03 /data/mysql/mysql3306/data]
16 #mysql
17 Welcome to the MySQL monitor.  Commands end with ; or g.
18 Your MySQL connection id is 2
19 Server version: 5.7.21-log MySQL Community Server (GPL)
20 
21 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
22 
23 Oracle is a registered trademark of Oracle Corporation and/or its
24 affiliates. Other names may be trademarks of their respective
25 owners.
26 
27 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
28 
29 zlm@192.168.56.101:3306 [(none)]>use sysbench
30 Reading table information for completion of table and column names
31 You can turn off this feature to get a quicker startup with -A
32 
33 Database changed
34 zlm@192.168.56.101:3306 [sysbench]>show tables;
35 +--------------------+
36 | Tables_in_sysbench |
37 +--------------------+
38 | sbtest1            |
39 | sbtest2            |
40 | sbtest3            |
41 | sbtest4            |
42 | sbtest5            |
43 | sbtest6            |
44 +--------------------+
45 6 rows in set (0.00 sec)
46 
47 zlm@192.168.56.101:3306 [sysbench]>select * from sbtest6;
48 +----+---+---+-----+
49 | id | k | c | pad |
50 +----+---+---+-----+
51 |  1 | 1 | a | b   |
52 +----+---+---+-----+
53 1 row in set (0.00 sec)

本文由皇家赌场手机版发布于首页,转载请注明出处:恢复误删除表黑科技之relay log大法

相关阅读