mysql之binlog日志以及利用binlog日志恢復數據的示例代碼分享

268

下面小編就為大家帶來一篇解說mysql之binlog日志以及利用binlog日志恢復數據的方法。小編覺得挺不錯的，現在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧

眾所周知，binlog日志對于mysql來說是十分重要的。在數據丟失的緊急情況下，我們往往會想到用binlog日志功能進行數據恢復（定時全備份+binlog日志恢復增量數據部分），化險為夷！

廢話不多說，下面是梳理的binlog日志操作解說：

一、初步了解binlog

mysql的二進制日志binlog可以說是MySQL最重要的日志，它記錄了所有的DDL和DML語句（除了數據查詢語句select），以事件形式記錄，還包含語句所執行的消耗的時間，MySQL的二進制日志是事務安全型的。
———————————————————————————————————————————————-
DDL
—-Data Definition Language 數據庫定義語言
主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定義或改變表（TABLE）的結構，數據類型，表之間的鏈接和約束等初始化工作上，他們大多在建立表時使用。

DML
—-Data Manipulation Language 數據操縱語言
主要的命令是SELECT、UPDATE、INSERT、DELETE，就象它的名字一樣，這4條命令是用來對數據庫里的數據進行操作的語言
———————————————————————————————————————————————-

mysqlbinlog常見的選項有以下幾個：

–start-datetime：從二進制日志中讀取指定等于時間戳或者晚于本地計算機的時間

–stop-datetime：從二進制日志中讀取指定小于時間戳或者等于本地計算機的時間 取值和上述一樣

–start-position：從二進制日志中讀取指定position 事件位置作為開始。

–stop-position：從二進制日志中讀取指定position 事件位置作為事件截至

*********************************************************************

一般來說開啟binlog日志大概會有1%的性能損耗。

binlog日志有兩個最重要的使用場景:

1）MySQL主從復制：MySQL Replication在Master端開啟binlog，Master把它的二進制日志傳遞給slaves來達到
master-slave數據一致的目的。

2）自然就是數據恢復了，通過mysqlbinlog工具來使恢復數據。

binlog日志包括兩類文件：

1）二進制日志索引文件（文件名后綴為.index）用于記錄所有的二進制文件

2）二進制日志文件（文件名后綴為.00000*）記錄數據庫所有的DDL和DML(除了數據查詢語句select)語句事件。

二、開啟binlog日志：

1）編輯打開mysqlmysql/etc/mys.cnf

[root@vm-002?~]#?vim?/etc/my.cnf

在[mysqld] 區塊添加
log-bin=mysql-bin 確認是打開mysql(mysql-bin 是日志的基本名或前綴名)；

2）重啟mysqld服務使配置生效

[root@vm-002?~]#?/etc/init.d/mysqld?stop  [root@vm-002?~]#?/etc/init.d/mysqld?restart  Stopping?mysqld:?[?OK?]  Starting?mysqld:?[?OK?]

3）查看binlog日志是否開啟

mysql>?show?variables?like?'log_%';?  +---------------------------------+---------------------+  |?Variable_name?|?Value?|  +---------------------------------+---------------------+  |?  log_bin?|?ON?|  |?log_bin_trust_function_creators?|?OFF?|  |?log_bin_trust_routine_creators?|?OFF?|  |?log_error?|?/var/log/mysqld.log?|  |?log_output?|?FILE?|  |?log_queries_not_using_indexes?|?OFF?|  |?log_slave_updates?|?OFF?|  |?log_slow_queries?|?OFF?|  |?log_warnings?|?1?|  +---------------------------------+---------------------+  9?rows?in?set?(0.00?sec)

三、常用的binlog日志操作命令

1）查看所有binlog日志列表

mysql>?show?master?logs;  +------------------+-----------+  |?Log_name?|?File_size?|  +------------------+-----------+  |?mysql-bin.000  001  ?|?149?|  |?mysql-bin.000002?|?4102?|  +------------------+-----------+  2?rows?in?set?(0.00?sec)

2）查看master狀態，即最后(最新)一個binlog日志的編號名稱，及其最后一個操作事件pos結束點(Position)值

mysql>?show?master?status;  +------------------+----------+--------------+------------------+  |?File?|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|  +------------------+----------+--------------+------------------+  |?mysql-bin.000002?|?4102?|?|?|  +------------------+----------+--------------+------------------+  1?row?in?set?(0.00?sec)

3）mysql刷新log日志，自此刻開始產生一個新編號的binlog日志文件

