Mysql操作binlog二進制日志數據的例子

記得在幾年前一個朋友沒有做數據庫備份但以前安裝mysql時開啟二進制日志功能，結果這一次這個二進制日志就救了它一命了，下面我們來看看在CentOS 6.5 x86_64操作binlog日志的例子。

系統環境：

服務器系統：CentOS 6.5 x86_64
Mysql版本 :Mysql 5.1

一、binlog介紹

1.binlog，即二進制日志，它記錄了數據庫上的所有改變。
2.改變數據庫的SQL語句執行結束時，將在binlog的末尾寫入一條記錄，同時通知語句解析器，語句執行完畢
3.binlog格式
1.基於語句，無法保證所有語句都在從庫執行成功，比如update…limit 1;
2.基於行，將每一次發動記為binlog中的一行，在執行一個特別復雜的update或delete操作時，基於行的格式會有優勢

二、登陸到mysql查看binlog

1.只查看第一個binlog文件的內容

mysql> show binlog events;
2.查看指定binlog文件的內容

mysql> show binlog events in 'binlog.000007';
3.查看當前正在寫入的binlog文件

mysql> show master status \G;
4.獲取binlog文件列表

mysql> show binary logs;

三、用mysqlbinlog工具查看

1.基於開始/結束時間

[root@localhost var]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --start-datetime='2014-08-04 00:00:00' --stop-datetime='2014-08-13 23:59:59' -d wordpress binlog.000007
2.基於pos值

[root@localhost var]# /usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position=107 --stop-position=1000 -d wordpress binlog.000007

注意：

1.不要查看當前正在寫入的binlog文件
2.不要加-force參數強制訪問
3.如果binlog格式是行模式的，請回-vv參數

四、用mysqlbinlog 工具來顯示記錄的二進制結果，然後導入到文本文件，為了以後的恢復。
詳細過程如下：
D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=4 --stop-position=106 yueliangd
ao_binglog.000001 > c:\\test1.txt
test1.txt的文件內容：

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#7122 16:9:18 server id 1 end_log_pos 106     Start: binlog v 4, server v 5.1.22-rc-community-log created 7122 16:9:18 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
第二行的記錄：
D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=106 --stop-position=134 yuelian
gdao_binglog.000001 > c:\\test1.txt
test1.txt內容如下：

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 106
#7122 16:22:36 server id 1 end_log_pos 134     Intvar
SET INSERT_ID=1/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

第三行記錄：
D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=254 yuelian
gdao_binglog.000001 > c:\\test1.txt
內容：
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 134
#7122 16:55:31 server id 1 end_log_pos 254     Query    thread_id=1    exec_time=0    error_code=0
use test/*!*/;
SET TIMESTAMP=1196585731/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
create table a1(id int not null auto_increment primary key,
str varchar(1000)) engine=myisam/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

第四行的記錄：
D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=254 --stop-position=330 yuelian
gdao_binglog.000001 > c:\\test1.txt
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 254
#7122 16:22:36 server id 1 end_log_pos 330     Query    thread_id=1    exec_time=0    error_code=0
use test/*!*/;
SET TIMESTAMP=1196583756/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=1344274432/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
use `test`; insert into a1(str) values ('I love you'),('You love me')/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

5、查看這些東西是為了恢復數據，而不是為了好玩。所以我們最中還是為了要導入結果到MYSQL中。

D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=330 yuelian
gdao_binglog.000001 | mysql -uroot -p

或者
D:\LAMP\MYSQL5\data>mysqlbinlog --start-position=134 --stop-position=330 yuelian
gdao_binglog.000001 >test1.txt
進入MYSQL導入
mysql> source c:\\test1.txt
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Charset changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
6、查看數據：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a1             |
+----------------+
1 row in set (0.01 sec)

mysql> select * from a1;
+----+-------------+
| id | str         |
+----+-------------+
| 1 | I love you |
| 2 | You love me |
+----+-------------+
2 rows in set (0.00 sec)
 
 
將一個mysqlbinlog文件導為sql文件
cd  cd /usr/local/mysql
./mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/001.sql
將mysql-bin.000001日志文件導成001.sql

可以在mysqlbinlog語句中通過--start-date和--stop-date選項指定DATETIME格式的起止時間
./mysqlbinlog --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 > /opt/004.sql
將mysql-bin.000002文件中截止到2009-04-10 17:41:28的日志導成004.sql
 
./mysqlbinlog --start-date="2009-04-10 17:30:05" --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002  /usr/local/mysql/data/mysql-bin.0000023> /opt/004.sql
----如果有多個binlog文件，中間用空格隔開，打上完全路徑
 
./mysqlbinlog --start-date="2009-04-10 17:30:05" --stop-date="2009-04-10 17:41:28" /usr/local/mysql/data/mysql-bin.000002 |mysql -u root -p123456
或者  source /opt/004.sql
將mysql-bin.000002日志文件中從2009-04-10 17:30:05到2008-04-10 17:41:28截止的sql語句導入到mysql中