[Mysql]binlog日志开启和使用

mysql的binlog日志会记录记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

一般来说，我们可以通过binlog查找操作日志，用来恢复数据，或者不需要我们关心的是，主从复制也是通过binlog的传输来实现的。

1、首先，开启binlog

直接在my.cnf，在win下面是my.ini中修改，在[mysqld] 区块下面有一个默认被注释掉的

重启mysql

查看配置项：show variables like'log_%';

另外其他配置项含义：

log_bin
设置此参数表示启用binlog功能，并指定路径名称
log_bin_index
设置此参数是指定二进制索引文件的路径与名称
binlog_do_db
此参数表示只记录指定数据库的二进制日志
binlog_ignore_db
此参数表示不记录指定的数据库的二进制日志
max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸
binlog_cache_size
此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。
binlog_cache_use
使用二进制日志缓存的事务数量
binlog_cache_disk_use
使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
max_binlog_size
Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

2、查看binlog
先做测试，删掉一个表

delete from sys_user_role;

binlog文件需要用mysql目录下的bin/mysqlbinlog打开：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000002

位置
位于文件中的位置，“at 421”说明“事件”的起点，是以第421字节开始；“end_log_pos 526”说明以第526字节结束

时间戳
事件发生的时间戳：“17110311:04:58”

事件执行时间
事件执行花费的时间:"exec_time=0"

错误码
错误码为：“error_code=0”

服务器的标识
服务器的标识id：“server id 1”

操作sql
delete from sys_user_role;

还有一种更好用的办法：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

选项解析：
IN 'log_name' 指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count 查询总条数(不指定就是所有行)

mysql> show binlog events in 'mysql-bin.000023';

截取部分查询结果：

• 20. row ******* Log_name: mysql-bin.000021 ----------------------------------------------> 查询的binlog日志文件名
  
  Pos: 11197 ----------------------------------------------------------> pos起始点:
  
  Event_type: Query ----------------------------------------------------------> 事件类型：Query
  
  Server_id: 1 --------------------------------------------------------------> 标识是由哪台服务器执行的
  
  End_log_pos: 11308 ----------------------------------------------------------> pos结束点:11308(即：下行的pos起始点)
  
  Info: use
  zyyshop
  ; INSERT INTO
  team2
  VALUES (0,345,'asdf8er5') ---> 执行的sql语句
• 21. row ******* Log_name: mysql-bin.000021
  
  Pos: 11308 ----------------------------------------------------------> pos起始点:11308(即：上行的pos结束点)
  
  Event_type: Query
  
  Server_id: 1
  
  End_log_pos: 11417
  
  Info: use
  zyyshop
  ; /*!40000 ALTER TABLE
  team2
  ENABLE KEYS */
• 22. row ******* Log_name: mysql-bin.000021
  
  Pos: 11417
  
  Event_type: Query
  
  Server_id: 1
  
  End_log_pos: 11510
  
  Info: use
  zyyshop
  ; DROP TABLE IF EXISTS
  type
  
  
  这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数；

A.查询第一个(最早)的binlog日志：

mysql> show binlog events\G;

B.指定查询 mysql-bin.000021 这个文件：

mysql> show binlog events in 'mysql-bin.000021'\G;

C.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起：

mysql> show binlog events in 'mysql-bin.000021' from 8224\G;

D.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，查询10条

mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10\G;

E.指定查询 mysql-bin.000021 这个文件，从pos点:8224开始查起，偏移2行，查询10条

mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10\G;