澳门皇冠金沙网站-澳门皇冠844网站

热门关键词: 澳门皇冠金沙网站,澳门皇冠844网站

恢复误删除表黑科技之relay,server及Xtrabackup备份

 

 

 1 (zlm@192.168.1.102 3306)[sysbench]>start slave io_thread;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000036
12           Read_Master_Log_Pos: 2523
13                Relay_Log_File: relay-bin.000001
14                 Relay_Log_Pos: 4
15         Relay_Master_Log_File: 
16              Slave_IO_Running: Yes
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: 776
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: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
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: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222 //The newest transaction has been retrieved.
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)

Execute start slave sql_thread util clause.

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

Step 1: Destroy the table.

 

 1 [root@zlm3 16:52:59 /data/mysql/mysql3306/logs]
 2 #rm -f mysql-bin.00009*
 3 [1]   Done                    mysqlbinlog -R --raw -h192.168.56.100 -urepl -prepl4slave -P3306 --stop-never mysql-bin.000098  (wd: /data)
 4 (wd now: /data/mysql/mysql3306/logs)
 5 
 6 [root@zlm3 17:00:06 /data/mysql/mysql3306/logs]
 7 #ls -l
 8 total 4
 9 -rw-r----- 1 mysql mysql 44 Jul 29 16:50 mysql-bin.index
10 
11 [root@zlm3 17:00:27 /data/mysql/mysql3306/logs]
12 #ps aux|grep mysqlbinlog
13 root      4475  0.0  0.0 112640   960 pts/0    R    17:00   0:00 grep --color=auto mysqlbinlog
14 
15 [root@zlm3 17:00:38 /data/mysql/mysql3306/logs]
16 #cp /data/mysql-bin* .
17 
18 [root@zlm3 17:02:48 /data/mysql/mysql3306/logs]
19 #ls -l
20 -rw-r----- 1 root  root  410 Jul 29 17:01 mysql-bin.000098
21 -rw-r----- 1 root  root  241 Jul 29 17:01 mysql-bin.000099
22 -rw-r----- 1 root  root  951 Jul 29 17:01 mysql-bin.000100
23 -rw-r----- 1 root  root  177 Jul 29 17:03 mysql-bin.index
24 
25 [root@zlm3 17:02:54 /data/mysql/mysql3306/logs]
26 #ls -1 | awk '{print i$0}' i=`pwd`'/'|grep mysql-bin.000 > mysql-bin.index
27 
28 [root@zlm3 17:03:06 /data/mysql/mysql3306/logs]
29 #cat mysql-bin.index 
30 /data/mysql/mysql3306/logs/mysql-bin.000098
31 /data/mysql/mysql3306/logs/mysql-bin.000099
32 /data/mysql/mysql3306/logs/mysql-bin.000100
33 
34 [root@zlm3 17:16:14 /data/mysql/mysql3306/logs]
35 #chown mysql.mysql *
36 
37 [root@zlm3 17:17:23 /data/mysql/mysql3306/logs]
38 #ls -l
39 total 16
40 -rw-r----- 1 mysql mysql 410 Jul 29 17:01 mysql-bin.000098
41 -rw-r----- 1 mysql mysql 241 Jul 29 17:01 mysql-bin.000099
42 -rw-r----- 1 mysql mysql 951 Jul 29 17:01 mysql-bin.000100
43 -rw-r----- 1 mysql mysql 177 Jul 29 17:03 mysql-bin.index

Try to analyze the binlog on slave zlm3.

 

 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 transaction of deleting 10 records befor dropping "sbtest7" ,together with those other transactions in mysql-bin.000035,was rolled back.Even if the slave had executed the newest GTID "uuid:3720222",the incremtental modifications on the table couldn't be rescued.


Generate Xtrabackup backup set.

Start the instance and check the table "sbtest7".

**Continue to executing some dml operations on the target table and then kill the 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)

Shutdown the instance on zlm3 and copy back the datafiles

Summary

 

 

