Mysql - 日志
作者:mmseoamin日期:2023-12-25

目录

Mysql日志

Mysql日志是什么,有什么用?

一、重做日志(redo log),回滚日志(undo log)的简单介绍

二、Mysql错误日志:(默认是开启的)

作用:

当然我们也可以自己配置error log的位置(配置文件路径:/etc/my.cnf)

三、Mysql的慢日志:(默认是关闭的)

作用:

可以很明显的看到,我们的Mysql慢日志是关闭的,所以当我们想要打开时,我们应该在Mysql的配置文件里进行修改:

但是为什么Mysql默认情况下没有打开慢日志呢?

四、Mysql的通用日志:(默认是关闭的)

作用:

我们如何打开Mysql的通用日志呢,我们应该与上面的慢日志一样,修改mysqld的配置文件

但是为什么Mysql默认情况下没有打开通用日志呢?

五、Mysql的二进制日志:(默认是关闭的)(很重要,有机密信息的记录)

作用:

二进制文件是如何产生的呢?

Mysql实例:

如何开启二进制日志文件呢?

什么时候会产生新的二进制日志文件呢?

我们又是如何知道我们现在正在使用哪个二进制日志文件呢:(通过show master status;命令)

我们可以通过show binary logs 查看所有二进制日志文件的大小

我们又是如何删除所有的二进制日志文件呢:(使用reset master)

如何手动清除二进制日志文件呢:(purge binary log 命令)

 参考文档:(13条消息) MySql自动清除binary logs日志_purge binary logs_hanchao5272的博客-CSDN博客

查看二进制日志:

我们可以使用mysqlbinlog命令:

参考文档:MySQL 数据库之Binlog日志使用总结 - 散尽浮华 - 博客园 (cnblogs.com)

MySQL的binlog日志 - 马丁传奇 - 博客园 (cnblogs.com)​​​​​​​

二进制日志文件的格式

二进制日志的格式:

1、row level

2、statement level

3、mixed level


Mysql日志:

Mysql日志是什么,有什么用?

MySQL日志是MySQL数据库中记录各种事件和操作的文件(应用程序把工作过程中的事情记录下来保存到文件中,保存下来的东西就是日志)。它包括多种不同类型的日志文件,如二进制日志、错误日志、慢查询日志等。这些日志文件可以用于诊断和解决问题,例如查找错误、分析数据库性能、恢复数据等。

可以帮助我们了解程序是否正常运行,用来排错,数据分析等

Mysql有很多类型的日志,如果按照组件划分的话,可以分为 服务层日志 和 存储引擎层日志

服务层日志:二进制日志、慢日志、通用日志、错误日志

存储引擎层日志:Innodb(重做日志、回滚日志)

日志文件存放需要考虑空间问题:最好是单独分区(避免空间被耗光),不要和数据目录放在一起。

一、重做日志(redo log),回滚日志(undo log)的简单介绍

重做日志(redo log)是数据库系统中一种记录数据修改操作的日志,它记录了所有的事务提交操作,以便在数据库发生故障时,能够恢复数据库的一致性。当数据库崩溃或者发生意外关机等异常情况时,通过重做日志可以恢复数据库的数据。

回滚日志(undo log)是一种记录数据修改操作的日志,它记录了事务执行中所有的变更操作,以便在事务回滚时能够撤销这些操作。当事务执行失败或者被回滚时,通过回滚日志可以将事务执行前的数据状态恢复。回滚日志也可以用于实现多版本并发控制(MVCC)等功能。

详细可以查看参考文档:(13条消息) mysql 日志回滚_MySQL中的重做日志(redo log),回滚日志(undo log),以及二进制日志(binlog)的简单总结..._昆宇的博客-CSDN博客

查看Mysql页的大小:(大概16K左右)

Mysql - 日志,第1张

二、Mysql错误日志:(默认是开启的)

作用:

MySQL错误日志记录了MySQL服务运行过程中发生的错误、警告和其他信息,包括但不限于以下内容:

  1. 启动和关闭MySQL服务时的信息;
  2. 客户端连接MySQL服务的成功或失败信息;
  3. MySQL服务运行过程中发生的错误信息;
  4. MySQL服务运行过程中发生的警告信息;
  5. MySQL服务运行过程中发生的其他一些信息。

MySQL错误日志的作用主要有以下几个方面:

  1. 提供MySQL服务运行过程中的重要信息,帮助管理员及时发现和解决问题;
  2. 收集MySQL服务运行过程中的错误和警告信息,为后续的故障排查和优化提供依据;
  3. 记录MySQL服务运行过程中的详细信息,帮助管理员了解MySQL服务的运行状况和性能情况;
  4. 通过分析MySQL错误日志,可以发现一些安全问题,如未授权访问、恶意攻击等。

