作者:幽鸿???Jan 20, 2016 9:42:21 PM
1、开启binlog
???????? 开启MySql的binlog其实不是像网上说的那么一致,我用的是Ubuntu系统。这些debian系统与普通的Linux在开启binlog的方法上是有区别的。在unbuntu下默认安装的MySql,开启binlog只需编辑/etc/mysql/my.cnf文件,修改配置:
??????? log_bin = mysql-bin------注意了,这里有个很大的坑,并不是打开注释,变成log_bin== /data/logs/mysql/mysql-bin.log
那么简单。打开注释后,重启MySql服务的时候,会fail to restart。这里只能配置成一个文件,会默认生成这个文件开头的日志文件,如2中查询的。
???????? 到此,binlog是开启了。但是,我们应用程序在使用MySql数据库的时候,会报错:
java.sql.SQLException: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA
in its declaration and binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)
???????? 这是因为,在function里面,只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。解决办法很简单:
???????? 在my.cnf配置文件中添加:log_bin_trust_function_creators=1 就OK了。
2、查询binlog
(1)查询第一个Binlog文件内容:
mysql> show binlog events; +------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+ | Log_name???????? | Pos | Event_type? | Server_id | End_log_pos | Info?????????????????????????????????????????????????? | +------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+ | mysql-bin.000001 |?? 4 | Format_desc |???????? 1 |???????? 107 | Server ver: 5.5.43-0ubuntu0.12.04.1-log, Binlog ver: 4 | | mysql-bin.000001 | 107 | Stop??????? |???????? 1 |???????? 126 |??????????????????????????????????????????????????????? | +------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
(2)查看指定binlog文件的内容
show?binlog?events?in?'mysql-bin.000002';
(3)查看当前正在写入的binlog文件
show?master?status\G
(4)获取binlog文件列表
show?binary?logs;
3、binlog存储路径
??????? 默认情况下,binlog存储于/var/lib/mysql路径下。