使用mysql的binlog恢复误操作(update | 您所在的位置:网站首页 › id数据恢复 › 使用mysql的binlog恢复误操作(update |
步骤一:场景准备 ① 创建测试用的数据表 mysql> create table t1 ( -> id int unsigned not null auto_increment, -> name char(20) not null, -> sex enum('f','m') not null default 'm', -> address varchar(30) not null, -> primary key(id) -> ); Query OK, 0 rows affected (0.31 sec) mysql>② 插入测试数据 mysql> insert into t1 (name,sex,address)values('daiiy','m','guangzhou'); Query OK, 1 row affected (0.01 sec) mysql> insert into t1 (name,sex,address)values('tom','f','shanghai'); Query OK, 1 row affected (0.00 sec)③ 删除数据 mysql> delete from t1; Query OK, 2 rows affected (0.03 sec)步骤二: 查询当前mysql的binlog文件 ① 查询mysql所有的binlog文件 mysql> show master logs; // 该结果返回的即为mysql记录的所有binlog文件 Log_nameFile_sizemysql81-bin.0001121654366mysql81-bin.000113100131397② 查询当前mysql已经记录到哪个binlog子文件中 mysql> show master status; // 该结果返回的即为当前mysql的操作所记录的binlog子文件 FilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Setmysql81-bin.0001131001313978df3aac9-262a-11e5步骤三: 通过binlog文件恢复被删除的数据 ①:在mysql的安装bin目录下(cd /usr/local/mysql/bin)找到mysqlbinlog命令执行文件 ②:找到当前mysql的binlog文件(通过上面show master status命令可以知道最新的binlog文件为mysql81-bin.000113): /var/mysql/data/mysql81-bin.000113 ③:在usr/local/mysql/bin目录下:在mysql81-bin.000113文件中查找”删除”操作记录 :/usr/local/mysql/bin>$ ./mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /var/mysql/data/mysql81-bin.000113 | grep -B 15 -A 15 'DELETE FROM'④:拷贝mysql81-bin.000113文件中的删除记录到指定文件中/var/mysql/data/delete.txt: :/usr/local/mysql/bin>$./mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /var/mysql/data/mysql81-bin.000113| sed -n '/### DELETE FROM 'pluto_core _1'.'t1’/,/COMMIT/p' > /var/mysql/data/delete.txt 注意: 1) 库名|表名需要被 “括起来,格式必需与第③步查找出来的格式一致 2) 指定/var/mysql/data/目录权限必需是可写的 (sudo chmod 755 data) ⑤:打开/var/mysql/data/delete.txt文件,这时应该可以看到该文件中已经有之前的删除操作记录了 //delete.txt文件 ### DELETE FROM `pluto_core_1`.`t1` ### WHERE ### @1=9 /* INT meta=0 nullable=0 is_null=0 */ ### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### DELETE FROM `pluto_core_1`.`t1` ### WHERE ### @1=10 /* INT meta=0 nullable=0 is_null=0 */ ### @2='呵呵' /* STRING(60) meta=65084 nullable=0 is_null=0 */ ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @4='上海' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ # at 10958544 #170908 14:59:23 server id 181 end_log_pos 10958575 CRC32 0x5c83521d Xid = 221304 COMMIT/*!*/;⑥: 将delete.txt中的记录转换为SQL语句 cat delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' > source.sql // source.sql文件 INSERT INTO `pluto_core_1`.`t1` SELECT 9 , 'daiiy' , 2 , 'guangzhou' ; INSERT INTO `pluto_core_1`.`t1` SELECT 10 , '呵呵' , 2 , '上海' ;⑦: 在source.sql文件所在目录下登录mysql导入source.sql `:/var/mysql/data>$mysql -h99.48.xxx.xxx -P3306 -uaaa -pbbbbb mysql>source source.sql⑧: 查看数据库pluto_core_1.t1,查看已删除的数据是否已经恢复了 步骤四: 通过binlog文件恢复被更新的数据 ①:在usr/local/mysql/bin目录下:在mysql81-bin.000113文件中查找”更新”操作记录 :/usr/local/mysql/bin>$ ./mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /var/mysql/data/mysql81-bin.000113 | grep -B 15 'zhuhai'②: 拷贝mysql81-bin.000113文件中的删除记录到指定文件中/var/mysql/data/delete.txt: :/usr/local/mysql/bin>$./mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /var/mysql/data/mysql81-bin.000113| sed -n '/# at 1679/,/COMMIT/p' > /var/mysql/data/delete.txt ③:将delete.txt中的记录转换为SQL语句 sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' t1.txt | sed -r '/WHERE/{:a;N;/@4/!ba;s/### @2.*//g}' | sed 's/### //g;s/\/\*.*/,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > recover.sql 转载至:MySQL误操作后数据恢复(update,delete忘记加where) |
CopyRight 2018-2019 实验室设备网 版权所有 |