Restore the backup on zlm3.

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)
 1 [root@zlm3 08:11:28 /data/backup/2018-07-27_07-57-43]
 2 #ps aux|grep mysqld
 3 mysql     5079  0.0 20.2 1110576 205684 pts/1  Sl   07:35   0:01 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
 4 root      5341  0.0  0.0 112640   956 pts/2    R    08:16   0:00 grep --color=auto mysqld
 5 
 6 [root@zlm3 08:16:38 /data/backup/2018-07-27_07-57-43]
 7 #mysqladmin shutdown
 8 
 9 [root@zlm3 08:16:43 /data/backup/2018-07-27_07-57-43]
10 #ps aux|grep mysqld
11 root      5352  0.0  0.0 112640   960 pts/2    R    08:16   0:00 grep --color=auto mysqld
12 
13 [root@zlm3 08:16:45 /data/backup/2018-07-27_07-57-43]
14 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-27_07-57-43/
15 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
16 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
17 180727 08:17:34 innobackupex: Starting the copy-back operation
18 
19 IMPORTANT: Please check that the copy-back run completes successfully.
20            At the end of a successful copy-back run innobackupex
21            prints "completed OK!".
22 
23 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
24 Original data directory /data/mysql/mysql3306/data is not empty! //The data directory need to be cleaned.
25 
26 [root@zlm3 08:17:34 /data/backup/2018-07-27_07-57-43]
27 #cd /data/mysql/mysql3306/data
28 
29 [root@zlm3 08:18:09 /data/mysql/mysql3306/data]
30 #ls -l
31 total 409700
32 -rw-r----- 1 mysql mysql        56 Jul 27 07:35 auto.cnf
33 -rw-r----- 1 mysql mysql     27980 Jul 27 08:16 error.log
34 -rw-r----- 1 mysql mysql       904 Jul 27 08:16 ib_buffer_pool
35 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:16 ibdata1
36 -rw-r----- 1 mysql mysql 104857600 Jul 27 08:16 ib_logfile0
37 -rw-r----- 1 mysql mysql 104857600 Jul 27 07:35 ib_logfile1
38 -rw-r----- 1 mysql mysql 104857600 Jul 27 07:35 ib_logfile2
39 drwxr-x--- 2 mysql mysql      4096 Jul 27 07:34 mysql
40 drwxr-x--- 2 mysql mysql      8192 Jul 27 07:34 performance_schema
41 -rw-r----- 1 mysql mysql       276 Jul 27 07:59 relay-bin.000004
42 -rw-r----- 1 mysql mysql      2562 Jul 27 08:16 relay-bin.000005
43 -rw-r----- 1 mysql mysql       169 Jul 27 08:16 relay-bin-group_replication_applier.000001
44 -rw-r----- 1 mysql mysql        45 Jul 27 07:35 relay-bin-group_replication_applier.index
45 -rw-r----- 1 mysql mysql       169 Jul 27 08:16 relay-bin-group_replication_recovery.000001
46 -rw-r----- 1 mysql mysql        46 Jul 27 07:35 relay-bin-group_replication_recovery.index
47 -rw-r----- 1 mysql mysql        38 Jul 27 07:59 relay-bin.index
48 -rw-r----- 1 mysql mysql       167 Jul 27 07:35 slow.log
49 drwxr-x--- 2 mysql mysql      8192 Jul 27 07:34 sys
50 drwxr-x--- 2 mysql mysql        55 Jul 27 07:34 sysbench
51 -rw-r----- 1 mysql mysql       584 Jul 27 07:34 xtrabackup_info
52 
53 [root@zlm3 08:18:18 /data/mysql/mysql3306/data]
54 #rm -rf *
55 
56 [root@zlm3 08:18:19 /data/mysql/mysql3306/data]
57 #ls -l
58 total 0
59 
60 [root@zlm3 08:18:22 /data/mysql/mysql3306/data]
61 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-27_07-57-43/
62 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
63 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3306/data --tmpdir=/data/mysql/mysql3306/tmp --open_files_limit=65535 --server-id=1023306 --log_bin=/data/mysql/mysql3306/logs/mysql-bin --innodb_buffer_pool_size=100M --innodb_data_file_path=ibdata1:100M:autoextend --innodb_flush_log_at_trx_commit=2 --innodb_log_buffer_size=8M --innodb_log_file_size=100M --innodb_log_files_in_group=3 --innodb_max_dirty_pages_pct=50 --innodb_file_per_table=1 --innodb_io_capacity=2000 --innodb_flush_method=O_DIRECT --server-id=1023306 
64 180727 08:18:53 innobackupex: Starting the copy-back operation
65 
66 IMPORTANT: Please check that the copy-back run completes successfully.
67            At the end of a successful copy-back run innobackupex
68            prints "completed OK!".
69 
70 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
71 180727 08:18:53 [01] Copying ib_logfile0 to /data/mysql/mysql3306/data/ib_logfile0
72 180727 08:18:54 [01]        ...done
73 180727 08:18:55 [01] Copying ib_logfile1 to /data/mysql/mysql3306/data/ib_logfile1
74 180727 08:18:55 [01]        ...done
75 180727 08:18:59 [01] Copying ib_logfile2 to /data/mysql/mysql3306/data/ib_logfile2
76 180727 08:19:00 [01]        ...done
77 180727 08:19:04 [01] Copying ibdata1 to /data/mysql/mysql3306/data/ibdata1
78 180727 08:19:06 [01]        ...done
79 
80 ... //Omitted.
81 
82 180727 08:19:13 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
83 180727 08:19:13 [01]        ...done
84 180727 08:19:13 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
85 180727 08:19:13 [01]        ...done
86 180727 08:19:13 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
87 180727 08:19:13 [01]        ...done
88 180727 08:19:13 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
89 180727 08:19:13 [01]        ...done
90 180727 08:19:13 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
91 180727 08:19:13 [01]        ...done
92 180727 08:19:14 completed OK!

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

 

    I guess the result is due to that the redo log files in backup set didn't contain the transactions information,thus,it chose to roll back the transactions.

 

 

Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm2 192.168.1.101/3306 master CentOS 7.0 5.7.21 on row
zlm3 192.168.1.102/3306 slave CentOS 7.0 5.7.21 on row
 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*/;
 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

