binlog 基本認識
? ? mysql的二進制日志可以說是MySQL最重要的日志了，它記錄了所有的DDL和DML(除了數據查詢語句)語句，以事件形式記錄，還包含語句所執行的消耗的時間，MySQL的二進制日志是事務安全型的。
? ? 一般來說開啟二進制日志大概會有1%的性能損耗(參見MySQL官方中文手冊 5.1.24版)。二進制有兩個最重要的使用場景:?
? ? 其一：MySQL Replication在Master端開啟binlog，Mster把它的二進制日志傳遞給slaves來達到master-slave數據一致的目的。?
? ? 其二：自然就是數據恢復了，通過使用mysqlbinlog工具來使恢復數據。
? ??
? ? 二進制日志包括兩類文件：二進制日志索引文件（文件名后綴為.index）用于記錄所有的二進制文件，二進制日志文件（文件名后綴為.00000*）記錄數據庫所有的DDL和DML(除了數據查詢語句)語句事件。?
一、開啟binlog日志：
? ? vi編輯打開mysql配置文件
? ? # vi /usr/local/mysql/etc/my.cnf
? ? 在[mysqld] 區塊
? ? 設置/添加 log-bin=mysql-bin? 確認是打開狀態(值 mysql-bin 是日志的基本名或前綴名)；
? ? 重啟mysqld服務使配置生效
? ? # pkill mysqld
? ? # /usr/local/mysql/bin/mysqld_safe –user=mysql &
二、也可登錄mysql服務器，通過mysql的變量配置表，查看二進制日志是否已開啟 單詞：variable[?v?ri?b?l] 變量
? ? 登錄服務器
????#?/usr/local/mysql/bin/mysql?-uroot?-p123456 ????mysql>?show?variables?like?'log_%';? ????+----------------------------------------+---------------------------------------+ ????|?Variable_name??????????????????????????|?Value?????????????????????????????????| ????+----------------------------------------+---------------------------------------+ ????|?log_bin????????????????????????????????|?ON????????????????????????????????????|?------>?ON表示已經開啟binlog日志 ????|?log_bin_basename???????????????????????|?/usr/local/mysql/data/mysql-bin???????| ????|?log_bin_index??????????????????????????|?/usr/local/mysql/data/mysql-bin.index?| ????|?log_bin_trust_function_creators????????|?OFF???????????????????????????????????| ????|?log_bin_use_v1_row_events??????????????|?OFF???????????????????????????????????| ????|?log_error??????????????????????????????|?/usr/local/mysql/data/martin.err??????| ????|?log_output?????????????????????????????|?FILE??????????????????????????????????| ????|?log_queries_not_using_indexes??????????|?OFF???????????????????????????????????| ????|?log_slave_updates??????????????????????|?OFF???????????????????????????????????| ????|?log_slow_admin_statements??????????????|?OFF???????????????????????????????????| ????|?log_slow_slave_statements??????????????|?OFF???????????????????????????????????| ????|?log_throttle_queries_not_using_indexes?|?0?????????????????????????????????????| ????|?log_warnings???????????????????????????|?1?????????????????????????????????????| ????+----------------------------------------+---------------------------------------+
三、常用binlog日志操作命令
? ? 1.查看所有binlog日志列表
? ? ? mysql> show master logs;
? ? 2.查看master狀態，即最后(最新)一個binlog日志的編號名稱，及其最后一個操作事件pos結束點(Position)值
? ? ? mysql> show master status;
? ? 3.刷新log日志，自此刻開始產生一個新編號的binlog日志文件
? ? ? mysql> flush logs;
? ? ? 注：每當mysqld服務重啟時，會自動執行此命令，刷新binlog日志；在mysqldump備份數據時加 -F 選項也會刷新binlog日志；
? ? 4.重置(清空)所有binlog日志
? ? ? mysql> reset master;
四、查看某個binlog日志內容，常用有兩種方式：
? ? 1.使用mysqlbinlog自帶查看命令法：
? ? ? 注: binlog是二進制文件，普通文件查看器cat more vi等都無法打開，必須使用自帶的 mysqlbinlog 命令查看
? ? ? ? ? binlog日志與數據庫文件在同目錄中(我的環境配置安裝是選擇在/usr/local/mysql/data中)
? ? ? 在MySQL5.5以下版本使用mysqlbinlog命令時如果報錯，就加上 “–no-defaults”選項
? ??
? ? ? # /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013
? ? ? ? 下面截取一個片段分析：
?????????............................................................................... ?????????#?at?552 ?????????#131128?17:50:46?server?id?1??end_log_pos?665???Query???thread_id=11????exec_time=0?????error_code=0?---->執行時間:17:50:46；pos點:665 ?????????SET?TIMESTAMP=1385632246/*!*/; ?????????update?zyyshop.stu?set?name='李四'?where?id=4??????????????---->執行的SQL ?????????/*!*/; ?????????#?at?665 ?????????#131128?17:50:46?server?id?1??end_log_pos?692???Xid?=?1454?---->執行時間:17:50:46；pos點:692? ?????????...............................................................................
? ? ? ? ?注: server id 1? ? ?數據庫主機的服務號；
? ? ? ? ? ? ?end_log_pos 665 pos點
? ? ? ? ? ? ?thread_id=11? ? 線程號
? ? 2.上面這種辦法讀取出binlog日志的全文內容較多，不容易分辨查看pos點信息，這里介紹一種更為方便的查詢命令：
? ? ? mysql> show binlog events [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count];
? ? ? ? ? ? ?選項解析：
? ? ? ? ? ? ? ?IN ‘log_name’? ?指定要查詢的binlog文件名(不指定就是第一個binlog文件)
? ? ? ? ? ? ? ?FROM pos? ? ? ? 指定從哪個pos起始點開始查起(不指定就是從整個文件首個pos點開始算)
? ? ? ? ? ? ? ?LIMIT [offset,] 偏移量(不指定就是0)
? ? ? ? ? ? ? ?row_count? ? ? ?查詢總條數(不指定就是所有行)
? ? ? ? ? ? ?截取部分查詢結果：
?????????????***************************?20.?row?*************************** ????????????????Log_name:?mysql-bin.000021??---------------------------------------------->?查詢的binlog日志文件名 ?????????????????????Pos:?11197?---------------------------------------------------------->?pos起始點: ??????????????Event_type:?Query?---------------------------------------------------------->?事件類型：Query ???????????????Server_id:?1?-------------------------------------------------------------->?標識是由哪臺服務器執行的 ?????????????End_log_pos:?11308?---------------------------------------------------------->?pos結束點:11308(即：下行的pos起始點) ????????????????????Info:?use?`zyyshop`;?INSERT?INTO?`team2`?VALUES?(0,345,'asdf8er5')?--->?執行的sql語句 ?????????????***************************?21.?row?*************************** ????????????????Log_name:?mysql-bin.000021 ?????????????????????Pos:?11308?---------------------------------------------------------->?pos起始點:11308(即：上行的pos結束點) ??????????????Event_type:?Query ???????????????Server_id:?1 ?????????????End_log_pos:?11417 ????????????????????Info:?use?`zyyshop`;?/*!40000?ALTER?TABLE?`team2`?ENABLE?KEYS?*/ ?????????????***************************?22.?row?*************************** ????????????????Log_name:?mysql-bin.000021 ?????????????????????Pos:?11417 ??????????????Event_type:?Query ???????????????Server_id:?1 ?????????????End_log_pos:?11510 ????????????????????Info:?use?`zyyshop`;?DROP?TABLE?IF?EXISTS?`type`
? ? ? 這條語句可以將指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos點的起始偏移，查詢條數；
? ? ??
? ? ? A.查詢第一個(最早)的binlog日志：
? ? ? ? mysql> show binlog eventsG;?
? ??
? ? ? B.指定查詢 mysql-bin.000021 這個文件：
? ? ? ? mysql> show binlog events in ‘mysql-bin.000021’G;
? ? ? C.指定查詢 mysql-bin.000021 這個文件，從pos點:8224開始查起：
? ? ? ? mysql> show binlog events in ‘mysql-bin.000021’ from 8224G;
? ? ? D.指定查詢 mysql-bin.000021 這個文件，從pos點:8224開始查起，查詢10條
? ? ? ? mysql> show binlog events in ‘mysql-bin.000021’ from 8224 limit 10G;
? ? ? E.指定查詢 mysql-bin.000021 這個文件，從pos點:8224開始查起，偏移2行，查詢10條
? ? ? ? mysql> show binlog events in ‘mysql-bin.000021’ from 8224 limit 2,10G;
五、恢復binlog日志實驗(zyyshop是數據庫)
? ? 1.假設現在是凌晨4:00，我的計劃任務開始執行一次完整的數據庫備份：
? ? ? 將zyyshop數據庫備份到 /root/BAK.zyyshop.sql 文件中：
? ? ? # /usr/local/mysql/bin/mysqldump -uroot -p123456 -lF –log-error=/root/myDump.err -B zyyshop > /root/BAK.zyyshop.sql
? ? ? ? ……
? ? ? ? 大約過了若干分鐘，備份完成了，我不用擔心數據丟失了，因為我有備份了，嘎嘎~~~
? ? ? 由于我使用了-F選項，當備份工作剛開始時系統會刷新log日志，產生新的binlog日志來記錄備份之后的數據庫“增刪改”操作，查看一下：
??????mysql>?show?master?status; ??????+------------------+----------+--------------+------------------+ ??????|?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?| ??????+------------------+----------+--------------+------------------+ ??????|?mysql-bin.000023?|??????120?|??????????????|??????????????????| ??????+------------------+----------+--------------+------------------+
? ? ? 也就是說， mysql-bin.000023 是用來記錄4:00之后對數據庫的所有“增刪改”操作。
? ? 2.早9:00上班了，業務的需求會對數據庫進行各種“增刪改”操作~~~~~~~
????@?比如：創建一個學生表并插入、修改了數據等等： ????????CREATE?TABLE?IF?NOT?EXISTS?`tt`?( ??????????`id`?int(10)?unsigned?NOT?NULL?AUTO_INCREMENT, ??????????`name`?varchar(16)?NOT?NULL, ??????????`sex`?enum('m','w')?NOT?NULL?DEFAULT?'m', ??????????`age`?tinyint(3)?unsigned?NOT?NULL, ??????????`classid`?char(6)?DEFAULT?NULL, ??????????PRIMARY?KEY?(`id`) ?????????)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;
? ? ? 導入實驗數據
??????mysql>?insert?into?zyyshop.tt(`name`,`sex`,`age`,`classid`)?values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');
? ? ? 查看數據
??????mysql>?select?*?from?zyyshop.tt; ??????+----+----------+-----+-----+---------+ ??????|?id?|?name?????|?sex?|?age?|?classid?| ??????+----+----------+-----+-----+---------+ ??????|??1?|?yiyi?????|?w???|??20?|?cls1????| ??????|??2?|?xiaoer???|?m???|??22?|?cls3????| ??????|??3?|?zhangsan?|?w???|??21?|?cls5????| ??????|??4?|?lisi?????|?m???|??20?|?cls4????| ??????|??5?|?wangwu???|?w???|??26?|?cls6????| ??????+----+----------+-----+-----+---------+
? ? ? 中午時分又執行了修改數據操作
??????mysql>?update?zyyshop.tt?set?name='李四'?where?id=4; ??????mysql>?update?zyyshop.tt?set?name='小二'?where?id=2;
? ? ? 修改后的結果：
??????mysql>?select?*?from?zyyshop.tt; ??????+----+----------+-----+-----+---------+ ??????|?id?|?name?????|?sex?|?age?|?classid?| ??????+----+----------+-----+-----+---------+ ??????|??1?|?yiyi?????|?w???|??20?|?cls1????| ??????|??2?|?小二?????|?m???|??22?|?cls3????| ??????|??3?|?zhangsan?|?w???|??21?|?cls5????| ??????|??4?|?李四?????|?m???|??20?|?cls4????| ??????|??5?|?wangwu???|?w???|??26?|?cls6????| ??????+----+----------+-----+-----+---------+
? ? ? 假設此時是下午18:00，莫名地執行了一條悲催的SQL語句，整個數據庫都沒了：
? ? ? mysql> drop database zyyshop;
? ? 3.此刻杯具了，別慌！先仔細查看最后一個binlog日志，并記錄下關鍵的pos點，到底是哪個pos點的操作導致了數據庫的破壞(通常在最后幾步)；
? ??
? ? ? 備份一下最后一個binlog日志文件：
??????#?ll?/usr/local/mysql/data?|?grep?mysql-bin ??????#?cp?-v?/usr/local/mysql/data/mysql-bin.000023?/root/
? ? ? 此時執行一次刷新日志索引操作，重新開始新的binlog日志記錄文件，理論說 mysql-bin.000023 這個文件不會再有后續寫入了(便于我們分析原因及查找pos點)，以后所有數據庫操作都會寫入到下一個日志文件；
????mysql>?flush?logs; ??????mysql>?show?master?status;
? ? ??
? ? 4.讀取binlog日志，分析問題
? ? ? 方式一：使用mysqlbinlog讀取binlog日志：
? ? ? ? # /usr/local/mysql/bin/mysqlbinlog? /usr/local/mysql/data/mysql-bin.000023
? ? ? 方式二：登錄服務器，并查看(推薦)：
? ? ? ? mysql> show binlog events in ‘mysql-bin.000023’;
? ? ? ??
? ? ? ? 以下為末尾片段：
????????+------------------+------+------------+-----------+-------------+------------------------------------------------------------+ ????????|?Log_name?????????|?Pos??|?Event_type?|?Server_id?|?End_log_pos?|?Info???????????????????????????????????????????????????????| ????????+------------------+------+------------+-----------+-------------+------------------------------------------------------------+ ????????|?mysql-bin.000023?|??922?|?Xid????????|?????????1?|?????????953?|?COMMIT?/*?xid=3820?*/??????????????????????????????????????| ????????|?mysql-bin.000023?|??953?|?Query??????|?????????1?|????????1038?|?BEGIN??????????????????????????????????????????????????????| ????????|?mysql-bin.000023?|?1038?|?Query??????|?????????1?|????????1164?|?use?`zyyshop`;?update?zyyshop.tt?set?name='李四'?where?id=4| ????????|?mysql-bin.000023?|?1164?|?Xid????????|?????????1?|????????1195?|?COMMIT?/*?xid=3822?*/??????????????????????????????????????| ????????|?mysql-bin.000023?|?1195?|?Query??????|?????????1?|????????1280?|?BEGIN??????????????????????????????????????????????????????| ????????|?mysql-bin.000023?|?1280?|?Query??????|?????????1?|????????1406?|?use?`zyyshop`;?update?zyyshop.tt?set?name='小二'?where?id=2| ????????|?mysql-bin.000023?|?1406?|?Xid????????|?????????1?|????????1437?|?COMMIT?/*?xid=3823?*/??????????????????????????????????????| ????????|?mysql-bin.000023?|?1437?|?Query??????|?????????1?|????????1538?|?drop?database?zyyshop??????????????????????????????????????| ????????+------------------+------+------------+-----------+-------------+------------------------------------------------------------+
? ? ? ? 通過分析，造成數據庫破壞的pos點區間是介于 1437–1538 之間，只要恢復到1437前就可。
? ? 5.現在把凌晨備份的數據恢復：
? ? ??
? ? ? # /usr/local/mysql/bin/mysql -uroot -p123456 -v
? ? ? 注: 至此截至當日凌晨(4:00)前的備份數據都恢復了。
? ? ? ? ? 但今天一整天(4:00–18:00)的數據腫么辦呢？就得從前文提到的 mysql-bin.000023 新日志做文章了……
? ? 6.從binlog日志恢復數據
? ? ??
? ? ? 恢復語法格式：
? ? ? # mysqlbinlog mysql-bin.0000xx | mysql -u用戶名 -p密碼 數據庫名
? ? ? ? 常用選項：
? ? ? ? ? –start-position=953? ? ? ? ? ? ? ? ? ?起始pos點
? ? ? ? ? –stop-position=1437? ? ? ? ? ? ? ? ? ?結束pos點
? ? ? ? ? –start-datetime=”2013-11-29 13:18:54″ 起始時間點
? ? ? ? ? –stop-datetime=”2013-11-29 13:21:53″? 結束時間點
? ? ? ? ? –database=zyyshop? ? ? ? ? ? ? ? ? ? ?指定只恢復zyyshop數據庫(一臺主機上往往有多個數據庫，只限本地log日志)
? ? ? ? ? ??
? ? ? ? 不常用選項：? ??
? ? ? ? ? -u –user=name? ? ? ? ? ? ? Connect to the remote server as username.連接到遠程主機的用戶名
? ? ? ? ? -p –password[=name]? ? ? ? Password to connect to remote server.連接到遠程主機的密碼
? ? ? ? ? -h –host=name? ? ? ? ? ? ? Get the binlog from server.從遠程主機上獲取binlog日志
? ? ? ? ? –read-from-remote-server? ?Read binary logs from a MySQL server.從某個MySQL服務器上讀取binlog日志
? ? ? 小結：實際是將讀出的binlog日志內容，通過管道符傳遞給mysql命令。這些命令、文件盡量寫成絕對路徑；
? ? ? A.完全恢復(本例不靠譜，因為最后那條 drop database zyyshop 也在日志里，必須想辦法把這條破壞語句排除掉，做部分恢復)
????????#?/usr/local/mysql/bin/mysqlbinlog??/usr/local/mysql/data/mysql-bin.000021?|?/usr/local/mysql/bin/mysql?-uroot?-p123456?-v?zyyshop
? ? ? B.指定pos結束點恢復(部分恢復)：
? ? ? ? @ –stop-position=953 pos結束點
? ? ? ? 注：此pos結束點介于“導入實驗數據”與更新“name=’李四’”之間，這樣可以恢復到更改“name=’李四’”之前的“導入測試數據”
????????#?/usr/local/mysql/bin/mysqlbinlog?--stop-position=953?--database=zyyshop?/usr/local/mysql/data/mysql-bin.000023?|?/usr/local/mysql/bin/mysql?-uroot?-p123456?-v?zyyshop
? ? ??
? ? ? ? 在另一終端登錄查看結果(成功恢復了)：
????????mysql>?select?*?from?zyyshop.tt; ????????+----+----------+-----+-----+---------+ ????????|?id?|?name?????|?sex?|?age?|?classid?| ????????+----+----------+-----+-----+---------+ ????????|??1?|?yiyi?????|?w???|??20?|?cls1????| ????????|??2?|?xiaoer???|?m???|??22?|?cls3????| ????????|??3?|?zhangsan?|?w???|??21?|?cls5????| ????????|??4?|?lisi?????|?m???|??20?|?cls4????| ????????|??5?|?wangwu???|?w???|??26?|?cls6????| ????????+----+----------+-----+-----+---------+
? ? ? C.指定pso點區間恢復(部分恢復)：
? ? ? ? 更新 name=’李四’ 這條數據，日志區間是Pos[1038] –> End_log_pos[1164]，按事務區間是：Pos[953] –> End_log_pos[1195]；
? ? ? ? 更新 name=’小二’ 這條數據，日志區間是Pos[1280] –> End_log_pos[1406]，按事務區間是：Pos[1195] –> End_log_pos[1437]；
? ? ? ? c1.單獨恢復 name=’李四’ 這步操作，可這樣：
???????????#?/usr/local/mysql/bin/mysqlbinlog?--start-position=1038?--stop-position=1164?--database=zyyshop??/usr/local/mysql/data/mysql-bin.000023?|?/usr/local/mysql/bin/mysql?-uroot?-p123456?-v?zyyshop
? ? ? ? ? ?也可以按事務區間單獨恢復，如下：
???????????#?/usr/local/mysql/bin/mysqlbinlog?--start-position=953?--stop-position=1195?--database=zyyshop??/usr/local/mysql/data/mysql-bin.000023?|?/usr/local/mysql/bin/mysql?-uroot?-p123456?-v?zyyshop
? ? ? ? c2.單獨恢復 name=’小二’ 這步操作，可這樣：
???????????#?/usr/local/mysql/bin/mysqlbinlog?--start-position=1280?--stop-position=1406?--database=zyyshop??/usr/local/mysql/data/mysql-bin.000023?|?/usr/local/mysql/bin/mysql?-uroot?-p123456?-v?zyyshop
? ??
? ? ? ? ? ?也可以按事務區間單獨恢復，如下：
???????????#?/usr/local/mysql/bin/mysqlbinlog?--start-position=1195?--stop-position=1437?--database=zyyshop??/usr/local/mysql/data/mysql-bin.000023?|?/usr/local/mysql/bin/mysql?-uroot?-p123456?-v?zyyshop
? ? ? ? c3.將 name=’李四’、name=’小二’ 多步操作一起恢復，需要按事務區間，可這樣：
???????????#?/usr/local/mysql/bin/mysqlbinlog?--start-position=953?--stop-position=1437?--database=zyyshop??/usr/local/mysql/data/mysql-bin.000023?|?/usr/local/mysql/bin/mysql?-uroot?-p123456?-v?zyyshop
? ? ? D.在另一終端登錄查看目前結果(兩名稱也恢復了)：
????????mysql>?select?*?from?zyyshop.tt; ????????+----+----------+-----+-----+---------+ ????????|?id?|?name?????|?sex?|?age?|?classid?| ????????+----+----------+-----+-----+---------+ ????????|??1?|?yiyi?????|?w???|??20?|?cls1????| ????????|??2?|?小二?????|?m???|??22?|?cls3????| ????????|??3?|?zhangsan?|?w???|??21?|?cls5????| ????????|??4?|?李四?????|?m???|??20?|?cls4????| ????????|??5?|?wangwu???|?w???|??26?|?cls6????| ????????+----+----------+-----+-----+---------+
? ? ? E.也可指定時間區間恢復(部分恢復)：除了用pos點的辦法進行恢復，也可以通過指定時間區間進行恢復，按時間恢復需要用mysqlbinlog命令讀取binlog日志內容，找時間節點。
? ? ? ? 比如，我把剛恢復的tt表刪除掉，再用時間區間點恢復
????????mysql>?drop?table?tt; ????????@?--start-datetime="2013-11-29?13:18:54"??起始時間點 ????????@?--stop-datetime="2013-11-29?13:21:53"???結束時間點 ????????#?/usr/local/mysql/bin/mysqlbinlog?--start-datetime="2013-11-29?13:18:54"?--stop-datetime="2013-11-29?13:21:53"?--database=zyyshop?/usr/local/mysql/data/mysql-bin.000021?|?/usr/local/mysql/bin/mysql?-uroot?-p123456?-v?zyyshop
? ? ? 總結：所謂恢復，就是讓mysql將保存在binlog日志中指定段落區間的sql語句逐個重新執行一次而已。
更多相關免費學習推薦：mysql教程(視頻)
