In our daily work, there will always be some erroneous deletion of database tables and data caused by hand shaking, wrong writing conditions, wrong table names, wrong connection with production database. Then, if the data can not be recovered, what DBA is needed.
Related articles
1 Preface
The premise of data recovery is backup, and binlog is opened in row format. If there is no backup file, then delete the database table and delete it. If there are records in lsof, it is possible to restore some files. But if the database does not open the table file, it will have to run away. If the binlog is not turned on, the data from the backup time point will be lost after the data is restored. If the binlog format is not row, then there is no way to do flash back operation after misoperation of data, only to honestly follow the backup recovery process.
2. Direct recovery
Direct recovery is full recovery using backup files, which is the most common scenario
2.1 Full Recovery of Mysqldump Backup
Using mysqldump file to recover data is very simple, decompressing the execution directly
gzip -d backup.sql.gz | mysql -u<user> -h<host> -P<port> -p
2.2 xtrabackup backup full recovery
Recovery process
# Step 1: Decompression (if there is no compression, you can ignore this step) Innobackupex -- decompress < directory where backup files are located > # Step 2: Apply logs Innobackupex -- apply-log < directory where backup files are located > # Step 3: Copy the backup file to the data directory Innobackupex -- dataDir = < MySQL Data Directory > -- copy-back < directory where backup files are located >
2.3 Time Point Based Recovery
Time-based recovery relies on binlog logs. You need to find all logs from backup point to recovery point in binlog, and then apply them. Let's test them.
New Test Form
chengqm-3306>>show create table mytest.mytest \G; *************************** 1. row *************************** Table: mytest Create Table: CREATE TABLE `mytest` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ctime` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
Insert one data per second
[mysql@mysql-test ~]$ while true; do mysql -S /tmp/mysql.sock -e 'insert into mytest.mytest(ctime)values(now())';date;sleep 1;done
backups
[mysql@mysql-test ~]$ mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -S /tmp/mysql.sock -A > backup.sql
Find the log location for backup
[mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE' -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000032', MASTER_LOG_POS=39654;
Suppose we want to restore to the time point of 2019-08-09 11:01:54, we look up the logs from 39654 to 019-08-09 11:01:54 in binlog.
[mysql@mysql-test ~]$ mysqlbinlog --start-position=39654 --stop-datetime='2019-08-09 11:01:54' /data/mysql_log/mysql_test/mysql-bin.000032 > backup_inc.sql [mysql@mysql-test-83 ~]$ tail -n 20 backup_inc.sql ...... ### INSERT INTO `mytest`.`mytest` ### SET ### @1=161 /* INT meta=0 nullable=0 is_null=0 */ ### @2='2019-08-09 11:01:53' /* DATETIME(0) meta=0 nullable=1 is_null=0 */ ......
Current number of data bars
-- 2019-08-09 11:01:54 Number of previous data bars chengqm-3306>>select count(*) from mytest.mytest where ctime < '2019-08-09 11:01:54'; +----------+ | count(*) | +----------+ | 161 | +----------+ 1 row in set (0.00 sec) -- Number of all data bars chengqm-3306>>select count(*) from mytest.mytest; +----------+ | count(*) | +----------+ | 180 | +----------+ 1 row in set (0.00 sec)
Then perform recovery
# Full recovery [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql # Applying Incremental Logging [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc.sql
Check data
chengqm-3306>>select count(*) from mytest.mytest; +----------+ | count(*) | +----------+ | 161 | +----------+ 1 row in set (0.00 sec) chengqm-3306>>select * from mytest.mytest order by id desc limit 5; +-----+---------------------+ | id | ctime | +-----+---------------------+ | 161 | 2019-08-09 11:01:53 | | 160 | 2019-08-09 11:01:52 | | 159 | 2019-08-09 11:01:51 | | 158 | 2019-08-09 11:01:50 | | 157 | 2019-08-09 11:01:49 | +-----+---------------------+ 5 rows in set (0.00 sec)
It has been restored to the time point of 11:01:54 in 2019-08-09.
3 Restore a table
3.1 Restore a table from mysqldump backup
Suppose the table to be restored is mytest.mytest
# Extract all data from a library sed -n '/^-- Current Database: `mytest`/,/^-- Current Database:/p' backup.sql > backup_mytest.sql # Extracting table-building statements from library backup files sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `mytest`/!d;q' backup_mytest.sql > mytest_table_create.sql # Extracting Insert Data Statements from Library Backup Files grep -i 'INSERT INTO `mytest`' backup_mytest.sql > mytest_table_insert.sql # Restore table structure to mytest Library mysql -u<user> -p mytest < mytest_table_create.sql # Restore table data to mytest.mytest table mysql -u<user> -p mytest < mytest_table_insert.sql
3.2 Restore a table from xtrabackup backup
Suppose. / backup_xtra_full directory is a backup file that has been logged after decompression
3.2.1 MyISAM table
Suppose you restore the table mytest.t_myisam from the backup file, find the three files t_myisam.frm t_myisam.MYD t_myisam.MYI from the backup file, copy them to the corresponding data directory, and authorize them.
Enter MySQL and check your expression
chengqm-3306>>show tables; +------------------+ | Tables_in_mytest | +------------------+ | mytest | | t_myisam | +------------------+ 2 rows in set (0.00 sec) chengqm-3306>>check table t_myisam; +-----------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------+-------+----------+----------+ | mytest.t_myisam | check | status | OK | +-----------------+-------+----------+----------+ 1 row in set (0.00 sec)
3.2.2 Innodb table
Suppose the table mytest.t_innodb is restored from the backup file, provided that innodb_file_per_table = on is set
- Take a new example
- Create an example table that looks exactly the same as the original
- Execute alter table t_innodb discard table space; delete table space, which will delete t_innodb.ibd
- Find the t_innodb.ibd file from the backup file, copy it to the corresponding data directory, and authorize it
- Execute alter table t_innodb IMPORT table space; load table space
- Execute flush table t_innodb;check table t_innodb; check table
- Export data using mysqldump and then import it into the database to be restored
Be careful:
- Recovering and dump on new instances is to avoid risks. If tested, steps 2-6 can be operated directly on the original library.
- Only prior to 8.0 versions are valid
4 skip error operation SQL
Skipping error operations SQL is generally used to perform operations that cannot be flashed back, such as drop table database
4.1 Recovery skip with backup file
4.1.1 Do not open GTID
The steps of using backup file recovery are similar to those based on point-in-time recovery, but the difference is that there is one more binlog lookup operation.
For example, I set up two tables a and b here, insert one data per minute, make a full backup, and delete table b. Now I want to skip this SQL.
Database status after deleting table b
chgnqm-3306>>show tables; +------------------+ | Tables_in_mytest | +------------------+ | a | +------------------+ 1 row in set (0.00 sec)
1 Find the log location for backup
[mysql@mysql-test ~]$ head -n 25 backup.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE' -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=38414;
2 Find the pos location where the drop table statement was executed
[mysql@mysql-test mysql_test]$ mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000034 | grep -i -B 3 'drop table `b`'; # at 120629 #190818 19:48:30 server id 83 end_log_pos 120747 CRC32 0x6dd6ab2a Query thread_id=29488 exec_time=0 error_code=0 SET TIMESTAMP=1566128910/*!*/; DROP TABLE `b` /* generated by server */
From the results, we can see that the starting position of the drop statement is 120629 and the ending position is 120747.
3 Extract other records from binglog that skip this statement
# The first start-position is the pos location of the backup file, and stop-position is the start location of the drop statement. mysqlbinlog -vv --start-position=38414 --stop-position=120629 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_1.sql # The start-position of the second entry is the end of the drop statement mysqlbinlog -vv --start-position=120747 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_2.sql
4 Restore backup files
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql
State after full recovery
chgnqm-3306>>show tables; +------------------+ | Tables_in_mytest | +------------------+ | a | | b | +------------------+ 2 rows in set (0.00 sec) chgnqm-3306>>select count(*) from a; +----------+ | count(*) | +----------+ | 71 | +----------+ 1 row in set (0.00 sec)
5 Restore Incremental Data
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_1.sql [mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc_2.sql
After recovery, you can see that the drop statement has been skipped
chgnqm-3306>>show tables; +------------------+ | Tables_in_mytest | +------------------+ | a | | b | +------------------+ 2 rows in set (0.00 sec) chgnqm-3306>>select count(*) from a; +----------+ | count(*) | +----------+ | 274 | +----------+ 1 row in set (0.00 sec)
4.1.2 Open GTID
Using GTID, you can skip the wrong SQL directly
- Find the log location for backup
- Find out the GTID value that executed the drop table statement
- Export the log location to the latest binglog log when backup is exported
- restore backed up files
-
Skip this GTID
SET SESSION GTID_NEXT='Corresponding GTID value'; BEGIN; COMMIT; SET SESSION GTID_NEXT = AUTOMATIC; - Incremental binlog logs obtained by applying step 3
4.2 Delay Library Skipping
4.2.1 Do not open GTID
The key to using delay library recovery is to start slave until
In the test environment, I built two MySQL nodes, two nodes delay 600 seconds, two new tables a and b, insert one data per second to simulate business data insertion.
localhost:3306 -> localhost:3307(delay 600)
Current Node Two State
chengqm-3307>>show slave status \G; ... Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000039 Read_Master_Log_Pos: 15524 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 22845 Relay_Master_Log_File: mysql-bin.000038 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Seconds_Behind_Master: 600 ...
Current Node Two Table
chengqm-3307>>show tables; +------------------+ | Tables_in_mytest | +------------------+ | a | | b | +------------------+
Delete table b at node one
chengqm-3306>>drop table b; Query OK, 0 rows affected (0.00 sec) chengqm-3306>>show tables; +------------------+ | Tables_in_mytest | +------------------+ | a | +------------------+ 1 row in set (0.00 sec)
The next step is to skip this SQL operation step.
1 Delay Library Stops Synchronization
stop slave;
2 Find the pos position of the first sentence of the drop table statement
[mysql@mysql-test ~]$ mysqlbinlog -vv /data/mysql_log/mysql_test/mysql-bin.000039 | grep -i -B 10 'drop table `b`'; ... # at 35134 #190819 11:40:25 server id 83 end_log_pos 35199 CRC32 0x02771167 Anonymous_GTID last_committed=132 sequence_number=133 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 35199 #190819 11:40:25 server id 83 end_log_pos 35317 CRC32 0x50a018aa Query thread_id=37155 exec_time=0 error_code=0 use `mytest`/*!*/; SET TIMESTAMP=1566186025/*!*/; DROP TABLE `b` /* generated by server */
From the result, we can see that the beginning of the first sentence of drop is 35134, so we synchronize to 35134.
3 Delay libraries synchronized to the previous SQL to skip
change master to master_delay=0; start slave until master_log_file='mysql-bin.000039',master_log_pos=35134;
View the status to see that it has been synchronized to the corresponding node
chengqm-3307>>show slave status \G; ... Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000039 Read_Master_Log_Pos: 65792 ... Slave_IO_Running: Yes Slave_SQL_Running: No Exec_Master_Log_Pos: 35134 ... Until_Log_File: mysql-bin.000039 Until_Log_Pos: 35134
4 Start synchronization after skipping an SQL
set global sql_slave_skip_counter=1; start slave;
Looking at the synchronization status, the statement deleting table b has been skipped
chengqm-3307>>show slave status \G; ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ... 1 row in set (0.00 sec) chengqm-3307>>show tables; +------------------+ | Tables_in_mytest | +------------------+ | a | | b | +------------------+ 2 rows in set (0.00 sec)
4.2.2 Open GTID
The steps to skip using GTID are much simpler and can be skipped by executing the same transaction as the GTID of the SQL to skip.
1 Stop Synchronization
2 Find the GTID that executed the drop table statement
3 Execute the GTID transaction
SET SESSION GTID_NEXT='Corresponding GTID value'; BEGIN; COMMIT; SET SESSION GTID_NEXT = AUTOMATIC;
4 Continue Synchronization
5 flashback
Flash-back operation is a reverse operation, such as delete from a where id=1. Flash-back will execute insert into a (id,...) values(1,...), which is used to misoperate data. It is only valid for DML statements and requires binlog format to be ROW. This chapter introduces two more useful open source tools
5.1 binlog2sql
Bilog2sql is an open source tool for public comment to parse binlog. It can be used to generate flashback statements and project addresses. binlog2sql
5.1.1 Installation
wget https://github.com/danfengcao/binlog2sql/archive/master.zip -O binlog2sql.zip unzip binlog2sql.zip cd binlog2sql-master/ # Installation dependency pip install -r requirements.txt
5.1.2 Generate rollback SQL
python binlog2sql/binlog2sql.py --flashback \ -h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name>\ --start-file='<binlog_file>' \ --start-datetime='<start_time>' \ --stop-datetime='<stop_time>' > ./flashback.sql python binlog2sql/binlog2sql.py --flashback \ -h<host> -P<port> -u<user> -p'<password>' -d<dbname> -t<table_name> \ --start-file='<binlog_file>' \ --start-position=<start_pos> \ --stop-position=<stop_pos> > ./flashback.sql
5.2 MyFlash
MyFlash is a rollback DML operation tool developed and maintained by the Technical Engineering Department of the Art Corps Review Company. Project links MyFlash
Limitations:
- The binlog format must be row, and binlog_row_image=full
- Support only 5.6 and 5.7
- DML can only be rolled back (add, delete, change)
5.2.1 Installation
# Dependency (centos) yum install gcc* pkg-config glib2 libgnomeui-devel -y # Download files wget https://github.com/Meituan-Dianping/MyFlash/archive/master.zip -O MyFlash.zip unzip MyFlash.zip cd MyFlash-master # Compile and install gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback mv binary /usr/local/MyFlash ln -s /usr/local/MyFlash/flashback /usr/bin/flashback
5.2.2 Use
Generate rollback statements
flashback --databaseNames=<dbname> --binlogFileNames=<binlog_file> --start-position=<start_pos> --stop-position=<stop_pos>
After execution, the binlog_output_base. flash back file is generated, which needs to be parsed out with mysqlbinlog for reuse.
mysqlbinlog -vv binlog_output_base.flashback | mysql -u<user> -p