Guide on recovering data in MySQL

In our daily work, there might be mistakes made which got some data or even databases deleted in MySQL. If this happens on production, it would be a nightmare. In case this happens, normally DBA would jump in to save the world. And they would try to recover the data from the backup if there is any. But if there is no backup, then the show stops.

Hence database backup is necessary on production environments to avoid such awkward situation. Also normally in MySQL, binlog should be enabled as well in ROW mode so that recovery can be replayed starting from the backup time point to the incident time point to recover as much data as possible. 

In this post, some common used techniques will be introduced to recover deleted data in MySQL

Recover from backup

Recovering data from backup files is the most frequently adopted technique. Normally database would be backed up every sometime so that most of the data can be recovered in case any incident happens.

mysqldump backup

Of a backup file is generated using mysqldump command, the backup can be restored with below command. 

gzip -d backup.sql.gz | mysql -u[user] -h[host] -P[port] -p

xtrabackup backup

The general steps to recover xtrabackup files are:

# step 1: decompress the backup file, if it's not compressed, can ignore this step
innobackupex --decompress [backup file directory]

# step 2: apply log
innobackupex --apply-log [backup file directory]

# step 3: copy the back up data to the MySQL data dir
innobackupex --datadir=[MySQL data directory] --copy-back [backup file directory]

binlog recovery

Sometimes recovery would be based on some time points, like the data between some time and some other time. binlog normally can be used in such case. To demonstrate this, below is one example.

First creating a table.

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

Now insert one record every second.

[mysql@mysql-test ~]$ while true; do mysql -S /tmp/mysql.sock -e 'insert into mytest.mytest(ctime)values(now())';date;sleep 1;done

Do data backup with mysqldump.

[mysql@mysql-test ~]$ mysqldump --opt --single-transaction --master-data=2 --default-character-set=utf8 -S /tmp/mysql.sock -A > backup.sql

Locate the log position while backup happens with below command.

[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;

Now assuming want to restore back the data until the timestamp 2019-08-09 11:01:54, the data between log pos 39654 and 2019-08-09 11:01:54 needs to be searched

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

The current status of the table.

-- Number of rows before 2019-08-09 11:01:54
chengqm-3306>>select count(*) from mytest.mytest where ctime < '2019-08-09 11:01:54';
+----------+
| count(*) |
+----------+
|      161 |
+----------+
1 row in set (0.00 sec)

-- Total number of rows in table
chengqm-3306>>select count(*) from mytest.mytest;
+----------+
| count(*) |
+----------+
|      180 |
+----------+
1 row in set (0.00 sec)

Now do the recovery.

# Original data
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql 

# Data incremental file
[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup_inc.sql

The recovered table status.

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)

The data is recovered to the timestamp 2019-08-09 11:01:54.

Recovering table

mysqldump backup

Recovering a table from mysqldump backup can be done with following steps. Assuming the table to be restored is mytest.mytest.

# Extract data for one database
sed -n '/^-- Current Database: `mytest`/,/^-- Current Database:/p' backup.sql > backup_mytest.sql

# Extract the create table statement
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `mytest`/!d;q' backup_mytest.sql > mytest_table_create.sql

# Extract data insertion statements
grep -i 'INSERT INTO `mytest`' backup_mytest.sql > mytest_table_insert.sql

# Restore the table structure in mytest 
mysql -u<user> -p mytest < mytest_table_create.sql

# Restore data to table mytest.mytest
mysql -u<user> -p mytest <  mytest_table_insert.sql

xtrabackup backup

Assuming ./backup_xtra_full is the directory containing the decompressed log files.

MyISAM table

Assuming the table to be restored is mytest.t_myisam, what needs to be done is to copy the t_myisam.frm, t_myisam.MYD, t_myisam.MYI files to the MySQL data directory and give proper permission.

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)

InnoDB table

Assuming the table to be restored is mytest.t_innodb. And one prerequisite is to have setting innodb_file_per_table = on. 

Follow below steps:

1. Create a new DB instance
2. Create a new table with same structure as original table in the new instance
3. Execute alter table t_innodb discard tablespace to discard the tablespace.
4. Copy the file t_innodb.ibd in backup directory to the MySQL data directory and give proper permission
5. Execute alter table t_innodb IMPORT tablespaceto to import the tablespace.
6. Execute flush table t_innodb;check table t_innodb  to flush the table.
7. Using mysqldump to back up the data and the restore it to the original DB instance.

The reason to create table on new instance is to avoid risk. If it's for testing purpose, can execute step 2 - 6 on the original DB instance itself. Above steps will work only on MySQL before 8.0.

Skip some of the statements

Sometimes there is a need to skip some of the statements in backup when doing restore. For example, some of the statements like drop table\database should be skipped during restoration.

A few steps need to be followed. Below is an example. Assuming two tables are created a and b. There is data inserted into the tables every one second and then table b gets deleted. Now the data needs to be restored and the drop table b statement needs to be skipped in order to restore b and keep it.

The table status after dropping table b.

chgnqm-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| a                |
+------------------+
1 row in set (0.00 sec)

Find the log position when back up starts.

[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;

Find the position where 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 */

Find other logs from binlog which has this drop table statement skipped.

# start from start-position of back up file log pos，stop-position is the beigging of drop table statement
mysqlbinlog -vv --start-position=38414 --stop-position=120629 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_1.sql

# start-position is the drop statement end position
mysqlbinlog -vv --start-position=120747 /data/mysql_log/mysql_test/mysql-bin.000034 > backup_inc_2.sql

Restore backup file

[mysql@mysql-test ~]$ mysql -S /tmp/mysql.sock < backup.sql

The DB status after restore.

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)

Restore the 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

The final status post restoration.

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)

Reference: https://segmentfault.com/a/1190000020116271