[Go] accidentally delete data--mysql data recovery

Originating From: http://blog.chinaunix.net/uid-26602509-id-4102099.html

Open the bin log feature of MySQL:
for MySQL is also support incremental backup, but to open the Bin log feature of MySQL.
We modify the MySQL configuration file. Linux is/etc/my.cnf,windows is the installation directory for MySQL/my.ini
We add a log-bin line of code below [Mysqld], as below.

2. 
   
4. [Mysqld]
   
6. log-bin=Mysql-bin
   

Copy CodeAfter the addition of the MySQL can be re-start.

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 opened the Bin-log, then had to take the entire log back to the local recovery.

After that, I also made a simple test to recover the data, as follows:

1. Create a new table
CREATE TABLE ' Lynn '. ' Sn_test ' (' Name ' VARCHAR () CHARACTER SET UTF8 COLLATE utf8_bin not NULL, ' age ' INT (3) Not NU LL) 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, need to open bin-log, even if not do the above two steps, you can also restore data through the log

[Go] accidentally delete data--mysql data recovery