【Mysql】從binlog中找出單個表的binlog資訊
無聊研究binlog寫了個提取單個表的binlog 的資訊，便於誤刪恢復資料，這樣做的目的是為了減少恢復時其他表可能引起的出錯，以及縮短恢復時間
首先研究binlog日誌的格式
思路就是先將第一部分以及最後一部分提取出來，我們csplite檔案（以COMMIT/*!*/; 為分隔標示，不熟悉csplit命令的先去熟悉一下用法），在第一以及最後一部分中間插入單個表的binlog 資訊而拼接成一個符合binlog格式的日誌檔案
測試：
TIP：指令碼有點小bug，就是有時vcommit0 第一個begin commit可能記錄的是別的表的資訊而導致恢復時報錯，那麼就手工替換這部分的為第一個我們目標表的binlog資訊再執行
首先研究binlog日誌的格式
- 如下：
-
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160229 15:19:52 server id 162 end_log_pos 123 CRC32 0xbbe9b551 Start: binlog v 4, server v 5.7.9-log created 160229 15:19:52 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
GPHTVg+iAAAAdwAAAHsAAAABAAQANS43LjktbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAY8dNWEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AVG16bs=
'/*!*/;
# at 123
#160229 15:19:52 server id 162 end_log_pos 154 CRC32 0xd473f6f4 Previous-GTIDs
# [empty]
# at 154
#160229 15:18:07 server id 162 end_log_pos 219 CRC32 0xe86bb499 Anonymous_GTID last_committed=0 sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#160229 15:18:07 server id 162 end_log_pos 291 CRC32 0xe1803afe Query thread_id=102887 exec_time=341 error_code=0
SET TIMESTAMP=1456730287/*!*/;
SET @@session.pseudo_thread_id=102887/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=524288/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#160229 15:18:07 server id 162 end_log_pos 343 CRC32 0xcfc435f2 Table_map: `test`.`ty` mapped to number 127
# at 343
#160229 15:18:07 server id 162 end_log_pos 463 CRC32 0xb4ef18fe Write_rows: table id 127 flags: STMT_END_F
BINLOG '
r/DTVhOiAAAANAAAAFcBAAAAAH8AAAAAAAEABHRlc3QAAnR5AAQDDw8DBBQAFAAP8jXEzw==
r/DTVh6iAAAAeAAAAM8BAAAAAH8AAAAAAAEAAgAE//AFAAAABEVFRUUCQzFfAAAA8AEAAAAEQUFB
QQJDMUMAAADwAwAAAARDQ0NDAkMxQwAAAPAEAAAABEREREQCQzFBAAAA8AIAAAAEQkJCQgJDMTcA
AAD+GO+0
'/*!*/;
# at 463
#160229 15:18:07 server id 162 end_log_pos 494 CRC32 0xed1fb95b Xid = 53
COMMIT/*!*/; -------到第一個commit為第一部分 為binlog必須的，缺失會報錯
# at 494
#160229 15:18:07 server id 162 end_log_pos 559 CRC32 0xef3ac14c Anonymous_GTID last_committed=1 sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 559
#160229 15:18:07 server id 162 end_log_pos 631 CRC32 0xe834f1d8 Query thread_id=102887 exec_time=350 error_code=0
SET TIMESTAMP=1456730287/*!*/;
BEGIN
/*!*/;
# at 631
#160229 15:18:07 server id 162 end_log_pos 683 CRC32 0xc64ac724 Table_map: `test`.`ty` mapped to number 127
# at 683
#160229 15:18:07 server id 162 end_log_pos 803 CRC32 0x4a5f1c75 Write_rows: table id 127 flags: STMT_END_F
BINLOG '
r/DTVhOiAAAANAAAAKsCAAAAAH8AAAAAAAEABHRlc3QAAnR5AAQDDw8DBBQAFAAPJMdKxg==
r/DTVh6iAAAAeAAAACMDAAAAAH8AAAAAAAEAAgAE//AFAAAABEVFRUUCQzFfAAAA8AEAAAAEQUFB
QQJDMUMAAADwAwAAAARDQ0NDAkMxQwAAAPAEAAAABEREREQCQzFBAAAA8AIAAAAEQkJCQgJDMTcA
AAB1HF9K
'/*!*/;
# at 803
#160229 15:18:07 server id 162 end_log_pos 834 CRC32 0x96148022 Xid = 77
COMMIT/*!*/; ------到下一個commit為第二部分，一個事物語句，可以缺失
# at 834
#160229 15:18:07 server id 162 end_log_pos 899 CRC32 0xa1e52982 Anonymous_GTID last_committed=2 sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 899
#160229 15:18:07 server id 162 end_log_pos 971 CRC32 0x89fc0e48 Query thread_id=102887 exec_time=359 error_code=0
SET TIMESTAMP=1456730287/*!*/;
BEGIN
/*!*/;
# at 971
#160229 15:18:07 server id 162 end_log_pos 1023 CRC32 0xc1309696 Table_map: `test`.`ty` mapped to number 127
# at 1023
#160229 15:18:07 server id 162 end_log_pos 1143 CRC32 0x0993d08b Write_rows: table id 127 flags: STMT_END_F
BINLOG '
r/DTVhOiAAAANAAAAP8DAAAAAH8AAAAAAAEABHRlc3QAAnR5AAQDDw8DBBQAFAAPlpYwwQ==
r/DTVh6iAAAAeAAAAHcEAAAAAH8AAAAAAAEAAgAE//AFAAAABEVFRUUCQzFfAAAA8AEAAAAEQUFB
QQJDMUMAAADwAwAAAARDQ0NDAkMxQwAAAPAEAAAABEREREQCQzFBAAAA8AIAAAAEQkJCQgJDMTcA
AACL0JMJ
'/*!*/;
# at 1143
#160229 15:18:07 server id 162 end_log_pos 1174 CRC32 0x7c2054fd Xid = 101
。。。。。省略
COMMIT/*!*/; -------最後一個commit到最後必須存在
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
- 程式碼如下：
-
[root@localhost chenliang]# more v1.sh
#!/bin/bash
# author:Jonnychen
# date:2016-3-1
# set -x
# get the key value of input
get_key_value()
{
echo "$1" | sed 's/^--[A-Za-z_-]*=//'
}
use_help()
{
cat << EOF
Info :
Author:Michael.xu
Usage :
Usage : $0 [configure-options]
-? , --help
--binlog=<> , set binlogfile
--database=<> , set database
--table=<>,set table
EOF
}
parse_options()
{
while [ $# -gt 0 ]
do
case "$1" in
--binlog=*)
vbinlog=`get_key_value "$1"`;;
--database=*)
vdatabase=`get_key_value "$1"`;;
--table=*)
vtable=`get_key_value "$1"`;;
-? | --help)
use_help
exit 0;;
*)
echo "unknown configure option '$1'"
exit 1;;
esac
shift
done
}
parse_options "$@"
echo "$vbinlog,$vdatabase,$vtable"
if [ ! -n "$vbinlog" ];then
use_help
exit
fi
if [ ! -n "$vdatabase" ];then
use_help
exit
fi
if [ ! -n "$vtable" ];then
use_help
exit
fi
#####binlog日誌格式化為txt檔案
mysqlbinlog_dir=`which mysqlbinlog`
${mysqlbinlog_dir} ${vbinlog}>${vbinlog}.txt
filename=${vbinlog}.txt
#####txt日誌檔案按照commit欄位進行切割
csplit $filename /COMMIT\/*\// -n 1 -s {*} -f vcommit
cou_comm=`ls -l | grep vcommit|awk -F " " '{print $9}'|wc -l`
let "maxseq=${cou_comm}-1"
#echo $maxseq
let "sec_max=$maxseq-1"
#echo ${sec_max}
cat vcommit0>>${vbinlog}.sql #####提取第一部分
for((i=1;i<=${sec_max};i++))
do
hastab=$(grep "\`${vdatabase}\`.\`${vtable}\`" vcommit$i|wc -l ) ---迴圈從每個分割部分去找出單個表的資訊
echo "vcommit$i:$hastab"
if [ $hastab -ge 1 ]
then
cat vcommit$i>>${vbinlog}.sql
fi
done
cat vcommit$maxseq>>${vbinlog}.sql ###提取最後一部分
rm -rf $filename vcommit*
測試：
- 1：mysql> select * from TEAMS; 原始資料
-
+--------+----------+----------+
| TEAMNO | PLAYERNO | DIVISION |
+--------+----------+----------+
| 1 | 6 | first |
| 2 | 27 | second |
| 3 | 89 | asd |
| 4 | 89 | BBBB |
| 5 | 89 | DDDD |
| 6 | 67 | GGGG |
| 7 | 77 | KKKK | -
-
- 模擬此時做了個備份
-
mysqldump -uroot -p`cat /etc/sqlpass ` -F TENNIS>TENNIS.sql --此時binlog到了19
- 模擬做些操作
-
mysql> insert into TEAMS values(8,77,'uuuuuu');
Query OK, 1 row affected (0.00 sec)
mysql> insert into TEAMS values(9,77,'iiiiii');
Query OK, 1 row affected (0.00 sec)
-
mysql> insert into TEAMS values(10,77,'oooo');
Query OK, 1 row affected (0.01 sec)
mysql> update TEAMS set DIVISION='pppp' where TEAMNO in(9,10); ---誤操作
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0
-
恢復：
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
[root@localhost chenliang]# mysql -uroot -p`cat /etc/sqlpass ` TENNIS<TENNIS.sql --恢復備份
[root@localhost chenliang]# sh v1.sh --binlog=mysqlbin.000019 --database=TENNIS --table=TEAMS ---提前單表的binlog 資訊
恢復binlog資訊
[root@localhost chenliang]# mysql -uroot -p`cat /etc/sqlpass ` TENNIS<mysqlbin.000019.sql
檢查
mysql> select * from TEAMS;
+--------+----------+----------+
| TEAMNO | PLAYERNO | DIVISION |
+--------+----------+----------+
| 1 | 6 | first |
| 2 | 27 | second |
| 3 | 89 | asd |
| 4 | 89 | BBBB |
| 5 | 89 | DDDD |
| 6 | 67 | GGGG |
| 7 | 77 | KKKK |
| 8 | 77 | uuuuuu |
| 9 | 77 | iiiiii |
| 10 | 77 | oooo |
+--------+----------+----------+
成功
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
[root@localhost chenliang]# mysql -uroot -p`cat /etc/sqlpass ` TENNIS<TENNIS.sql --恢復備份
[root@localhost chenliang]# sh v1.sh --binlog=mysqlbin.000019 --database=TENNIS --table=TEAMS ---提前單表的binlog 資訊
點選(此處)摺疊或開啟
- 檢視提取出來的資訊，刪除update那部分即可
-
COMMIT/*!*/;
# at 704
#160301 13:55:35 server id 162 end_log_pos 769 CRC32 0x0593de75 Anonymous_GTID last_committed=2 sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 769
#160301 13:55:35 server id 162 end_log_pos 843 CRC32 0x2978f290 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1456811735/*!*/;
BEGIN
/*!*/;
# at 843
#160301 13:55:35 server id 162 end_log_pos 897 CRC32 0xe52bc252 Table_map: `TENNIS`.`TEAMS` mapped to number 134
# at 897
#160301 13:55:35 server id 162 end_log_pos 946 CRC32 0x7331e254 Update_rows: table id 134 flags: STMT_END_F
BINLOG '
1y7VVhOiAAAANgAAAIEDAAAAAIYAAAAAAAEABlRFTk5JUwAFVEVBTVMAAwMD/gL+EgBSwivl
1y7VVh6iAAAAMQAAALIDAAAAAIYAAAAAAAEAAgAD//gKAAAATQAAAARvb29vVOIxcw==
'/*!*/;
# at 946
#160301 13:55:35 server id 162 end_log_pos 977 CRC32 0x7da650c4 Xid = 271
COMMIT/*!*/; ----從sql中將update的binlog資訊刪除即可
[root@localhost chenliang]# mysql -uroot -p`cat /etc/sqlpass ` TENNIS<mysqlbin.000019.sql
檢查
mysql> select * from TEAMS;
+--------+----------+----------+
| TEAMNO | PLAYERNO | DIVISION |
+--------+----------+----------+
| 1 | 6 | first |
| 2 | 27 | second |
| 3 | 89 | asd |
| 4 | 89 | BBBB |
| 5 | 89 | DDDD |
| 6 | 67 | GGGG |
| 7 | 77 | KKKK |
| 8 | 77 | uuuuuu |
| 9 | 77 | iiiiii |
| 10 | 77 | oooo |
+--------+----------+----------+
成功
TIP：指令碼有點小bug，就是有時vcommit0 第一個begin commit可能記錄的是別的表的資訊而導致恢復時報錯，那麼就手工替換這部分的為第一個我們目標表的binlog資訊再執行
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29096438/viewspace-2019854/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL如何通過分析binlog日誌找出操作頻繁的表MySql
- mysql binlog 刪除master- binlogMySqlAST
- MYSQL如何識別一個binlog中的一個事物MySql
- Mysql的binlog原理MySql
- mysql的binlog格式MySql
- MySQL：Redo & binlogMySql
- mysql binlog管理MySql
- MySQL中3種清除binlog的方法!MySql
- mysql binlog_format和臨時表MySqlORM
- MySQL中binlog cache使用流程解惑MySql
- [MySQL binlog]徹底解析Mixed日誌格式的binlogMySql
- MySQL 的日誌：binlogMySql
- MySQL的binlog日誌MySql
- mysql清理binlog的方法MySql
- Mysql Binlog的介紹MySql
- MySQL閃回技術之binlog2sql恢復binlog中的SQLMySql
- 提取MySQL binlog中指定表的操作記錄MySql
- MySQL通過Binlog恢復刪除的表MySql
- MySQL Binlog 介紹MySql
- MySql Binlog 初識MySql
- MySQL binlog日期解析MySql
- MySQL基於binlog主從複製配置MySql
- MySQL中binlog備份指令碼分享MySql指令碼
- 課時7-備份與恢復----從dump檔案中找出單個表的資訊
- Mysql的redolog和binlogMySql
- Java程式碼中，如何監控Mysql的binlog？JavaMySql
- Mysql5.6主從複製-基於binlogMySql
- mysql清除binlog日誌MySql
- 清除MySQL Binlog日誌MySql
- 【mysql】關於binlog格式MySql
- MySQL binlog和redo的組提交MySql
- MySQL中的binlog相關命令和恢復技巧MySql
- MySQL 主從複製搭建，基於日誌（binlog）MySql
- MySQL資料庫binlog解析神器-binlog2sql應用MySql資料庫
- 教你MySQL Binlog實用攻略MySql
- mysql binlog詳細介紹MySql
- 【MySQL】如何快速執行 binlogMySql
- MySQL Binlog 事件介紹篇MySql事件