MySQL Database recovery process

MySQL Database recovery process

A customer update data, mistakenly deleted the contents of the database, because the database did the master and slave, but did not do backup (backup is very important AH!) Fortunately, it opened the Bin-log. Then I had to take the whole log back to the local recovery. After that, I also made a simple test to recover the data, as follows:

What is Binlog?

The Binlog log is used to record all statements that have been updated and have submitted data or that a potential update has submitted data (for example, a delete that does not match any row). Statements are saved as "events," which describe data changes

1. Create a new table

CREATE TABLE ' Lynn '. ' Sn_test ' (' Name ' VARCHAR () CHARACTER SET UTF8 COLLATE utf8_bin not NULL, ' age ' INT (3) Not NUL L) ENGINE = MYISAM;

2. Inserting more than one data

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 data and delete

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. Mysqlbinlog Recovery Data

Mysqlbinlog mysql-bin.000006 > 1.sql

Check the data inserted in 1.txt, and restore the data before deleting it.

Mysqlbinlog mysql-bin.000006--start-position=2471--stop-position=2876 | Mysql-uroot-p123

Re-login, view data, OK, has been successfully restored

For database operations, you should be aware of the following issues:

1, to often backup (full standby, incremental backup), the problem can be the fastest recovery data;

2, before the operation of the database, the need to operate the database or table dump out;

3. Use Backup tool: Multi-backup. Automatic data backup, you need a key to recover. Upload data to multiple cloud platforms with zero risk of loss

4, need to open bin-log, even if not do the above two steps, you can also restore data through the log

This article is from the "Big Meatball" blog, please make sure to keep this source http://12478147.blog.51cto.com/9663367/1600370

MySQL Database recovery process