mysql>?flush?logs;?  Query?OK,?0?rows?affected?(0.13?sec)  mysql>?show?master?logs;?  +------------------+-----------+  |?Log_name?|?File_size?|  +------------------+-----------+  |?mysql-bin.000001?|?149?|  |?mysql-bin.000002?|?4145?|  |?mysql-bin.000003?|?106?|  +------------------+-----------+  3?rows?in?set?(0.00?sec)

注意：

每當mysqld服務重啟時，會自動執行此命令，刷新binlog日志；在mysqldump備份數據時加 -F 選項也會刷新binlog日志；

4）重置(清空)所有binlog日志

mysql>?reset?master;  Query?OK,?0?rows?affected?(0.12?sec)  mysql>?show?master?logs;?  +------------------+-----------+  |?Log_name?|?File_size?|  +------------------+-----------+  |?mysql-bin.000001?|?106?|  +------------------+-----------+  1?row?in?set?(0.00?sec)

四、查看binlog日志內容，常用有兩種方式：

1）使用mysqlbinlog自帶查看命令法：

注意：

–>binlog是二進制文件，普通文件查看器cat、more、vim等都無法打開，必須使用自帶的mysqlbinlog命令查看

–>binlog日志與數據庫文件在同目錄中

–>在MySQL5.5以下版本使用mysqlbinlog命令時如果報錯，就加上 “–no-defaults”選項

查看mysql的數據存放目錄，從下面結果可知是/var/lib//mysql

[root@vm-002?~]#?ps?-ef|grep?mysql  root?9791?1?0?21:18?pts/0?00:00:00?/bin/sh?/usr/bin/mysqld_safe?--datadir=/var/lib/mysql?  --socket=/var/lib/mysql/mysql.sock?--pid-file=/var/run/mysqld/mysqld.pid?--basedir=/usr?--user=mysql  mysql?9896?9791?0?21:18?pts/0?00:00:00?/usr/libexec/mysqld?--basedir=/usr?--datadir=/var/lib/mysql?  --user=mysql?--log-error=/var/log/mysqld.log?--pid-file=/var/run/mysqld/mysqld.pid?--socket=/var/lib/mysql/mysql.sock  root?9916?9699?0?21:18?pts/0?00:00:00?mysql?-px?xxxx  root?9919?9715?0?21:23?pts/1?00:00:00?grep?--color?mysql  [root@vm-002?~]#?cd?/var/lib/mysql/  [root@vm-002?mysql]#?ls  ibdata1?ib_logfile0?ib_logfile1?mysql?mysql-bin.000001?mysql-bin.000002?mysql-bin.index?mysql.sock?ops?test

使用mysqlbinlog命令查看binlog日志內容，下面截取其中的一個片段分析：

[root@vm-002?mysql]#?mysqlbinlog?mysql-bin.000002  ..............  #?at?624  #160925?21:29:53?server?id?1?  end  _log_pos?796?Query?thread_id=3?exec_time=0?error_code=0  SET?TIMESTAMP=1474810193/*!*/;  insert?into?member(`name`,`sex`,`age`,`  class  id`)?values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2')????????  #執行的sql語句  /*!*/;  #?at?796  #160925?  21:29:53  ?server?id?1?end_log_pos?823?Xid?=?17??????????????????  #執行的時間  .............

解釋：

server id 1 ： 數據庫主機的服務號；
end_log_pos 796： sql結束時的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 ：查詢總條數(不指定就是所有行)

