MySQL 日志管理

4.6）日志管理

MySQL 支持丰富的日志类型，如下：

事务日志：transaction log

事务日志的写入类型为 "追加"，因此其操作为 "顺序IO"；

通常也被称为：预写式日志 write ahead logging

事务日志文件： ib_logfile0， ib_logfile1

错误日志：error log

通用日志：general log

慢查询日志：slow query log

二进制日志：binary log

中继日志：reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

4.6.1）事务日志

事务日志：transaction log

redo log：实现 WAL（ Write Ahead Log 写前日志 )，数据更新前先记录 redo log。

undo log：保存与执行的操作相反的操作，用于实现 rollback 撤销操作。

事务型存储引擎自行管理和使用，建议和数据文件分开存放

参考：

什么是 WAL - 知乎

MySQL 日志：undo log、redo log、binlog 有什么用？ | 小林coding

WAL（Write Ahead Log）预写日志，是数据库系统中常见的一种手段，用于保证 数据操作的原子性和持久性。在计算机科学中，「预写式日志」（ Write-ahead logging，缩写 WAL ）是关系数据库系统中用于提供原子性和持久性（ACID 属性中的两个）的一系列技术。在使用 WAL 的系统中，所有的修改在提交之前都要先写入 log 文件中。

redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的 持久性，主要用于掉电等故障恢复；（ 记录即将执行的操作 ）

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的 原子性，主要用于 ROLLBACK 事务回滚和 MVCC。（ 记录即将执行操作的相反操作，用于实现事务回滚 ）

Innodb 事务日志相关配置

// 列出与 Innodb 事务日志相关配置MariaDB [hellodb]> show variables like '%innodb_log%';innodb_log_file_size 50331648      // 每个事务日志文件的大小 ( 建议生产环境把该文件大小配置的更大一些 )innodb_log_files_in_group 2        // 事务日志文件的个数innodb_log_group_home_dir ./       // 事务日志文件的路径 ( 默认 MySQL 安装路径: /usr/lib/mysql )

事务日志性能优化

生产案例：mysql服务器io飚满百分之百的案例分析 - lmcc-老马吃草的博客

默认 MySQL 的刷盘策略 是 1，最安全的，但是安全的同时，自然也就会带来一定的性能压力。

在写压力巨大的情况下，根据具体的业务场景，牺牲安全性的将其调为 0 或 2。

1：此为默认值，日志缓冲区将写入日志文件,，并在每次事务后执行刷新到磁盘。这是完全遵守 ACID 特性。（ 每完成一次事务就会写入一次磁盘，比较占用磁盘 IO ）

因此假如 1 秒完成了 100 个事务。就会有 100 次写硬盘的操作。

0：提交时没有写磁盘的操作；而是 每秒执行一次 将 MySQL 日志缓冲区 的提交事务写入刷新到磁盘。这样可提供更好的性能，但 MySQL 应用或服务器崩溃可能丢失最后一秒的事务。

（ 事务完成时没有写磁盘的操作，而是每秒写入一次磁盘 ）

因此假如 1 秒完成 100 个事务，该值只会写入 1 次磁盘操作。

2：每次提交后都会写入 OS 操作系统的缓冲区，也是每秒才会进行一次刷新到磁盘文件中。性能比 0 略差一些，只有操作系统或停电可能导致最后一秒的事务丢失。

效率和 0 几乎一样差别不大。因为都是每 1 秒将缓冲区的数据写入刷新到磁盘。

0 与 2 的区别：

0：先将事务日志缓存在 MySQL 的 Log_buffer 缓存区 中，在一秒后将其写入磁盘。

2：先将事务日志缓存在 OS 操作系统的缓存区 中，在一秒后将其写入磁盘。

因此：

2 的安全性胜过 0（ 因为 2 将缓存存放在 OS 操作系统的缓存区中 ）

而 0 的性能胜过 2（ 而 0 将缓存存放在 MySQL 的缓冲区中 ）

