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

Posted by

 

Preface

 

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

  •     full mysqldump backup + binlog on
    master(master was normally running)
  •     full Xtrabackup backup + binlog on
    master
    (master was normally
    running)**
  •     full Xtrabackup backup + binlog on
    binlog server(
     binlog server had acquired binlogs
    before master crashed.)

 

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

 

Procedure

 

***Step

  1. Destroy***

 

Check
the table on master.

 1 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 2 ERROR 2006 (HY000): MySQL server has gone away
 3 No connection. Trying to reconnect...
 4 Connection id:    4
 5 Current database: sysbench
 6 
 7 +--------------------+
 8 | Tables_in_sysbench |
 9 +--------------------+
10 | sbtest1            |
11 | sbtest2            |
12 | sbtest3            |
13 | sbtest4            |
14 | sbtest5            |
15 +--------------------+
16 5 rows in set (0.00 sec)
17 
18 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5;
19 +----------+
20 | count(*) |
21 +----------+
22 |    10000 |
23 +----------+
24 1 row in set (0.01 sec)

 

**Generate
a full Xtrabackup of master.**

 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!

 

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

 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

 

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

1 [root@zlm2 06:43:42 ~]
2 #scp -r /data/backup/2018-07-31_06-29-03/ zlm3:/data/backup
3 root@zlm3's password: 
4 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
5 ibdata1                                                                                                            100%  100MB  50.0MB/s   00:02    
6 plugin.ibd                                                                                                         100%   96KB  96.0KB/s   00:00    
7 servers.ibd                                                                                                        100%   96KB  96.0KB/s   00:00
8 ... //Omitted.

 

**Step 2.
Rescue***


 