mysql>?show?master?logs;  +------------------+-----------+  |?Log_name?|?File_size?|  +------------------+-----------+  |?mysql-bin.000001?|?125?|  |?mysql-bin.000002?|?823?|  +------------------+-----------+  2?rows?in?set?(0.00?sec)  mysql>?show?binlog?events?in?'mysql-bin.000002'G;  ***************************?1.?row?***************************  Log_name:?mysql-bin.000002  Pos:?4  Event_type:?Format_desc  Server_id:?1  End_log_pos:?106  Info:?Server?ver:?5.1.73-log,?Binlog?ver:?4  ***************************?2.?row?***************************  Log_name:?mysql-bin.000002  Pos:?106  Event_type:?Query  Server_id:?1  End_log_pos:?188  Info:?use?`ops`;?drop?table?customers  ***************************?3.?row?***************************  Log_name:?mysql-bin.000002  Pos:?188  Event_type:?Query  Server_id:?1  End_log_pos:?529  Info:?use?`ops`;?CREATE?TABLE?IF?NOT?EXISTS?`member`?(  `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  ***************************?4.?row?***************************  Log_name:?mysql-bin.000002  Pos:?529  Event_type:?Query  Server_id:?1  End_log_pos:?596  Info:?BEGIN  ***************************?5.?row?***************************  Log_name:?mysql-bin.000002  Pos:?596  Event_type:?Intvar  Server_id:?1  End_log_pos:?624  Info:?INSERT_ID=1  ***************************?6.?row?***************************  Log_name:?mysql-bin.000002  Pos:?624  Event_type:?Query  Server_id:?1  End_log_pos:?796  Info:?use?`ops`;?insert?into?member(`name`,`sex`,`age`,`classid`)?values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2')  ***************************?7.?row?***************************  Log_name:?mysql-bin.000002  Pos:?796  Event_type:?Xid  Server_id:?1  End_log_pos:?823  Info:?COMMIT?/*?xid=17?*/  7?rows?in?set?(0.00?sec)  ERROR:?  No?query?specified  mysql>

上面這條語句可以將指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos點的起始偏移，查詢條數！
如下操作示例：

a）查詢第一個(最早)的binlog日志：

mysql>?show?binlog?eventsG;

b）指定查詢 mysql-bin.000002這個文件：

mysql>?show?binlog?events?in?'mysql-bin.000002'G;

c）指定查詢 mysql-bin.000002這個文件，從pos點:624開始查起：

mysql>?show?binlog?events?in?'mysql-bin.000002'?from?624G;

d）指定查詢 mysql-bin.000002這個文件，從pos點:624開始查起，查詢10條（即10條語句）

mysql>?show?binlog?events?in?'mysql-bin.000002'?from?624?limit?10G;

e）指定查詢 mysql-bin.000002這個文件，從pos點:624開始查起，偏移2行（即中間跳過2個），查詢10條

mysql>?show?binlog?events?in?'mysql-bin.000002'?from?624?limit?2,10G;

五、利用binlog日志恢復mysql數據

以下對ops庫的member表進行操作

mysql>?use?ops；  mysql>?CREATE?TABLE?IF?NOT?EXISTS?`member`?(  ->?`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;  Query?OK,?0?rows?affected?(0.10?sec)  mysql>?show?tables;  +---------------+  |?Tables_in_ops?|  +---------------+  |?member?|  +---------------+  1?row?in?set?(0.00?sec)  mysql>?desc?member;  +---------+---------------------+------+-----+---------+----------------+  |?Field?|?Type?|?Null?|?Key?|?Default?|?Extra?|  +---------+---------------------+------+-----+---------+----------------+  |?id?|?int(10)?unsigned?|?NO?|?PRI?|?NULL?|?auto_increment?|  |?name?|?varchar(16)?|?NO?|?|?NULL?|?|  |?sex?|?enum('m','w')?|?NO?|?|?m?|?|  |?age?|?tinyint(3)?unsigned?|?NO?|?|?NULL?|?|  |?classid?|?char(6)?|?YES?|?|?NULL?|?|  +---------+---------------------+------+-----+---------+----------------+  5?rows?in?set?(0.00?sec)

事先插入兩條數據

mysql>?insert?into?member(`name`,`sex`,`age`,`classid`)?values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2');  Query?OK,?2?rows?affected?(0.08?sec)  Rec  ord  s:?2?Duplicates:?0?Warnings:?0  mysql>?select?*?from?member;  +----+-----------+-----+-----+---------+  |?id?|?name?|?sex?|?age?|?classid?|  +----+-----------+-----+-----+---------+  |?1?|?wangshibo?|?m?|?27?|?cls1?|  |?2?|?guohuihui?|?w?|?27?|?cls2?|  +----+-----------+-----+-----+---------+  2?rows?in?set?(0.00?sec)

下面開始進行場景模擬：

1）

ops庫會在每天凌晨4點進行一次完全備份的定時計劃任務，如下：

[root@vm-002?~]#?crontab?-l  0?4?*?*?*?/usr/bin/mysqldump?-uroot?-p?-B?-F?-R?-x?--master-data=2?ops|g  zip  ?>/opt/backup/ops_$(date?+%F).sql.gz

這里手動執行下，將ops數據庫備份到/opt/backup/ops_$(date +%F).sql.gz文件中：

[root@vm-002?~]#?mysqldump?-uroot?-p?-B?-F?-R?-x?--master-data=2?ops|gzip?>/opt/backup/ops_$(date?+%F).sql.gz  Enter?password:?  [root@vm-002?~]#?ls?/opt/backup/  ops_2016-09-25.sql.gz

—————–

參數說明：

-B：指定數據庫
-F：刷新日志
-R：備份mysql等
-x：鎖表
–master-data：在備份語句里添加CHANGE MASTER語句以及binlog文件及位置點信息
—————–

待到數據庫備份完成，就不用擔心數據丟失了，因為有完全備份數據在！！

由于上面在全備份的時候使用了-F選項，那么當數據備份操作剛開始的時候系統就會自動刷新log，這樣就會自動產生
一個新的binlog日志，這個新的binlog日志就會用來記錄備份之后的數據庫“增刪改”操作

查看一下：

mysql>?show?master?status;  +------------------+----------+--------------+------------------+  |?File?|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|  +------------------+----------+--------------+------------------+  |?mysql-bin.000003?|?106?|?|?|  +------------------+----------+--------------+------------------+  1?row?in?set?(0.00?sec)

也就是說， mysql-bin.000003 是用來記錄4:00之后對數據庫的所有“增刪改”操作。

2）

早上9點上班了，由于業務的需求會對數據庫進行各種“增刪改”操作。

比如：在ops庫下member表內插入、修改了數據等等：

先是早上進行mysql：

mysql>?insert?into?ops.member(`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');  Query?OK,?5?rows?affected?(0.08?sec)  Records:?5?Duplicates:?0?Warnings:?0  mysql>?select?*?from?member;  +----+-----------+-----+-----+---------+  |?id?|?name?|?sex?|?age?|?classid?|  +----+-----------+-----+-----+---------+  |?1?|?wangshibo?|?m?|?27?|?cls1?|  |?2?|?guohuihui?|?w?|?27?|?cls2?|  |?3?|?yiyi?|?w?|?20?|?cls1?|  |?4?|?xiaoer?|?m?|?22?|?cls3?|  |?5?|?zhangsan?|?w?|?21?|?cls5?|  |?6?|?lisi?|?m?|?20?|?cls4?|  |?7?|?wangwu?|?w?|?26?|?cls6?|  +----+-----------+-----+-----+---------+  7?rows?in?set?(0.00?sec)

3）

中午又執行了修改數據操作：

mysql>?update?ops.member?set?name='李四'?where?id=4;  Query?OK,?1?row?affected?(0.07?sec)  Rows?matched:?1?Changed:?1?Warnings:?0  mysql>?update?ops.member?set?name='小二'?where?id=2;  Query?OK,?1?row?affected?(0.06?sec)  Rows?matched:?1?Changed:?1?Warnings:?0  mysql>?select?*?from?member;  +----+-----------+-----+-----+---------+  |?id?|?name?|?sex?|?age?|?classid?|  +----+-----------+-----+-----+---------+  |?1?|?wangshibo?|?m?|?27?|?cls1?|  |?2?|?小二?|?w?|?27?|?cls2?|  |?3?|?yiyi?|?w?|?20?|?cls1?|  |?4?|?李四?|?m?|?22?|?cls3?|  |?5?|?zhangsan?|?w?|?21?|?cls5?|  |?6?|?lisi?|?m?|?20?|?cls4?|  |?7?|?wangwu?|?w?|?26?|?cls6?|  +----+-----------+-----+-----+---------+  7?rows?in?set?(0.00?sec)

4）

在下午18:00的時候，悲劇莫名其妙的出現了！

手賤執行了drop語句，直接刪除了ops庫！嚇尿！

mysql>?drop?database?ops;  Query?OK,?1?row?affected?(0.02?sec)

5）

這種時候，一定不要慌張！！！

先仔細查看最后一個binlog日志，并記錄下關鍵的pos點，到底是哪個pos點的操作導致了數據庫的破壞(通常在最后幾步)；

先備份一下最后一個binlog日志文件：

[root@vm-002?~]#?cd?/var/lib/mysql/  [root@vm-002?mysql]#?cp?-v?mysql-bin.000003?/opt/backup/  `mysql-bin.000003'?->?`/opt/backup/mysql-bin.000003'  [root@vm-002?mysql]#?ls?/opt/backup/  mysql-bin.000003?ops_2016-09-25.sql.gz

接著執行一次刷新日志索引操作，重新開始新的binlog日志記錄文件。按理說mysql-bin.000003

這個文件不會再有后續寫入了，因為便于我們分析原因及查找ops節點，以后所有mysql都會寫入到下一個日志文件。

mysql>?flush?logs;Query?OK,?0?rows?affected?(0.13?sec)  mysql>?show?master?status;  +------------------+----------+--------------+------------------+  |?File?|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|  +------------------+----------+--------------+------------------+  |?mysql-bin.000004?|?106?|?|?|  +------------------+----------+--------------+------------------+  1?row?in?set?(0.00?sec)

6）

讀取binlog日志，分析問題。
讀取binlog日志的方法上面已經說到。

方法一：使用mysqlbinlog讀取binlog日志：

[root@vm-002?~]#?cd?/var/lib/mysql/  [root@vm-002?mysql]#?mysqlbinlog?mysql-bin.000003

方法二：登錄服務器，并查看(推薦此種方法)

mysql>?show?binlog?events?in?'mysql-bin.000003';  +------------------+-----+-------------+-----------+-------------+--------------------------------------------------+  |?Log_name?|?Pos?|?Event_type?|?Server_id?|?End_log_pos?|?Info?|  +------------------+-----+-------------+-----------+-------------+---------------------------------------------+  |?mysql-bin.000003?|?4?|?Format_desc?|?1?|?106?|?Server?ver:?5.1.73-log,?Binlog?ver:?4?|  |?mysql-bin.000003?|?106?|?Query?|?1?|?173?|?BEGIN?|  |?mysql-bin.000003?|?173?|?Intvar?|?1?|?201?|?INSERT_ID=3?|  |?mysql-bin.000003?|?201?|?Query?|?1?|?444?|?use?`ops`;?  insert?into?ops.member(`name`,`sex`,`age`,`gsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6')?|  |?mysql-bin.000003?|?444?|?Xid?|?1?|?471?|?COMMIT?/*?xid=66?*/?|  |?mysql-bin.000003?|?471?|?Query?|?1?|?538?|?BEGIN?|  |?mysql-bin.000003?|?538?|?Query?|?1?|?646?|?use?`ops`;?update?ops.member?set?name='李四'?where?id=?|  |?mysql-bin.000003?|?646?|?Xid?|?1?|?673?|?COMMIT?/*?xid=68?*/?|  |?mysql-bin.000003?|?673?|?Query?|?1?|?740?|?BEGIN?|  |?mysql-bin.000003?|?740?|?Query?|?1?|?848?|?use?`ops`;?update?ops.member?set?name='小二'?where?id=?|  |?mysql-bin.000003?|?848?|?Xid?|?1?|?875?|?COMMIT?/*?xid=69?*/?|  |?mysql-bin.000003?|?875?|?Query?|?1?|?954?|?drop?database?ops?|  |?mysql-bin.000003?|?954?|?Rotate?|?1?|?997?|?mysql-bin.000004;pos=4?|  +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+  13?rows?in?set?(0.00?sec)

或者：

mysql>?  show?binlog?events?in?'mysql-bin.000003'G;  .........  .........  ***************************?12.?row?***************************  Log_name:?mysql-bin.000003  Pos:?  875  Event_type:?Query  Server_id:?1  End_log_pos:?  954  Info:?  drop?database?ops  ***************************?13.?row?***************************  Log_name:?mysql-bin.000003  Pos:?954  Event_type:?Rotate  Server_id:?1  End_log_pos:?997  Info:?mysql-bin.000004;pos=4  13?rows?in?set?(0.00?sec)

通過分析，造成數據庫破壞的pos點區間是介于 875–954 之間（這是按照日志區間的pos節點算的），只要恢復到875前就可。

7）

先把凌晨4點全備份的數據恢復：

[root@vm-002?~]#?cd?/opt/backup/  [root@vm-002?backup]#?lsmysql-bin.000003?ops_2016-09-25.sql.gz  [root@vm-002?backup]#?gzip?-d?ops_2016-09-25.sql.gz?  [root@vm-002?backup]#?mysql?-uroot?-p?-v?<p>這樣就恢復了截至當日凌晨(4:00)前的備份數據都恢復了。</p><pre class="brush:sql;toolbar:false">mysql&gt;?show?databases;????????????????????????#發現ops庫已經恢復回來了  mysql&gt;?use?ops;  Reading?table?information?for?completion?of?table?and?column?names  You?can?turn?off?this?feature?to?get?a?quicker?startup?with?-A  Database?changed  mysql&gt;?show?tables;  +---------------+  |?Tables_in_ops?|  +---------------+  |?member?|  +---------------+  1?row?in?set?(0.00?sec)  mysql&gt;?select?*?from?member;  +----+-----------+-----+-----+---------+  |?id?|?name?|?sex?|?age?|?classid?|  +----+-----------+-----+-----+---------+  |?1?|?wangshibo?|?m?|?27?|?cls1?|  |?2?|?guohuihui?|?w?|?27?|?cls2?|  +----+-----------+-----+-----+---------+  2?rows?in?set?(0.00?sec)  mysql&gt;

但是這僅僅只是恢復了當天凌晨4點之前的數據，在4:00–18:00之間的數據還沒有恢復回來！！
怎么辦呢？
莫慌！這可以根據前面提到的mysql-bin.000003的新binlog日志進行恢復。

8）

從binlog日志恢復數據
恢復命令的語法格式：

mysqlbinlog?mysql-bin.0000xx?|?mysql?-u用戶名?-p密碼?數據庫名

——————————————————–
常用參數選項解釋：

--start-position=875?起始pos點  --stop-position=954?結束pos點  --start-datetime="2016-9-25?22:01:08"?起始時間點  --stop-datetime="2019-9-25?22:09:46"?結束時間點  --database=zyyshop?指定只恢復zyyshop數據庫(一臺主機上往往有多個數據庫，只限本地log日志)

——————————————————–
不常用選項： 

-u?--user=name?連接到遠程主機的用戶名  -p?--password[=name]?連接到遠程主機的密碼  -h?--host=name?從遠程主機上獲取binlog日志  --read-from-remote-server?從某個MySQL服務器上讀取binlog日志

——————————————————–

小結：實際是將讀出的binlog日志內容，通過管道符傳遞給mysql命令。這些命令、文件盡量寫成絕對路徑；

a）完全恢復(需要手動vim編輯mysql-bin.000003，將那條drop語句剔除掉)

[root@vm-002?backup]#?/usr/bin/mysqlbinlog?/var/lib/mysql/mysql-bin.000003?|?/usr/bin/mysql?-uroot?-p123456?-v?ops

b）指定pos結束點恢復(部分恢復)：

–stop-position=471 pos結束節點（按照事務區間算，是471）

注意：

此pos結束節點介于“member表原始數據”與mysql“name=’李四’”之前的數據，這樣就可以恢復到更改“name=’李四’”之前的數據了。

操作如下：

[root@vm-002?~]#?/usr/bin/mysqlbinlog?--stop-position=471?--database=ops?/var/lib/mysql/mysql-bin.000003?|?/usr/bin/mysql?-uroot?-p123456?-v?ops  mysql&gt;?select?*?from?member;  +----+-----------+-----+-----+---------+  |?id?|?name?|?sex?|?age?|?classid?|  +----+-----------+-----+-----+---------+  |?1?|?wangshibo?|?m?|?27?|?cls1?|  |?2?|?guohuihui?|?w?|?27?|?cls2?|  |?3?|?yiyi?|?w?|?20?|?cls1?|  |?4?|?xiaoer?|?m?|?22?|?cls3?|  |?5?|?zhangsan?|?w?|?21?|?cls5?|  |?6?|?lisi?|?m?|?20?|?cls4?|  |?7?|?wangwu?|?w?|?26?|?cls6?|  +----+-----------+-----+-----+---------+  7?rows?in?set?(0.00?sec)

恢復截止到更改“name=’李四’”之間的數據（按照事務區間算，是673）

[root@vm-002?~]#?/usr/bin/mysqlbinlog?--stop-position=673?--database=ops?/var/lib/mysql/mysql-bin.000003?|?/usr/bin/mysql?-uroot?-p123456?-v?ops  mysql&gt;?select?*?from?member;  +----+-----------+-----+-----+---------+  |?id?|?name?|?sex?|?age?|?classid?|  +----+-----------+-----+-----+---------+  |?1?|?wangshibo?|?m?|?27?|?cls1?|  |?2?|?guohuihui?|?w?|?27?|?cls2?|  |?3?|?yiyi?|?w?|?20?|?cls1?|  |?4?|?李四?|?m?|?22?|?cls3?|  |?5?|?zhangsan?|?w?|?21?|?cls5?|  |?6?|?lisi?|?m?|?20?|?cls4?|  |?7?|?wangwu?|?w?|?26?|?cls6?|  +----+-----------+-----+-----+---------+  7?rows?in?set?(0.00?sec)

c）指定pso點區間恢復(部分恢復)：

更新 name=’李四’ 這條數據，日志區間是Pos[538] –> End_log_pos[646]，按事務區間是：Pos[471] –> End_log_pos[673]

更新 name=’小二’ 這條數據，日志區間是Pos[740] –> End_log_pos[848]，按事務區間是：Pos[673] –> End_log_pos[875]

c1）

單獨恢復 name=’李四’ 這步操作，可這樣：

按照binlog日志區間單獨恢復：

[root@vm-002?~]#?/usr/bin/mysqlbinlog?--start-position=538?--stop-position=646?--database=ops?/var/lib/mysql/mysql-bin.000003?|?  /usr/bin/mysql?-uroot?-p123456?-v?ops

按照事務區間單獨恢復

[root@vm-002?~]#?/usr/bin/mysqlbinlog?--start-position=471?--stop-position=673?--database=ops?/var/lib/mysql/mysql-bin.000003?|?  /usr/bin/mysql?-uroot?-p123456?-v?ops

c2）

單獨恢復 name=’小二’ 這步操作，可這樣：
按照binlog日志區間單獨恢復：

[root@vm-002?~]#?/usr/bin/mysqlbinlog?--start-position=740?--stop-position=848?--database=ops?/var/lib/mysql/mysql-bin.000003?|?  /usr/bin/mysql?-uroot?-p123456?-v?ops

按照事務區間單獨恢復

[root@vm-002?~]#?/usr/bin/mysqlbinlog?--start-position=673?--stop-position=875?--database=ops?/var/lib/mysql/mysql-bin.000003?|?  /usr/bin/mysql?-uroot?-p123456?-v?ops

c3）

將 name=’李四’、name=’小二’ 多步操作一起恢復，需要按事務區間，可這樣：

[root@vm-002?~]#?/usr/bin/mysqlbinlog?--start-position=471?--stop-position=875?--database=ops?/var/lib/mysql/mysql-bin.000003?|?  /usr/bin/mysql?-uroot?-p123456?-v?ops

查看數據庫：

mysql&gt;?select?*?from?member;  +----+-----------+-----+-----+---------+  |?id?|?name?|?sex?|?age?|?classid?|  +----+-----------+-----+-----+---------+  |?1?|?wangshibo?|?m?|?27?|?cls1?|  |?2?|?小二?|?w?|?27?|?cls2?|  |?3?|?yiyi?|?w?|?20?|?cls1?|  |?4?|?李四?|?m?|?22?|?cls3?|  |?5?|?zhangsan?|?w?|?21?|?cls5?|  |?6?|?lisi?|?m?|?20?|?cls4?|  |?7?|?wangwu?|?w?|?26?|?cls6?|  +----+-----------+-----+-----+---------+  7?rows?in?set?(0.00?sec)

這樣，就恢復了刪除前的數據狀態了！！

另外：

也可指定時間節點區間恢復(部分恢復)：
除了用pos節點的辦法進行恢復，也可以通過指定時間節點區間進行恢復，按時間恢復需要用mysqlbinlog命令讀取binlog日志內容，找時間節點。

如上，誤刪除ops庫后：

先進行全備份恢復

[root@vm-002?backup]#?mysql?-uroot?-p?-v?<p><span style="background-color: #ffcc99">查看ops數據庫</span><br></p><pre class="brush:sql;toolbar:false">mysql&gt;?select?*?from?member;  +----+-----------+-----+-----+---------+  |?id?|?name?|?sex?|?age?|?classid?|  +----+-----------+-----+-----+---------+  |?1?|?wangshibo?|?m?|?27?|?cls1?|  |?2?|?guohuihui?|?w?|?27?|?cls2?|  +----+-----------+-----+-----+---------+  2?rows?in?set?(0.00?sec)  mysql&gt;

查看mysq-bin00003日志，找出時間節點

[root@vm-002?~]#?cd?/var/lib/mysql  [root@vm-002?mysql]#?mysqlbinlog?mysql-bin.000003?  .............  .............  BEGIN  /*!*/;  #?at?173  #160925?21:57:19?server?id?1?end_log_pos?201?Intvar  SET?INSERT_ID=3/*!*/;  #?at?201  #160925?21:57:19?server?id?1?end_log_pos?444?Query?thread_id=3?exec_time=0?error_code=0  use?`ops`/*!*/;  SET?TIMESTAMP=1474811839/*!*/;  insert?into?ops.member(`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')???????????????????????????????  #執行的sql語句  /*!*/;  #?at?444  #160925?  21:57:19  ?server?id?1?end_log_pos?471?Xid?=?66????  #開始執行的時間  COMMIT  /*!*/;  #?at?471  #160925?  21:58:41  ?server?id?1?end_log_pos?538?Query?thread_id=3?exec_time=0?error_code=0????  #結束時間  SET?TIMESTAMP=1474811921/*!*/;  BEGIN  /*!*/;  #?at?538  #160925?  21:58:41  ?server?id?1?end_log_pos?646?Query?thread_id=3?exec_time=0?error_code=0  SET?TIMESTAMP=1474811921/*!*/;  update?ops.member?set?name='李四'?where?id=4?????  #執行的sql語句  /*!*/;  #?at?646  #160925?  21:58:41  ?server?id?1?end_log_pos?673?Xid?=?68????  #開始執行的時間  COMMIT/*!*/;  #?at?673  #160925?  21:58:56  ?server?id?1?end_log_pos?740?Query?thread_id=3?exec_time=0?error_code=0???  #結束時間  SET?TIMESTAMP=1474811936/*!*/;  BEGIN  /*!*/;  #?at?740  #160925?21:58:56?server?id?1?end_log_pos?848?Query?thread_id=3?exec_time=0?error_code=0  SET?TIMESTAMP=1474811936/*!*/;  update?ops.member?set?name='小二'?where?id=2?????  ?  #執行的sql語句  /*!*/;  #?at?848  #160925?  21:58:56  ?server?id?1?end_log_pos?875?Xid?=?69???  #開始執行的時間  COMMIT/*!*/;  #?at?875  #160925?  22:01:08  ?server?id?1?end_log_pos?954?Query?thread_id=3?exec_time=0?error_code=0????  #結束時間  SET?TIMESTAMP=1474812068/*!*/;  drop?database?ops  /*!*/;  #?at?954  #160925?22:09:46?server?id?1?end_log_pos?997?Rotate?to?mysql-bin.000004?pos:?4  DELIMITER?;  #?End?of?log?file  ROLLBACK?/*?added?by?mysqlbinlog?*/;  /*!50003?SET?COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

恢復到更改“name=’李四’”之前的數據

[root@vm-002?~]#?/usr/bin/mysqlbinlog?--start-datetime="2016-09-25?21:57:19"?--stop-datetime="2016-09-25?21:58:41"?--  database=ops?/var/lib/mysql/mysql-bin.000003?|?/usr/bin/mysql?-uroot?-p123456?-v?ops  mysql>?select?*?from?member;  +----+-----------+-----+-----+---------+  |?id?|?name?|?sex?|?age?|?classid?|  +----+-----------+-----+-----+---------+  |?1?|?wangshibo?|?m?|?27?|?cls1?|  |?2?|?guohuihui?|?w?|?27?|?cls2?|  |?3?|?yiyi?|?w?|?20?|?cls1?|  |?4?|?xiaoer?|?m?|?22?|?cls3?|  |?5?|?zhangsan?|?w?|?21?|?cls5?|  |?6?|?lisi?|?m?|?20?|?cls4?|  |?7?|?wangwu?|?w?|?26?|?cls6?|  +----+-----------+-----+-----+---------+  7?rows?in?set?(0.00?sec)    [root@vm-002?~]#?/usr/bin/mysqlbinlog?--start-datetime="2016-09-25?21:58:41"?--stop-datetime="2016-09-25?21:58:56"  ?--database=ops?/var/lib/mysql/mysql-bin.000003?|?/usr/bin/mysql?-uroot?-p123456?-v?ops  mysql>?select?*?from?member;  +----+-----------+-----+-----+---------+  |?id?|?name?|?sex?|?age?|?classid?|  +----+-----------+-----+-----+---------+  |?1?|?wangshibo?|?m?|?27?|?cls1?|  |?2?|?guohuihui?|?w?|?27?|?cls2?|  |?3?|?yiyi?|?w?|?20?|?cls1?|  |?4?|?李四?|?m?|?22?|?cls3?|  |?5?|?zhangsan?|?w?|?21?|?cls5?|  |?6?|?lisi?|?m?|?20?|?cls4?|  |?7?|?wangwu?|?w?|?26?|?cls6?|  +----+-----------+-----+-----+---------+  7?rows?in?set?(0.00?sec)    [root@vm-002?~]#?/usr/bin/mysqlbinlog?--start-datetime="2016-09-25?21:58:56"?--stop-datetime="2016-09-25?22:01:08"  ?--database=ops?/var/lib/mysql/mysql-bin.000003?|?/usr/bin/mysql?-uroot?-p123456?-v?ops  mysql&gt;?select?*?from?member;  +----+-----------+-----+-----+---------+  |?id?|?name?|?sex?|?age?|?classid?|  +----+-----------+-----+-----+---------+  |?1?|?wangshibo?|?m?|?27?|?cls1?|  |?2?|?小二?|?w?|?27?|?cls2?|  |?3?|?yiyi?|?w?|?20?|?cls1?|  |?4?|?李四?|?m?|?22?|?cls3?|  |?5?|?zhangsan?|?w?|?21?|?cls5?|  |?6?|?lisi?|?m?|?20?|?cls4?|  |?7?|?wangwu?|?w?|?26?|?cls6?|  +----+-----------+-----+-----+---------+  7?rows?in?set?(0.00?sec)

這樣，就恢復了刪除前的狀態了！

總結：

所謂恢復，就是讓mysql將保存在binlog日志中指定段落區間的sql語句逐個重新執行一次而已。

以上就是

? 版權聲明
THE END
數據庫
# mysql
喜歡就支持一下吧
點贊8 分享