Mariadb/Mysql binlog大全--持续更新

默认情况下，Mariadb/Mysql配置里面没有启用

修改配置：vim /etc/my. cnf

增加：

log_bin = mysql-bin #是否开启了二进制日志

binlog_format = mixed #二进制日志记录的模式

expire_logs_days = 30 #二进制日志自动删除的天数。默认值为0,表示"没有自动删除"

#cat /etc/my.cnf | egrep 'log[_-]bin|binlog_format|expire_logs_days' 查看配置

当没配置binglog_format格式，可以用set session binglog_format=''和set global binglog_format='' 在线修改

show global variables like 'log_bin';==>查看是否已经成功启用

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin     | ON   |

+---------------+-------+

1 row in set (0.00 sec)

show binary logs;==>显示当前日志文件

+-------------------+-----------+

| Log_name       | File_size |

+-------------------+-----------+

| master_bin.000001 |     821 |

+-------------------+-----------+

1 row in set (0.00 sec)

flush logs;==>刷新log，生成新的

show master status;

+-------------------+----------+--------------+------------------+

| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master_bin.000002 |    608 |         |            |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

reset master;==>清空log，重新开始

Query OK, 0 rows affected (0.03 sec)

show master status;

+-------------------+----------+--------------+------------------+

| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master_bin.000001 |    245 |         |            |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

show binlog events in 'master_bin.000001'\G ==>查询binlog内容

*************************** 1. row ***************************

   Log_name: master_bin.000001

        Pos: 4

 Event_type: Format_desc

  Server_id: 1

End_log_pos: 245

       Info: Server ver: 5.5.44-MariaDB-log, Binlog ver: 4

*************************** 2. row ***************************

   Log_name: master_bin.000001

        Pos: 245

 Event_type: Query

  Server_id: 1

End_log_pos: 332

       Info: use `school`; truncate table teacher

2 rows in set (0.00 sec)

或mysqlbinlog --start-date="2016-02-28 00:00:00"  --stop-date="2016-02-29 00:00:00" master_bin.000001 按时间查询 也可按位置恢复 positon

      本文转自whshurk 51CTO博客，原文链接：http://blog.51cto.com/shurk/1745630 ，如需转载请自行联系原作者