mysql 的binlog使用（一）

binlog 就是binary log，二进制日志文件，这个文件记录了mysql所有的dml操作。通过binlog日志我们可以做数据恢复，做主住复制和主从复制等等。

开启binlog

如果数据库安装在linux下，则在对应的。/etc/my.cnf文件中[mysqld]位置添加如下代码，如果是windows,则对应的是my.ini文件：

[mysqld]
server-id        = 1
log_bin          = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size  = 100M
binlog-format    = row #Very important if you want to receive write, update and delete row events

通过数据库查看对应的日志

一下一些指令，可以查看到对应的数据库的日志，但是查出来的数据，基本上很难懂具体什么意思；

show variables like 'log_%';

show master logs;

show master status;
show binlog events; 
show binlog events in 'master.000001' from 2009700;

set global binlog_row_image=FULL;

grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

insert into maxwell.test set id = 5, daemon = 'firebus!  firebus!'

通过一些库文件美化日志

因此，应用而生的就是一些格式化工具，很可以很有规则的拿到对应的日志；
比如一些库文件比如：
python版本：https://github.com/noplay/python-mysql-replication,
当然还有Java版本的，go语言版本的;
Java: https://github.com/shyiko/mysql-binlog-connector-java
GO: https://github.com/siddontang/go-mysql
PHP: https://github.com/krowinski/php-mysql-replication and https://github.com/fengxiangyun/mysql-replication

from pymysqlreplication import BinLogStreamReader

mysql_settings = {'host': '127.0.0.1', 'port': 3306, 'user': 'root', 'passwd': ''}

stream = BinLogStreamReader(connection_settings = mysql_settings, server_id=100)

for binlogevent in stream:
    binlogevent.dump()

stream.close()

可以看到如下一些输出：

=== RotateEvent ===
Date: 1970-01-01T01:00:00
Event size: 24
Read bytes: 0

=== FormatDescriptionEvent ===
Date: 2012-10-07T15:03:06
Event size: 84
Read bytes: 0

=== QueryEvent ===
Date: 2012-10-07T15:03:16
Event size: 64
Read bytes: 64
Schema: test
Execution time: 0
Query: CREATE DATABASE test

=== QueryEvent ===
Date: 2012-10-07T15:03:16
Event size: 151
Read bytes: 151
Schema: test
Execution time: 0
Query: CREATE TABLE test4 (id int NOT NULL AUTO_INCREMENT, data VARCHAR(255), data2 VARCHAR(255), PRIMARY KEY(id))

当然如果还有更进一步的要求，可以使用
- maxwell’s deamon:https://github.com/zendesk/maxwell