1. Demonstration environment description:
System CentOS Linux release 7.2.1511 (Core) X_86 64-bit minimal installation
The mysql version is the official binary version 5.7.22-22. mysql is a binary installation. Open two mysql instances on a single machine, open the timer event_scheduler=ON for the mysql instances, and open Gtid for both mysql instances
xtrabackup is installed using an rpm package, version 2.4.13
MySQL backup uses daily full backup and incremental binlog backup
2. Simulate deletion library for data recovery demonstration:
Tip: Of course, this is just a demonstration. It is strictly forbidden to delete libraries or tables for simulation. You know the consequences
Fault simulation:
Mistakenly delete a test1_event table from the testdb library online, restoring the data to the mistakenly deleted table test1_event using the full backup of mysql on that day + the binlog file of mysql generated on that day
Recovery method introduction:
The official recommendation for recovery using mysql binlog is no longer rigorous (the method below is officially recommended).
The recovery process is as follows:
2.1 Confirm the approximate error operation time at the first time before accepting the error deletion
2.2 Log in to the main library to see the current binlog location (remember the binlog file at this point, which you will use later for recovery)
(root@'mgr01':mysql3306.sock)[testdb]>show master status\G
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 15211
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: bde7b592-b966-11e9-8c64-000c294f3e61:1-10445
1 row in set (0.00 sec)2.3 It is best to have the next sql written to the new binlog file under flush logs
2.4 Locate the location point of the binglog file where the drop table statement is located:
[root@mgr01 binlog]# mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/binlog/mysql-bin.000005|grep -i -C 15 drop ### @1=10422 ### @2='tomcat' ### @3='xiaohuahua' ### @4='2019-08-08 14:22:18' # at 14987 #190808 14:22:18 server id 63306 end_log_pos 15018 CRC32 0x873943dd Xid = 20695 COMMIT/*!*/; #at15018################################### #190808 14:22:19 server id 63306 end_log_pos 15083 CRC32 0xcc8773ce GTID last_committed=34 sequence_number=35 rbr_only=no SET @@SESSION.GTID_NEXT= 'bde7b592-b966-11e9-8c64-000c294f3e61:10445'/*!*/; #at 15083 #190808 14:22:19 server id 63306 end_log_pos 15211 CRC32 0x8d445019 Query thread_id=7213 exec_time=0 error_code=0 use `testdb`/*!*/; SET TIMESTAMP=1565245339/*!*/; SET @@session.sql_auto_is_null=0/*!*/; DROP TABLE `test1_event` /* generated by server */ /*!*/; 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*/;
Locate to 1518, the end point for file recovery using binlog
2.5 Restore xtrabackup backup to mysql3308 instance:
Tip: mysql 3308 instance to open Gtid's
Command to restore backup:
innobackupex --apply-log /data/backup/db_3306_20190808/ innobackupex --defaults-file=/data/mysql/mysql3308/my3308.cnf --copy-back /data/backup/db_3306_20190808/
Give data mysql permission to the data directory:
chown -R mysql.mysql /data/mysql/mysql3308/data/
Start mysql3308 instance:
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
You can see the data restored to 3308 instances, but there are still a lot of missing data to retrieve from the incremental binlog file during the period from backup of 3306 libraries to deletion of the table test1_event:
| 10273 | tomcat | xiaohuahua | 2019-08-08 14:17:16 | | 10274 | tomcat | xiaohuahua | 2019-08-08 14:17:18 | +-------+----------+------------+---------------------+
(root@'mgr01':mysql3308.sock)[testdb]>select count(*) from test1_event; +----------+ | count(*) | +----------+ | 10273 | +----------+ 1 row in set (0.00 sec)
xtrabackup backup is Gtid enabled, so when using mysqlbinlog command for incremental binglog file recovery data below, add a parameter--skip-gtids Ignores the Gtid information in the binlog file, if skip-gtids is not used for recovery, the data will not recover to 3308 Libraries
The correct recovery command is as follows:
mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000001 --skip-gtids |mysql -f --binary-mode -S /tmp/mysql3308.sock mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000002 --skip-gtids |mysql -f --binary-mode -S /tmp/mysql3308.sock mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000003 --skip-gtids |mysql -f --binary-mode -S /tmp/mysql3308.sock mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000004 --skip-gtids |mysql -f --binary-mode -S /tmp/mysql3308.sock mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000005 --stop-position="15018" --skip-gtids |mysql -f --binary-mode -S /tmp/mysql3308.sock
Parameter description:
--skip-gtids ignores Gtid information in binlog files
--binary-mode is mainly used to solve Chinese scrambling or special string scrambling problem
-f Force recovery, ignore errors
After executing the above command, the data is restored to the point before deleting the table test1_event
The following recovery command will result in data not being recovered on the mysql 3308 instance: (because the my3308 instance turned on the Gtid parameter)
mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000001 |mysql -f --binary-mode -S /tmp/mysql3308.sock mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000002 |mysql -f --binary-mode -S /tmp/mysql3308.sock mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000003 |mysql -f --binary-mode -S /tmp/mysql3308.sock mysqlbinlog /data/mysql/mysql3306/binlog/mysql-bin.000004 |mysql -f --binary-mode -S /tmp/mysql3308.sock mysqlbinlog --stop-position="15018" /data/mysql/mysql3306/binlog/mysql-bin.000005|mysql -f --binary-mode -S /tmp/mysql3308.sock
2.6 If the Gtid parameter is turned off from my3308.cnf before the new instance mysql3308 starts:
3.
In this case, using MySQL binlog/data/mysql/mysql/mysql3306/binlog/mysql-bin.00000* |mysql-f --binary-mode-S/tmp/mysql3308.sock would restore the data to the mysql 3308 instance, but an error occurred during the recovery process.This restores the data to 308.
The resulting binlog file does not record Gtid information
[root@mgr01 backup]# mysqlbinlog --stop-position="15018" /data/mysql/mysql3306/binlog/mysql-bin.000005|mysql -f --binary-mode -S /tmp/mysql3308.sock ERROR 1781 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF. ERROR 1781 (HY000) at line 50: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF. ERROR 1781 (HY000) at line 74: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF. ERROR 1781 (HY000) at line 98: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.
Of course, the following methods can be used to recover the same 3308 instance of the binglog file is not recorded Gtid information, this method may have errors in the recovery process, do not fully guarantee the integrity and correctness of the data, so production is best not to use this method to recover the retrieved data
[root@mgr01 backup]# mysqlbinlog --skip-gtids /data/mysql/mysql3306/binlog/mysql-bin.000001|mysql -f --binary-mode -S /tmp/mysql3308.sock ERROR 1050 (42S01) at line 27: Table 'test1_event' already exists ERROR 1062 (23000) at line 92: Duplicate entry '1' for key 'PRIMARY' ERROR 1537 (HY000) at line 132: Event 'e_test' already exists mysqlbinlog --skip-gtids /data/mysql/mysql3306/binlog/mysql-bin.000002|mysql -f --binary-mode -S /tmp/mysql3308.sock mysqlbinlog --skip-gtids /data/mysql/mysql3306/binlog/mysql-bin.000003|mysql -f --binary-mode -S /tmp/mysql3308.sock mysqlbinlog --skip-gtids /data/mysql/mysql3306/binlog/mysql-bin.000004|mysql -f --binary-mode -S /tmp/mysql3308.sock mysqlbinlog --skip-gtids --stop-position="15018" /data/mysql/mysql3306/binlog/mysql-bin.000005|mysql -f --binary-mode -S /tmp/mysql3308.sock
Friendly Tip: It's best to turn on Gtid in production. It's very convenient to restore data and database synchronization and resolve synchronization errors
Summary:
For DDL statements such as drop database, drop tables, drop tables, truncate table, mysqlbinlog can be used to recover them.You can also use the binlog2sql tool to flash back and forth
For such statements, whether the binglog format is row, the statement format, or the Mixed format, the binlog format of the record is row
Extraction can only be supported at the database level with mysqlbinlog
Full+Restore to a point in time using mysqlbinlog
Using mysqlbinlog--skip-gtids has a high risk point