mysql之binlog日志以及使用binlog日志恢復(fù)數(shù)據(jù)的示例代碼分享

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

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

一、初步了解binlog

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

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

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

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

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

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

--stop-position：從二進(jìn)制日志中讀取指定position 事件位置作為事件截至

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

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

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

1）MySQL主從復(fù)制：MySQL Replication在Master端開啟binlog，Master把它的二進(jìn)制日志傳遞給slaves來達(dá)到
master-slave數(shù)據(jù)一致的目的。

2）自然就是數(shù)據(jù)恢復(fù)了，通過使用mysqlbinlog工具來使恢復(fù)數(shù)據(jù)。

binlog日志包括兩類文件：

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

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

二、開啟binlog日志：

1）編輯打開mysql配置文件/etc/mys.cnf

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

在[mysqld] 區(qū)塊添加
log-bin=mysql-bin 確認(rèn)是打開狀態(tài)(mysql-bin 是日志的基本名或前綴名)；

2）重啟mysqld服務(wù)使配置生效

[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狀態(tài)，即最后(最新)一個binlog日志的編號名稱，及其最后一個操作事件pos結(jié)束點(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）flush刷新log日志，自此刻開始產(chǎn)生一個新編號的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)

注意：

每當(dāng)mysqld服務(wù)重啟時，會自動執(zhí)行此命令，刷新binlog日志；在mysqldump備份數(shù)據(jù)時加 -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日志內(nèi)容，常用有兩種方式：

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

注意：

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

-->binlog日志與數(shù)據(jù)庫文件在同目錄中

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

查看mysql的數(shù)據(jù)存放目錄，從下面結(jié)果可知是/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日志內(nèi)容，下面截取其中的一個片段分析：

[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')        
#執(zhí)行的sql語句
/*!*/;
# at 796
#160925 
21:29:53
 server id 1 end_log_pos 823 Xid = 17                  
#執(zhí)行的時間
.............

解釋：

server id 1 ： 數(shù)據(jù)庫主機的服務(wù)號；
end_log_pos 796： sql結(jié)束時的pos節(jié)點
thread_id=11： 線程號

2）上面這種辦法讀取出binlog日志的全文內(nèi)容比較多，不容易分辨查看到pos點信息

下面介紹一種更為方便的查詢命令：

命令格式：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

參數(shù)解釋：

IN 'log_name' ：指定要查詢的binlog文件名(不指定就是第一個binlog文件)
FROM pos ：指定從哪個pos起始點開始查起(不指定就是從整個文件首個pos點開始算)
LIMIT [offset,] ：偏移量(不指定就是0)
row_count ：查詢總條數(shù)(不指定就是所有行)

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點的起始偏移，查詢條數(shù)！
如下操作示例：

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

mysql> show binlog events\G;

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 624\G;

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

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

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

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

五、利用binlog日志恢復(fù)mysql數(shù)據(jù)

以下對ops庫的member表進(jìn)行操作

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)

事先插入兩條數(shù)據(jù)

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)

下面開始進(jìn)行場景模擬：

1）

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

[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

這里手動執(zhí)行下，將ops數(shù)據(jù)庫備份到/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

-----------------

參數(shù)說明：

-B：指定數(shù)據(jù)庫
-F：刷新日志
-R：備份存儲過程等
-x：鎖表
--master-data：在備份語句里添加CHANGE MASTER語句以及binlog文件及位置點信息
-----------------

待到數(shù)據(jù)庫備份完成，就不用擔(dān)心數(shù)據(jù)丟失了，因為有完全備份數(shù)據(jù)在！！

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

查看一下：

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之后對數(shù)據(jù)庫的所有“增刪改”操作。

2）

早上9點上班了，由于業(yè)務(wù)的需求會對數(shù)據(jù)庫進(jìn)行各種“增刪改”操作。

比如：在ops庫下member表內(nèi)插入、修改了數(shù)據(jù)等等：

先是早上進(jìn)行插入數(shù)據(jù)：

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）

中午又執(zhí)行了修改數(shù)據(jù)操作：

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的時候，悲劇莫名其妙的出現(xiàn)了！

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

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

5）

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

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

先備份一下最后一個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

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

這個文件不會再有后續(xù)寫入了，因為便于我們分析原因及查找ops節(jié)點，以后所有數(shù)據(jù)庫操作都會寫入到下一個日志文件。

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日志的方法上面已經(jīng)說到。

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

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

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

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)

通過分析，造成數(shù)據(jù)庫破壞的pos點區(qū)間是介于 875--954 之間（這是按照日志區(qū)間的pos節(jié)點算的），只要恢復(fù)到875前就可。

