mysql err日志_MySQL错误日志和二进制日志

大家好，我是anyux。本文介绍MySQL错误日志和二进制日志。

MySQL日志有5种，对于运维人员，最常用到的日志有3种，分别是错误日志、二进制日志和慢日志

错误日志(log_error)

错误日志通常用于记录数据库服务端启动、重启、主从复制时，记录错误，将日志详情保留在文件中，方便DBA、运维开发人员阅读。

一般地阅读错误日志，都会通过grep命令查找到[ERROR]这种字符串，这通常能够快速定位到数据库服务错误的根源，而不至于手足无措

数据库错误日志，可以以下sql命令来查找

select @@log_error;

上图中的stderr就表示错误日志，默认情况下，数据库错误日志功能是关闭的，错误日志会被写入到标准错误输出(stderr)。如果要开启，在配置文件中添加log-error=/data/mysql_error.log,后重启服务

[mysqld]

log-error=/data/mysql_error.log

如上图，就是配置成功。配置时注意权限问题，一定要让文件的权限和服务的权限相同

二进制日志(bin_log)

开启二进制日志

记录的是二进制日志，是sql层的日志

这个二进制日志，直接打开是乱码，它本就不是给人看的。

它的作用有两个，一是用于主从复制，二是在数据恢复

二进制日志默认是关闭的，开启它需要配置多个参数,下面这两个参数必须要配置，最为重要

[mysqld]给服务指定一个id号，取值区间0-65535，用于在主从复制时，标识不同的服务server_id=6

log_bin可以用于指定二进制文件存放目录

注意，定要让文件目录的权限和服务的权限相同

log-bin是二进制日志文件名的前缀，随着日志的滚动，会产生较多的二进制日志文件如，log-bin.00001,log-bin.00002等

log_bin=/data/mysql/log-bin

二进制日志其他参数

5.7版本以后，默认值为row

binlog_format=row

重启服务，查看二进制日志

如上图，log-bin.000001是二进制日志文件，log-bin是我们定义的前缀，你可以随意指定，后面的000001是数据库自动生成的，可以手动地以命令形式产生新的二进制日志文件。log-bin.index可以认为是一个索引文件，它会记录哪些文件是二进制日志文件。log-bin.000001是二进制文件，打开也是乱码，log-bin.index是索引文件，它是按行记录每一个二进制日志文件的位置。可以通过cat命令查看所有的二进制日志文件位置

查看二进制日志

二进制日志记录的是数据库中所有的变更类的操作日志，包括DDL(数据定义语句)、DCL(数据控制语句)、DML(数据操作语句)

对于DDL和DCL，二进制日志会以语句的形式，原样记录

对于DML，它记录的是已提交的事务。

对于DML语句，二进制日志文件记录它的方式有3种，分别是row(RBR行模式)、statement(SBR语句模式)、mixed(RBR混合模式)，通过binlog_format=row参数控制。

注意啦，binlog_format只会对DML语句起作用，对于DDL和DCL它是无效的。

二进制日志记录单元

event事件

二进制日志的最小单元

对于DDL、DCL语句，每一个语句都是一个事件

对于DML语句

begin; 事件1update table set column_1='values' where id=2; 事件2update table set column_2='others' where id=3; 事件3commit; 事件4

上面代码是一个事务，包含了4行语句，每一个语句都被称为一个事件。对于上面的4行语句，如果执行事务失败，需要回滚或写入磁盘。就需要将此事务完整的语句拿到，进行回滚操作或重新写入磁盘

event事件的开始和结束号码

方便运维管理人员从二进日志中截取需要的日志事件

show binary logs;

通过命令flush logs可以新建二进制日志文件

flush logs;

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

二进制日志文件默认使用最好一个，无法指定二进制文件，这是数据库自定义的

show master status;

查看二进制日志事件

创建数据库

create database binlog charset utf8mb4;use binlog;create table temp (id int);

查看二进制日志文件中否记录DDL语句

第一步先确认哪一个二进制日志文件是当前使用的

show master status

第二步查看日志内容

show binlog events in 'log-bin.000005';

也可以使用limit 查看日志文件

show binlog events in 'log-bin.000005' limit 2,3;

上图中的Event_type列中包含两个Query,对应的info字段中包含create语句。这表明二进制日志文件如实记录了原样的sql语句

第三步提交事务，查看日志详情