执行效率差别不大。

innodb_flush_log_at_trx_commit=0|1|2// 默认 MySQL 的刷盘策略: 1MariaDB [(none)]> select @@innodb_flush_log_at_trx_commit;+----------------------------------+| @@innodb_flush_log_at_trx_commit |+----------------------------------+|                                1 |+----------------------------------+1 row in set (0.00 sec)

实例：事务日志性能优化

注：生产环境需结合需要进行优化。

这个案例展示了如何通过调整 InnoDB 的日志刷写设置来 巧妙地影响事务提交的速度。通过将 innodb_flush_log_at_trx_commit 设置为 2，我们可以显著提升事务提交的速度，但与此同时，这也意味着增加了数据丢失的风险，特别是在数据库遭遇崩溃的情况下。在部署和优化数据库时，务必根据实际需求仔细权衡性能与数据安全性的关系，以确保最佳的实践效果。

// 清空名为 "testlog" 的表MariaDB [hellodb]> truncate table testlog;Query OK, 0 rows affected (0.00 sec)// 查询 "testlog" 表中的记录数MariaDB [hellodb]> select count(*) from testlog;+----------+| count(*) |+----------+|        0 |+----------+1 row in set (0.00 sec)// 查询当前 InnoDB 存储引擎的日志刷写设置MariaDB [hellodb]> select @@innodb_flush_log_at_trx_commit;+----------------------------------+| @@innodb_flush_log_at_trx_commit |+----------------------------------+|                                1 |+----------------------------------+1 row in set (0.00 sec)// 将 InnoDB 的日志刷写设置为 2// 这意味着日志会在每个事务提交时写入到日志缓冲, 但不会立即刷新到磁盘// 这可以提高事务的提交速度MariaDB [hellodb]> set global innodb_flush_log_at_trx_commit=2;Query OK, 0 rows affected (0.00 sec)// 验证MariaDB [hellodb]> select @@innodb_flush_log_at_trx_commit;+----------------------------------+| @@innodb_flush_log_at_trx_commit |+----------------------------------+|                                2 |+----------------------------------+1 row in set (0.00 sec)// 调用存储过程 "sp_testlog"// 执行速度得到了大幅度提升// 这是因为之前设置的事务日志刷写策略提高了事务提交的速度MariaDB [hellodb]> call sp_testlog;Query OK, 1 row affected (0.65 sec)        // 速度大幅度提升// 再次查询 "testlog" 表中的记录数MariaDB [hellodb]> select count(*) from testlog;+----------+| count(*) |+----------+|   100000 |+----------+1 row in set (0.01 sec)

高并发业务行业最佳实践，是使用第三种折衷配置（=2）：

1. 配置为 2 和配置为 0, 性能差异并不大, 因为将数据从 Log Buffer 拷贝到 OS cache, 虽然跨越用户态与内核态, 但毕竟只是内存的数据拷贝, 速度很快.

2. 配置为 2 和配置为 0, 安全性差异巨大, 操作系统崩溃的概率相比 MySQL 应用程序崩溃的概率, 小很多, 设置为2, 只要操作系统不奔溃, 也绝对不会丢数据.

说明：

设置为 1，同时 sync_binlog = 1 表示最高级别的容错

innodb_use_global_flush_log_at_trx_commit=0 时，将不能用 SET 语句重置此变量（ MariaDB 10.2.6 后废弃）

4.6.2）错误日志

错误日志

mysqld 启动和关闭过程中输出的事件信息

mysqld 运行中产生的错误信息

event scheduler 运行一个 event 时产生的日志信息

在主从复制架构中的从服务器上启动从服务器线程时产生的信息

// 查看错误日志tail /var/log/mariadb/mariadb.log

错误文件路径

SHOW GLOBAL VARIABLES LIKE 'log_error';

配置 错误日志 路径

vim /etc/my.cnflog-error=/var/log/mariadb/mariadb.log

查看 错误日志

