《MYSQL教程解說mysql之binlog日志以及利用binlog日志恢復數據的方法》要點：
本文介紹了MYSQL教程解說mysql之binlog日志以及利用binlog日志恢復數據的方法，希望對您有用。如果有疑問，可以聯系我們。
MYSQL教程眾所周知,binlog日志對于mysql數據庫來說是十分重要的.在數據丟失的緊急情況下,我們往往會想到用binlog日志功能進行數據恢復(定時全備份+binlog日志恢復增量數據部分),化險為夷!
MYSQL教程廢話不多說,下面是梳理的binlog日志操作解說：
MYSQL教程一、初步了解binlog
MYSQL教程MySQL的二進制日志binlog可以說是MySQL最重要的日志,它記錄了所有的DDL和DML語句(除了數據查詢語句select),以事件形式記錄,還包含語句所執行的消耗的時間,MySQL的二進制日志是事務平安型的.
----------------------------------------------------------------------------------------------------------------------------------------------
DDL
----Data Definition Language 數據庫定義語言
主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定義或改變表(TABLE)的結構,數據類型,表之間的鏈接和約束等初始化工作上,他們大多在建立表時使用.
MYSQL教程DML
----Data Manipulation Language 數據把持語言
主要的命令是SELECT、UPDATE、INSERT、DELETE,就象它的名字一樣,這4條命令是用來對數據庫里的數據進行操作的語言
----------------------------------------------------------------------------------------------------------------------------------------------
MYSQL教程mysqlbinlog常見的選項有以下幾個：
MYSQL教程--start-datetime：從二進制日志中讀取指定等于時間戳或者晚于本地計算機的時間
MYSQL教程--stop-datetime：從二進制日志中讀取指定小于時間戳或者等于本地計算機的時間 取值和上述一樣
MYSQL教程--start-position：從二進制日志中讀取指定position 事件位置作為開始.
MYSQL教程--stop-position：從二進制日志中讀取指定position 事件位置作為事件截至
MYSQL教程*********************************************************************
MYSQL教程一般來說開啟binlog日志大概會有1%的性能損耗.
MYSQL教程binlog日志有兩個最重要的使用場景:
MYSQL教程1)MySQL主從復制：MySQL Replication在Master端開啟binlog,Master把它的二進制日志傳遞給slaves來達到
master-slave數據一致的目的.
MYSQL教程2)自然就是數據恢復了,通過使用mysqlbinlog工具來使恢復數據.
binlog日志包括兩類文件：
MYSQL教程1)二進制日志索引文件(文件名后綴為.index)用于記錄所有的二進制文件
MYSQL教程2)二進制日志文件(文件名后綴為.00000*)記錄數據庫所有的DDL和DML(除了數據查詢語句select)語句事件.
MYSQL教程二、開啟binlog日志：
MYSQL教程1)編輯打開mysql配置文件/etc/mys.cnf
MYSQL教程[root@vm-002 ~]# vim /etc/my.cnf
在[mysqld] 區塊添加
log-bin=mysql-bin 確認是打開狀態(mysql-bin 是日志的基本名或前綴名)；
MYSQL教程2)重啟mysqld服務使配置生效
MYSQL教程[root@vm-002 ~]# /etc/init.d/mysqld stop
[root@vm-002 ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
MYSQL教程3)查看binlog日志是否開啟
MYSQL教程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)
MYSQL教程三、常用的binlog日志操作命令
MYSQL教程1)查看所有binlog日志列表
MYSQL教程mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 149 |
| mysql-bin.000002 | 4102 |
+------------------+-----------+
2 rows in set (0.00 sec)
MYSQL教程2)查看master狀態,即最后(最新)一個binlog日志的編號名稱,及其最后一個操作事件pos結束點(Position)值
MYSQL教程mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 4102 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MYSQL教程3)flush刷新log日志,自此刻開始產生一個新編號的binlog日志文件
MYSQL教程mysql> flush logs;
Query OK, 0 rows affected (0.13 sec)
MYSQL教程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)
MYSQL教程注意：
MYSQL教程每當mysqld服務重啟時,會自動執行此命令,刷新binlog日志；在mysqldump備份數據時加 -F 選項也會刷新binlog日志；
MYSQL教程4)重置(清空)所有binlog日志
MYSQL教程mysql> reset master;
Query OK, 0 rows affected (0.12 sec)
MYSQL教程mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 106 |
+------------------+-----------+
1 row in set (0.00 sec)
MYSQL教程四、查看binlog日志內容,常用有兩種方式：
MYSQL教程1)使用mysqlbinlog自帶查看命令法：
MYSQL教程注意：
MYSQL教程-->binlog是二進制文件,普通文件查看器cat、more、vim等都無法打開,必須使用自帶的mysqlbinlog命令查看
MYSQL教程-->binlog日志與數據庫文件在同目錄中
MYSQL教程-->在MySQL5.5以下版本使用mysqlbinlog命令時如果報錯,就加上 “--no-defaults”選項
MYSQL教程查看mysql的數據存放目錄,從下面結果可知是/var/lib//mysql
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
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
MYSQL教程使用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`,`classid`) 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 ? ? ? ? ? ? ? ? ?#執行的時間
.............
MYSQL教程解釋：
MYSQL教程server id 1 ： 數據庫主機的服務號；
end_log_pos 796： sql結束時的pos節點
thread_id=11： 線程號
MYSQL教程2)上面這種方法讀取出binlog日志的全文內容比較多,不容易分辨查看到pos點信息
MYSQL教程下面介紹一種更為方便的查詢命令：
MYSQL教程命令格式：
MYSQL教程mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
MYSQL教程參數解釋：
MYSQL教程IN 'log_name' ：指定要查詢的binlog文件名(不指定就是第一個binlog文件)
FROM pos ：指定從哪個pos起始點開始查起(不指定就是從整個文件首個pos點開始算)
LIMIT [offset,] ：偏移量(不指定就是0)
row_count ：查詢總條數(不指定就是所有行)
MYSQL教程mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 125 |
| mysql-bin.000002 | 823 |
+------------------+-----------+
2 rows in set (0.00 sec)
MYSQL教程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)
MYSQL教程ERROR:
No query specified
MYSQL教程mysql>
MYSQL教程上面這條語句可以將指定的binlog日志文件,分成有效事件行的方式返回,并可使用limit指定pos點的起始偏移,查詢條數!
如下操作示例：
MYSQL教程a)查詢第一個(最早)的binlog日志：
MYSQL教程mysql> show binlog events\G;
MYSQL教程b)指定查詢 mysql-bin.000002這個文件：
MYSQL教程mysql> show binlog events in 'mysql-bin.000002'\G;
MYSQL教程c)指定查詢 mysql-bin.000002這個文件,從pos點:624開始查起：
MYSQL教程mysql> show binlog events in 'mysql-bin.000002' from 624\G;
MYSQL教程d)指定查詢 mysql-bin.000002這個文件,從pos點:624開始查起,查詢10條(即10條語句)
MYSQL教程mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G;
MYSQL教程e)指定查詢 mysql-bin.000002這個文件,從pos點:624開始查起,偏移2行(即中間跳過2個),查詢10條
MYSQL教程mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;
MYSQL教程五、利用binlog日志恢復mysql數據
MYSQL教程以下對ops庫的member表進行操作
MYSQL教程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教程mysql> show tables;
+---------------+
| Tables_in_ops |
+---------------+
| member |
+---------------+
1 row in set (0.00 sec)
MYSQL教程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教程事先插入兩條數據
MYSQL教程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)
MYSQL教程下面開始進行場景模擬：
MYSQL教程1)
MYSQL教程ops庫會在每天凌晨4點進行一次完全備份的定時計劃任務,如下：
[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
MYSQL教程這里手動執行下,將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
-----------------
MYSQL教程參數說明：
MYSQL教程-B：指定數據庫
-F：刷新日志
-R：備份存儲過程等
-x：鎖表
--master-data：在備份語句里添加CHANGE MASTER語句以及binlog文件及位置點信息
-----------------
MYSQL教程待到數據庫備份完成,就不用擔心數據丟失了,因為有完全備份數據在!!
MYSQL教程由于上面在全備份的時候使用了-F選項,那么當數據備份操作剛開始的時候系統就會自動刷新log,這樣就會自動產生
一個新的binlog日志,這個新的binlog日志就會用來記錄備份之后的數據庫“增刪改”操作
MYSQL教程查看一下：
MYSQL教程mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MYSQL教程也就是說, mysql-bin.000003 是用來記錄4:00之后對數據庫的所有“增刪改”操作.
MYSQL教程2)
MYSQL教程早上9點上班了,由于業務的需求會對數據庫進行各種“增刪改”操作.
MYSQL教程比如：在ops庫下member表內插入、修改了數據等等：
MYSQL教程先是早上進行插入數據：
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教程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)
MYSQL教程3)
MYSQL教程中午又執行了修改數據操作：
MYSQL教程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教程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教程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)
MYSQL教程4)
MYSQL教程在下午18:00的時候,悲劇莫名其妙的出現了!
MYSQL教程手賤執行了drop語句,直接刪除了ops庫!嚇尿!
mysql> drop database ops;
Query OK, 1 row affected (0.02 sec)
MYSQL教程5)
MYSQL教程這種時候,一定不要慌張!!!
MYSQL教程先仔細查看最后一個binlog日志,并記錄下關鍵的pos點,到底是哪個pos點的操作導致了數據庫的破壞(通常在最后幾步)；
MYSQL教程先備份一下最后一個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
MYSQL教程接著執行一次刷新日志索引操作,重新開始新的binlog日志記錄文件.按理說mysql-bin.000003
MYSQL教程這個文件不會再有后續寫入了,因為便于我們分析原因及查找ops節點,以后所有數據庫操作都會寫入到下一個日志文件.
MYSQL教程mysql> flush logs;
Query OK, 0 rows affected (0.13 sec)
MYSQL教程mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MYSQL教程6)
MYSQL教程讀取binlog日志,分析問題.
讀取binlog日志的辦法上面已經說到.
MYSQL教程辦法一：使用mysqlbinlog讀取binlog日志：
MYSQL教程[root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000003
MYSQL教程辦法二：登錄服務器,并查看(推薦此種辦法)
MYSQL教程mysql> show binlog events in 'mysql-bin.000003';
MYSQL教程+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
| 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教程或者：
MYSQL教程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)
MYSQL教程通過分析,造成數據庫破壞的pos點區間是介于 875--954 之間(這是依照日志區間的pos節點算的),只要恢復到875前就可.
MYSQL教程7)
MYSQL教程先把凌晨4點全備份的數據恢復：
[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 */
--------------
MYSQL教程--------------
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */
--------------
MYSQL教程.............
.............
MYSQL教程--------------
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */
--------------
MYSQL教程這樣就恢復了截至當日凌晨(4:00)前的備份數據都恢復了.
MYSQL教程mysql> show databases; ? ? ? ? ? ? ? ? ? ? ? ?#發現ops庫已經恢復回來了
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
MYSQL教程Database changed
mysql> show tables;
+---------------+
| Tables_in_ops |
+---------------+
| member |
+---------------+
1 row in set (0.00 sec)
MYSQL教程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教程mysql>
MYSQL教程
但是這僅僅只是恢復了當天凌晨4點之前的數據,在4:00--18:00之間的數據還沒有恢復回來!!
怎么辦呢?
莫慌!這可以根據前面提到的mysql-bin.000003的新binlog日志進行恢復.
MYSQL教程8)
MYSQL教程從binlog日志恢復數據
恢復命令的語法格式：
mysqlbinlog mysql-bin.0000xx | mysql -u用戶名 -p暗碼 數據庫名
MYSQL教程--------------------------------------------------------
常用參數選項解釋：
--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日志
--------------------------------------------------------
MYSQL教程小結：實際是將讀出的binlog日志內容,通過管道符傳遞給mysql命令.這些命令、文件盡量寫成絕對路徑；
MYSQL教程a)完全恢復(需要手動vim編輯mysql-bin.000003,將那條drop語句剔除掉)
MYSQL教程[root@vm-002 backup]# /usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
MYSQL教程b)指定pos結束點恢復(部分恢復)：
MYSQL教程--stop-position=471 pos結束節點(依照事務區間算,是471)
MYSQL教程注意：
MYSQL教程此pos結束節點介于“member表原始數據”與更新“name='李四'”之前的數據,這樣就可以恢復到更改“name='李四'”之前的數據了.
MYSQL教程操作如下：
MYSQL教程[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教程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)
MYSQL教程恢復截止到更改“name='李四'”之間的數據(依照事務區間算,是673)
MYSQL教程[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教程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)
MYSQL教程c)指定pso點區間恢復(部分恢復)：
MYSQL教程更新 name='李四' 這條數據,日志區間是Pos[538] --> End_log_pos[646],按事務區間是：Pos[471] --> End_log_pos[673]
MYSQL教程更新 name='小二' 這條數據,日志區間是Pos[740] --> End_log_pos[848],按事務區間是：Pos[673] --> End_log_pos[875]
MYSQL教程c1)
MYSQL教程單獨恢復 name='李四' 這步操作,可這樣：
MYSQL教程依照binlog日志區間單獨恢復：
MYSQL教程[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
MYSQL教程依照事務區間單獨恢復
[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
MYSQL教程c2)
MYSQL教程單獨恢復 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
MYSQL教程依照事務區間單獨恢復
[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
MYSQL教程c3)
MYSQL教程將 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教程查看數據庫：
MYSQL教程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)
MYSQL教程這樣,就恢復了刪除前的數據狀態了!!
MYSQL教程另外：
MYSQL教程也可指定時間節點區間恢復(部分恢復)：
除了用pos節點的方法進行恢復,也可以通過指定時間節點區間進行恢復,按時間恢復需要用mysqlbinlog命令讀取binlog日志內容,找時間節點.
MYSQL教程如上,誤刪除ops庫后：
MYSQL教程先進行全備份恢復
[root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql
MYSQL教程查看ops數據庫
MYSQL教程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教程mysql>
MYSQL教程查看mysq-bin00003日志,找出時間節點
MYSQL教程[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*/;
MYSQL教程恢復到更改“name='李四'”之前的數據
MYSQL教程[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:57:19" --stop-datetime="2016-09-25 21:58:41" --
MYSQL教程database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
MYSQL教程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)
MYSQL教程
[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)
MYSQL教程
[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)
MYSQL教程
這樣,就恢復了刪除前的狀態了!
MYSQL教程總結：
MYSQL教程所謂恢復,就是讓mysql將保存在binlog日志中指定段落區間的sql語句逐個重新執行一次罷了.
MYSQL教程以上這篇解說mysql之binlog日志以及利用binlog日志恢復數據的辦法就是小編分享給大家的全部內容了,希望能給大家一個參考,也希望大家多多支持維易PHP.
《MYSQL教程解說mysql之binlog日志以及利用binlog日志恢復數據的方法》是否對您有啟發，歡迎查看更多與《MYSQL教程解說mysql之binlog日志以及利用binlog日志恢復數據的方法》相關教程，學精學透。維易PHP學院為您提供精彩教程。
