binlog日志對(duì)于mysql數(shù)據(jù)庫(kù)重要性講析

本文主要給大家介紹binlog日志對(duì)于MySQL數(shù)據(jù)庫(kù)重要性講析，希望可以給大家補(bǔ)充和更新些知識(shí)，如有其它問(wèn)題需要了解的可以持續(xù)在創(chuàng)新互聯(lián)行業(yè)資訊里面關(guān)注我的更新文章的。

目前成都創(chuàng)新互聯(lián)已為近千家的企業(yè)提供了網(wǎng)站建設(shè)、域名、網(wǎng)絡(luò)空間、網(wǎng)站托管、服務(wù)器租用、企業(yè)網(wǎng)站設(shè)計(jì)、五指山網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長(zhǎng)，共同發(fā)展。

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

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

一、初步了解binlog
MySQL的二進(jìn)制日志binlog可以說(shuō)是MySQL最重要的日志，它記錄了所有的DDL和DML語(yǔ)句（除了數(shù)據(jù)查詢語(yǔ)句select），以事件形式記錄，還包含語(yǔ)句所執(zhí)行的消耗的時(shí)間，MySQL的二進(jìn)制日志是事務(wù)安全型的。

DDL
----Data Definition Language 數(shù)據(jù)庫(kù)定義語(yǔ)言
主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定義或改變表（TABLE）的結(jié)構(gòu)，數(shù)據(jù)類型，表之間的鏈接和約束等初始化工作上，他們大多在建立表時(shí)使用。

binlog日志對(duì)于mysql數(shù)據(jù)庫(kù)重要性講析

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

mysqlbinlog常見(jiàn)的選項(xiàng)有以下幾個(gè)：
--start-datetime：從二進(jìn)制日志中讀取指定等于時(shí)間戳或者晚于本地計(jì)算機(jī)的時(shí)間
--stop-datetime：從二進(jìn)制日志中讀取指定小于時(shí)間戳或者等于本地計(jì)算機(jī)的時(shí)間取值和上述一樣
--start-position：從二進(jìn)制日志中讀取指定position 事件位置作為開(kāi)始。
--stop-position：從二進(jìn)制日志中讀取指定position 事件位置作為事件截至

一般來(lái)說(shuō)開(kāi)啟binlog日志大概會(huì)有1%的性能損耗。
binlog日志有兩個(gè)最重要的使用場(chǎng)景:
1）MySQL主從復(fù)制：MySQL Replication在Master端開(kāi)啟binlog，Master把它的二進(jìn)制日志傳遞給slaves來(lái)達(dá)到
master-slave數(shù)據(jù)一致的目的。
2）自然就是數(shù)據(jù)恢復(fù)了，通過(guò)使用mysqlbinlog工具來(lái)使恢復(fù)數(shù)據(jù)。
binlog日志包括兩類文件：
1）二進(jìn)制日志索引文件（文件名后綴為.index）用于記錄所有的二進(jìn)制文件
2）二進(jìn)制日志文件（文件名后綴為.00000*）記錄數(shù)據(jù)庫(kù)所有的DDL和DML(除了數(shù)據(jù)查詢語(yǔ)句select)語(yǔ)句事件。

二、開(kāi)啟binlog日志：
1）編輯打開(kāi)mysql配置文件/etc/mys.cnf
[root@vm-002 ~]# vim /etc/my.cnf
在[mysqld] 區(qū)塊添加
log-bin=mysql-bin 確認(rèn)是打開(kāi)狀態(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 ]

