恢复误删除表黑科技之relay log大法
2024-08-31 23:53:55
Preface
In my previous blogs,I've demonstrated several mothods of how to rescue a dropped table(or truncated table as well).
- full mysqldump backup + binlog on master(master was normally running)
- full Xtrabackup backup + binlog on master(master was normally running)
- full Xtrabackup backup + binlog on binlog server( binlog server had acquired binlogs before master crashed.)
Les't see another way to achieve the goal more simply.
Procedure
Step 1. Destroy
Check the table on master.
(zlm@192.168.1.101 )[sysbench]>show tables;
ERROR (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:
Current database: sysbench +--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
+--------------------+
rows in set (0.00 sec) (zlm@192.168.1.101 )[sysbench]>select count(*) from sbtest5;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.01 sec)
Generate a full Xtrabackup of master.
[root@zlm2 :: /data/mysql/mysql3306/data]
#innobackupex --default-file=/data/mysql/mysql3306/my.cnf --host=localhost -uroot -pPassw0rd /data/backup
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
:: innobackupex: Starting the backup operation
... //Omitted. :: Executing UNLOCK TABLES
:: All tables unlocked
:: [] Copying ib_buffer_pool to /data/backup/--31_06--/ib_buffer_pool
:: [] ...done
:: Backup created in directory '/data/backup/2018-07-31_06-29-03/'
MySQL binlog position: filename 'mysql-bin.000043', position '', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229'
:: [] Writing /data/backup/--31_06--/backup-my.cnf
:: [] ...done
:: [] Writing /data/backup/--31_06--/xtrabackup_info
:: [] ...done
xtrabackup: Transaction log of lsn () to () was copied.
:: completed OK!
Continue to executing some dml operations on the target table and then kill the mysqld.
(zlm@192.168.1.101 )[sysbench]>delete from sbtest5 limit ;
Query OK, rows affected (0.10 sec) (zlm@192.168.1.101 )[sysbench]>delete from sbtest5 limit ;
Query OK, rows affected (0.07 sec) (zlm@192.168.1.101 )[sysbench]>select count(*) from sbtest5;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) (zlm@192.168.1.101 )[sysbench]>drop table sbtest5;
Query OK, rows affected (0.01 sec) (zlm@192.168.1.101 )[sysbench]>flush logs;
Query OK, rows affected (0.02 sec) (zlm@192.168.1.101 )[sysbench]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------------+
| mysql-bin. | | | | 1b7181ee-6eaf-11e8-998e-080027de0e0e:- |
+------------------+----------+--------------+------------------+------------------------------------------------+
row in set (0.00 sec) [root@zlm2 :: ~]
#pkill mysqld [root@zlm2 :: ~]
#ps aux|grep mysqld
root 0.0 0.0 pts/ R+ : : grep --color=auto mysqld
Scp the Xtrabackup backup to another server zlm3 with newly initialized instance
[root@zlm2 :: ~]
#scp -r /data/backup/--31_06--/ zlm3:/data/backup
root@zlm3's password:
xtrabackup_logfile % .5KB/s :
ibdata1 % 100MB .0MB/s :
plugin.ibd % 96KB .0KB/s :
servers.ibd % 96KB .0KB/s :
... //Omitted.
Step 2. Rescue
Restore the backup on zlm3.
[root@zlm3 :: ~]
#innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --apply-log /data/backup/--31_06--/
... //Omitted. [root@zlm3 :: ~]
#cd /data/mysql/mysql3306/data/ [root@zlm3 :: /data/mysql/mysql3306/data]
#ls -l
total
-rw-r----- mysql mysql Jul : auto.cnf
-rw-r----- mysql mysql Jul : error.log
-rw-r----- mysql mysql Jul : ib_buffer_pool
-rw-r----- mysql mysql Jul : ibdata1
-rw-r----- mysql mysql Jul : ib_logfile0
-rw-r----- mysql mysql Jul : ib_logfile1
-rw-r----- mysql mysql Jul : ib_logfile2
drwxr-x--- mysql mysql Jul : mysql
drwxr-x--- mysql mysql Jul : performance_schema
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin-group_replication_applier.
-rw-r----- mysql mysql Jul : relay-bin-group_replication_applier.index
-rw-r----- mysql mysql Jul : relay-bin-group_replication_recovery.
-rw-r----- mysql mysql Jul : relay-bin-group_replication_recovery.index
-rw-r----- mysql mysql Jul : relay-bin.index
-rw-r----- mysql mysql Jul : slow.log
drwxr-x--- mysql mysql Jul : sys
drwxr-x--- mysql mysql Jul : sysbench
-rw-r----- mysql mysql Jul : xtrabackup_binlog_pos_innodb
-rw-r----- mysql mysql Jul : xtrabackup_info
-rw-r----- mysql mysql Jul : xtrabackup_master_key_id [root@zlm3 :: /data/mysql/mysql3306/data]
#rm -rf * [root@zlm3 :: /data/mysql/mysql3306/data]
#ps aux|grep mysqld
root 0.0 0.0 pts/ R+ : : grep --color=auto mysqld [root@zlm3 :: ~]
#innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/--31_06--/
... //Omitted. :: [] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
:: [] ...done
:: [] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
:: [] ...done
:: [] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
:: [] ...done
:: [] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
:: [] ...done
:: [] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
:: [] ...done
:: completed OK! [root@zlm3 :: /data/mysql/mysql3306/data]
#ls -l
total
-rw-r----- root root Jul : ib_buffer_pool
-rw-r----- root root Jul : ibdata1
-rw-r----- root root Jul : ib_logfile0
-rw-r----- root root Jul : ib_logfile1
-rw-r----- root root Jul : ib_logfile2
-rw-r----- root root Jul : ibtmp1
drwxr-x--- root root Jul : mysql
drwxr-x--- root root Jul : performance_schema
drwxr-x--- root root Jul : sys
drwxr-x--- root root Jul : sysbench
-rw-r----- root root Jul : xtrabackup_binlog_pos_innodb
-rw-r----- root root Jul : xtrabackup_info
-rw-r----- root root Jul : xtrabackup_master_key_id
drwxr-x--- root root Jul : zlm [root@zlm3 :: /data/mysql/mysql3306/data]
#chown -R mysql.mysql *
Startup the MySQL instance on zlm3.
[root@zlm3 :: /data/mysql/mysql3306/data]
#sh /root/mysqld.sh [root@zlm3 :: /data/mysql/mysql3306/data]
#ps aux|grep mysqld
mysql 20.0 17.7 pts/ Sl : : mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
root 0.0 0.0 pts/ R+ : : grep --color=auto mysqld [root@zlm3 :: /data/mysql/mysql3306/data]
#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL) Copyright (c) , , Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (zlm@192.168.1.102 )[(none)]>select count(*) from sysbench.sbtest5;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.02 sec)
The data in Xtrabackup of master has been restored on zlm3. Notice,it doesn't contain the operations of deletion 5000 rows. Firstly,I supposed that the mysqld has crashed and it can never start again. Secondly,I don't have binlog server any more this time.Is there any other way to restore the dropping table and guarantee the change will not lose on it?How can we restore the data safely and simply?Surely there is.
Even thought the mysqld process is down on master.I still can get the binlog files on it.How about change the master binlog files into relay log files and apply them on zlm3?Let's have a try.
Step 3. Special technique
Make sure which binlogs we need and copy them to zlm3.
[root@zlm3 :: /data/backup/--31_06--]
#cat xtrabackup_binlog_info
mysql-bin. 1b7181ee-6eaf-11e8-998e-080027de0e0e:- //It means that the binlog we need should begin from mysql-bin.000043. [root@zlm2 :: /data/mysql/mysql3306/data]
#cd ../logs [root@zlm2 :: /data/mysql/mysql3306/logs]
#ls -l
total
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.index [root@zlm2 :: /data/mysql/mysql3306/logs]
#scp mysql-bin.{,} zlm3:/data/backup root@zlm3's password:
mysql-bin. % 932KB .1KB/s :
mysql-bin. % .2KB/s :
Transfer the mysql-bin files into relay-bin files.
[root@zlm3 :: /data/backup/--31_06--]
#cd .. [root@zlm3 :: /data/backup]
#ls -l
total
drwxr-x--- root root Jul : --31_06--
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : mysql-bin. [root@zlm3 :: /data/backup]
#for i in $(ls mysql-bin.*)
> do
> ext=$(echo $i | cut -d'.' -f2);
> cp $i relay-bin.$ext;
> done [root@zlm3 :: /data/backup]
#ls -l
total
drwxr-x--- root root Jul : --31_06--
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : relay-bin.
-rw-r----- root root Jul : relay-bin. [root@zlm3 :: /data/backup]
#ls ./relay-bin.* > relay-bin.index [root@zlm3 :: /data/backup]
#ls -l
total
drwxr-x--- root root Jul : --31_06--
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : relay-bin.
-rw-r----- root root Jul : relay-bin.
-rw-r--r-- root root Jul : relay-bin.index [root@zlm3 :: /data/backup]
#cat relay-bin.index
./relay-bin.
./relay-bin. [root@zlm3 :: /data/backup]
#chown mysql.mysql relay* [root@zlm3 :: /data/backup]
#ls -l
total
drwxr-x--- root root Jul : --31_06--
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : mysql-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r--r-- mysql mysql Jul : relay-bin.index
Copy these relay-bin files to the proper directory.
[root@zlm3 :: /data/backup]
#cp relay* /data/mysql/mysql3306/data [root@zlm3 :: /data/backup]
#cd /data/mysql/mysql3306/data [root@zlm3 :: /data/mysql/mysql3306/data]
#ls -l|grep relay
-rw-r----- root root Jul : relay-bin. //Notice,the owner and group has been changed.
-rw-r----- root root Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin-group_replication_applier.
-rw-r----- mysql mysql Jul : relay-bin-group_replication_applier.index
-rw-r----- mysql mysql Jul : relay-bin-group_replication_recovery.
-rw-r----- mysql mysql Jul : relay-bin-group_replication_recovery.index
-rw-r--r-- root root Jul : relay-bin.index [root@zlm3 :: /data/mysql/mysql3306/data]
#chown mysql.mysql relay* [root@zlm3 :: /data/mysql/mysql3306/data]
#ls -l|grep relay
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin-group_replication_applier.
-rw-r----- mysql mysql Jul : relay-bin-group_replication_applier.index
-rw-r----- mysql mysql Jul : relay-bin-group_replication_recovery.
-rw-r----- mysql mysql Jul : relay-bin-group_replication_recovery.index
-rw-r--r-- mysql mysql Jul : relay-bin.index
Restart the mysqld process.
[root@zlm3 :: /data/mysql/mysql3306/data]
#mysqladmin shutdown [root@zlm3 :: /data/mysql/mysql3306/data]
#ps aux|grep mysqld
root 0.0 0.0 pts/ R+ : : grep --color=auto mysqld [root@zlm3 :: /data/mysql/mysql3306/data]
#sh /root/mysqld.sh [root@zlm3 :: /data/mysql/mysql3306/data]
#ps aux|grep mysqld
mysql 11.5 17.8 pts/ Sl : : mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
root 0.0 0.0 pts/ R+ : : grep --color=auto mysqld
Check out the first consistent position we need.
[root@zlm3 :: /data/backup/--31_06--]
#cat xtrabackup_binlog_info
mysql-bin. 1b7181ee-6eaf-11e8-998e-080027de0e0e:-
Execute "change master to" as below.
(zlm@192.168.1.102 )[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=,master_host='xxx';
ERROR (HY000): Failed initializing relay log position: Could not find first log during relay log initialization
(zlm@192.168.1.102 )[(none)]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------------+
| mysql-bin. | | | | 1b7181ee-6eaf-11e8-998e-080027de0e0e:- |
+------------------+----------+--------------+------------------+------------------------------------------------+
row in set (0.00 sec) (zlm@192.168.1.102 )[(none)]>reset master;
Query OK, rows affected (0.02 sec) (zlm@192.168.1.102 )[(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin. | | | | |
+------------------+----------+--------------+------------------+-------------------+
row in set (0.00 sec) (zlm@192.168.1.102 )[(none)]>set @@global.gtid_purged='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229';
Query OK, rows affected (0.01 sec) (zlm@192.168.1.102 )[(none)]>show master status;
+------------------+----------+--------------+------------------+------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------------+
| mysql-bin. | | | | 1b7181ee-6eaf-11e8-998e-080027de0e0e:- |
+------------------+----------+--------------+------------------+------------------------------------------------+
row in set (0.00 sec) (zlm@192.168.1.102 )[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=,master_host='xxx';
ERROR (HY000): Failed initializing relay log position: Could not find first log during relay log initialization (zlm@192.168.1.102 )[(none)]>show slave status\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: xxx
Master_User: test
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: //No relay logs was retrieved here.
Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec)
I was stuck again,faint!T_T...
Supplemented on August 1.
After discussing with my classmate Shuaibing Zhang,I found out that the reason why I got failure above was due to not executing "reset slave all;".Therefore,it meantioned that "Could not find the first log..." when I Executed "change master to ... ".
Execute "change master to ... "
(zlm@192.168.1.102 )[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=,master_host='xxx';
Query OK, rows affected (0.01 sec) (zlm@192.168.1.102 )[(none)]>show slave status\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: xxx
Master_User:
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec)
Analyze the relay-bin file to find out the until postion before dropping operation.
[root@zlm3 :: /data/mysql/mysql3306/data]
#cd /data/backup/ [root@zlm3 :: /data/backup]
#mysqlbinlog --base64-output=decode-rows relay-bin. > .log [root@zlm3 :: /data/backup]
#tail - .log
# :: server id end_log_pos Delete_rows: table id flags: STMT_END_F
# at
# :: server id end_log_pos Xid =
COMMIT/*!*/;
# at
# :: server id end_log_pos GTID last_committed= sequence_number= rbr_only=no
SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730232'/*!*/;
# at
# :: server id end_log_pos Query thread_id= exec_time= error_code=
use `sysbench`/*!*/;
SET TIMESTAMP=/*!*/;
DROP TABLE `sbtest5` /* generated by server */ //Here's the dropping operation.Therefore,the util position we need is "954251" which just below the "COMMIT/*!*/;"
/*!*/;
# at
# :: server id end_log_pos Rotate to mysql-bin. pos:
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Execute start slave sql_thread util clause.
(zlm@192.168.1.102 )[(none)]>start slave sql_thread until relay_log_file='relay-bin.000043',relay_log_pos=;
Query OK, rows affected (0.00 sec) (zlm@192.168.1.102 )[(none)]>show slave status\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: xxx
Master_User:
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: Relay
Until_Log_File: relay-bin.
Until_Log_Pos:
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) (zlm@192.168.1.102 )[(none)]>select count(*) from sysbench.sbtest5;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.01 sec)
Summary
The simply description of rescuing data with relay log method is shown below:
The precondition is that the master has a up-to-date full backup.(Xtrabackup or mysqldump)
1. Copy all the binlog files to another server which has a newly initialized instance.
2. Restore the backup on new instance and check data is restored normally.
3. Execute "reset slave all;" to clear the original replication information(restored from master).
4. Reconfigure those binlog files with "relay-bin.xxxxxx" format together with relay-bin.index file.
5. Copy those relay logs to the proper datadir and change the ownership and group of them.
6. Execute "change master to ... " with "relay_log_file" and "relay_log_pos".
7. Execute "change replication filter ... " if you're supposed to merely restore a single table.(optional)
8. Execute "start slave sql_thread until ... " to restore data until the position you need.(both relay_log_pos and sql_before_gtids is okay)
最新文章
- mongdo通用类(C#版)
- 使用spring boot+mybatis+mysql 构建RESTful Service
- 13Mybatis_SqlMapConfig.xml专题讲解
- js中object的申明方法
- GNOME Shell叫板Ubuntu Unity:优劣PK
- [其他]win7下chrome浏览器插件导出与导入
- 把Eclipse项目转换成Maven项目
- 备忘录《一》基于cookie使用拦截器实现客户每次访问自登陆一次
- vue路由
- $ Django 调API的几种方式
- C 标准库头文件
- case class 和class的区别以及构造器参数辨析
- nginx反向代理服务器获取不到端口的问题的解决办法
- 基于官方镜像MySQL做自定义业务扩充镜像
- 解决FatalThrowableError in EloquentUserProvider.php line 126: Class '\App\User'问题
- mysql join left join区别
- 使用SQL语句的子查询批量复制表数据
- Golang学习系列：（一）介绍和安装
- Mac下使用svn命令
- 图解Python可以做些什么