MySQL日志管理

日志管理

在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这种时候，我们必须

保证数据的安全性和完整性，就需要使用日志来查看或者恢复数据了

数据库中数据丢失或被破坏可能原因：

• 误删除数据库

• 数据库工作时，意外断电或程序意外终止

• 由于病毒造成的数据库损坏或丢失

• 文件系统损坏后，系统进行自检操作

• 升级数据库时，命令语句不严格

• 设备故障

• ......

MySQL日志类型

日志类型	记入文件中的信息类型
错误日志	记录启动，运行或停止时出现的问题
查询日志	记录建立的客户端连接和执行的语句
二进制日志	记录所有更改数据的语句，主要用于复制和即时点恢复
慢日志	记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
事务日志	记录InnoDB等支持事务的存储引擎执行事务时产生的日志

默认情况下，所有日志创建于mysqld数据目录中。通过刷新日志，你可以强制 mysqld来关

闭和重新打开日志文件

刷新日志

在mysql中：执行语句 flush logs 刷新日志

在shell中：通过mysqladmin命令执行日志刷新

mysqladmin flush-logs
mysqladmin refresh

错误日志

错误日志记录内容

• 服务器启动和关闭过程中的信息

• 服务器运行过程中的错误信息

• 事件调度器运行一个时间产生的信息

• 在从服务器上启动从服务器进程是产生的信息

指定错误日志保存的位置

可以⽤--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置

如果file_name也就是文件名没有指定，name默认名字将会是host_name.err

如果你执行FLUSH LOGS，错误日志用-old重新命名后缀并且mysqld创建⼀个新的空日志文件

查看当前错误日志配置

show global variables like "log_error%";

是否记录警告日志

show global variables like "log_warnings";

查询日志

通用查询日志

启动开关：general_log={ON|OFF}

日志文件变量:general_log_file[=/PATH/TO/file]

全局日志开关:log={ON|OFF} 该开关打开后所有日志都会被开启

记录类型：log_output={TABLE|FILE|NONE}

可见要开启通用查询日志，至少需要配置

general_log=ON，
log_output={TABLE|FILE}
general_log_file

查看默认配置

SHOW GLOBAL VARIABLES LIKE '%general_log%';
SHOW GLOBAL VARIABLES LIKE '%log_output%';

慢查询日志

MySQL如果启⽤了 slow_query_log=ON 选项，就会记录执⾏时间超过long_query_time的查询

日志文件为：slow_query_log_file[=file_name]

如果没有给出file_name值， 默认为主机名，后缀为-slow.log

如果给出了文件名，但不是绝对路径名，文件则写入数据目录

开启慢查询的方法

(只在当前有效)

set global slow_query_log =ON;

查看配置文件

SHOW GLOBAL VARIABLES LIKE '%slow_query_log%';

# 使用 mysqldumpslow 命令获得日志中显示的查询摘要来处理慢查询日志
# mysqldumpslow slow.log
# 那么多久算是慢呢？
# 如果查询时长超过long_query_time的定义值（默认10秒），即为慢查询：
mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';

二进制日志文件

开关

二进制日志启动开关：log-bin [=file_name]

作用

1.⼆进制日志包含了所有更新了数据或者已经潜在更新了数据的所有语句

2.语句以“事件”的形式保存，它描述数据更改。二进制日志还包含关于每个更新数据库的语

句的执行时间信息。它不包含没有修改任何数据的语句行

3.二进制日志的主要⽬的是在数据库存在故障时，恢复时能够最⼤可能地更新数据库（即时点

恢复），因为二进制日志包含备份后进行的所有更新。

4.二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句

二进制日志的记录

二进制会自行选择记录执行语句或者执行后的数据结果

有以下情况

1. 假设一个表有10万行数据，现在要执行一个语句，将表中的某个字段的值全部替换

这个时候因为记录执行后的结果数据的话日志会非常大，会选择记录执行语句

2.如果向某个字段插入当前时间，这种不同时间执行结果时不一样的，那就得记录执行后的数据结果

3.实际上也可以结合两种方式来记录，这种情况叫做混合方式的二进制日志

二进制日志的管理

日志滚动

在my.cnf中设定max_binlog_size = 200M，表示限制二进制日志最大尺存为200M，超过200M后进行滚动。

MySQL的滚动方式与其他日志不太⼀样，滚动时会创建⼀个新的编号大1的日志用于记录最新的日志，而原日志名字不会被改变。每次重启MySQL服务，日志都会自动滚动⼀次。

另外如果需要手动滚动，则使⽤命令 mysql> FLUSH LOGS ;

日志查看

查看有那些二进制文件

show binary logs;

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

show master status;

查看二进制内容

show binlog events in 'mysqld-binlog.000002';
mysqlbinlog [option] log-files

二进制日志还原数据

使用mysqlbinlog读取需要的日志内容，使用标准输入重定向到一个sql文件，然后在mysql

服务器上导入即可

举个例子

mysqlbinlog mysqld-binlog.000002 >/root/temp_date.sql

二进制日志还原出错

如果在导入的时候出现了

错误——mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'

原因——mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8mb4这个指令

解决方式

1. 添加 --no-defaults 参数

mysqlbinlog --no-defaults binlog.000069 > c:/a.sql
/*注意需要指定binlog的位置，如果是当前路径，则可以直接使⽤名称即可*/

2.修改配置文件 my.cnf

default-character-set=utf8mb4 修改为 character-set-server = utf8mb4，但是需要重

启MySQL服务

删除二进制日志文件

二进制日志文件不能直接删除，如果使用rm等命令直接删除日志文件，可能导致数据库崩溃

必须使用命令 PURGE 删除日志文件

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

注意：如果数据库使用的编码是utf8mb4编码，mysqlbinlog命令可能不能解析这种编码

两种解决方法

1. 在 MySQL 的 配 置 /etc/my.cnf 中 将 default-character-set=utf8mb4 修 改 为

character-set-server = utf8 ，但是这需要重启MySQL服务，如果你的MySQL服务正

在忙，那这样的代价会比较大。

2. 用mysqlbinlog --no-defaults mysql-bin.000004 命令打开