Restore the
backup on zlm3.

 1 [root@zlm3 06:47:52 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --apply-log /data/backup/2018-07-31_06-29-03/
 3 ... //Omitted.
 4 
 5 [root@zlm3 06:46:39 ~]
 6 #cd /data/mysql/mysql3306/data/
 7 
 8 [root@zlm3 06:46:44 /data/mysql/mysql3306/data]
 9 #ls -l
10 total 409716
11 -rw-r----- 1 mysql mysql        56 Jul 27 11:15 auto.cnf
12 -rw-r----- 1 mysql mysql     19677 Jul 27 11:25 error.log
13 -rw-r----- 1 mysql mysql      2005 Jul 27 11:25 ib_buffer_pool
14 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:25 ibdata1
15 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:25 ib_logfile0
16 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:14 ib_logfile1
17 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:14 ib_logfile2
18 drwxr-x--- 2 mysql mysql      4096 Jul 27 11:15 mysql
19 drwxr-x--- 2 mysql mysql      8192 Jul 27 11:15 performance_schema
20 -rw-r----- 1 mysql mysql       276 Jul 27 11:18 relay-bin.000003
21 -rw-r----- 1 mysql mysql      2771 Jul 27 11:25 relay-bin.000004
22 -rw-r----- 1 mysql mysql       292 Jul 27 11:25 relay-bin.000005
23 -rw-r----- 1 mysql mysql       454 Jul 27 11:25 relay-bin.000006
24 -rw-r----- 1 mysql mysql       344 Jul 27 11:25 relay-bin.000007
25 -rw-r----- 1 mysql mysql       169 Jul 27 11:25 relay-bin-group_replication_applier.000001
26 -rw-r----- 1 mysql mysql        45 Jul 27 11:15 relay-bin-group_replication_applier.index
27 -rw-r----- 1 mysql mysql       169 Jul 27 11:25 relay-bin-group_replication_recovery.000001
28 -rw-r----- 1 mysql mysql        46 Jul 27 11:15 relay-bin-group_replication_recovery.index
29 -rw-r----- 1 mysql mysql        95 Jul 27 11:25 relay-bin.index
30 -rw-r----- 1 mysql mysql       334 Jul 27 11:25 slow.log
31 drwxr-x--- 2 mysql mysql      8192 Jul 27 11:15 sys
32 drwxr-x--- 2 mysql mysql      4096 Jul 27 11:15 sysbench
33 -rw-r----- 1 mysql mysql        24 Jul 27 11:15 xtrabackup_binlog_pos_innodb
34 -rw-r----- 1 mysql mysql       587 Jul 27 11:15 xtrabackup_info
35 -rw-r----- 1 mysql mysql         1 Jul 27 11:15 xtrabackup_master_key_id
36 
37 [root@zlm3 06:46:45 /data/mysql/mysql3306/data]
38 #rm -rf *
39 
40 [root@zlm3 06:46:50 /data/mysql/mysql3306/data]
41 #ps aux|grep mysqld
42 root      3913  0.0  0.0 112640   960 pts/1    R+   06:50   0:00 grep --color=auto mysqld
43 
44 [root@zlm3 06:51:00 ~]
45 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-31_06-29-03/
46 ... //Omitted.
47 
48 180731 06:51:36 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
49 180731 06:51:36 [01]        ...done
50 180731 06:51:36 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
51 180731 06:51:36 [01]        ...done
52 180731 06:51:36 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
53 180731 06:51:36 [01]        ...done
54 180731 06:51:36 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
55 180731 06:51:36 [01]        ...done
56 180731 06:51:36 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
57 180731 06:51:36 [01]        ...done
58 180731 06:51:36 completed OK!
59 
60 [root@zlm3 06:50:14 /data/mysql/mysql3306/data]
61 #ls -l
62 total 421936
63 -rw-r----- 1 root root      1017 Jul 31 06:51 ib_buffer_pool
64 -rw-r----- 1 root root 104857600 Jul 31 06:51 ibdata1
65 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile0
66 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile1
67 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile2
68 -rw-r----- 1 root root  12582912 Jul 31 06:51 ibtmp1
69 drwxr-x--- 2 root root      4096 Jul 31 06:51 mysql
70 drwxr-x--- 2 root root      8192 Jul 31 06:51 performance_schema
71 drwxr-x--- 2 root root      8192 Jul 31 06:51 sys
72 drwxr-x--- 2 root root      4096 Jul 31 06:51 sysbench
73 -rw-r----- 1 root root        22 Jul 31 06:51 xtrabackup_binlog_pos_innodb
74 -rw-r----- 1 root root       600 Jul 31 06:51 xtrabackup_info
75 -rw-r----- 1 root root         1 Jul 31 06:51 xtrabackup_master_key_id
76 drwxr-x--- 2 root root       120 Jul 31 06:51 zlm
77 
78 [root@zlm3 06:53:49 /data/mysql/mysql3306/data]
79 #chown -R mysql.mysql *

 

**Startup
the MySQL instance on zlm3.**

 1 [root@zlm3 06:53:57 /data/mysql/mysql3306/data]
 2 #sh /root/mysqld.sh
 3 
 4 [root@zlm3 06:55:16 /data/mysql/mysql3306/data]
 5 #ps aux|grep mysqld
 6 mysql     3940 20.0 17.7 1110004 180300 pts/1  Sl   06:55   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
 7 root      3975  0.0  0.0 112640   956 pts/1    R+   06:55   0:00 grep --color=auto mysqld
 8 
 9 [root@zlm3 06:55:44 /data/mysql/mysql3306/data]
10 #mysql
11 Welcome to the MySQL monitor.  Commands end with ; or g.
12 Your MySQL connection id is 3
13 Server version: 5.7.21-log MySQL Community Server (GPL)
14 
15 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
16 
17 Oracle is a registered trademark of Oracle Corporation and/or its
18 affiliates. Other names may be trademarks of their respective
19 owners.
20 
21 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
22 
23 (zlm@192.168.1.102 3306)[(none)]>select count(*) from sysbench.sbtest5;
24 +----------+
25 | count(*) |
26 +----------+
27 |    10000 |
28 +----------+
29 1 row in set (0.02 sec)

 

** 
  The data in Xtrabackup of master has been restored on zlm3. *Notice,it doesn’t contain the
operations of deletion 5000 rows. Firstly,I supposed that the mysqld
has crashed and it can never start again. 
Secondly,I don’t have binlog
server any more this time.Is *there any other way to restore the
dropping table and guarantee the change will not lose on it?**How
can we restore the data safely and simply?Surely there
is.**

   
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.

 

**Step

  1. Special technique*

 

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

 1 [root@zlm3 07:23:49 /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 //It means that the binlog we need should begin from mysql-bin.000043.
 4 
 5 [root@zlm2 06:38:09 /data/mysql/mysql3306/data]
 6 #cd ../logs
 7 
 8 [root@zlm2 07:20:00 /data/mysql/mysql3306/logs]
 9 #ls -l
10 total 64848
11 -rw-r----- 1 mysql mysql      233 Jul 23 11:21 mysql-bin.000023
12 -rw-r----- 1 mysql mysql      209 Jul 23 11:21 mysql-bin.000024
13 -rw-r----- 1 mysql mysql      233 Jul 24 11:27 mysql-bin.000025
14 -rw-r----- 1 mysql mysql      209 Jul 24 11:27 mysql-bin.000026
15 -rw-r----- 1 mysql mysql      233 Jul 25 06:12 mysql-bin.000027
16 -rw-r----- 1 mysql mysql      209 Jul 25 06:12 mysql-bin.000028
17 -rw-r----- 1 mysql mysql  5727732 Jul 25 11:33 mysql-bin.000029
18 -rw-r----- 1 mysql mysql      209 Jul 25 11:33 mysql-bin.000030
19 -rw-r----- 1 mysql mysql 58202858 Jul 26 09:12 mysql-bin.000031
20 -rw-r----- 1 mysql mysql   477279 Jul 26 09:13 mysql-bin.000032
21 -rw-r----- 1 mysql mysql      383 Jul 26 11:21 mysql-bin.000033
22 -rw-r----- 1 mysql mysql      209 Jul 26 11:21 mysql-bin.000034
23 -rw-r----- 1 mysql mysql   954930 Jul 27 07:59 mysql-bin.000035
24 -rw-r----- 1 mysql mysql     2566 Jul 27 11:25 mysql-bin.000036
25 -rw-r----- 1 mysql mysql      209 Jul 27 11:25 mysql-bin.000037
26 -rw-r----- 1 mysql mysql     1394 Jul 30 11:29 mysql-bin.000038
27 -rw-r----- 1 mysql mysql      209 Jul 30 11:29 mysql-bin.000039
28 -rw-r----- 1 mysql mysql      418 Jul 31 04:37 mysql-bin.000040
29 -rw-r----- 1 mysql mysql      233 Jul 31 04:37 mysql-bin.000041
30 -rw-r----- 1 mysql mysql      233 Jul 31 04:37 mysql-bin.000042
31 -rw-r----- 1 mysql mysql   954479 Jul 31 06:35 mysql-bin.000043
32 -rw-r----- 1 mysql mysql      209 Jul 31 06:38 mysql-bin.000044
33 -rw-r----- 1 mysql mysql      968 Jul 31 06:35 mysql-bin.index
34 
35 [root@zlm2 07:28:32 /data/mysql/mysql3306/logs]
36 #scp mysql-bin.00004{3,4} zlm3:/data/backup37 root@zlm3's password: 
38 mysql-bin.000043                                                                                                   100%  932KB 932.1KB/s   00:00    
39 mysql-bin.000044                                                                                                   100%  209     0.2KB/s   00:00

 

**Transfer
the mysql-bin files into relay-bin files.**

 1 [root@zlm3 07:24:08 /data/backup/2018-07-31_06-29-03]
 2 #cd ..
 3 
 4 [root@zlm3 07:30:46 /data/backup]
 5 #ls -l
 6 total 944
 7 drwxr-x--- 7 root root   4096 Jul 31 06:48 2018-07-31_06-29-03
 8 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043
 9 -rw-r----- 1 root root    209 Jul 31 07:28 mysql-bin.000044
10 
11 [root@zlm3 07:30:47 /data/backup]
12 #for i in $(ls mysql-bin.0*)
13 > do
14 >     ext=$(echo $i | cut -d'.' -f2);
15 >     cp $i relay-bin.$ext;
16 > done
17 
18 [root@zlm3 07:31:19 /data/backup]
19 #ls -l
20 total 1884
21 drwxr-x--- 7 root root   4096 Jul 31 06:48 2018-07-31_06-29-03
22 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043
23 -rw-r----- 1 root root    209 Jul 31 07:28 mysql-bin.000044
24 -rw-r----- 1 root root 954479 Jul 31 07:31 relay-bin.000043
25 -rw-r----- 1 root root    209 Jul 31 07:31 relay-bin.000044 
26 
27 [root@zlm3 07:36:18 /data/backup]
28 #ls ./relay-bin.0* > relay-bin.index
29 
30 [root@zlm3 07:36:20 /data/backup]
31 #ls -l
32 total 1888
33 drwxr-x--- 7 root root   4096 Jul 31 06:48 2018-07-31_06-29-03
34 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043
35 -rw-r----- 1 root root    209 Jul 31 07:28 mysql-bin.000044
36 -rw-r----- 1 root root 954479 Jul 31 07:31 relay-bin.000043
37 -rw-r----- 1 root root    209 Jul 31 07:31 relay-bin.000044
38 -rw-r--r-- 1 root root     38 Jul 31 07:36 relay-bin.index
39 
40 [root@zlm3 07:36:23 /data/backup]
41 #cat relay-bin.index 
42 ./relay-bin.000043
43 ./relay-bin.000044
44 
45 [root@zlm3 07:36:27 /data/backup]
46 #chown mysql.mysql relay*
47 
48 [root@zlm3 07:37:12 /data/backup]
49 #ls -l
50 total 1888
51 drwxr-x--- 7 root  root    4096 Jul 31 06:48 2018-07-31_06-29-03
52 -rw-r----- 1 root  root  954479 Jul 31 07:28 mysql-bin.000043
53 -rw-r----- 1 root  root     209 Jul 31 07:28 mysql-bin.000044
54 -rw-r----- 1 mysql mysql 954479 Jul 31 07:31 relay-bin.000043
55 -rw-r----- 1 mysql mysql    209 Jul 31 07:31 relay-bin.000044
56 -rw-r--r-- 1 mysql mysql     38 Jul 31 07:36 relay-bin.index

 

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

 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

 

**Restart
the mysqld process.**

 1 [root@zlm3 08:25:18 /data/mysql/mysql3306/data]
 2 #mysqladmin shutdown
 3 
 4 [root@zlm3 08:31:25 /data/mysql/mysql3306/data]
 5 #ps aux|grep mysqld
 6 root      4309  0.0  0.0 112640   956 pts/1    R+   08:31   0:00 grep --color=auto mysqld
 7 
 8 [root@zlm3 08:31:35 /data/mysql/mysql3306/data]
 9 #sh /root/mysqld.sh
10 
11 [root@zlm3 08:31:45 /data/mysql/mysql3306/data]
12 #ps aux|grep mysqld
13 mysql     4315 11.5 17.8 1044468 181776 pts/1  Sl   08:31   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
14 root      4348  0.0  0.0 112640   960 pts/1    R+   08:31   0:00 grep --color=auto mysqld

 

**Check
out the first consistent position we need.**

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

 

**Execute
“change master to” as below.**

 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)

 

    I was stuck
again,faint!T_T…

 

Supplemented on August 1.

 

   
After discussing with my classmate Shuaibing Zhang,I found out that the
reason why I got failure above was due to not executing “
reset slave
all;”.Therefore,it meantioned that “Could not find the first log…”
when I Executed “change master to … “.

 

Execute “change master to …

 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 Query OK, 0 rows affected (0.01 sec)
 3 
 4 (zlm@192.168.1.102 3306)[(none)]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: 
 7                   Master_Host: xxx
 8                   Master_User: 
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: 
12           Read_Master_Log_Pos: 4
13                Relay_Log_File: relay-bin.000043
14                 Relay_Log_Pos: 190
15         Relay_Master_Log_File: 
16              Slave_IO_Running: No
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 0
28               Relay_Log_Space: 954838
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 0
46                   Master_UUID: 
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229
59                 Auto_Position: 0
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)

 

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

 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*/;

 