Step 2: Begin to restore backup set.

Check the table on master.

*Step 4: 澳门皇冠金沙网站,**Implement a slave with binlog server.***

    Okay,I didn't get my supposed consequences in the above test this time.In my opinion,the miss dropped table "sbtest7" should have been recovered to the state of "9990" instead of "10000" what really astonished me.

**Execute "change master to" as below.**

Framework

Analyze the binlog file on zlm2 to get the GTID before dropping operation.

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

 

 1 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest7;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 5  -------------------- 
 6 | Tables_in_sysbench |
 7  -------------------- 
 8 | sbtest1            |
 9 | sbtest2            |
10 | sbtest3            |
11 | sbtest4            |
12 | sbtest5            |
13 | sbtest6            |
14  -------------------- 
15 6 rows in set (0.00 sec)
16 
17 (zlm@192.168.1.101 3306)[sysbench]>show master status;
18  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
19 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
20  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
21 | mysql-bin.000036 |     2523 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222 |
22  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
23 1 row in set (0.00 sec)
 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

 

Start slave SQL_Thread with until_option clause.

 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)

 

 

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

 

 

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.

 

 

 

 

 

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
 1 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 2  -------------------- 
 3 | Tables_in_sysbench |
 4  -------------------- 
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 | sbtest5            |
10 | sbtest6            |
11 | sbtest7            |
12  -------------------- 
13 7 rows in set (0.00 sec)
14 
15 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest7;
16  ---------- 
17 | count(*) |
18  ---------- 
19 |    10000 |
20  ---------- 
21 1 row in set (0.00 sec)
22 
23 (zlm@192.168.1.101 3306)[sysbench]>show master status;
24  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
25 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
26  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
27 | mysql-bin.000035 |   954887 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220 |
28  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
29 1 row in set (0.00 sec)
30 
31 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
32 Query OK, 0 rows affected (0.02 sec)
33 
34 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest7 limit 10;
35 Query OK, 10 rows affected (0.00 sec)
36 
37 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest7;
38  ---------- 
39 | count(*) |
40  ---------- 
41 |     9990 |
42  ---------- 
43 1 row in set (0.00 sec)
44 
45 (zlm@192.168.1.101 3306)[sysbench]>show master status;
46  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
47 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
48  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
49 | mysql-bin.000036 |     2338 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221 |
50  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
51 1 row in set (0.00 sec)

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

  1 zlm@192.168.56.101:3306 [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';
  2 Query OK, 0 rows affected (0.00 sec)
  3 
  4 zlm@192.168.56.101:3306 [(none)]>change master to master_host='192.168.56.102',master_port=3306,master_user='repl',master_password='repl4slave',master_auto_position=1;
  5 Query OK, 0 rows affected, 2 warnings (0.01 sec)
  6 
  7 zlm@192.168.56.101:3306 [(none)]>start slave io_thread;
  8 Query OK, 0 rows affected (0.00 sec)
  9 
 10 zlm@192.168.56.101:3306 [(none)]>show slave statusG
 11 *************************** 1. row ***************************
 12                Slave_IO_State: Waiting for master to send event
 13                   Master_Host: 192.168.56.102
 14                   Master_User: repl
 15                   Master_Port: 3306
 16                 Connect_Retry: 60
 17               Master_Log_File: mysql-bin.000105
 18           Read_Master_Log_Pos: 194
 19                Relay_Log_File: relay-bin.000001
 20                 Relay_Log_Pos: 4
 21         Relay_Master_Log_File: 
 22              Slave_IO_Running: Yes
 23             Slave_SQL_Running: No
 24               Replicate_Do_DB: 
 25           Replicate_Ignore_DB: 
 26            Replicate_Do_Table: 
 27        Replicate_Ignore_Table: 
 28       Replicate_Wild_Do_Table: 
 29   Replicate_Wild_Ignore_Table: 
 30                    Last_Errno: 0
 31                    Last_Error: 
 32                  Skip_Counter: 0
 33           Exec_Master_Log_Pos: 0
 34               Relay_Log_Space: 3360
 35               Until_Condition: None
 36                Until_Log_File: 
 37                 Until_Log_Pos: 0
 38            Master_SSL_Allowed: No
 39            Master_SSL_CA_File: 
 40            Master_SSL_CA_Path: 
 41               Master_SSL_Cert: 
 42             Master_SSL_Cipher: 
 43                Master_SSL_Key: 
 44         Seconds_Behind_Master: NULL
 45 Master_SSL_Verify_Server_Cert: No
 46                 Last_IO_Errno: 0
 47                 Last_IO_Error: 
 48                Last_SQL_Errno: 0
 49                Last_SQL_Error: 
 50   Replicate_Ignore_Server_Ids: 
 51              Master_Server_Id: 1023306
 52                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e //I changed back the uuid of zlm3 to its previous value.
 53              Master_Info_File: /data/mysql/mysql3306/data/master.info
 54                     SQL_Delay: 0
 55           SQL_Remaining_Delay: NULL
 56       Slave_SQL_Running_State: 
 57            Master_Retry_Count: 86400
 58                   Master_Bind: 
 59       Last_IO_Error_Timestamp: 
 60      Last_SQL_Error_Timestamp: 
 61                Master_SSL_Crl: 
 62            Master_SSL_Crlpath: 
 63            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715695-12715696 //New gtid was received.
 64             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
 65                 Auto_Position: 1
 66          Replicate_Rewrite_DB: 
 67                  Channel_Name: 
 68            Master_TLS_Version: 
 69 1 row in set (0.00 sec)
 70 
 71 zlm@192.168.56.101:3306 [sysbench]>start slave sql_thread until sql_before_gtids='2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715696';
 72 Query OK, 0 rows affected (0.00 sec)
 73 
 74 zlm@192.168.56.101:3306 [sysbench]>show slave statusG
 75 *************************** 1. row ***************************
 76                Slave_IO_State: Waiting for master to send event
 77                   Master_Host: 192.168.56.102
 78                   Master_User: repl
 79                   Master_Port: 3306
 80                 Connect_Retry: 60
 81               Master_Log_File: mysql-bin.000105
 82           Read_Master_Log_Pos: 194
 83                Relay_Log_File: relay-bin.000002
 84                 Relay_Log_Pos: 696
 85         Relay_Master_Log_File: mysql-bin.000100
 86              Slave_IO_Running: Yes
 87             Slave_SQL_Running: No
 88               Replicate_Do_DB: 
 89           Replicate_Ignore_DB: 
 90            Replicate_Do_Table: 
 91        Replicate_Ignore_Table: 
 92       Replicate_Wild_Do_Table: 
 93   Replicate_Wild_Ignore_Table: 
 94                    Last_Errno: 0
 95                    Last_Error: 
 96                  Skip_Counter: 0
 97           Exec_Master_Log_Pos: 758
 98               Relay_Log_Space: 3360
 99               Until_Condition: SQL_BEFORE_GTIDS
100                Until_Log_File: 
101                 Until_Log_Pos: 0
102            Master_SSL_Allowed: No
103            Master_SSL_CA_File: 
104            Master_SSL_CA_Path: 
105               Master_SSL_Cert: 
106             Master_SSL_Cipher: 
107                Master_SSL_Key: 
108         Seconds_Behind_Master: NULL
109 Master_SSL_Verify_Server_Cert: No
110                 Last_IO_Errno: 0
111                 Last_IO_Error: 
112                Last_SQL_Errno: 0
113                Last_SQL_Error: 
114   Replicate_Ignore_Server_Ids: 
115              Master_Server_Id: 1023306
116                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e
117              Master_Info_File: /data/mysql/mysql3306/data/master.info
118                     SQL_Delay: 0
119           SQL_Remaining_Delay: NULL
120       Slave_SQL_Running_State: 
121            Master_Retry_Count: 86400
122                   Master_Bind: 
123       Last_IO_Error_Timestamp: 
124      Last_SQL_Error_Timestamp: 
125                Master_SSL_Crl: 
126            Master_SSL_Crlpath: 
127            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715695-12715696
128             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715695
129                 Auto_Position: 1
130          Replicate_Rewrite_DB: 
131                  Channel_Name: 
132            Master_TLS_Version: 
133 1 row in set (0.00 sec)
134 
135 zlm@192.168.56.101:3306 [sysbench]>select * from sbtest6;
136  ---- --- --- ----- 
137 | id | k | c | pad |
138  ---- --- --- ----- 
139 |  1 | 1 | a | b   |
140 |  2 | 2 | c | d   |
141  ---- --- --- ----- 
142 2 rows in set (0.00 sec)
143 
144 //The incremental data in "sbtest6" had been come again.It was okay this time.

 

  1. Destroy***

  

 1 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 2 Empty set (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show master status;
 5  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
 6 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
 7  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
 8 | mysql-bin.000003 |      190 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221 |
 9  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
10 1 row in set (0.00 sec)
11 
12 (zlm@192.168.1.102 3306)[sysbench]>change master to 
13     -> master_host='192.168.1.101',
14     -> master_port=3306,
15     -> master_user='repl',
16     -> master_password='repl4slave',
17     -> master_auto_position=1;
18 Query OK, 0 rows affected, 2 warnings (0.05 sec)
 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!
  • 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.
 1 [root@zlm3 08:06:58 /data/backup/2018-07-27_07-57-43/sysbench]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --apply-log /data/backup/2018-07-27_07-57-43/
 3 xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1013306 --redo-log-version=1 
 4 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1013306 --redo-log-version=1 
 5 180727 08:08:41 innobackupex: Starting the apply-log operation
 6 
 7 IMPORTANT: Please check that the apply-log run completes successfully.
 8            At the end of a successful apply-log run innobackupex
 9            prints "completed OK!".
10 
11 innobackupex version 2.4.12 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 170eb8c)
12 xtrabackup: cd to /data/backup/2018-07-27_07-57-43/
13 
14 ... //Omitted.
15 
16 InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
17 InnoDB: New log files created, LSN=10141352841
18 InnoDB: Highest supported file format is Barracuda.
19 InnoDB: Log scan progressed past the checkpoint lsn 10141352972
20 InnoDB: Doing recovery: scanned up to log sequence number 10141352981 (0%)
21 InnoDB: Database was not shutdown normally!
22 InnoDB: Starting crash recovery.
23 InnoDB: xtrabackup: Last MySQL binlog file position 954219, file name mysql-bin.000035
24 InnoDB: Removed temporary tablespace data file: "ibtmp1"
25 InnoDB: Creating shared tablespace for temporary tables
26 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
27 InnoDB: File './ibtmp1' size is now 12 MB.
28 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
29 InnoDB: 32 non-redo rollback segment(s) are active.
30 InnoDB: Waiting for purge to start
31 InnoDB: 5.7.19 started; log sequence number 10141352981
32 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
33 InnoDB: page_cleaner: 1000ms intended loop took 18267ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
34 InnoDB: FTS optimize thread exiting.
35 InnoDB: Starting shutdown...
36 InnoDB: Shutdown completed; log sequence number 10141353000
37 180727 08:09:03 completed OK!
38 
39 [root@zlm3 08:09:03 /data/backup/2018-07-27_07-57-43/sysbench]
40 #cd ..
41 
42 [root@zlm3 08:11:14 /data/backup/2018-07-27_07-57-43]
43 #ls -l
44 total 430144
45 -rw-r----- 1 root root       495 Jul 27 07:57 backup-my.cnf
46 -rw-r----- 1 root root      8988 Jul 27 07:57 ib_buffer_pool
47 -rw-r----- 1 root root 104857600 Jul 27 08:09 ibdata1
48 -rw-r----- 1 root root 104857600 Jul 27 08:09 ib_logfile0
49 -rw-r----- 1 root root 104857600 Jul 27 08:08 ib_logfile1
50 -rw-r----- 1 root root 104857600 Jul 27 08:08 ib_logfile2
51 -rw-r----- 1 root root  12582912 Jul 27 08:09 ibtmp1
52 drwxr-x--- 2 root root      4096 Jul 27 07:57 mysql
53 drwxr-x--- 2 root root      8192 Jul 27 07:57 performance_schema
54 drwxr-x--- 2 root root      8192 Jul 27 07:57 sys
55 drwxr-x--- 2 root root        55 Jul 27 08:06 sysbench
56 -rw-r----- 1 root root        71 Jul 27 07:57 xtrabackup_binlog_info
57 -rw-r--r-- 1 root root        24 Jul 27 08:08 xtrabackup_binlog_pos_innodb
58 -rw-r----- 1 root root       121 Jul 27 08:08 xtrabackup_checkpoints
59 -rw-r----- 1 root root       587 Jul 27 07:57 xtrabackup_info
60 -rw-r----- 1 root root   8388608 Jul 27 08:08 xtrabackup_logfile
61 -rw-r--r-- 1 root root         1 Jul 27 08:08 xtrabackup_master_key_id
62 
63 [root@zlm3 08:11:15 /data/backup/2018-07-27_07-57-43]
64 #cat xtrabackup_binlog_info
65 mysql-bin.000035    954887    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730220
66 
67 [root@zlm3 08:11:21 /data/backup/2018-07-27_07-57-43]
68 #cat xtrabackup_binlog_pos_innodb
69 mysql-bin.000035    954219
70 
71 //The result of "show master status;" on zlm2.
72 (zlm@192.168.1.101 3306)[(none)]>show master status;
73  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
74 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
75  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
76 | mysql-bin.000036 |     2523 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222 | //The newest GTID is "uuid:1-3730222"
77  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
78 1 row in set (0.00 sec)
 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)

 

 1 (zlm@192.168.1.102 3306)[sysbench]>start slave sql_thread until sql_before_gtids='1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222';
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: Waiting for master to send event
 7                   Master_Host: 192.168.1.101
 8                   Master_User: repl
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000036
12           Read_Master_Log_Pos: 2523
13                Relay_Log_File: relay-bin.000002
14                 Relay_Log_Pos: 398
15         Relay_Master_Log_File: mysql-bin.000036
16              Slave_IO_Running: Yes
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: sysbench.sbteset7
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: 2338
28               Relay_Log_Space: 776
29               Until_Condition: SQL_BEFORE_GTIDS
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: 1013306
46                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
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: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730221
59                 Auto_Position: 1
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)[sysbench]>select count(*) from sbtest7;
66  ---------- 
67 | count(*) |
68  ---------- 
69 |    10000 |
70  ---------- 
71 1 row in set (0.00 sec)
72 
73 //Why the amount of record isn't 9990?

    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.

 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)
 1 (zlm@192.168.1.102 3306)[sysbench]>stop slave;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[sysbench]>start slave;
 5 Query OK, 0 rows affected (0.00 sec)
 6 
 7 (zlm@192.168.1.102 3306)[sysbench]>show slave statusG
 8 *************************** 1. row ***************************
 9                Slave_IO_State: Waiting for master to send event
