Data recovery for MySQL

Reprinted from: http://ourmysql.com/archives/1293

Database data is mistakenly deleted is often seen, the recovery of data has naturally become the DBA is a very important basic effort, the more clumsy way is to pull the historical backup to another machine to recover, but this method if the data volume is larger, it often takes a long time, Prior to the use of Oracle, there are many ways to recover data, the common way is to use the flashback flashback, or through the LOGMNR in the analysis log to complete the recovery of data, but in MySQL, the recovery of data becomes a very difficult thing.

The Monday colleague's database was due to the developer's data for the right and wrong operation, resulting in a table of all the data is emptied, because the library's data capacity has reached hundreds of G, recovery from the backup takes a long time, so contact me to help restore, because the database is in row mode, The deleted action is a row of records in Binlog, so the recovery is to parse the contents of the Binlog into the corresponding INSERT statement, and the recovery steps are as follows:

1. Use mysqlbing to parse the Binlog file:

mysqlbinlog-vvv/home/mysql/data3006/mysql/mysql-bin.000004 >/tmp/master.log.20120925

2. Because the table that was mistakenly deleted has 13 fields, plus two rows of delete and where, take 15 of these rows:

grep "# #" master.log.20120925 | grep "DELETE from Master.agentgroup"-A >/tmp/xx.log

[Email protected] # More/tmp/xx.log

# # # DELETE from master.del_table

# # WHERE

# # @1=15 */INT meta=0 nullable=0 is_null=0 */

# # @2=1 */INT meta=0 nullable=0 is_null=0 */

# # # @3=2010-09-07 18:03:13 */DATETIME meta=0 nullable=0 is_null=0 * *

# # @4=1 */INT meta=0 nullable=0 is_null=0 */

# # # @5=2012-09-24 01:13:56 */DATETIME meta=0 nullable=0 is_null=0 * *

# # # @6= ' yahoo_yst '/* varstring (384) meta=384 nullable=0 is_null=0 */

# # @7=5259 */INT meta=0 nullable=1 is_null=0 */

# # @8=22 */INT meta=0 nullable=1 is_null=0 */

# # # @9=b ' 0′/* BIT (1) meta=1 nullable=0 is_null=0 * *

# # # @10=b ' 1′/* BIT (1) meta=1 nullable=0 is_null=0 * *

# # @11=null */BIT (1) meta=0 nullable=1 is_null=1 */

# # # @12=b ' 0′/* BIT (1) meta=1 nullable=1 is_null=0 * *

# # @13=18170 */INT meta=0 nullable=1 is_null=0 */

3. Replace ' # # # ' with sed:

[Email protected] # More/tmp/xx.log

DELETE from Master.del_table

WHERE

@1=15/* INT meta=0 nullable=0 is_null=0 */

@2=1/* INT meta=0 nullable=0 is_null=0 */

@3=2010-09-07 18:03:13/* DATETIME meta=0 nullable=0 is_null=0 */

@4=1/* INT meta=0 nullable=0 is_null=0 */

@5=2012-09-24 01:13:56/* DATETIME meta=0 nullable=0 is_null=0 */

@6= ' yahoo_yst '/* varstring (384) meta=384 nullable=0 is_null=0 */

@7=5259/* INT meta=0 nullable=1 is_null=0 */

@8=22/* INT meta=0 nullable=1 is_null=0 */

@9=b ' 0′/* BIT (1) meta=1 nullable=0 is_null=0 * *

@10=b ' 1′/* BIT (1) meta=1 nullable=0 is_null=0 * *

@11=null/* BIT (1) meta=0 nullable=1 is_null=1 */

@12=b ' 0′/* BIT (1) meta=1 nullable=1 is_null=0 * *

@13=18170/* INT meta=0 nullable=1 is_null=0 */

4. Replace ' */' for ', ':

[Email protected] # sed-i ' s/\*\//\*\/,/g '/tmp/xx.log

[Email protected] # More/tmp/xx.log

DELETE from Master.del_table

WHERE

@1=15/* INT meta=0 nullable=0 is_null=0 */,

@2=1/* INT meta=0 nullable=0 is_null=0 */,

@3=2010-09-07 18:03:13/* DATETIME meta=0 nullable=0 is_null=0 */,

@4=1/* INT meta=0 nullable=0 is_null=0 */,

@5=2012-09-24 01:13:56/* DATETIME meta=0 nullable=0 is_null=0 */,

@6= ' yahoo_yst '/* varstring (384) meta=384 nullable=0 is_null=0 */,

@7=5259/* INT meta=0 nullable=1 is_null=0 */,

@8=22/* INT meta=0 nullable=1 is_null=0 */,

@9=b ' 0′/* BIT (1) meta=1 nullable=0 is_null=0 * *,

@10=b ' 1′/* BIT (1) meta=1 nullable=0 is_null=0 * *,

@11=null/* BIT (1) meta=0 nullable=1 is_null=1 */,

@12=b ' 0′/* BIT (1) meta=1 nullable=1 is_null=0 * *,

@13=18170/* INT meta=0 nullable=1 is_null=0 */,

DELETE from Master.del_table

5. Replace the last ' in the log ', ' for '; ':

A.delete the former plus '; ':