7）

先把凌晨4點全備份的數(shù)據(jù)恢復(fù)：

[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 < ops_2016-09-25.sql Enter password:
 --------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */
--------------
--------------/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */--------------
..........................
--------------/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */--------------

這樣就恢復(fù)了截至當(dāng)日凌晨(4:00)前的備份數(shù)據(jù)都恢復(fù)了。

mysql> show databases;                        #發(fā)現(xiàn)ops庫已經(jīng)恢復(fù)回來了
mysql> 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> show tables;
+---------------+
  Tables_in_ops  
+---------------+
  member  
+---------------+
1 row in set (0.00 sec)
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)
mysql>

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

8）

從binlog日志恢復(fù)數(shù)據(jù)
恢復(fù)命令的語法格式：

mysqlbinlog mysql-bin.0000xx   mysql -u用戶名 -p密碼 數(shù)據(jù)庫名

--------------------------------------------------------
常用參數(shù)選項解釋：

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

--------------------------------------------------------
不常用選項：

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

--------------------------------------------------------

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

a）完全恢復(fù)(需要手動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結(jié)束點恢復(fù)(部分恢復(fù))：

--stop-position=471 pos結(jié)束節(jié)點（按照事務(wù)區(qū)間算，是471）

注意：

此pos結(jié)束節(jié)點介于“member表原始數(shù)據(jù)”與更新“name='李四'”之前的數(shù)據(jù)，這樣就可以恢復(fù)到更改“name='李四'”之前的數(shù)據(jù)了。

操作如下：

[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> 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)

恢復(fù)截止到更改“name='李四'”之間的數(shù)據(jù)（按照事務(wù)區(qū)間算，是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> 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點區(qū)間恢復(fù)(部分恢復(fù))：

更新 name='李四' 這條數(shù)據(jù)，日志區(qū)間是Pos[538] --> End_log_pos[646]，按事務(wù)區(qū)間是：Pos[471] --> End_log_pos[673]

更新 name='小二' 這條數(shù)據(jù)，日志區(qū)間是Pos[740] --> End_log_pos[848]，按事務(wù)區(qū)間是：Pos[673] --> End_log_pos[875]

c1）

單獨恢復(fù) name='李四' 這步操作，可這樣：

按照binlog日志區(qū)間單獨恢復(fù)：

[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

按照事務(wù)區(qū)間單獨恢復(fù)

[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）

單獨恢復(fù) name='小二' 這步操作，可這樣：
按照binlog日志區(qū)間單獨恢復(fù)：

[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

按照事務(wù)區(qū)間單獨恢復(fù)

[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='小二' 多步操作一起恢復(fù)，需要按事務(wù)區(qū)間，可這樣：

[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

查看數(shù)據(jù)庫：

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)

這樣，就恢復(fù)了刪除前的數(shù)據(jù)狀態(tài)了��！

另外：

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

如上，誤刪除ops庫后：

先進(jìn)行全備份恢復(fù)

[root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql

查看ops數(shù)據(jù)庫

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)
mysql>

查看mysq-bin00003日志，找出時間節(jié)點

[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')                               
#執(zhí)行的sql語句
/*!*/;
# at 444
#160925 
21:57:19
 server id 1 end_log_pos 471 Xid = 66    
#開始執(zhí)行的時間
COMMIT
/*!*/;
# at 471
#160925 
21:58:41
 server id 1 end_log_pos 538 Query thread_id=3 exec_time=0 error_code=0    
#結(jié)束時間
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     
#執(zhí)行的sql語句
/*!*/;
# at 646
#160925 
21:58:41
 server id 1 end_log_pos 673 Xid = 68    
#開始執(zhí)行的時間
COMMIT/*!*/;
# at 673
#160925 
21:58:56
 server id 1 end_log_pos 740 Query thread_id=3 exec_time=0 error_code=0   
#結(jié)束時間
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     
 
#執(zhí)行的sql語句
/*!*/;
# at 848
#160925 
21:58:56
 server id 1 end_log_pos 875 Xid = 69   
#開始執(zhí)行的時間
COMMIT/*!*/;
# at 875
#160925 
22:01:08
 server id 1 end_log_pos 954 Query thread_id=3 exec_time=0 error_code=0    
#結(jié)束時間
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*/;

恢復(fù)到更改“name='李四'”之前的數(shù)據(jù)

[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> 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)

這樣，就恢復(fù)了刪除前的狀態(tài)了！

總結(jié)：

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

以上就是mysql之binlog日志以及利用binlog日志恢復(fù)數(shù)據(jù)的示例代碼分享的詳細(xì)內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！