insert into temp values(1);commit;

如果要截取二进制日志文件中完整的事务，只需要截取从BEGIN开始时的Pos，到COMMIT结束的End_log_pos，也就是上图的，569到763。提示截取事务时，一定要从BEGIN开始截取到COMMIT，因为必须要保证事务的完整性

查看二进制日志文件内容

通过file文件查看对应的二进制日志文件

file log-bin.000005;

说明log-bin.000005是MySQL复制集日志，可用于主从复制。可以使用mysqlbinlog 工具打开

mysqlbinlog log-bin.000005 | less

以上命令可以按上下箭头查看日志信息，比较方便

上图中的内容可以理解为二进制日志的固定格式，重要的内容在下面

区分一个完整的方法，就是从一个at开始，找下一个at开始的上一行，就个范围就是完整的events事件

现在找一个条较长的事件，对于SET开头的行，忽略即可，

#200308 1:15:04 server id 6 end_log_pos 335

上面这句是条注释，它的意思是在2020年3月8日，1点15分4秒 服务器id6，结束位置在335

create database binlog charset utf8mb4

上面这条sql语句就是创建数据库的原语句

查找事务日志,使用grep命令快速定位

#排除以SET和/开头的行

mysqlbinlog log-bin.000005 | egrep -v "^SET|^/" | less

从show binlog events in 'log-bin.000005';语句中找到BEGIN和COMMIT对应的开始和结束位置，如上图所示

以井号开头的不做解释，可以看到自692位置开始到732位置结束，就是以RBR模式记录的二进制日志值

对于BINLOG后的内容看不懂，实际是被base64编码过了，需要解码才能看到

mysqlbinlog --base64-output=decode-rows -vvv log-bin.000005 | egrep -v "^SET|^/" | less

看到上图中有类似伪sql代码，INSERT INTO binlog.temp

表示向binlog库的temp表插入数据，SET @1=1，@1表示第一列，=1表示赋值操作，完整翻译就是向binlog库的temp表的每一列插入数据1。

按范围查看二进行日志

此处选择的是创建数据库的起止节点

mysqlbinlog --start-position=154 --stop-position=335 --base64-output=decode-rows -vvv log-bin.000005 | egrep -v "^SET|^/" | less

如果二进制日志很大，不方便查看，可以通过指定起始节点来操作

如果查看到二进制日志没有问题，想用来恢复日志，可以通过重定向到sql文件，再恢复数据

mysqlbinlog --start-position=154 --stop-position=335 --base64-output=decode-rows -vvv log-bin.000005 | egrep -v "^SET|^/" | less > /tmp/a.sql

查看特定数据库的信息

mysqlbinlog -d binlog --base64-output=decode-rows -vvv log-bin.000005 | egrep -v "^SET|^/" | less

通过binlog恢复数据

模拟数据

create database log_db charset utf8mb4;use log_db;create table log_01(id int);insert into log_01 values(1);commit;

模拟故障

drop database log_db;

基于binlog恢复日志

首先，找到当前记录的二进制日志文件，

show master status;

查看事件信息

提示：如果二进制日志过多，可以使用参数模式查看。使用重定向或grep过滤快速位置

show binlog events in 'log-bin.000006';

找到创建库语句的Pos数值，找到删除数据库的PoS数值

打开新的窗口，重定向到sql文件

进入二进制日志文件目录

cd /data/mysqlmysqlbinlog --start-position=219 --stop-position=766 log-bin.000006 > /tmp/log_01.sql

设置临时会话状态

注意，恢复数据，暂停数据库记录二进制日志。原因是，恢复数据的过程不需要再次被记录

下面语句的表示：在当前会话中，临时关闭二进制日志，不影响业务的正常运行

set sql_log_bin=0;

恢复数据

source /tmp/log_01.sql;

设置临时会话状态

set sql_log_bin=1;

查看数据

use log_db;select * from log_01;

提示：以上恢复数据过程是一个简单的示例，对于复杂的环境下，如基于2年来的二进制日志进行数据恢复，是可以进行。但是恢复速度会很慢，需要查看的节点也会较多。通常的解决方案是，数据为备份+二进制日志进行数据恢复。

欢迎在评论区一起讨论，质疑。文章都是手打原创，每天最浅显的介绍运维、数据库相关的技术，喜欢我的文章就关注一波吧，可以看到最新更新和之前的文章。