Mysql错误日志路径默认会存放在/data目录下  --》 存放在数据目录下

名字是 : 主机名.err

Mysql - 日志,第2张

当然我们也可以自己配置error log的位置(配置文件路径:/etc/my.cnf)

Mysql - 日志,第3张

如上图所示,我们通过在配置文件里添加log-error命令,指定Mysql错误日志的路径和文件,接下来我们需要刷新Mysql服务

Mysql - 日志,第4张

成功配置了我们的Mysql错误日志的路径啦

Mysql - 日志,第5张

我们也可以通过SQL命令查看我们的error日志的路径

Mysql - 日志,第6张

三、Mysql的慢日志:(默认是关闭的)

作用:

MySQL慢日志用于记录执行时间超过一定阈值的SQL语句,可以帮助开发人员和DBA分析和优化慢查询,提高数据库性能。慢日志可以记录SQL语句的执行时间、执行次数、执行位置、执行用户等信息,通过分析慢日志可以发现哪些SQL语句执行时间较长,哪些SQL语句执行次数较多,哪些SQL语句需要优化等,从而进行针对性的优化,提高数据库的性能和稳定性。

我们可以通过SQL命令查看阈值(默认是10毫秒,如果一条SQL语句执行的时间超过了10毫秒,那么就算一条慢SQL语句,会产生慢日志)

Mysql - 日志,第7张

我们可以查看我们的Mysql慢日志是否打开,命令如下:

Mysql - 日志,第8张

可以很明显的看到,我们的Mysql慢日志是关闭的,所以当我们想要打开时,我们应该在Mysql的配置文件里进行修改:

Mysql - 日志,第9张

我们在mysqld里面添加了slow_query_log = 1 表示打开Mysql的慢日志,而long_query_log 表示设置慢日志的阈值的大小。

修改配置文件后需要刷新mysqld服务

 Mysql - 日志,第10张

我们再检查我们的Mysql慢日志是否打开了(下图所示,已经打开了,路径也显示出来了)

Mysql - 日志,第11张

我们通过tail -f命令动态查看Mysql的慢日志

Mysql - 日志,第12张

但是为什么Mysql默认情况下没有打开慢日志呢?

Mysql默认情况下没有打开慢日志,主要是因为慢日志可能会对数据库的性能产生一定的影响。当开启慢日志时,Mysql需要在每次执行语句时判断是否超过了慢日志的阈值,这会增加额外的计算和存储开销。此外,如果慢日志记录的数据量过大,也会占用大量的磁盘空间,进一步影响数据库性能。因此,开启慢日志需要在保证数据库性能的前提下进行,需要根据实际需求进行设置。

四、Mysql的通用日志:(默认是关闭的)

作用:

MySQL通用日志是MySQL服务器的一种日志文件,它可以记录所有的客户端连接(Mysql连接)和所用的查询操作,包括查询语句select、执行时间time、返回结果等信息。通用查询日志的作用包括以下几个方面:

  1. 审计数据库操作:通用查询日志可以记录所有的数据库操作,包括增删改查等操作,可以帮助管理员监控和审计数据库的使用情况,发现异常操作和安全问题。

  2. 优化SQL语句:通用查询日志可以记录SQL查询语句的执行时间和返回结果等信息,可以帮助开发人员优化SQL语句的性能,提高数据库的查询效率。

  3. 故障排查:通用查询日志可以记录数据库操作的详细信息,包括错误信息和异常情况,可以帮助管理员快速定位和解决故障问题。

  4. 监控数据库性能:通用查询日志可以记录数据库的性能指标,如查询次数、响应时间等,可以帮助管理员监控数据库的性能状况,及时发现并解决性能问题。

总之,通用查询日志是MySQL服务器非常重要的一种日志文件,能够提供丰富的信息和数据,帮助管理员和开发人员更好地管理和维护MySQL数据库。

我们可以SQL命令查看我们的通用日志是否打开了

Mysql - 日志,第13张

我们如何打开Mysql的通用日志呢,我们应该与上面的慢日志一样,修改mysqld的配置文件

Mysql - 日志,第14张

我们直接在配置文件里添加general_log就可以打开我们的通用日志了

记得修改配置文件后需要刷新mysqld服务
Mysql - 日志,第15张

我们再次检查是否打开了通用日志

Mysql - 日志,第16张

