利用binlog server及Xtrabackup备份集来恢复误删表（drop）
程序员文章站
2022-07-01 13:12:38
Preface Today I'm gonna test how to rescue a dropped table from binlog server based on a full Xtrabackup backup set. Preface Today I'm gonna test how ......
Preface
Today I'm gonna test how to rescue a dropped table from binlog server based on a full Xtrabackup backup set.
Framework
| 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 |
Precedure
Step 1: Create binlog server.
Check the position on master
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)
Make binlog server begin to receive binlog.
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
Flush two logs on master.
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)
Check whether the newly generated binlogs are successfully transmited to binlog server.
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: Destroy the table.
Check target table on master.
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)
Generate Xtrabackup backup set.
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!
Insert one record of "sbtest6" and drop it.
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)
Shutdown the mysqld on master(mimic the MySQL instance down).
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
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.
Step 3: Rescue data.
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!
Shutdown instance and copy back.
1 [root@zlm2 16:39:39 /data/backup] 2 #ps aux|grep mysqld 3 mysql 3771 0.0 18.7 1071816 190784 pts/0 Sl 15:52 0:01 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf 4 root 4451 0.0 0.0 112640 956 pts/1 R+ 16:43 0:00 grep --color=auto mysqld 5 6 [root@zlm2 16:43:10 /data/backup] 7 #mysqladmin shutdown 8 9 [root@zlm2 16:43:15 /data/backup] 10 #ps aux|grep mysqld 11 root 4463 0.0 0.0 112640 956 pts/1 R+ 16:43 0:00 grep --color=auto mysqld 12 13 [root@zlm2 16:43:18 /data/backup] 14 #cd /data/mysql/mysql3306/data/ 15 16 [root@zlm2 16:43:36 /data/mysql/mysql3306/data] 17 #rm -rf * 18 19 [root@zlm2 16:43:39 /data/mysql/mysql3306/data] 20 #cd ../logs 21 22 [root@zlm2 16:43:50 /data/mysql/mysql3306/logs] 23 #rm -rf * 24 25 [root@zlm2 16:43:53 /data/mysql/mysql3306/logs] 26 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-29_16-32-33/ 27 ... //Omitted.
Restart instance and check table.
1 [root@zlm2 16:44:41 /data/mysql/mysql3306/logs] 2 #cd ../data 3 4 [root@zlm2 16:47:40 /data/mysql/mysql3306/data] 5 #chown -R mysql.mysql * 6 7 [root@zlm2 16:47:49 /data/mysql/mysql3306/data] 8 #sh /root/mysqld.sh 9 10 [root@zlm2 16:47:56 /data/mysql/mysql3306/data] 11 #ps aux|grep mysqld 12 mysql 4514 3.2 17.9 1071804 182316 pts/1 Sl 16:47 0:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf 13 root 4547 0.0 0.0 112640 960 pts/1 R+ 16:48 0:00 grep --color=auto mysqld 14 15 [root@zlm2 16:48:03 /data/mysql/mysql3306/data] 16 #mysql 17 Welcome to the MySQL monitor. Commands end with ; or \g. 18 Your MySQL connection id is 2 19 Server version: 5.7.21-log MySQL Community Server (GPL) 20 21 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 22 23 Oracle is a registered trademark of Oracle Corporation and/or its 24 affiliates. Other names may be trademarks of their respective 25 owners. 26 27 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 28 29 zlm@192.168.56.101:3306 [(none)]>use sysbench 30 Reading table information for completion of table and column names 31 You can turn off this feature to get a quicker startup with -A 32 33 Database changed 34 zlm@192.168.56.101:3306 [sysbench]>show tables; 35 +--------------------+ 36 | Tables_in_sysbench | 37 +--------------------+ 38 | sbtest1 | 39 | sbtest2 | 40 | sbtest3 | 41 | sbtest4 | 42 | sbtest5 | 43 | sbtest6 | 44 +--------------------+ 45 6 rows in set (0.00 sec) 46 47 zlm@192.168.56.101:3306 [sysbench]>select * from sbtest6; 48 +----+---+---+-----+ 49 | id | k | c | pad | 50 +----+---+---+-----+ 51 | 1 | 1 | a | b | 52 +----+---+---+-----+ 53 1 row in set (0.00 sec)
Step 4: Implement a slave with binlog server.
Start mysqld on binlog server.
1 [root@zlm3 16:50:00 /data/mysql/mysql3306/logs] 2 #ps aux|grep mysqld 3 root 4405 0.0 0.0 112640 960 pts/0 R+ 16:50 0:00 grep --color=auto mysqld 4 5 [root@zlm3 16:50:08 /data/mysql/mysql3306/logs] 6 #sh /root/mysqld.sh 7 8 [root@zlm3 16:50:13 /data/mysql/mysql3306/logs] 9 #ps aux|grep mysqld 10 mysql 4411 16.5 15.6 498232 159408 pts/0 Rl 16:50 0:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf 11 root 4429 0.0 0.0 112640 960 pts/0 R+ 16:50 0:00 grep --color=auto mysqld 12 13 [root@zlm3 16:51:03 /data/mysql/mysql3306/logs] 14 #ls -l 15 total 11216 16 -rw-r----- 1 root root 4128 Jul 29 08:42 mysql-bin.000095 17 -rw-r----- 1 root root 241 Jul 29 08:42 mysql-bin.000096 18 -rw-r----- 1 root root 11461562 Jul 29 09:07 mysql-bin.000097 19 -rw-r----- 1 mysql mysql 154 Jul 29 16:50 mysql-bin.000098 20 -rw-r----- 1 mysql mysql 44 Jul 29 16:50 mysql-bin.index 21 22 [root@zlm3 16:51:04 /data/mysql/mysql3306/logs] 23 #cat mysql-bin.index 24 /data/mysql/mysql3306/logs/mysql-bin.000098
Disguise the binlog server is a fake master.
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
Change the server-uuid of binlog server to be equal with master.
1 [root@zlm3 18:01:50 /data/mysql/mysql3306] 2 #cd ../data 3 4 [root@zlm3 18:01:53 /data/mysql/mysql3306/data] 5 #cat auto.cnf 6 [auto] 7 server-uuid=e00ef9f5-6c4b-11e8-8389-080027de0e0e 8 9 [root@zlm3 18:04:11 /data/mysql/mysql3306/data] 10 #vim auto.cnf 11 12 [root@zlm3 18:04:26 /data/mysql/mysql3306/data] 13 #cat auto.cnf 14 [auto] 15 server-uuid=2a4b3562-2ab6-11e8-be7a-080027de0e0e
Execute "change master to ... " on zlm2.
1 zlm@192.168.56.101:3306 [sysbench]>change master to \ 2 -> master_host='192.168.56.102',\ 3 -> master_port=3306,\ 4 -> master_user='repl',\ 5 -> master_password='repl4slave',\ 6 -> master_auto_position=1; 7 Query OK, 0 rows affected, 2 warnings (0.01 sec)
Start IO_Thread on zlm2.
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 status\G 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)
I was stucked here.Slave IO_Thread could not be started with 1236,although I had replaced the server-uuid and set the variable of gtid_purged.Only if the slave get binlog which contains the incremental data and start the slave SQL_Thread.We can get back our dropped table together with the modification on it.
Supplemented on July 31:
The previous fault of 1236 was due to the wrong configuration of variable "gtid_purged".There were two individual gtids generated by the other masters with different uuid as bellow:
Therefore,the configuration command should be executed like this:
1 set @@global.gtid_purged='27af30ca-6800-11e8-ad7e-080027de0e0e:1, 2 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694, 3 e00ef9f5-6c4b-11e8-8389-080027de0e0e:1';
not merely set it like this:
1 set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';
Eventually,In order to make the gtid more clear,I executed "reset master" on binlog server(zlm3) and then copyed those necessary binlogs which were transfered from original master.Further more,I changed back the uuid of zlm3.Because I found that there's no need to change it at all.
The new procedure of demonstration was shown below:
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 status\G 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 status\G 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.
Summary
- 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.