cat /var/log/mariadb/mariadb.log | grep -i ERR

记录哪些警告信息至错误日志文件

// CentOS7 mariadb 5.5 默认值为 1// CentOS8 mariadb 10.3 默认值为 2log_warnings=0|1|2|3...              // MySQL5.7 之前log_error_verbosity=0|1|2|3...       // MySQL8.0

范例：

MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE 'log_warnings';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_warnings  | 2     |+---------------+-------+1 row in set (0.001 sec)

范例：MySQL8.0 变量变化

mysql> SHOW GLOBAL VARIABLES LIKE 'log_error_verbosity';+---------------------+-------+| Variable_name       | Value |+---------------------+-------+| log_error_verbosity | 2     |+---------------------+-------+1 row in set (0.00 sec)

4.6.3）通用日志

通用日志：记录用户对数据库的通用操作，包括：错误的 SQL 语句

通用日志可以保存在：file（默认值）或 table（ mysql.general_log 表 ）

通用日志相关设置

general_log=ON|OFFgeneral_log_file=HOSTNAME.loglog_output=TABLE|FILE|NONE

通用日志 可以用来观察数据库发生的一些事件。

范例：启用通用日志记录，并记录日志在文件中

// 默认没有启用通用日志MariaDB [(none)]> select @@general_log;+---------------+| @@general_log |+---------------+|             0 |+---------------+1 row in set (0.00 sec)// 默认没有启用通用日志 ( 等价于上一条命令 )MariaDB [(none)]> show variables like 'general_log';+---------------+-------+| Variable_name | Value |+---------------+-------+| general_log   | OFF   |+---------------+-------+1 row in set (0.00 sec)// 启用通用日志功能MariaDB [(none)]> set global general_log=1;Query OK, 0 rows affected (0.00 sec)// 验证MariaDB [(none)]> select @@general_log;+---------------+| @@general_log |+---------------+|             1 |+---------------+1 row in set (0.00 sec)// 默认通用日志存放在 FILE 文件中 ( 由 log_output 变量定义 )MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'log_output';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output    | FILE  |+---------------+-------+1 row in set (0.00 sec)// 通用日志存放的文件路径 ( 开启通用日志功能后,自动生成如下文件 )MariaDB [(none)]> select @@general_log_file;+--------------------+| @@general_log_file |+--------------------+| blog.log           |+--------------------+1 row in set (0.00 sec)// 会记录用户对数据库的通用操作// 包括: 错误的 SQL 语句也会记录 ( 追溯用户执行的操作 )// "类似于: .bash_history 文件"tail -f /var/lib/mysql/blog.log

范例：通用日志记录到数据表中

注意：我们如下案例中使用 set 修改变量值都仅是临时修改。

如果需要永久修改 需要写到 MySQL 配置文件 中。

// 永久修改vim /etc/my.cnflog_output=TABLE// 重启 Mariadb 服务systemctl restart mariadb.service// "修改通用日志"// 将记录通用日志至 mysql.general_log 表中MariaDB [mysql]> set global log_output="table";MariaDB [mysql]> SHOW GLOBAL VARIABLES LIKE 'log_output';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_output    | TABLE |+---------------+-------+1 row in set (0.00 sec)// 进入 mysql 数据库MariaDB [(none)]> use mysql;MariaDB [mysql]> show tables;+---------------------------+| Tables_in_mysql           |+---------------------------+| columns_priv              || db                        || event                     || func                      || general_log               |        // 会记录在该 general_log 表中| help_category             || help_keyword              || help_relation             || help_topic                || host                      || ndb_binlog_index          || plugin                    || proc                      || procs_priv                || proxies_priv              || servers                   || slow_log                  || tables_priv               || time_zone                 || time_zone_leap_second     || time_zone_name            || time_zone_transition      || time_zone_transition_type || user                      |+---------------------------+24 rows in set (0.00 sec)// 查看数据表内容MariaDB [mysql]> select * from general_log;Empty set (0.00 sec)// 数据表内容实则存放在该文件中[root@centos8 ~] cat /var/lib/mysql/mysql/general_log.CSV

