mysql数据库指定字符集位utf8,同时表的字符集也得为utf8,同时mysql要开启row模式的bin-log日志

创建一张测试表测试：

create table MyClass(   id int(4) not null primary key auto_increment, name char(20) not null, sex varchar(10) not null default '0', degree varchar(10) not null );

插入数据：

insert into myclass (id,name,sex,degree) values (21,'小王','男','学士');insert into myclass (id,name,sex,degree) values (22,'小花','女','学士'); insert into myclass (id,name,sex,degree) values (23,'小李','女','硕士'); insert into myclass (id,name,sex,degree) values (24,'王雪','女','硕士'); insert into myclass (id,name,sex,degree) values (25,'小强','男','博士'); MySQL [zixun3]> select * from myclass; +----+--------+-----+--------+ | id | name | sex | degree | +----+--------+-----+--------+ | 20 | 张三 | 男 | 学士 | | 21 | 小王 | 男 | 学士 | | 22 | 小花 | 女 | 学士 | | 23 | 小李 | 女 | 硕士 | | 24 | 王雪 | 女 | 硕士 | | 25 | 小强 | 男 | 博士 | +----+--------+-----+--------+ 6 rows in set (0.00 sec)

查看binlog位置点：

MySQL [zixun3]> show master status\G*************************** 1. row *************************** File: mysql-bin.000037 Position: 1803 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

从二级制binlog日志文件中导出DELETE误操作的sql语句

/usr/local/mysql/bin/mysqlbinlog  --no-defaults  --base64-output=decode-rows  -v -v /data/mysql/data/mysql-bin.000037|sed -n '/### DELETE FROM `zixun3`.`myclass`/,/COMMIT/p' >/tmp/delete.txt

sql语句内容如下：

[root@git-server ~]# cat /tmp/delete.txt ### DELETE FROM `zixun3`.`myclass`### WHERE ### @1=20 /* INT meta=0 nullable=0 is_null=0 */ ### @2='张三' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3='男' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ ### @4='学士' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ ### DELETE FROM `zixun3`.`myclass` ### WHERE ### @1=21 /* INT meta=0 nullable=0 is_null=0 */ ### @2='小王' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3='男' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ ### @4='学士' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ ### DELETE FROM `zixun3`.`myclass` ### WHERE ### @1=22 /* INT meta=0 nullable=0 is_null=0 */ ### @2='小花' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3='女' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ ### @4='学士' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ ### DELETE FROM `zixun3`.`myclass` ### WHERE ### @1=23 /* INT meta=0 nullable=0 is_null=0 */ ### @2='小李' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3='女' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ ### @4='硕士' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ ### DELETE FROM `zixun3`.`myclass` ### WHERE ### @1=24 /* INT meta=0 nullable=0 is_null=0 */ ### @2='王雪' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3='女' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ ### @4='硕士' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ ### DELETE FROM `zixun3`.`myclass` ### WHERE ### @1=25 /* INT meta=0 nullable=0 is_null=0 */ ### @2='小强' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3='男' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ ### @4='博士' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ # at 428 #180517 18:30:08 server id 1 end_log_pos 459 CRC32 0x29e84ce6 Xid = 8 COMMIT/*!*/;

然后通过sed命令替换字符串进行拼接的方式，把原来的delete语句转换成insert 语句

cat /tmp/delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@5.*),/\1;/g' | sed 's/@[1-5]=//g' > /tmp/test.txt[root@git-server ~]# cat /tmp/test.txt INSERT INTO `zixun3`.`myclass` SELECT 20 , '张三' , '男' , '学士' ; INSERT INTO `zixun3`.`myclass` SELECT 21 , '小王' , '男' , '学士' ; INSERT INTO `zixun3`.`myclass` SELECT 22 , '小花' , '女' , '学士' ; INSERT INTO `zixun3`.`myclass` SELECT 23 , '小李' , '女' , '硕士' ; INSERT INTO `zixun3`.`myclass` SELECT 24 , '王雪' , '女' , '硕士' ; INSERT INTO `zixun3`.`myclass` SELECT 25 , '小强' , '男' , '博士' ;

登录mysql导入数据

use zixun3;直接导入 source /tmp/test.txtMySQL [zixun3]> select * from myclass;+----+--------+-----+--------+| id | name   | sex | degree |+----+--------+-----+--------+| 20 | 张三 | 男 | 学士 | | 21 | 小王 | 男 | 学士 | | 22 | 小花 | 女 | 学士 | | 23 | 小李 | 女 | 硕士 | | 24 | 王雪 | 女 | 硕士 | | 25 | 小强 | 男 | 博士 | +----+--------+-----+--------+ 6 rows in set (0.00 sec)

当然此种sed命令拼接的方式也是一种思路，但是上述的这种方式的恢复，也只适合表结构字段都是基本的简单的字段的表进行恢复数据，并不适合复杂的表结构的数据的恢复

下面是表字段中含有url链接内容的恢复数据的命令

/usr/local/mysql/bin/mysqlbinlog  --no-defaults  --base64-output=decode-rows  -v -v /data/mysql/data/mysql-bin.000038|sed -n '/### DELETE FROM `zhangyou`.`dede_uploads`/,/COMMIT/p' >/tmp/delete.txt cat /tmp/delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@11.*),/\1;/g' | sed 's/@[1-9]=//g;s/@10=//g;s/@11=//g;' > /tmp/test11.txt

提示：@11代表恢复数据表的表字段的个数

*提示:下面的代码才是通用的delete删除数据时进行恢复的正确语句，经本人测试，再复杂的表结构，只要delete误删除数据后，采用下面的命令语句是完全可以恢复的。但是需要注意的是sed -r 's/(@11.),/\1;/g'中的@数字，必须是表的字段的个数**

/usr/local/mysql/bin/mysqlbinlog  --no-defaults  --base64-output=decode-rows  -v -v /data/mysql/data/mysql-bin.000038|sed -n '/### DELETE FROM `zhangyou`.`dede_uploads`/,/COMMIT/p' >/tmp/delete.txt cat /tmp/delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@11.*),/\1;/g' | sed 's/@\([0-9]\+\)=//g;' >>/tmp/2.txt

重要提示：上述的数据库数据的的恢复命令仅仅是本人自己应用环境测试的结果，不代表网友的环境就一定可以直接套用，使用前一定多测试，不建议直接在自己的生产环境套用，博主发表此博文仅是共网友参考学习，如有套用造成损失的和本博文无关。