2）查看master狀態(tài)，即最后(最新)一個(gè)binlog日志的編號(hào)名稱，及其最后一個(gè)操作事件pos結(jié)束點(diǎn)(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日志，自此刻開(kāi)始產(chǎn)生一個(gè)新編號(hào)的binlog日志文件
mysql> flush logs;
Query OK, 0 rows affected (0.13 sec)

注意：
每當(dāng)mysqld服務(wù)重啟時(shí)，會(huì)自動(dòng)執(zhí)行此命令，刷新binlog日志；在mysqldump備份數(shù)據(jù)時(shí)加 -F 選項(xiàng)也會(huì)刷新binlog日志；

4）重置(清空)所有binlog日志
mysql> reset master;
Query OK, 0 rows affected (0.12 sec)

四、查看binlog日志內(nèi)容，常用有兩種方式：
1）使用mysqlbinlog自帶查看命令法：
注意：
-->binlog是二進(jìn)制文件，普通文件查看器cat、more、vim等都無(wú)法打開(kāi)，必須使用自帶的mysqlbinlog命令查看
-->binlog日志與數(shù)據(jù)庫(kù)文件在同目錄中
-->在MySQL5.5以下版本使用mysqlbinlog命令時(shí)如果報(bào)錯(cuò)，就加上 “--no-defaults”選項(xiàng)

查看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)容，下面截取其中的一個(gè)片段分析：
[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,classid) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2') #執(zhí)行的sql語(yǔ)句
/!/;
#at 796
#160925 21:29:53 server id 1 end_log_pos 823 Xid = 17 #執(zhí)行的時(shí)間
.............

解釋：
server id 1 ：數(shù)據(jù)庫(kù)主機(jī)的服務(wù)號(hào)；
end_log_pos 796： sql結(jié)束時(shí)的pos節(jié)點(diǎn)
thread_id=11：線程號(hào)

2）上面這種辦法讀取出binlog日志的全文內(nèi)容比較多，不容易分辨查看到pos點(diǎn)信息
下面介紹一種更為方便的查詢命令：
命令格式：
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
參數(shù)解釋：
IN 'log_name' ：指定要查詢的binlog文件名(不指定就是第一個(gè)binlog文件)
FROM pos ：指定從哪個(gè)pos起始點(diǎn)開(kāi)始查起(不指定就是從整個(gè)文件首個(gè)pos點(diǎn)開(kāi)始算)
LIMIT [offset,] ：偏移量(不指定就是0)
row_count ：查詢總條數(shù)(不指定就是所有行)

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>

上面這條語(yǔ)句可以將指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos點(diǎn)的起始偏移，查詢條數(shù)！
如下操作示例：
a）查詢第一個(gè)(最早)的binlog日志：
mysql> show binlog events\G;

b）指定查詢 mysql-bin.000002這個(gè)文件：
mysql> show binlog events in 'mysql-bin.000002'\G;

c）指定查詢 mysql-bin.000002這個(gè)文件，從pos點(diǎn):624開(kāi)始查起：
mysql> show binlog events in 'mysql-bin.000002' from 624\G;

d）指定查詢 mysql-bin.000002這個(gè)文件，從pos點(diǎn):624開(kāi)始查起，查詢10條（即10條語(yǔ)句）
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

e）指定查詢 mysql-bin.000002這個(gè)文件，從pos點(diǎn):624開(kāi)始查起，偏移2行（即中間跳過(guò)2個(gè)），查詢10條
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

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

以下對(duì)ops庫(kù)的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)

事先插入兩條數(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)
Records: 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)

下面開(kāi)始進(jìn)行場(chǎng)景模擬：
1）
ops庫(kù)會(huì)在每天凌晨4點(diǎn)進(jìn)行一次完全備份的定時(shí)計(jì)劃任務(wù)，如下：
[root@vm-002 ~]# crontab -l
0 4 * /usr/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz

這里手動(dòng)執(zhí)行下，將ops數(shù)據(jù)庫(kù)備份到/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ù)說(shuō)明：
-B：指定數(shù)據(jù)庫(kù)
-F：刷新日志
-R：備份存儲(chǔ)過(guò)程等
-x：鎖表
--master-data：在備份語(yǔ)句里添加CHANGE MASTER語(yǔ)句以及binlog文件及位置點(diǎn)信息