10                   Master_Host: 192.168.1.101
11                   Master_User: repl
12                   Master_Port: 3306
13                 Connect_Retry: 60
14               Master_Log_File: mysql-bin.000036
15           Read_Master_Log_Pos: 2523
16                Relay_Log_File: relay-bin.000003
17                 Relay_Log_Pos: 438
18         Relay_Master_Log_File: mysql-bin.000036
19              Slave_IO_Running: Yes
20             Slave_SQL_Running: Yes
21               Replicate_Do_DB: 
22           Replicate_Ignore_DB: 
23            Replicate_Do_Table: sysbench.sbteset7
24        Replicate_Ignore_Table: 
25       Replicate_Wild_Do_Table: 
26   Replicate_Wild_Ignore_Table: 
27                    Last_Errno: 0
28                    Last_Error: 
29                  Skip_Counter: 0
30           Exec_Master_Log_Pos: 2523
31               Relay_Log_Space: 1064
32               Until_Condition: None
33                Until_Log_File: 
34                 Until_Log_Pos: 0
35            Master_SSL_Allowed: No
36            Master_SSL_CA_File: 
37            Master_SSL_CA_Path: 
38               Master_SSL_Cert: 
39             Master_SSL_Cipher: 
40                Master_SSL_Key: 
41         Seconds_Behind_Master: 0
42 Master_SSL_Verify_Server_Cert: No
43                 Last_IO_Errno: 0
44                 Last_IO_Error: 
45                Last_SQL_Errno: 0
46                Last_SQL_Error: 
47   Replicate_Ignore_Server_Ids: 
48              Master_Server_Id: 1013306
49                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
50              Master_Info_File: mysql.slave_master_info
51                     SQL_Delay: 0
52           SQL_Remaining_Delay: NULL
53       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
54            Master_Retry_Count: 86400
55                   Master_Bind: 
56       Last_IO_Error_Timestamp: 
57      Last_SQL_Error_Timestamp: 
58                Master_SSL_Crl: 
59            Master_SSL_Crlpath: 
60            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730222
61             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730222
62                 Auto_Position: 1
63          Replicate_Rewrite_DB: 
64                  Channel_Name: 
65            Master_TLS_Version: 
66 1 row in set (0.00 sec)
67 
68 (zlm@192.168.1.102 3306)[sysbench]>show tables;
69  -------------------- 
70 | Tables_in_sysbench |
71  -------------------- 
72 | sbtest7            |
73  -------------------- 
74 1 row in set (0.00 sec)
75 
76 (zlm@192.168.1.102 3306)[sysbench]>select count(*) from sbtest7;
77  ---------- 
78 | count(*) |
79  ---------- 
80 |    10000 |
81  ---------- 
82 1 row in set (0.01 sec)
83 
84 //Even though it has executed the newest GTID "uuid:3730222",the amount of record is unchanged.

**Generate a full Xtrabackup of master.**

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

Precedure

 

 

 

 

*Step 3: Rescue data.**

Generate a backup with Xtrabackup first.

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

 

 

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

 

 

**Startup the MySQL instance on zlm3.**

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

Change replication filter "replicate_do_table" option.

 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 *

Check the position on master 

 

本文由澳门皇冠金沙网站发布于数据库研究,转载请注明出处:恢复误删除表黑科技之relay,server及Xtrabackup备份