范例：查找执行次数最多的前三条语句

MariaDB [mysql]> select argument,count(argument) num from mysql.general_log group by argument order by num desc limit 3;+---------------------------+-----------------+| argument                  |             num |+---------------------------+-----------------+| select * from teachers    |               6 || select * from general_log |               4 || select * from students    |               3 |+---------------------------+-----------------+3 rows in set (0.002 sec)

范例：对访问的语句进行排序

[root@centos8 ~] mysql -e 'select argument from mysql.general_log' | awk '{sql[$0]++}END{for(i in sql){print sql[i],i}}' | sort -nr[root@centos8 ~] mysql -e 'select argument from mysql.general_log' | sort | uniq -c | sort -nr// 对日志文件进行排序[root@centos8 ~] cat /var/lib/mysql/blog.log | sort | uniq -c | sort -nr[root@centos8 ~] cat /var/lib/mysql/blog.log | awk '{sql[$0]++}END{for(i in sql){print sql[i],i}}' | sort -nr

4.6.4）慢查询日志

慢查询日志：记录执行查询时长超出指定时长的操作

慢查询日志 记录了执行时间较长的 SQL 语句，我们可以通过分析慢查询日志，找出执行时间较长的 SQL 语句和查询的问题所在，从而优化数据库的性能。

慢查询相关变量

slow_query_log=ON|OFF    // "开启或关闭慢查询" 支持全局和会话, 只有全局设置才会生成慢查询文件long_query_time=N        // "慢查询的阀值" 单位秒, 默认为 10sslow_query_log_file=HOSTNAME-slow.log    // "慢查询日志文件"

// "慢查询相关行为"log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk        // 上述查询类型且查询时长超过 long_query_time, 则记录日志// 记录没有使用索引的查询语句,即使你未到达指定阈值 ( "建议开启" )log_queries_not_using_indexes=ON    // 不使用索引或使用全索引扫描, 不论是否达到慢查询阀值的语句是否记录日志, 默认 OFF, 即不记录

--- 了解 --log_slow_rate_limit = 1             // 多少次查询才记录, mariadb 特有log_slow_verbosity= Query_plan,explain    // 记录内容log_slow_queries = OFF                    // 同 slow_query_log, MariaDB 10.0/MySQL 5.6.1 版后已删除

范例：（ MySQL 小技巧：慢查询日志 ）

慢查询日志：用于记录 SQL 执行时间超过某个临界值的 SQL 日志文件，可用于快速定位慢查询，为我们的 SQL优化 做参考。

// 我们可以这样进行永久配置vim /etc/my.cnf.d/mysql-server.cnfslow_query_log=ON        // "开启慢查询功能"long_query_time=1        // "将慢查询的阀值设置为一秒"log_queries_not_using_indexes=ON    // 记录未使用索引的行为,即使你未到达指定阈值 ( "建议开启" )// 重启服务systemctl restart mysqld// 默认没有开启慢查询日志功能MariaDB [mysql]> select @@slow_query_log;+------------------+| @@slow_query_log |+------------------+|                0 |+------------------+1 row in set (0.00 sec)// 开启慢查询日志功能MariaDB [mysql]> set GLOBAL slow_query_log=1;Query OK, 0 rows affected (0.00 sec)// 查看慢查询日志默认阀值 ( 生成环境建议设置更小一些 )// 当然,结合你实际环境进行配置// "运行时间超过如下阀值的 SQL 语句将会被记录"MariaDB [mysql]> select @@long_query_time;+-------------------+| @@long_query_time |+-------------------+|         10.000000 |+-------------------+1 row in set (0.00 sec)// 将超时阀值设置为 1 秒MariaDB [mysql]> set long_query_time=1;Query OK, 0 rows affected (0.00 sec)// 慢查询日志文件MariaDB [mysql]> select @@slow_query_log_file;+-----------------------+| @@slow_query_log_file |+-----------------------+| blog-slow.log         |+-----------------------+1 row in set (0.00 sec)// 测试 ( 超过阈值 )MariaDB [(none)]> select sleep(1);+----------+| sleep(1) |+----------+|        0 |+----------+1 row in set (1.00 sec)// 经过如上配置// 目前该慢查询日志会记录运行时间超过规定阀值的记录tail -f /var/lib/mysql/centos8-slow.log

