阅读 958

[Mysql]binlog日志开启和使用

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

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

1、首先,开启binlog

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

log_bin
配置项,取消注释就可以使用了,当然更推荐写成
log_bin=xxx
的形式。这里的xxx会作为日志的前缀名,否则会采用当前机器名作为前缀。

重启mysql

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



我这里就没有配置前缀名,所以有点难看,log_bin为on的话就配置成功了;

另外其他配置项含义:

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;复制代码


文章分类
后端
文章标签