文章目录

  • 分类
  • 二进制日志
  • 启动和设置二进制日志
  • 查看二进制日志
  • 删除二进制日志文件
  • 使用二进制日志还原数据库
  • 暂停二进制日志功能
  • MySQL的binlog有有几种录入格式?分别有什么区别?
  • 错误日志
  • 通用查询日志
  • 慢查询日志



MySQL的日志记录了MySQL的日志操作和错误信息，从日志中可以查看到MySQL的运行状况，用户操作，错误信息等，可以为MySQL的管理和优化提供必要的信息。

分类

MySQL的日志主要分为四类，分别是：

  • 错误日志：提供MySQL服务在启动，运行和关闭时发生的错误
  • 查询日志：记录建立的客户端连接和执行的语句
  • 二进制日志：记录所有更改数据的语句，可用于数据复制
  • 慢查询日志：记录所有执行时间唱过long_query_time的查询和不使用索引的查询

默认情况下，所有的日志创建在MySQL的数据目录中，通过刷新日志（flush logs）可以强制关闭和重新打开日志文件。另外，启动日志功能会降低数据库性能，MySQL服务器需要花费时间记录日志，日志文件要占用很多磁盘空间。

二进制日志

二进制日志主要记录MySQL数据库的变化，它以一种有效地格式并且是事务安全的方式包含更新日志中可用的所有信息，包含了所有更新了数据或者已经潜在更新了数据的语句（如没有匹配任何行的delete语句）。二进制日志还包含了关于更新了数据库的语句的执行时间信息，它不包含没有修改任何数据的语句。如果要记录所有语句，需要使用一般查询日志。使用二进制日志最大的目的的尽可能的恢复数据库，因为二进制日志包含备份后进行的所有的数据更新。

启动和设置二进制日志

默认情况下，二进制日志是关闭的，可通过修改配置文件来启动和设置二进制日志。my.ini找那个mysqld组下有几个设置二进制日志的参数：

log_bin[=path/filename]
expire_logs_days=10
max_binlog_size=100M
  • log_bin定义开启二进制日志，path表明日志文件所在的文件目录，filename指定了日志文件的名称，如filename.00001,filename.00002,除此之外，还有个文件称作filename.index，文件内容为所有日志的清单，可以用记事本打开
  • expire_logs_days定义了清除过期日志的时间，即自动删除的天数，默认为0，不会自动删除。
  • max_binlong_size定义了单个文件的大小限制，如果文件大小超过了最大值，会产生新的日志文件，这个大小不能小于4096字节，不能大于1GB，默认1GB。

使用show variables like '%log_%' 查看日志设置

查看二进制日志

当MySQL创建二进制日志时，首先创建一个以filename为名称，以.index为后缀名的文件，再创建一个以filename为名称，以.00001为后缀的文件，当MySQL服务重启或者文件大小超出限制时，都会创建一个新的日志文件，后缀递增1.

-- 查看当前二进制文件
show binary logs;
-- 或者
show master status;
-- 查看日志文件内容
show binlog events;
-- 查看特定的日志文件
show binlog events in 'log.00001';
-- 切换二进制日志
flush logs;

每次重启MySQL服务会生成一个新的二进制日志文件，相当于二进制日志的切换。同时生成一个.index文件，存储所有二进制日志的清单又称为二进制日志文件的索引。

如果配置文件中没有配置日志目录，日志默认目录为安装目录data文件夹中。

删除二进制日志文件

MySQL的二进制日志文件可以配置自动删除，也可以安全的手动删除二进制日志文件。

-- 删除所有
reset master;
-- 删除某个日志之前的所有
purge {master|binary} logs to 'log_name';
-- 删除某个时间之前的所有
purge {master|binary} logs before date

使用二进制日志还原数据库

如果MySQL数据库启用了二进制日志，当数据库出现意外丢失数据时，可以使用mysqlbinlog工具在指定时间内的日志中恢复数据。要想从二进制日志恢复数据，需要知道当前二进制日志文件的路径和名字，一般可从my.ini中找到路径。

mysqlbinlog [option] filename|mysql -u root -p password;
-- option
-- 指定恢复数据库的时间段
--start--date/--stop--date
-- 指定恢复位置（位置可为文件名）
--start--position/--stop--position

暂停二进制日志功能

-- 0表示暂停，1表示运行
set sql_log_bin={0|1}

MySQL的binlog有有几种录入格式?分别有什么区别?

有三种格式,statement,row和mixed.

  • statement模式下,记录单元为语句.即每一个sql造成的影响会记录.由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制.
  • row级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大.
  • mixed. 一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row.

此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录.

错误日志

错误日志文件包含了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。默认情况下，错误日志记录在数据库的data目录下，如果没有在配置文件中指定文件名称，文件名默认为hostname.err，错误日志的启动和停止以及文件名都可通过修改my.ini来配置，配置项为log-error。

log_error=[path/filename]

修改配置后，重启服务生效。如果不知道错误日志的存储路径,可使用命令查看

show variables like 'log_error';

错误日志文件以文本文件的形式保存在文件系统中，可以直接删除。在运行状态下删除错误日志文件后，MySQL不会自动创建日志文件，flush logs在重新加载日志文件的时候，如果不存在会自动创建。

-- 删除日志后，重新创建的命令
mysqladmin -u root -p flush-logs;

通用查询日志

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

MySQL默认情况下没有开启通用查询日志记录。通过修改my.ini启动

log=[path/filename]

默认记录在data目录下hostname.log文件中，以文本文件的形式保存在文件系统中，可使用文本编辑器直接查看。

慢查询日志

慢查询日志时记录查询时间超过指定时间的查询，通过慢查询日志可以找到执行时间较长，效率较低的查询，从而进行优化。

MySQL中慢查询日志默认是关闭的，可通过修改my.ini或者my.cnf中的log_slow_quries选项打开，也可以在服务启动时使用log_slow_quries=[filename]启动慢查询日志。启动慢查询日志时，需要在配置文件中指定记录阈值，如果某条查询语句的执行时间超过了这个值则记录在慢查询日志汇总，开启语句：

log_slow_quries=[path/filename]
long_query_time=n
-- 默认时间10s