情景：
突然某一天，二进制记录文件，一天能产生20G多，导致磁盘不够用，io不够用。
1、查看某一个binlog文件，看看到底是什么语句导致的
/usr/local/mysql/bin/mysqlbinlog mysql-bin.001435 > 123.sql
注意：如果出现BINLOG 'XXXXX'的，说明是Base64编码后的，需要中间加上--base64-output=decode-rows -v命令就能展示出来sql了：如：
# at 4
#200904 15:59:30 server id 1 end_log_pos 120 CRC32 0x13ff3b26 Start: binlog v 4, server v 5.6.45-log created 200904 15:59:30 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
4vNRXw8BAAAAdAAAAHgAAAABAAQANS42LjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADi81FfEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAASY7
/xM=
'/*!*/;解析：
base64-output，可以控制输出语句输出base64编码的BINLOG语句;decode-rows：选项将把基于行的事件解码成一个SQL语句
-r, --result-file=name 将输入的文本格式的文件转储到指定的文件。
2、过滤事件
cat 123.sql |egrep "DELETE|INSERT|UPDATE|ALTER|CREATE" > 111.txt
3、事件的记录次数统计
#cat 111.txt | sort | uniq -c
149206 ### DELETE FROM `inf`.`statresult_202201`
149214 ### INSERT INTO `inf`.`statresult_202201`
96 ### UPDATE `inf`.`pay_action`
90 ### UPDATE `inf`.`player`
2 ### UPDATE `inf`.`stattarget`
4、bin-log的其他内容介绍
来自：https://www.jianshu.com/p/3eb4c44307c1
mysql查看binlog日志详解
1. 查看binlog日志是否开启
mysql默认是不开启binlog日志的，需要手动打开
mysql> show variables like 'log_%';
+----------------------------------------+-------------------------------------------------+
| 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 |
| log_builtin_as_identified_by_password | OFF |
| log_error | C:\Program Files\mysql\data\DESKTOP-7MV7HDB.err |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
+----------------------------------------+-------------------------------------------------+
19 rows in set, 1 warning (0.02 sec)
可以看到log.bin日志为OFF并没有开启
2. 开启binlog日志
Linux找到my.cnf配置文件
windows找到my.ini配置文件
修改其中的内容
#第一种方式:
#开启binlog日志
log_bin=ON
#binlog日志的基本文件名
log_bin_basename=/var/lib/mysql/mysql-bin
#binlog文件的索引文件，管理所有binlog文件
log_bin_index=/var/lib/mysql/mysql-bin.index
#配置serverid
server-id=1
#第二种方式:
#此一行等同于上面log_bin三行
log-bin=/var/lib/mysql/mysql-bin
#配置serverid
server-id=1
修改完后重新启动mysql
linux：service mysqld restart
windows：服务->mysql->右键重新启动
执行完后再次查看binlog
mysql> show variables like 'log_%';
+----------------------------------------+-------------------------------------------------+
| Variable_name | Value |
+----------------------------------------+-------------------------------------------------+
| log_bin | ON |
| log_bin_basename | D:\data\mysql\mysql-bin |
| log_bin_index | D:\data\mysql\mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_builtin_as_identified_by_password | OFF |
| log_error | C:\Program Files\mysql\data\DESKTOP-7MV7HDB.err |
| log_error_verbosity | 3 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_syslog | ON |
| log_syslog_tag | |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
| log_warnings | 2 |
+----------------------------------------+-------------------------------------------------+
19 rows in set, 1 warning (0.00 sec)
D:\data\mysql即是binlog日志存放的目录
3. 操作数据库，查看效果
3.1 查看当前正在写入的binlog文件
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 382
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
3.2 查看指定binlog文件的内容
这里做了个测试，感觉linux展示的效果比windows的要好，不知道原因，希望有大佬不吝赐教
#linux环境
mysql> mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.45-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Query | 1 | 211 | BEGIN |
| mysql-bin.000001 | 211 | Query | 1 | 351 | use `usercenter`; DELETE FROM `usercenter`.`sms_vercode` WHERE `id` = 36 |
| mysql-bin.000001 | 351 | Xid | 1 | 382 | COMMIT /* xid=48 */ |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------------------------+
#windows环境
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.23-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 1 | 291 | BEGIN |
| mysql-bin.000001 | 291 | Table_map | 1 | 358 | table_id: 109 (test.student) |
| mysql-bin.000001 | 358 | Update_rows | 1 | 532 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000001 | 532 | Xid | 1 | 563 | COMMIT /* xid=29 */ |
| mysql-bin.000001 | 563 | Anonymous_Gtid | 1 | 628 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 628 | Query | 1 | 700 | BEGIN |
| mysql-bin.000001 | 700 | Table_map | 1 | 767 | table_id: 109 (test.student) |
| mysql-bin.000001 | 767 | Update_rows | 1 | 941 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000001 | 941 | Xid | 1 | 972 | COMMIT /* xid=45 */ |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
12 rows in set (0.01 sec)
3.3 用mysqlbinlog工具查看
直接使用
#linux环境
/usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200904 15:59:30 server id 1 end_log_pos 120 CRC32 0x13ff3b26 Start: binlog v 4, server v 5.6.45-log created 200904 15:59:30 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
4vNRXw8BAAAAdAAAAHgAAAABAAQANS42LjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADi81FfEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAASY7
/xM=
'/*!*/;
# at 120
#200904 16:24:21 server id 1 end_log_pos 211 CRC32 0xd1be92c3 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1599207861/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 211
#200904 16:24:21 server id 1 end_log_pos 351 CRC32 0xf46910bb Query thread_id=4 exec_time=0 error_code=0
use `usercenter`/*!*/;
SET TIMESTAMP=1599207861/*!*/;
DELETE FROM `usercenter`.`sms_vercode` WHERE `id` = 36
/*!*/;
# at 351
#200904 16:24:21 server id 1 end_log_pos 382 CRC32 0x238a94fc Xid = 48
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
注：上面的BINLOG的部分看不懂，因为是Base64编码后的，所以中间加上--base64-output=decode-rows -v命令就能展示出来sql了
（base64-output，可以控制输出语句输出base64编码的BINLOG语句;decode-rows：选项将把基于行的事件解码成一个SQL语句）
-r, --result-file=name 将输入的文本格式的文件转储到指定的文件。
windows环境下执行这个命令报错
C:\Program Files\mysql\bin> mysqlbinlog D:\data\mysql\mysql-bin.000001;
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'
原因是mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令。
解决方法1：my.cnf(my.ini)中将default-character-set=utf8 修改为 character-set-server = utf8 修改完后要重启mysql
解决方法2：用mysqlbinlog --no-defaults D:\data\mysql\mysql-bin.000001打开
解决方法3：原来是我的my.ini文件中有两个设置默认字符集的，删掉一个即可
[client]
port=3306
#干掉这个或者下面那个
default-character-set=utf8
[mysqld]
port=3306
character_set_server=utf8
最后执行后的结果跟linux下的差不多，就不粘结果了
基于开始/结束时间查看
/usr/bin/mysqlbinlog --start-datetime="2020-09-04 16:00:00" --stop-datetime="2020-09-04 18:00:00" /var/lib/mysql/mysql-bin.000001
3.4 解析binlog格式
BEGIN
/*!*/;
# at 211
#200904 16:24:21 server id 1 end_log_pos 351 CRC32 0xf46910bb Query thread_id=4 exec_time=0 error_code=0
use `usercenter`/*!*/;
SET TIMESTAMP=1599207861/*!*/;
DELETE FROM `usercenter`.`sms_vercode` WHERE `id` = 36
/*!*/;
# at 351
#200904 16:24:21 server id 1 end_log_pos 382 CRC32 0x238a94fc Xid = 48
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
- at 351：说明"事务"的起点，是以第211字节开始的
- end_log_pos 382：说明以第282字节结束
- 200904 16:24:21：事务发生的时间
- exec_time=0：事务执行的时间
- error_code=0：错误码
- server id 1：服务器的标识id
最后贴一个将binlog导出到用户目录下的语句，查询当天，且BINLOG内容进行base64解码
mysqlbinlog --base64-output=decode-rows -v --start-datetime="2021-12-14 00:00:00" --stop-datetime="2021-12-15 00:00:00" /usr/local/mysql/data/binlog.000029 -r /Users/xxx/temp/binlog.sql
Comments | NOTHING