mysql之binlog日志

binlog日志(binary log)

1.开启mysql的binlog日志

1.1 查看binlog是否开启

mysql> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+-------+ 6 rows in set (0.01 sec) 

如果没开启

1.2 编辑my.cnf

vim /etc/my.cnf 

增加

#节点Id,注意集群中不能重复,单节点不配置也可以 server-id=123 #开启binlog日志,指定其存放位置 log-bin=/var/lib/mysql/mysql-bin #开启binlog自动过期 expire_logs_days=3 

1.3 重启数据库

service mysqld restart 

然后

mysql> show variables like '%log_bin%'; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+--------------------------------+ 6 rows in set (0.00 sec) 

发现已开启binlog

/var/lib/mysql/mysql-bin是日志文件

/var/lib/mysql/mysql-bin.index是索引

1.4 查看日志索引和事件位置

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 

mysql-bin是日志文件

000004是索引

pos=154是事件位置也可以叫偏移值

2.binlog的常见命令

2.1 查看所有binlog日志列表

mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 7083 | | mysql-bin.000002 | 201 | | mysql-bin.000003 | 201 | | mysql-bin.000004 | 467 | | mysql-bin.000005 | 1218 | +------------------+-----------+ 5 rows in set (0.00 sec) 

2.2查看master状态

即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 

2.3flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件

mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 7083 | | mysql-bin.000002 | 201 | | mysql-bin.000003 | 201 | | mysql-bin.000004 | 467 | | mysql-bin.000005 | 1265 | | mysql-bin.000006 | 154 | +------------------+-----------+ 6 rows in set (0.00 sec) 

注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

2.4重置(清空)所有binlog日志

mysql> reset master; 

3.查看binlog日志的内容

3.1:使用mysqlbinlog自带查看命令法：

注意：

–>binlog是二进制文件，普通文件查看器cat、more、vim等都无法打开，必须使用自带的mysqlbinlog命令查看

–>binlog日志位置在my.cnf中配置 log-bin=/var/lib/mysql/mysql-bin

–>在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “–no-defaults”选项

mysqlbinlog mysql-bin.000004 

mysqlbinlog常见的选项有以下几个：

--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间 --stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地服务器的时间 取值和上述一样 --start-position：从二进制日志中读取指定position 事件位置作为开始。 --stop-position：从二进制日志中读取指定position 事件位置作为事件截至 -vvv 显示sql -d 指定数据库 

--base64-output=decode-rows 

例如

[root@iZ2ze316xjjg0mvije7695Z mysql]# mysqlbinlog mysql-bin.000005 -vvv --base64-output=decode-rows -v --start-position 389 --stop-position 485 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 389 # 10:53:29 server id 1 end_log_pos 420 CRC32 0x63638b44 Xid = 5568 COMMIT/*!*/; # at 420 # 10:50:21 server id 1 end_log_pos 485 CRC32 0x19318c44 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; BEGIN /*added by mysqlbinlog */ /*!*/; ROLLBACK /* added by mysqlbinlog */ /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@iZ2ze316xjjg0mvije7695Z mysql]# mysqlbinlog mysql-bin.000005 --start-position 389 --stop-position 485 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 # 10:52:58 server id 1 end_log_pos 123 CRC32 0xed4fb7b6 Start: binlog v 4, server v 5.7.37-log created 10:52:58 BINLOG ' inMlYg8BAAAAdwAAAHsAAAAAAAQANS43LjM3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA Aba3T+0= '/*!*/; # at 389 # 10:53:29 server id 1 end_log_pos 420 CRC32 0x63638b44 Xid = 5568 COMMIT/*!*/; # at 420 # 10:50:21 server id 1 end_log_pos 485 CRC32 0x19318c44 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; BEGIN /*added by mysqlbinlog */ /*!*/; ROLLBACK /* added by mysqlbinlog */ /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 

[root@iZ2ze316xjjg0mvije7695Z mysql]# mysqlbinlog mysql-bin.000005 -vvv --base64-output=decode-rows --start-position 389 --stop-position 485 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 389 # 10:53:29 server id 1 end_log_pos 420 CRC32 0x63638b44 Xid = 5568 COMMIT/*!*/; # at 420 # 10:50:21 server id 1 end_log_pos 485 CRC32 0x19318c44 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; BEGIN /*added by mysqlbinlog */ /*!*/; ROLLBACK /* added by mysqlbinlog */ /*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 

server id 1 :服务器id

end_log_pos 123 :当前事务结束时的pos

3.2 mysqlbinlog读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息

下面介绍一种更为方便的查询命令：

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 ：查询总条数(不指定就是所有行)

例1:查询mysql-bin.000001日志

show binlog events 

例2:查询mysql-bin.000002日志

\G:可读性好点

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

例3:查询mysql-bin.000002日志,从154查起

show binlog events in 'mysql-bin.000002' from 154\G; 

例4:查询mysql-bin.000002日志,从154查起,并且 跳过前两个,和sql的limit一样

show binlog events in 'mysql-bin.000002' from 154 limit 2,10\G;