上图所示,我们打开了通用日志啦。

我们通过tail -f命令动态查看Mysql的通用日志

Mysql - 日志,第17张

但是为什么Mysql默认情况下没有打开通用日志呢?

MySQL默认情况下没有打开通用日志是因为开启通用查询日志会导致MySQL的性能下降,并且占用大量的磁盘空间。通用日志记录了所有的查询语句,包括SELECT、INSERT、UPDATE、DELETE等所有的操作,因此会产生大量的日志信息,对于大型的数据库系统来说,这些日志信息可能会非常庞大,因此默认情况下MySQL并不会开启通用查询日志。如果需要使用通用日志,需要根据具体的需求手动开启,并且需要注意日志文件的大小和定期清理日志文件。

缺点:消耗大量的磁盘空间、消耗cpu、内存、磁盘资源

优点:审计、会记录所有的SQL操作

五、Mysql的二进制日志:(默认是关闭的)(很重要,有机密信息的记录)

作用:

MySQL二进制日志(Binary Log)记录了所有对MySQL数据库进行的修改操作,包括插入、更新、删除等操作。它的作用主要有以下几个方面:

  1. 数据备份与恢复:二进制日志可以用于数据备份,通过将二进制日志备份到另一台服务器上,可以在主服务器出现问题时快速恢复数据。

  2. 数据复制:通过将主服务器的二进制日志复制到从服务器上,可以实现数据同步,将主服务器上的数据复制到从服务器上,从而提高系统的可用性和可靠性。

  3. 数据恢复:二进制日志可以用于数据恢复,通过回放二进制日志中的操作,可以将数据库恢复到指定的时间点。

  4. 数据审计:二进制日志可以用于数据审计,通过分析二进制日志,可以了解数据库中的数据修改历史,从而帮助管理员发现潜在的问题。

总之,二进制日志是MySQL数据库中非常重要的组成部分,它为数据备份、数据同步、数据恢复和数据审计等方面提供了强大的支持。

并且我们的二进制日志文件里存放的是二进制的数据,因此我们不能使用tail、head、cat命令去查看,但是可以使用mysqlbinlog这个工具去查看

而且我们的二进制日志文件记录了整个Mysql进程里的所有操作,所有库所有表的操作,

但是如果我们需要记录到不同的日志文件里,那我们可以采用多实例。

二进制文件是如何产生的呢?

 Mysql - 日志,第18张

Mysql - 日志,第19张二进制文件的产生过程,因为有事件出现(insert等),让后将事件commit到log Buffer日志缓存池里面(在mysql内存中),然后再提交到os的缓存池里面去(操作系统的内存),最后通过fsync()函数写入磁盘中。

Mysql实例:

一个正在运行的一个mysql的进程,这个进程可以由哪些库可以操作,二进制日志就记录哪些库的操作。

Mysql的多实例:

        多启用几个mysqld的进程,一个mysqld进程对应一个库

        隔离应用,避免一个库使用的特别频繁,从而影响其他的库

        但是多实例任然受到整个机器整体系统的资源限制。

在创建MySQL实例时,需要指定以下参数:

  1. 端口号:用于客户端连接MySQL服务器的端口号。
  2. 数据目录:MySQL实例存储数据的目录。
  3. 配置文件:包含MySQL实例的配置选项,如缓存大小、最大连接数等。

多实例的替换方案:我们可以使用云服务器,可以增加系统的新能,从而替代Mysql多实例的使用

我们可以SQL命令查看我们的二进制日志是否打开了

Mysql - 日志,第20张

我们可以查看二进制日志的大小:(大约1G)(默认一个二进制文件最大只能一个1G)

Mysql - 日志,第21张

如何开启二进制日志文件呢?

我们通过在mysql配置文件里添加如下命令:
Mysql - 日志,第22张

修改Mysql配置文件后需要刷新mysqld服务
Mysql - 日志,第15张

我们再次检查是否打开了二进制日志

Mysql - 日志,第24张

我们的Mysql数据文件夹里也出现了二进制日志文件了(mysql-bin.000001)(我们每次刷新Mysql的服务,他就会重新产生二进制日志文件mysql-bin.000002往后按顺序排下去)

Mysql - 日志,第25张

什么时候会产生新的二进制日志文件呢?

Mysql会在以下情况下产生新的二进制日志文件:

  1. 当前二进制日志文件大小超过了max_binlog_size参数设置的大小限制;
  2. 当前二进制日志文件的写入时间超过了max_binlog_time参数设置的时间限制;
  3. 手动执行flush logs语句。(刷新日志文件的时候,产生新的二进制日志文件)
  4. 当我们刷新Mysql服务的时候(service mysqld restart)