// 测试 ( 记录没有使用索引的查询语句,即使你未到达指定阈值 )mysql> select * from students where age=20;+-------+--------------+-----+--------+---------+-----------+| StuID | Name         | Age | Gender | ClassID | TeacherID |+-------+--------------+-----+--------+---------+-----------+|     9 | Ren Yingying |  20 | F      |       6 |      NULL ||    22 | Xiao Qiao    |  20 | F      |       1 |      NULL |+-------+--------------+-----+--------+---------+-----------+2 rows in set (0.00 sec)// 经过如上配置// 目前该慢查询日志会记录未使用索引的查询语句tail -f /var/lib/mysql/centos8-slow.log

范例：慢查询分析工具 mysqldumpslow

[01:05:24 root@blog ~]# mysqldumpslow --helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ][01:05:24 root@blog ~]# mysqldumpslow -s c -t 2 /var/lib/mysql/blog-slow.logReading mysql slow query log from /var/lib/mysql/blog-slow.logCount: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows_sent=0.0 (0), Rows_examined=0.0 (0), 0users@0hostsDied at /usr/bin/mysqldumpslow line 178, <> chunk 1.

4.6.5）二进制日志（ 备份功能 ）

二进制日志 主要作用：

为了确保数据库能够最大程度地更新，需要恢复并启用二进制日志。

这是因为二进制日志记录了备份后所进行的所有更新，并且还用于在主复制服务器上记录所有将发送给从服务器的语句。

记录着 导致数据改变 或 潜在导致数据改变 的 SQL 语句（ 例如：增删改 ）

类似于家中的摄像头可以设置仅录制并储存有异动的画面。

这样可以有效地节约存储空间。

记录已提交的日志

不依赖于存储引擎类型

功能：通过 "重放" 日志文件中的事件来生成数据副本

（ 基于 Mariadb 演示 ）

案例：开启二进制日志功能（ 重要 ）

( 并将 二进制日志文件 与 数据库文件 分开存放 )

// 创建二进制日志文件的存放目录mkdir /mysql/data/logbin -pvchown -R mysql:mysql /mysql/data/logbin/// 定义二进制日志文件路径 ( 服务器选项: log-bin )vim /etc/my.cnfvim /etc/my.cnf.d/mysql-server.cnf      # MySQL 8.0[mysqld]log_bin                                 # 开启二进制日志功能 ( 必须 )log-bin=/mysql/data/logbin/mysql-bin    # 定义二进制日志文件路径// 重启 mariadb 服务setenforce 0         # 关闭 SELinux ( 重要 )systemctl restart mariadb.service# 如重启报错 ( 可以尝试查看日志 tail -f /var/log/mysql/mysqld.log )# 将报错信息发送至 ChatGPT 为你提供解决思路// 验证ll /mysql/data/logbincat /mysql/data/logbin/mysql-bin.index ( 该文件记录当前已有的二进制日志文件列表 )// 验证select @@sql_log_bin;select @@log_bin;

二进制日志文件的构成

// 有两类文件

1. 日志文件: mysql|mariadb-bin.文件名后缀, 二进制格式, 如: on.000001, mariadb-bin.000002

2. 索引文件: mysql|mariadb-bin.index, 文本格式, 记录当前已有的二进制日志文件列表

格式配置

