MySQL Data Recovery

When a customer updates data, the database content is deleted by mistake, because the database is master-slave, but not backup (backup is very important !) Fortunately, the bin-log is enabled, and the entire log record has to be taken back to the local for recovery.

Then I did a simple test to restore the data as follows:

1. Create a new table
Create table 'lynn '. 'sn _ test' ('name' VARCHAR (10) character set utf8 COLLATE utf8_bin not null, 'age' INT (3) not null) ENGINE = MYISAM;

2. Insert multiple data entries
Insert into 'lynn '. 'sn _ test' ('name', 'age') VALUES ('lynn1', '1 ');
Insert into 'lynn '. 'sn _ test' ('name', 'age') VALUES ('lynn2', '2 ');
Insert into 'lynn '. 'sn _ test' ('name', 'age') VALUES ('lynn3', '3 ');
Insert into 'lynn '. 'sn _ test' ('name', 'age') VALUES ('lynn4', '4 ');

3. View and delete data
Mysql> select * from sn_test;
+ ------- + ----- +
| Name | age |
+ --------- + --- +
| Lynn1 | 1 |
| Lynn2 | 2 |
| Lynn3 | 3 |
| Lynn4 | 4 |
+ --------- + ----- +
4 rows in set (0.00 sec)

Mysql> delete from sn_test;
Query OK, 4 rows affected (0.00 sec)

Mysql> select * from sn_test;
Empty set (0.00 sec)

4. Restore data using mysqlbinlog
Mysqlbinlog mysql-bin.000006> 1. SQL
Records inserted into the TXT file to restore the deleted data.
Mysqlbinlog mysql-bin.000006 -- start-position = 2471 -- stop-position = 2876 | mysql-uroot-p123

Log On again to view the data. OK. The data has been successfully restored.

Pay attention to the following issues for database operations:
1. Always back up (full backup, Incremental Backup). If something goes wrong, you can restore the data as quickly as possible;
2. dump the database or table to be operated before operating the database;
3. You need to open the bin-log. Even if you have not done the above two steps, you can restore data through the log.