Mysql - 日志,第26张

Mysql - 日志,第27张

mysql-bin.index会记录我们一共由多少个二进制日志文件。 

Mysql - 日志,第28张

我们又是如何知道我们现在正在使用哪个二进制日志文件呢:(通过show master status;命令)

Mysql - 日志,第29张

我们可以通过show binary logs 查看所有二进制日志文件的大小

Mysql - 日志,第30张

我们又是如何删除所有的二进制日志文件呢:(使用reset master)

Mysql - 日志,第31张

如何手动清除二进制日志文件呢:(purge binary log 命令)

 参考文档:(13条消息) MySql自动清除binary logs日志_purge binary logs_hanchao5272的博客-CSDN博客

Mysql - 日志,第32张

如下图所示,我们使用 purge binary logs to 'mysql-bin.000003';删除了mysql-bin.000003之前的所有二进制日志文件(不包括mysql-bin.000003日志文件)

 Mysql - 日志,第33张

我们可以设置自动清除日志文件:

通过改变 expire_logs_days 的大小,可以设置过几天时间自动清除二进制日志文件(默认为0表示日志文件永不过期,永不清除)

Mysql - 日志,第34张

设置改变expire_logs_days的大小,设置日志文件自动清除的日期。

 Mysql - 日志,第35张

我们在Mysql的配置文件里添加了 expire_logs_days = 7  表示二进制日志文件过 7天 自动清除

Mysql - 日志,第36张

 修改Mysql配置文件后需要刷新mysqld服务
Mysql - 日志,第15张

我们再次检查是否打开自动清除二进制日志文件的配置

Mysql - 日志,第38张

查看二进制日志:

我们可以使用mysqlbinlog命令:

mysqlbinlog命令的常用选项包括:

  • -h:指定MySQL服务器的主机名或IP地址。
  • -u:指定连接MySQL服务器的用户名。
  • -p:提示输入连接MySQL服务器的密码。
  • -t:在输出中包含时间戳。
  • -v:将二进制日志文件转换为可读的文本格式,可以输出更详细的信息。
  • -vv:信息更加的多
  • --base64-output=decode-rows:将ROW事件的内容以Base64格式输出。
  • --database=db_name:只输出指定数据库的日志。
  • --start-datetime=datetime:从指定的日期时间开始输出日志。(根据时间点进行备份)
  • --stop-datetime=datetime:在指定的日期时间停止输出日志。
  • --start-position=pos:从指定的日志位置开始输出日志。(根据位置号进行备份)
  • --stop-position=pos:在指定的日志位置停止输出日志。
  • --no-defaults:不使用默认的配置文件。
  • --verbose:输出更详细的信息。
  • --help:显示帮助信息。

Mysql - 日志,第39张

参考文档:​​​​​​​MySQL 数据库之Binlog日志使用总结 - 散尽浮华 - 博客园 (cnblogs.com)

MySQL的binlog日志 - 马丁传奇 - 博客园 (cnblogs.com)​​​​​​​

二进制日志文件的格式

参考文档:MySQL之二进制日志 - Yuki_xiong - 博客园 (cnblogs.com)

二进制日志的格式:

1、row level

2、statement level

3、mixed level

1、row level

说明: 记录操作的每一行数据

记录操作的每一行数据  --》表里的哪些行的数据发生了变化

优点:

  • 相比statement更加安全的复制格式
  • 系统的特殊函数也可以复制
  • 更少的锁
  • 数据一致性高

    缺点:

    • binlog 文件会比较大
    • 无法从binlog中看见用户执行的SQL
    • 每个表最好都要有一个主键

       从mysql5.7之后,默认的格式为Row格式

我们可以通过查看show variables like 'binlog_format';来查看二进制日志文件的格式Mysql - 日志,第40张

2、statement level

说明:基于语句的,记录操作的sql语句

优点:

  • binlog文件大小较小
  • 易于理解,方便阅读
  • 日志中包含原始SQL,方便统计和审计

    缺点:

    • 存在安全隐患,可能导致主从不一致
    • 对一些系统函数不能复制,比如sysdate,uuid等
    • 不支持不确定的SQL语句

3、mixed level

说明:混合使用Row和Statement格式,对DDL记录会使用Statement,对于table里的行操作会记录Row格式

如果是Innodb表,事务级别为Read committed 或者 read uncommitted日志级别只能使用Row格式

(一般不推荐使用Mixed格式)