MariaDB [hellodb]> show variables like 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | MIXED |+---------------+-------+1 row in set (0.001 sec)// MySQL 8.0 默认使用 ROW 方式mysql> show variables like 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW   |+---------------+-------+1 row in set (0.07 sec)----// 修改二进制格式类型vim /etc/my.cnf.d/mysql-server.cnf[mysqld]binlog_format=STATEMENT// 重启 MySQL 服务systemctl restart mysqld// 验证类型mysql> select @@binlog_format;+-----------------+| @@binlog_format |+-----------------+| STATEMENT       |+-----------------+1 row in set (0.00 sec)

二进制日志相关的 服务器变量：

MySQL :: MySQL 8.0 Reference Manual :: 7.1.4 Server Option, System Variable, and Status Variable Reference

服务器选项：全局级别，可以写入配置文件永久修改。

// 服务器选项 ( 指定二进制日志文件存放路径 )vim /etc/my.cnf[mysqld]log_bin              # 开启二进制日志功能log-bin=/mysql/data/logbin/mysql-bin    # 定义二进制日志文件路径

系统变量：会话级别，仅能临时在 MySQL 系统中修改。

// 系统变量 ( ON|OFF 开启或关闭 )set sql_log_bin=ON;--- 验证 ---select @@sql_log_bin;select @@log_bin;

二进制日志相关的 服务器变量

sql_log_bin=ON|OFF:            // "是否记录二进制日志" 默认 ON, 支持动态修改 ( 系统变量, 并非服务器选项 )

log_bin=/PATH/BIN_LOG_FILE:    // "指定文件位置"; 默认 OFF, 表示不启用二进制日志功能, "需上述两项都开启才可以"

----

// "二进制日志记录的格式" 老版本 mariadb5.5 默认 STATEMENT

binlog_format=STATEMENT|ROW|MIXED:

  1. STATEMENT: 语句型记录 ( 数据不全,在不同的时间执行结果不同 )

  2. ROW: 行型记录 ( 数据完全,在不同的时间执行结果相同 "建议使用该方式,更安全" )

  3. MIXED: 混合型记录 ( 结合两者特性 不建议 )

----

max_binlog_size=1073741824:    // "单个二进制日志文件的最大体积" # 到达最大值会自动滚动, 默认为 1G 说明: 文件达到上限时的大小未必为指定的精确值

binlog_cache_size=4m           // 此变量确定在每次事务中保存二进制日志更改记录的缓存的大小(每次连接)

max_binlog_cache_size=512m     // 限制用于缓存多事务查询的字节大小

sync_binlog=1|0:               // 设定是否启动二进制日志即时同步磁盘功能, 默认 0, 由操作系统负责同步日志到磁盘 ( 建议: 1 安全但效率更低 每次操作都会直接写磁盘日志 )

expire_logs_days=N:            // "二进制日志可以自动删除的天数", 默认为 0, 即不自动删除 ( 建议: 30 , 保留最近一个月的二进制日志 )

二进制日志记录 三种格式

参考：MySQL中的日志“binlog”的三种格式_binlog_format-CSDN博客

基于 "语句" 记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少。

基于 "行" 记录：row，记录数据，日志量较大，更加安全，建议使用的格式，MySQL8.0 默认格式。

混合 模式：mixed，让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4 及版本以上）

二进制日志相关配置

查看 mariadb 自行管理使用中的二进制日志文件列表，及大小

SHOW {BINARY | MASTER} LOGS

// 举例 ( 二进制日志文件列表 )

show master logs;

查看正在使用中的二进制日志文件

在两种情况下，会生成一个新的二进制日志文件

1）当前使用的二进制日志文件达到了 max_binlog_size 变量所定义的最大体积

2）重启数据库服务

SHOW MASTER STATUS

// 举例 ( 查看目前正在使用的二进制日志文件 )

SHOW MASTER STATUS;

// 插入一条数据

use hellpdb;

insert teachers values(10,'a',30,'M');

// 验证

// 从 156 - 458 之间记录的应该就是我们上面这条插入数据的操作

SHOW MASTER STATUS;

在线查看二进制文件中的指定内容

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