Execute
start slave sql_thread util clause.

 1 (zlm@192.168.1.102 3306)[(none)]>start slave sql_thread until relay_log_file='relay-bin.000043',relay_log_pos=954251;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[(none)]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: 
 7                   Master_Host: xxx
 8                   Master_User: 
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: 
12           Read_Master_Log_Pos: 4
13                Relay_Log_File: relay-bin.000043
14                 Relay_Log_Pos: 954251
15         Relay_Master_Log_File: 
16              Slave_IO_Running: No
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 954251
28               Relay_Log_Space: 954838
29               Until_Condition: Relay
30                Until_Log_File: relay-bin.000043
31                 Until_Log_Pos: 954251
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 0
46                   Master_UUID: 
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730231
59                 Auto_Position: 0
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)
64 
65 (zlm@192.168.1.102 3306)[(none)]>select count(*) from sysbench.sbtest5;
66 +----------+
67 | count(*) |
68 +----------+
69 |     5000 |
70 +----------+
71 1 row in set (0.01 sec)

 

Summary

 

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

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

1. Copy all the binlog files to
another server which has a newly initialized instance.

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

3. Execute “reset slave all;” to clear
the original replication information(restored from
master).**

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

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

6. Execute “change master to … ”
with “relay_log_file” and “relay_log_pos”.

7. Execute “change replication
filter … ” if you’re supposed to merely restore a single
table.(optional)

8. Execute “start slave sql_thread
until … ” to restore data until the position you need.(both
relay_log_pos and sql_before_gtids is okay)

 

相关文章

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注