观察数据库变化——mysqlbinlog使用方法、常见问题和从binlog二进制超大文本中筛选出自己想要的信息

原创已于 2024-01-03 17:30:21 修改 · 1.5k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#big data #大数据 #mysql #linux

于 2024-01-03 14:42:32 首次发布

数据同步同时被 2 个专栏收录

7 篇文章

订阅专栏

SQL

5 篇文章

订阅专栏

本文介绍了如何在MySQL中查看和处理binlog日志，包括如何开启binlog、查看日志文件，以及遇到`default_character-set=utf8`错误时的解决方法。还提供了使用mysqlbinlog工具的各种参数和示例，如过滤DML操作和去重等技巧。

1./*查看binlog是否开启*/
SHOW VARIABLES LIKE 'log_bin'

2.开启binlog后的一些指令

    /*查看所有日志的记录文件*/
    SHOW BINARY LOGS;

    // 查看当前日志的记录文件
    SHOW MASTER STATUS;

    // 查看日志文件所在位置
    SHOW VARIABLES LIKE '%log_bin%';

    /*查看所有日志的记录文件*/
    SHOW BINARY LOGS;

    // 查看当前日志的记录文件
    SHOW MASTER STATUS;

    // 查看日志文件所在位置
    SHOW VARIABLES LIKE '%log_bin%';

常见问题

1.执行查看命令时报错：mysqlbinlog: [ERROR] unknown variable 'default_character-set=utf8'

    [root@172-xx-xxx-x data]# mysqlbinlog mysql-bin.000002
    mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'

        原因是mysql自带的 mysqlbinlog工具无法识别binlog中的配置中的default-character-set=utf8这    个指令
        解决办法一： 修改mysql配置文件（my.cnf）
        将配置 default-character-set=utf8mb4 修改为 character-set-server = utf8mb4
        注意：此操作需要重启MySQL服务线上不建议操作

解决办法二：增加参数--no-defaults

        ../bin/mysqlbinlog --no-defaults mysql-bin.000002

        文件虽然打开了但是乱码了
        问题解决办法：增加参数 --no-defaults --base64-output=decode-rows -v
        ../bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

小结：参数--no-defaults 表示不读取任何选项文件，可以解决【mysqlbinlog: [ERROR] unknown variable 'default_character-set=utf8'】问题，但读取的数据依旧乱码。

参数--no-defaults --base64-output=decode-rows -v 则可以读取到有效的数据

gpt的解决方法：

2.-bash: mysqlbinlog: 未找到命令

  [root@172-xx-xxx-x data]# mysqlbinlog mysql-bin.000002
    -bash: mysqlbinlog: 未找到命令

解决步骤一：
在mysql/data目录下建立软链接

ln -s /usr/local/mysql/bin/mysqlbinlog mysqlbinlog

解决步骤二：添加MySQL环境变量（添加完毕记得 source /etc/profile ）

vim /etc/profile.d/my_env.sh

添加以下内容

#mysqlbinlog
export PATH=$PATH:/usr/local/mysql/bin    （自己的mysql路径）

不确定有没有安装mysqlbinlog 命令的可以yum一下

如果你使用的是 CentOS 或者其他基于 Red Hat 的 Linux 发行版，可以通过以下命令安装 mysqlbinlog 命令：

$ sudo yum install mysql

gpt提供的解决办法：

总结：mysql查看binlog日志

方法一：登录到mysql查看binlog
获取binlog文件列表：
mysql> show binary logs;

查看当前使用的binlog文件：
mysql> show master status;

只查看第一个binlog文件的内容：
mysql> show binlog events;

查看指定binlog文件的内容：
mysql> show binlog events in 'mysql-bin.000002';

方法二：使用mysqlbinlog工具查看
查看mysql-bin.000002：
mysqlbinlog mysql-bin.000002

基于开始/结束时间：
mysqlbinlog --start-datetime="2017-09-17 07:21:09" --stop-datetime="2017-09-19 07:59:50" mysql-bin.000002

基于pos值：
mysqlbinlog --start-position=205 --stop-position=2205 mysql-bin.000002

指定数据库：
mysqlbinlog --no-defaults -d databasename mysql-bin.000002

注：--no-defaults 不读取任何选项文件

文心一言给出的其它参数

binlog文件信息过于庞大，人无法做到完全分析数据，所以可以适当增加筛选条件，过滤掉不必要的信息

示例

1.查看多个二进制日志文件并指定 "开始时间、结束时间、指定数据库" 的命令，指定将内容写入到一个文件 outputEIS.txt 里的命令

./mysqlbinlog  --no-defaults --base64-output=decode-rows -v   --start-datetime="2023-12-12 00:00:00" --stop-datetime="2023-12-28 00:00:00"  --database=DBname mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008 mysql-bin.000009 > outputEIS.txt

2.此时生成一个文本文件，但文件中的内容过多，还需要进一步过滤出想要的数据【无法在第一步查看binlog文件时过滤，只能在查看文本文件时过滤】

3.过滤出DML操作的信息。使用tail -n结合grep命令来过滤文件中的DML操作。DML操作通常包括INSERT、UPDATE和DELETE语句。

这个命令使用tail -n +1来从文件的第一行开始显示所有内容，然后通过grep -E使用正则表达式匹配包含INSERT、UPDATE或DELETE关键字的行。

从DBname数据的binlog文件outputEIS.txt 中过滤出DML语句内容，写入到outputdml1.txt文件中

tail -n +1 outputEIS.txt | grep -E "(INSERT|UPDATE|DELETE)" > outputdml1.txt

4. 查看行数命令。此时outputdml1.txt文件中的内容还是有百万行信息，但很多重复内容，可以根据wc -l your_file.txt 查看文本有多少行信息

wc -l outputdml1.txt

5.去重。然后使用sort和uniq结合的方式来过滤掉文本中的重复内容

sort outputdml1.txt | uniq

或者使用命令

sort -u outputdml1.txt

这样将同时进行排序和去重

6.此时，终于从数十G的文本中筛选出DML操作的内容了

注意：此时如果还是没有想要的信息，可以调整上面的开始时间、结束时间和读取的binlog块的个数等参数的值