// 举例 ( 查看二进制文件中的指定内容 )

SHOW BINLOG EVENTS in 'binlog.000005';

范例

show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

范例

MariaDB [hellodb]> SHOW BINLOG EVENTS;

MariaDB [hellodb]> SHOW BINLOG EVENTS in 'mysql-bin.000002' from 614 limit 2,3\G

MariaDB [hellodb]> SHOW BINLOG EVENTS in 'mysql-bin.000002' from 614 limit 2,3\G

mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志

无需登录数据库 即可查看二进制日志

mysqlbinlog [OPTIONS] log_file…

    --start-position=        # 指定开始位置

    --stop-position=         #

    --start-datetime=        # 时间格式: YYYY-MM-DD hh:mm:ss

    --stop-datetime=

    --base64-output[=name]

    -v -vvv

范例：

// 指定起始位置 结束位置

// 注意: 填写正确的二进制文件名称和路径

mysqlbinlog --start-position=156 --stop-position=458 /var/lib/mysql/binlog.000005 -v

// 指定时间段

mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv

二进制日志事件的格式

# at 328

# 151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1    

exec_time=0 error_code=0

use `mydb`/*!*/;

SET TIMESTAMP=1446712300/*!*/;

CREATE TABLE tb1 (id int, name char(30))

/*!*/;  

事件发生的日期和时间：151105 16:31:40

事件发生的服务器标识：server id 1

事件的结束位置：end_log_pos 431

事件的类型: Query

事件发生时所在服务器执行此事件的线程的ID: thread_id=1

语句的时间戳与将其写入二进制文件中的时间差: exec_time=0

错误代码: error_code=0

事件内容:

GTID：Global Transaction ID, mysql5.6 以 mariadb10 以上版本专属属性: GTID

清除指定二进制日志

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

// 示例

PURGE BINARY LOGS TO 'binlog.000003';

show master logs;

范例：

PURGE BINARY LOGS TO 'mariadb-bin.000003';    // 删除 mariadb-bin.000003 之前的日志

PURGE BINARY LOGS BEFORE '2017-01-23';

PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';

删除所有二进制日志（ index 索引文件重新记数 ）