待到數(shù)據(jù)庫(kù)備份完成，就不用擔(dān)心數(shù)據(jù)丟失了，因?yàn)橛型耆珎浞輸?shù)據(jù)在?。?/p>

由于上面在全備份的時(shí)候使用了-F選項(xiàng)，那么當(dāng)數(shù)據(jù)備份操作剛開(kāi)始的時(shí)候系統(tǒng)就會(huì)自動(dòng)刷新log，這樣就會(huì)自動(dòng)產(chǎn)生
一個(gè)新的binlog日志，這個(gè)新的binlog日志就會(huì)用來(lái)記錄備份之后的數(shù)據(jù)庫(kù)“增刪改”操作
查看一下：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

也就是說(shuō)， mysql-bin.000003 是用來(lái)記錄4:00之后對(duì)數(shù)據(jù)庫(kù)的所有“增刪改”操作。

2）
早上9點(diǎn)上班了，由于業(yè)務(wù)的需求會(huì)對(duì)數(shù)據(jù)庫(kù)進(jìn)行各種“增刪改”操作。
比如：在ops庫(kù)下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的時(shí)候，悲劇莫名其妙的出現(xiàn)了！
手賤執(zhí)行了drop語(yǔ)句，直接刪除了ops庫(kù)！嚇尿！
mysql> drop database ops;
Query OK, 1 row affected (0.02 sec)

5）
這種時(shí)候，一定不要慌張?。?！
先仔細(xì)查看最后一個(gè)binlog日志，并記錄下關(guān)鍵的pos點(diǎn)，到底是哪個(gè)pos點(diǎn)的操作導(dǎo)致了數(shù)據(jù)庫(kù)的破壞(通常在最后幾步)；

先備份一下最后一個(gè)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í)行一次刷新日志索引操作，重新開(kāi)始新的binlog日志記錄文件。按理說(shuō)mysql-bin.000003
這個(gè)文件不會(huì)再有后續(xù)寫(xiě)入了，因?yàn)楸阌谖覀兎治鲈蚣安檎襬ps節(jié)點(diǎn)，以后所有數(shù)據(jù)庫(kù)操作都會(huì)寫(xiě)入到下一個(gè)日志文件。
mysql> flush logs;
Query OK, 0 rows affected (0.13 sec)

