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

2019-10-02 05:08 来源:未知

 

 

Preface

Preface

 

 

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

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

  •     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.)

 

 

Framework

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

 

 

Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm1 192.168.56.100/3306 master CentOS 7.0 5.7.21 on row
zlm2 192.168.56.101/3306 slave CentOS 7.0 5.7.21 on row
zlm3 192.168.56.102/3306 binlog server CentOS 7.0 5.7.21 on row

Procedure

 

 

Precedure

***Step

 

  1. Destroy***

Step 1: Create binlog server.

 

 

Check the table on master.

Check the position 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)
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)

 

 

**Generate a full Xtrabackup of master.**

**Make binlog server begin to receive binlog.**

 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!
 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

 

 

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

**Flush two logs on master.**

 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
 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)

 

 

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

**Check whether the newly generated binlogs are successfully transmited to binlog server.**

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.
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

 

 

**Step 2. Rescue***

Step 2: Destroy the table.


澳门新浦金网站, 

 

Check target table on master.

Restore the backup on zlm3.

 1 zlm@192.168.56.100:3306 [sysbench]>show tables;
 2 +--------------------+
 3 | Tables_in_sysbench |
 4 +--------------------+
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 | sbtest5            |
10 | sbtest6            |
11 +--------------------+
12 6 rows in set (0.00 sec)
13 
14 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest6;
15 +----------+
16 | count(*) |
17 +----------+
18 |        0 |
19 +----------+
20 1 row in set (0.00 sec)
21 
22 zlm@192.168.56.100:3306 [sysbench]>insert into sbtest6 values(1,1,'a','b');
23 Query OK, 1 row affected (0.00 sec)
24 
25 zlm@192.168.56.100:3306 [sysbench]>select * from sbtest6;
26 +----+---+---+-----+
27 | id | k | c | pad |
28 +----+---+---+-----+
29 |  1 | 1 | a | b   |
30 +----+---+---+-----+
31 1 row in set (0.00 sec)
 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 *

 

 

Generate Xtrabackup backup set.

**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 [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)

 

 

Insert one record of "sbtest6" and drop it.

**    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.**

 1 zlm@192.168.56.100:3306 [sysbench]>insert into sbtest6 values(2,2,'c','d');
 2 Query OK, 1 row affected (0.00 sec)
 3 
 4 zlm@192.168.56.100:3306 [sysbench]>show master status;
 5 +------------------+----------+--------------+------------------+-------------------------------------------------+
 6 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 7 +------------------+----------+--------------+------------------+-------------------------------------------------+
 8 | mysql-bin.000100 |      758 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715695 |
 9 +------------------+----------+--------------+------------------+-------------------------------------------------+
10 1 row in set (0.00 sec)
11 
12 zlm@192.168.56.100:3306 [sysbench]>drop tables sbtest6;
13 Query OK, 0 rows affected (0.02 sec)
14 
15 zlm@192.168.56.100:3306 [sysbench]>show master status;
16 +------------------+----------+--------------+------------------+-------------------------------------------------+
17 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
18 +------------------+----------+--------------+------------------+-------------------------------------------------+
19 | mysql-bin.000100 |      951 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
20 +------------------+----------+--------------+------------------+-------------------------------------------------+
21 1 row in set (0.00 sec)

    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.

 

 

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

**Step

 1 [root@zlm1 16:56:54 /data/backup]
 2 #mysqladmin shutdown
 3 
 4 [root@zlm1 16:57:05 /data/backup]
 5 #ps aux|grep mysqld
 6 mysql     3804  0.0 20.7 1072396 210620 pts/0  Sl   15:52   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
 7 root      4512  0.0  0.0 112640   960 pts/2    R+   16:57   0:00 grep --color=auto mysqld
 8 
 9 [root@zlm1 16:57:10 /data/backup]
10 #ps aux|grep mysqld
11 mysql     3804  0.0 20.1 1038828 204780 pts/0  Sl   15:52   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
12 root      4518  0.0  0.0 112640   956 pts/2    R+   16:57   0:00 grep --color=auto mysqld
13 
14 [root@zlm1 16:57:15 /data/backup]
15 #mysqladmin shutdown
16 mysqladmin: connect to server at '192.168.56.100' failed
17 error: 'Can't connect to MySQL server on '192.168.56.100' (111)'
18 Check that mysqld is running on 192.168.56.100 and that the port is 3306.
19 You can check this by doing 'telnet 192.168.56.100 3306'
20 
21 [root@zlm1 16:57:23 /data/backup]
22 #ps aux|grep mysqld
23 root      4529  0.0  0.0 112640   960 pts/2    R+   16:57   0:00 grep --color=auto mysqld
  1. Special technique*

 


Copy backup set to zlm2.

 

 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.

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

*Step 3: Rescue data.**

 


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

Prepare to restore the backup with "--apply-log" on zlm2.

 

 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!

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

 

TAG标签:
版权声明:本文由澳门新浦金网站发布于数据库,转载请注明出处:恢复误删除表黑科技之relay log大法