Sed-i ' s/delete/;D elete/g '/tmp/xx.log

[Email protected] # More/tmp/xx.log

DELETE from Master.del_table

WHERE

@1=15/* INT meta=0 nullable=0 is_null=0 */,

@2=1/* INT meta=0 nullable=0 is_null=0 */,

@3=2010-09-07 18:03:13/* DATETIME meta=0 nullable=0 is_null=0 */,

@4=1/* INT meta=0 nullable=0 is_null=0 */,

@5=2012-09-24 01:13:56/* DATETIME meta=0 nullable=0 is_null=0 */,

@6= ' yahoo_yst '/* varstring (384) meta=384 nullable=0 is_null=0 */,

@7=5259/* INT meta=0 nullable=1 is_null=0 */,

@8=22/* INT meta=0 nullable=1 is_null=0 */,

@9=b ' 0′/* BIT (1) meta=1 nullable=0 is_null=0 * *,

@10=b ' 1′/* BIT (1) meta=1 nullable=0 is_null=0 * *,

@11=null/* BIT (1) meta=0 nullable=1 is_null=1 */,

@12=b ' 0′/* BIT (1) meta=1 nullable=1 is_null=0 * *,

@13=18170/* INT meta=0 nullable=1 is_null=0 */,

;D elete from Master.del_table

B.delete before the ',; ' to replace with '; ':

vi/tmp/xx.log-->:%s/,$\n^;/;/g

DELETE from Master.del_table

WHERE

@1=29/* INT meta=0 nullable=0 is_null=0 */,

@2=1/* INT meta=0 nullable=0 is_null=0 */,

@3=2010-09-07 18:03:13/* DATETIME meta=0 nullable=0 is_null=0 */,

@4=1/* INT meta=0 nullable=0 is_null=0 */,

@5=2012-06-01 13:05:00/* DATETIME meta=0 nullable=0 is_null=0 */,

@6= ' ALIPAY_FRONT_JX '/* varstring (384) meta=384 nullable=0 is_null=0 */,

@7=5267/* INT meta=0 nullable=1 is_null=0 */,

@8=58/* INT meta=0 nullable=1 is_null=0 */,

@9=b ' 0′/* BIT (1) meta=1 nullable=0 is_null=0 * *,

@10=b ' 1′/* BIT (1) meta=1 nullable=0 is_null=0 * *,

@11=null/* BIT (1) meta=0 nullable=1 is_null=1 */,

@12=b ' 0′/* BIT (1) meta=1 nullable=1 is_null=0 * *,

@13=null/* BIT (1) meta=0 nullable=1 is_null=1 */

;D elete from Master.del_table

@1,@2,@3 .... Corresponds to the field of the table;

6. Finally change the delete from table xx where to insert into XX values (",", "," ...) Both can;

The deleted data can be recovered from Binlog in the 6 steps above, so the Parse_binlog tool is generated, and the tool is written at @ CSL, which converts the binlog of the row pattern to the corresponding SQL statement:

Mysql> Use T1

Database changed

mysql> Delete from T1 where id<12;

Query OK, 2 rows Affected (0.00 sec)

mysqlbinlog-vvv/home/mysql/data3006/mysql/mysql-bin.000004 |/root/parse_binlog.pl >/TMP/PARSE.SQL1

More/tmp/parse/sql1

-DML type:delete, num of Cols:2

Replace into t1.t1 values (, ' ni hao1′);

-DML type:delete, num of Cols:2

Replace into T1.T1 values (one, ' ni hao1′);

This allows the DBA to easily recover the data;

Recently @plinux has completed the patch for this MySQL flashback scheme, and in row mode Binlog The complete information for each row is recorded, insert contains the value of each field, and delete contains the value of each field. Update contains all the field values in the set and where sections. So Binlog is a complete logic redo, the operation of its inverse, is the need for "undo"; @ Wu Bingxi The good guy has compiled it for the open source community and can download it here:

Mysql> show master logs;

+ —————— + ———-+

| Log_name | File_size |

+ —————— + ———-+

| mysql-bin.000004 | 2293035 |

+ —————— + ———-+

mysql> Use T1

Database changed

mysql> Delete from T1 where id=15;

Query OK, 1 row Affected (0.00 sec)

Mysql> show master logs;

+ —————— + ———-+

| Log_name | File_size |

+ —————— + ———-+

| mysql-bin.000004 | 2293211 |

+ —————— + ———-+

[Email protected] #/mysqlbinlog.txt-v-base64-output=decode-rows-b-start-position=2293035/home/mysql/data3006/ mysql/mysql-bin.000004 >/tmp/1.sql

[Email protected] # More/tmp/1.sql

DELIMITER;

#121004 19:59:35 server ID 3703006010 end_log_pos 2293211 Xid = 13145226

commit/*!*/;

#121004 19:59:35 server ID 3703006010 end_log_pos 2293143 table_map: ' t1 '. ' T1 ' mapped to number 1584

#121004 19:59:35 server ID 3703006010 end_log_pos 2293184 delete_rows:table ID 1584 flags:stmt_end_f

# # INSERT into T1.t1

# # SET

# # @1=15

# # # @2= ' ni xxx '

DELIMITER;

Have to say that the power of open source is really big.

Data recovery for MySQL