6）
讀取binlog日志，分析問(wèn)題。
讀取binlog日志的方法上面已經(jīng)說(shuō)到。
方法一：使用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 | useops; 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 | useops`; 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)

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

7）
先把凌晨4點(diǎn)全備份的數(shù)據(jù)恢復(fù)：
[root@vm-002 ~]# cd /opt/backup/
[root@vm-002 backup]# ls
mysql-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庫(kù)已經(jīng)恢復(fù)回來(lái)了
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>

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

8）
從binlog日志恢復(fù)數(shù)據(jù)
恢復(fù)命令的語(yǔ)法格式：
mysqlbinlog mysql-bin.0000xx | mysql -u用戶名 -p密碼數(shù)據(jù)庫(kù)名

常用參數(shù)選項(xiàng)解釋：
--start-position=875 起始pos點(diǎn)
--stop-position=954 結(jié)束pos點(diǎn)
--start-datetime="2016-9-25 22:01:08" 起始時(shí)間點(diǎn)
--stop-datetime="2019-9-25 22:09:46" 結(jié)束時(shí)間點(diǎn)
--database=zyyshop 指定只恢復(fù)zyyshop數(shù)據(jù)庫(kù)(一臺(tái)主機(jī)上往往有多個(gè)數(shù)據(jù)庫(kù)，只限本地log日志)

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

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

a）完全恢復(fù)(需要手動(dòng)vim編輯mysql-bin.000003，將那條drop語(yǔ)句剔除掉)
[root@vm-002 backup]# /usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

b）指定pos結(jié)束點(diǎn)恢復(fù)(部分恢復(fù))：
--stop-position=471 pos結(jié)束節(jié)點(diǎn)（按照事務(wù)區(qū)間算，是471）
注意：
此pos結(jié)束節(jié)點(diǎn)介于“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

恢復(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點(diǎn)區(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）
單獨(dú)恢復(fù) name='李四' 這步操作，可這樣：
按照binlog日志區(qū)間單獨(dú)恢復(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ū)間單獨(dú)恢復(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）
單獨(dú)恢復(fù) name='小二' 這步操作，可這樣：
按照binlog日志區(qū)間單獨(dú)恢復(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ū)間單獨(dú)恢復(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ù)庫(kù)：
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)了！！

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

如上，誤刪除ops庫(kù)后：
先進(jìn)行全備份恢復(fù)
[root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql

查看ops數(shù)據(jù)庫(kù)
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日志，找出時(shí)間節(jié)點(diǎn)
[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語(yǔ)句
/!/;
#at 444
#160925 21:57:19 server id 1 end_log_pos 471 Xid = 66 #開(kāi)始執(zhí)行的時(shí)間
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é)束時(shí)間
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語(yǔ)句
/!/;
#at 646
#160925 21:58:41 server id 1 end_log_pos 673 Xid = 68 #開(kāi)始執(zhí)行的時(shí)間
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é)束時(shí)間
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語(yǔ)句
/!/;
#at 848
#160925 21:58:56 server id 1 end_log_pos 875 Xid = 69 #開(kāi)始執(zhí)行的時(shí)間
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é)束時(shí)間
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

[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語(yǔ)句逐個(gè)重新執(zhí)行一次而已。

看了以上關(guān)于binlog日志對(duì)于mysql數(shù)據(jù)庫(kù)重要性講析，希望能給大家在實(shí)際運(yùn)用中帶來(lái)一定的幫助。本文由于篇幅有限，難免會(huì)有不足和需要補(bǔ)充的地方，如有需要更加專業(yè)的解答，可在官網(wǎng)聯(lián)系我們的24小時(shí)售前售后，隨時(shí)幫您解答問(wèn)題的。

文章名稱：binlog日志對(duì)于mysql數(shù)據(jù)庫(kù)重要性講析
網(wǎng)站網(wǎng)址：http://muchs.cn/article36/gdessg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)頁(yè)設(shè)計(jì)公司、網(wǎng)站營(yíng)銷、品牌網(wǎng)站設(shè)計(jì)、關(guān)鍵詞優(yōu)化、網(wǎng)站內(nèi)鏈、網(wǎng)站制作

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

binlog日志對(duì)于mysql數(shù)據(jù)庫(kù)重要性講析

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

這里手動(dòng)執(zhí)行下，將ops數(shù)據(jù)庫(kù)備份到/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.gzEnter password:[root@vm-002 ~]# ls /opt/backup/ops_2016-09-25.sql.gz

參數(shù)說(shuō)明：-B：指定數(shù)據(jù)庫(kù)-F：刷新日志-R：備份存儲(chǔ)過(guò)程等-x：鎖表--master-data：在備份語(yǔ)句里添加CHANGE MASTER語(yǔ)句以及binlog文件及位置點(diǎn)信息

7）先把凌晨4點(diǎn)全備份的數(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.sqlEnter 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 /

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

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

參數(shù)說(shuō)明：
-B：指定數(shù)據(jù)庫(kù)
-F：刷新日志
-R：備份存儲(chǔ)過(guò)程等
-x：鎖表
--master-data：在備份語(yǔ)句里添加CHANGE MASTER語(yǔ)句以及binlog文件及位置點(diǎn)信息

7）
先把凌晨4點(diǎn)全備份的數(shù)據(jù)恢復(fù)：
[root@vm-002 ~]# cd /opt/backup/
[root@vm-002 backup]# ls
mysql-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:

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