Technology Sharing | How to restore a table and the data in the table by mistake?

Author: Yang Xiaoyun

Akerson database engineer, responsible for MySQL daily maintenance and DMP product support. Good at mysql troubleshooting.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

Scenes:

The customer deleted a table by mistake, so he wants to restore the data of a certain table, hoping to restore the data before the deletion.

premise:

The recovery method of a database accidentally deleted a table, the method described below is for the database that has a backup every day and the binlog log is turned on.

Note: The test library in this article is the test database, and the test table is the student table in the test.

1. Open the binlog log and back up the data

1. Check whether the database has binlog logging enabled

If it is not enabled, the following method is required to enable it

(1) Under the Linux system, modify the /etc/my.cnf file

 #编辑模式进入/etc/my.cnf
vi /etc/my.cnf
# i开始进行编辑
# 在#log bin 后面添加内容
server_id=2
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30
log_bin_basename= /数据库实例安装目录/log/binlog/端口号/mysql-bin       
log_bin_index=/数据库实例安装目录/log/binlog/端口号/mysql-bin.index
# esc 退出编辑，shift+:保存

(2) Restart the mysql service

systemctl restart mysqld

At this point, binlog has been turned on. You can use the check command to see if it is turned on.

2. Take a look at the data in the datasheet

3. Backup data

Backup command format:

 mysqldump [选项] 数据库名 [表名] > 脚本名
mysqldump [选项] --数据库名 [选项 表名] > 脚本名
mysqldump [选项] --all-databases [选项] > 脚本名

(1) Backup all databases

 mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ -all-databases > /test1.sql

(2) Backup a single database (space between multiple databases)

 mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ database > test2.sql

(3) Backup a single table (multiple tables are separated by spaces)

 mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ database table  > test3.sql

(4) Backup the specified database to exclude certain tables

 mysqldump -h10.186.63.4 -P4149 -uu1 -p1234567890q@ database --ignore-table=db.tb --ignore-table=db.tb2 > /test4.sql

4. Delete the database after inserting data

Insert data, generate binlog log

 mysql> insert into student values('201215130','张三','男',21,'IS');
Query OK, 1 row affected (0.03 sec)
mysql>  insert into student values('201215131','李四','女',20,'MA');
Query OK, 1 row affected (0.02 sec)

delete database

 mysql> drop database test;
Query OK, 1 row affected (0.10 sec)

Remember not to do anything at this time! ! !

2. Restoring data

1. View the current binlog

 mysql>  show master status\G;
************************ 1. row ***************************
File: mysql-bin.000021
Position: 68403303
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 130df5fa-01c1-11ed-916a-02000aba3f04:1-446151
1 row in set (0.00 sec)
ERROR:
No query specified
注：mysql-bin.000021文件将作为恢复删除数据的来源之一

2. Copy the binlog log

Copy the current binlog log to another directory to prevent subsequent operations from affecting the binlog log

 cp  /test/data/mysql/log/binlog/4149/mysql-bin.000021  /root

3. Convert binlog log to sql

The command format is: mysqlbinlog -d database mysql-bin file > xx.sql
like:

 /data/mysql/base/5.7.25/bin/mysqlbinlog -d test mysql-bin.000021 > 0021bin.sql
[root@test2 4149]# /data/mysql/base/5.7.25/bin/mysqlbinlog -d test mysql-bin.000022 > 0022.sql
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.

Edit the converted sql file

vi 0021bin.sql

Delete all the misoperation commands (DROP commands) in it

Start to restore data after saving

4. Restore the backup file

 /data/mysql/base/5.7.25/bin/mysql -h10.186.63.4 -P4149 -uu1 -p1234567890q@  < test.sql

Check whether the database backup file is restored

 mysql> show databases;
mysql> use test;
mysql> show tables;
mysql>select * from table;

5. Restore deleted data after backup

Comment out this line in the sql file converted by binlog

 SET @@GLOBAL.GTID_PURGED=
/*SET @@GLOBAL.GTID_PURGED=XXXX*/;

Specify the database where the deleted table is located and import the data

 /data/mysql/base/5.7.25/bin/mysql -h10.186.63.4 -P4149 -uu1 -p1234567890q@  test  < 0021bin.sql

View recovered data

 mysql> select * from test.student;
+-----------+-----------+------+------+-------+
| Sno       | Sname     | Ssex | Sage | Sdept |
+-----------+-----------+------+------+-------+
| 201215121 | 李勇      | 男   |   20 | CS    |
| 201215122 | 刘晨      | 女   |   19 | CS    |
| 201215123 | 王敏      | 女   |   18 | MA    |
| 201215125 | 张立      | 男   |   19 | IS    |
| 201215126 | 李一平    | 男   |   18 | IS    |
| 201215127 | 张琴      | 女   |   19 | CS    |
| 201215128 | 王方      | 女   |   20 | MA    |
| 201215129 | 黄林林    | 男   |   21 | IS    |
| 201215130 | 李四      | 女   |   20 | MA    |
| 201215131 | 张三      | 男   |   21 | IS    |
+-----------+-----------+------+------+-------+
10 rows in set (0.00 sec)