標籤：txt mysql binlog工具 .sql string binlog sel 結束 bin
mysql> select * from tet3;
+----+-------------+
| id | dd |
+----+-------------+
| 1 | XX |
| 2 | YY |
| 3 | aaa |
| 4 | 5002301999X |
| 5 | 0000000X |
| 6 | oi80 |
| 7 | 887 |
| 8 | 887 |
| 10 | jju |
+----+-------------+
9 rows in set (0.03 sec)
mysql> delete from tet3 where id>3;
Query OK, 6 rows affected (0.03 sec)
mysql> select * from tet3;
+----+------+
| id | dd |
+----+------+
| 1 | XX |
| 2 | YY |
| 3 | aaa |
+----+------+
3 rows in set (0.00 sec)
[[email protected] data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v db-bin.000016| sed -n ‘/### DELETE FROM `test`.`tet3`/,/COMMIT/p‘> /root/delete.txt
[[email protected] data]# more /root/delete.txt
### DELETE FROM `test`.`tet3`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2=‘5002301999X‘ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### DELETE FROM `test`.`tet3`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2=‘0000000X‘ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### DELETE FROM `test`.`tet3`
### WHERE
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2=‘oi80‘ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### DELETE FROM `test`.`tet3`
### WHERE
### @1=7 /* INT meta=0 nullable=0 is_null=0 */
### @2=‘887‘ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### DELETE FROM `test`.`tet3`
### WHERE
### @1=8 /* INT meta=0 nullable=0 is_null=0 */
### @2=‘887‘ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### DELETE FROM `test`.`tet3`
### WHERE
### @1=10 /* INT meta=0 nullable=0 is_null=0 */
### @2=‘jju‘ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
# at 3640
#150426 23:17:36 server id 199 end_log_pos 3671 CRC32 0xb946f7f5 Xid = 164
COMMIT/*!*/;
[[email protected] ~]# cat delete.txt | sed -n ‘/###/p‘ | sed ‘s/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;‘ |sed -r ‘s/(@2.*),/\1;/g‘ | sed ‘s/@[1-9]=//g‘ >insert.sql
[[email protected] ~]#
[[email protected] ~]#
[[email protected] ~]# more insert.sql
INSERT INTO `test`.`tet3`
SELECT
4 ,
‘5002301999X‘ ;
INSERT INTO `test`.`tet3`
SELECT
5 ,
‘0000000X‘ ;
INSERT INTO `test`.`tet3`
SELECT
6 ,
‘oi80‘ ;
INSERT INTO `test`.`tet3`
SELECT
7 ,
‘887‘ ;
INSERT INTO `test`.`tet3`
SELECT
8 ,
‘887‘ ;
INSERT INTO `test`.`tet3`
SELECT
10 ,
‘jju‘ ;
以上就是我們需要的復原sql了...執行就行了..
命令解釋:
mysqlbinlog --no-defaults --base64-output=decode-rows -v -v db-bin.000016| sed -n ‘/### DELETE FROM `test`.`tet3`/,/COMMIT/p‘> /root/delete.txt
mysqlbinlog --no-defaults --base64-output=decode-rows -v -v db-bin.000016
這屬於mysqlbinlog命令參數...
--no-defaults 阻止mysqlbinlog工具從任何設定檔讀取參數(保證密碼安全)
--base64-output=decode-rows 顯示出row模式帶來的sql變更
-v -v 採用二進位記錄檔方式查看
sed -n ‘/### DELETE FROM `test`.`tet3`/,/COMMIT/p‘
列印從‘### DELETE FROm `test`.`tet3`‘開始到‘COMMIT‘結束的內容...
cat delete.txt | sed -n ‘/###/p‘ | sed ‘s/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;‘ |sed -r ‘s/(@2.*),/\1;/g‘ | sed ‘s/@[1-9]=//g‘ >insert.sql
sed -n ‘/###/p‘
列印‘###‘開頭的行
sed ‘s/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;‘
分開解讀: s/### //g;s/\/\*.*/,/g; 這部分是把‘### ‘ 和/*..*/去除掉;
s/DELETE FROM/INSERT INTO/g; 這部分是吧delete from 換成insert into;
s/WHERE/SELECT/g; 這部分是吧where換成select;
|sed -r ‘s/(@2.*),/\1;/g‘
-r是Regex,意思是在@2開頭的一行末尾加一個分號.
sed ‘s/@[1-9]=//g‘
這個就簡單了..就是將@[email protected]的去除.當然本例中只有@1和@2.
mysql 誤刪除 使用binlog 進行復原