RESET MASTER [TO #];    // 删除所有二进制日志文件, 并重新生成日志文件, 文件名从 # 开始记数, 默认从 1

 开始, 一般是 master 主机第一次启动时执行, MariaDB 10.1.6 开始支持 TO

// 举例

reset master;

show master logs;

show master status;

刷新日志文件：

flush logs;    # 触发二进制日志文件的重新生成,也就是生成一个新的二进制文件

范例：切换二进制日志

// 基于 mysqladmin 刷新日志文件

[root@centos8 ~] mysqladmin -uroot flush-binary-log

[root@centos8 ~] mysqladmin -uroot flush-logs

[root@centos8 ~] mysql

MariaDB [hellodb]> flush logs;

举例：语句型记录与行型记录的区别( MySQL 小技巧 )

更新学生表数据

查看二进制日志内容（ 语句型记录 ）

二进制日志内容不详细，但占用空间小

mysql hellodb

mysql> select @@binlog_format;

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

| @@binlog_format |

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

| STATEMENT       |

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

1 row in set (0.00 sec)

mysql> select * from students;

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

| StuID | Name          | Age | Gender | ClassID | TeacherID |

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

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |

|     6 | Shi Qing      |  46 | M      |       5 |      NULL |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |

|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |

|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |

|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |

|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |

|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |

|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |

|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |

|    15 | Duan Yu       |  19 | M      |       4 |      NULL |

|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |

|    17 | Lin Chong     |  25 | M      |       4 |      NULL |

|    18 | Hua Rong      |  23 | M      |       7 |      NULL |

|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |

|    20 | Diao Chan     |  19 | F      |       7 |      NULL |

|    21 | Huang Yueying |  22 | F      |       6 |      NULL |

|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |

|    23 | Ma Chao       |  23 | M      |       4 |      NULL |

|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |

|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |

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

25 rows in set (0.00 sec)

mysql> update students set teacherid=1 where stuid >=6;

Query OK, 20 rows affected (0.01 sec)

Rows matched: 20  Changed: 20  Warnings: 0

mysql> show master logs;

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

| Log_name      | File_size | Encrypted |

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

| binlog.000001 |       179 | No        |

| binlog.000002 |      7754 | No        |

| binlog.000003 |       179 | No        |

| binlog.000004 |       200 | No        |

| binlog.000005 |  11890535 | No        |

| binlog.000006 |       502 | No        |        # 占用 502 KB

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

6 rows in set (0.00 sec)

更新学生表数据库

查看二进制日志内容（ 行型记录 ）

二进制日志内容详细，但占用空间大

mysql hellodb

mysql> select @@binlog_format;

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

| @@binlog_format |

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

| ROW             |

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

1 row in set (0.00 sec)

mysql> update students set teacherid=2 where stuid >=6;

Query OK, 20 rows affected (0.01 sec)

Rows matched: 20  Changed: 20  Warnings: 0

mysql> show master logs;

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

| Log_name      | File_size | Encrypted |

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

| binlog.000001 |       179 | No        |

| binlog.000002 |      7754 | No        |

| binlog.000003 |       179 | No        |

| binlog.000004 |       200 | No        |

| binlog.000005 |  11890535 | No        |

| binlog.000006 |       525 | No        |

| binlog.000007 |      1476 | No        |        # 占用了 1476 KB

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

7 rows in set (0.00 sec)

mysqlbinlog /var/lib/mysql/binlog.000006 -v        # 语句型记录二进制日志文件

mysqlbinlog /var/lib/mysql/binlog.000007 -v        # 行型记录二进制日志文件

举例：

可以基于该小案例还原数据（ 存在备份效果 ）

MySQL 小技巧：MySQL 通过 binlog 二进制日志恢复数据

参考：MySQL 通过binlog日志恢复数据_mysql 从库基于主库binlog恢复-CSDN博客

参考：https://www.cnblogs.com/michael9/p/11923483.html

参考：通过binlog文件恢复mysql数据的三种方式 - 掘金

刷新二进制日志文件

意味着将当前的二进制日志文件切换为新的日志文件

flush logs;

show master status;

在 teachers 表中添加一条记录

在名为 teachers 的数据库表中添加一条新的记录。

这一操作被记录在二进制日志中。

use hellodb;

insert teachers values(100,'d',50,'F');

破坏该数据表

由于误操作或其他原因，导致 teachers 表的数据被损坏或丢失

delete from teachers where tid>=100;

基于 mysqlbinlog 查看二进制日志记录（ 可以看到上述操作过程 ）

通过使用这个工具，可以查看之前所有的数据库操作

包括添加新记录的操作。

mysqlbinlog /var/lib/mysql/binlog.000002 -v

当我们发现误操作了。（ 如何解决 ）

在查看二进制日志后，发现之前的一个操作导致了 “teachers” 表的损坏。

我们可以 基于 mysqlbinlog 截取误操作之前的操作记录（ 并生成一个 sql 文件）

由于已经知道误操作导致的数据问题

可以截取误操作发生之前的所有操作记录。

这可以通过 mysqlbinlog 工具完成，并生成一个 SQL 文件。

这个 SQL 文件包含了在误操作之前对 teachers 表所做的所有操作。

mysqlbinlog /var/lib/mysql/binlog.000002 --stop-position=615 > /root/1.sql

执行该 sql 文件（ 恢复数据 ）

最后，通过执行这个 SQL 文件

可以将 teachers 表恢复到误操作之前的状态。

这样，数据丢失的问题就得到了解决。

mysql hellodb < /root/1.sql

总结：这个案例描述了一个常见的数据库恢复场景。通过使用二进制日志和

mysqlbinlog工具，可以追踪和恢复数据库中的数据。