MySQL日志(十四)

Published on in MySQL with 0 views and 0 comments

MySQL日志

  • MySQL日志记录了MySQL数据库日常操作和错误信息。

日志简介

  • MySQL日志主要分为4类,使用这些日志文件,可以查看MySQL内部发生的事情。这4类日志分别是:

    • 错误日志:记录MySQL服务的启动、运行或停止MySQL服务时出现的问题。
    • 查询日志:记录建立的客户端连接和执行的语句。
    • 二进制日志:记录所有更改数据的语句,可以用于数据复制
    • 慢查询日志:记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。
  • 通过刷新日志,可以强制关闭和重新打开日志文件(或者在某个情况下切换到一个新的日志)。当执行一个 FLUSH LOGS 语句或执行 mysqladmin flush-logsmysqladmin refresh 时,将刷新日志。

二进制日志

  • 二进制日志包含所有更新了数据或者已经存在更新了数据(例如,没有匹配任何行的一个DELETE)的语句。使用二进制日志的主要目的是最大可能地恢复数据库,因为二进制日志包含备份后进行的所有更新。

启动和设置二进制日志

  • 默认情况下,二进制日志是关闭的,可以通过修改MySQL的配置文件来启动和设置二进制日志。

  • my.ini中[MySQLd]组下面有几个设置是关于二进制日志的:

    log-bin [=path/[filename] ]
    expire_logs_days = 10
    max_binlog_size = 100M
    

    log-bin定义开启二进制日志;日志文件后缀为000001000002...,每启动一次MySQL服务会自动生成一个新的日志文件,后缀名加1递增;filename.index文件内容为所有日志的清单,可以使用记事本打开。

    expire_logs_days定义了MySQL清除过期日志的时间,即二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。当MySQL启动或刷新二进制日志时可能删除该文件。

    max_binlog_size定义了单个文件的大小限制,如果二进制日志写入的内容大小超出给定值,日志就会发生滚动(关闭当前文件,重新打开一个新的日志文件)。不能将该变量设置大于1GB或小于4096B默认值是1GB

  • 通过使用SHOW VARIABLES语句可以查询日志设置,执行的语句及结果如下:

    SHOW VARIABLES LIKE 'log_%';
    
  • 提示:数据库文件最好不要与日志文件放在同一个磁盘上,这样,当数据库文件所在的磁盘发生故障时,可以使用日志文件恢复数据。

查看二进制日志

  • show binary logs语句可以查看当前的二进制日志文件个数及其文件名

  • MySQL二进制日志并不能直接查看,如果要查看日志内容,可以通过mysqlbinlog命令查看,在命令行下执行的语句格式如下:

    mysqlbinlog 路径  --不需要加引号,正反斜杆都可以
    
  • 在MySQL中查看二进制日志,执行语句格式如下:

    SHOW BINLOG EVENTS IN 'log_name';
    
  • 使用 SHOW MASTER STATUS; 可以查看当前的二进制日志POS位置。

删除二进制日志

  • MySQL的二进制日志文件可以配置自动删除,同时MySQL也提供了安全的手动删除二进制的方法;RESET MASTER删除所有的二进制日志文件;PURGE MASTER LOGS只删除部分二进制日志文件。
  1. 使用RESET MASTER语句删除所有二进制日志文件

    RESET MASTER 语法格式如下:

    RESET MASTER;
    

    执行完该语句后,所有二进制日志将被删除,MySQL会重新创建二进制日志,新的日志文件扩展名将重新从000001开始编号。

  2. 使用PURGE MASTER LOGS语句删除指定日志文件

    PURGE MASTER LOGS 语法如下:

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

    第1种方法指定文件名,执行该命令将删除文件名编号比指定文件名编号小的所有日志文件。

    第2种方法指定日期,执行该命令将删除指定日期以前的所有日志文件。

使用二进制日志恢复数据库

  • 使用mysqlbinlog工具从指定的时间点开始直到现在,或另一个指定的时间点的日志中恢复数据。

  • mysqlbinlog 恢复数据的语法如下:

    mysqlbinlog [option] filename | mysql -u username -p
    

    option是一些可选的选项,filename是日志文件名。

    option参数是 --start-date--stop-date--start-position--stop-position。--start-date和--stop-date可以指定恢复数据库的起始时间点和结束时间点。--start-position、--stop-position可以指定恢复数据的开始位置和结束位置。

暂时停止二进制日志功能

  • 修改配置文件,可以停止二进制日志,但是需要重启MySQL数据库。MySQL提供了暂时停止二进制日志的功能。通过SET SQL_LOG_BIN语句可以暂停或者启动二进制日志。

  • SET SQL_LOG_BIN的语法如下:

    SET sql_log_bin = {0|1}  --0暂停,1启动
    

错误日志

  • 错误日志文件包含了当mysqld启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。

启动和设置错误日志

  • 在默认情况下,错误日志会记录到数据库的数据目录下。如果没有在配置文件中指定文件名,则文件名默认为hostname.err

  • 如果执行了 FLUSH LOGS,错误日志文件会重新加载。

  • 错误日志的启动和停止以及指定日志文件名都可以通过修改my.ini(或者my.cnf)来配置。错误日志的配置项是log-err。如果需要指定文件名,则配置项如下:

    [mysqld]
    log-error = [path / [file_name]]
    

    修改配置项后,需要重启MySQL服务以生效。

查看错误日志

  • MySQL错误日志是以文本文件形式存储的,可以使用文本编辑器直接查看MySQL错误日志。

  • 如果不知道日志文件的存储路径,可以使用SHOW VARIABLES语句查询错误日志的存储路径。

  • SHOW VARIABLES 语句如下:

    SHOW VARIABLES LIKE 'log_error';
    

删除错误日志

  • MySQL的错误日志是以文本文件的形式存储在文件系统中的,可以直接删除。

  • flush logs 只是重新打开日志文件,并不做日志备份和创建的操作。如果日志文件不存在,MySQL启动或者执行 flush logs 时会创建新的日志文件。

  • 在运行状态下删除错误日志文件后,MySQL并不会自动创建日志文件。flush logs 在重新加载日志的时候,如果文件不存在,则会自动创建。如果需要重建日志文件,则需要在服务器端执行以下命令:

    mysqladmin -u username -p flush-logs
    

    或者在客户端电路MySQL数据库,执行 flush logs 语句:

    flush logs;
    

通用查询日志

  • 通用查询日志记录MySQL的所有用户操作,包括启动和关闭服务执行查询更新语句等。

启动和设置通用查询日志

  • MySQL服务器默认情况下并没有开启通用查询日志。如果需要通用查询日志,可以通过修改my.ini(或my.cnf)配置文件来开启。在my.ini(或my.cnf)的 [mysqld] 组下加入log选项,形式如下:

    [mysqld]
    general-log=1
    general_log_file="path/filename.log"
    

    如果不指定目录和文件名,通用查询日志将默认存储在MySQL数据目录中的hostname.log文件中。

查看通用查询日志

  • 通用查询日志中记录了用户的所有操作。通用查询日志是以文本文件的形式存储在文件系统中,可以使用文本编辑器直接打开通用日志进行查看。

  • 通过SHOW VARIABLES语句可以查看通用查询日志开启状态及日志所在路径,语句如下:

    SHOW VARIABLES LIKE '%general%';
    

删除通用查询日志

  • 可以用直接删除日志文件的方式删除通用查询日志。要重新建立新的日志文件,可使用语句 mysqladmin flush-logs 或者 flush logs;

慢查询日志

  • 慢查询日志是记录查询时长超过指定时间的日志。通过慢查询日志,可以找出执行时间较长、执行效率较低的语句,然后进行优化。

启动和设置慢查询日志

  • MySQL中慢查询日志默认是关闭的,可以通过配置文件my.ini或者my.cnf中的slow-query-log选项启动慢查询日志。另外,还需要配置long_query_time选项指定记录阈值。

    my.ini或者my.cnf开启慢查询日志的配置如下:

    [mysqld]
    slow-query-log = 1
    slow_query_log_file = "path/filename.log"
    long-query-time = n    --单位为秒,默认值为10秒
    

删除慢查询日志

  • 和通用日志一样,慢查询日志也可以直接删除。删除后在不重启服务器的情况下,需要执行 mysqladmin flush-logs 或者 flush logs; 语句重建日志文件。

注意事项

  • 日志既会影响MySQL的性能,又会占用大量磁盘空间。因此,如果不必要,应尽可能少地开启日志。在开发环境中优化查询效率低的语句,可以开启慢查询日志;如果需要记录用户的所有查询操作,可以开启通用查询日志;如果需要记录数据的变更,可以开启二进制日志;错误日志是默认开启的。
  • 二进制日志还可以用来进行数据恢复,还可以用来数据复制。在数据库定期备份的情况下,如果出现数据丢失,可以先用备份恢复大部分数据,然后使用二进制日志恢复最近备份后变更的数据。在双机热备情况下,可以使用MySQL的二进制日志记录数据的变更,然后将变更部分复制到备份服务器上。

标题:MySQL日志(十四)
作者:wangdj
地址:https://www.dblog.cool/articles/2024/11/18/1731943872821.html