一、日志简介

MySQL数据库中支持多种日志类型，通过分析日志，我们可以优化数据库性能，排除故障，甚至能够还原数据，本节内容将带你了解MySQL数据库中的日志管理

 

二、日志分类

  • 错误日志
  • 查询日志
  • 慢查询日志
  • 二进制日志
  • 中继日志
  • 事务日志
  • 滚动日志

 

三、日志详解

1.错误日志

1.1 错误日志作用

  • 记录服务器运行中产生的错误信息

  • 记录服务器启动、停止时产生的信息

  • 如果服务器启动了复制进程，复制进程的信息也会被记录

  • 记录event错误日志

1.2 查看错误日志

查看mysql中错误日志的位置

  • 在mysql命令行中查看
mysql> show variables like "log_error";
  • 在bash命令行中使用mysqladmin查看
[root@db01 ~]# mysqladmin -uroot -p123 variables | grep -w "log_error"

1.3 设置错误日志

修改mysql的主配置文件/etc/my.cnf，在服务端中添加如下配置：

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_error=/var/log/mysql/mysql.err
#log_error=mysql.err   #默认在数据目录下

注意：

  • 错误日志默认是开启的
  • 当没有指定错误日志时，错误日志保存在 数据目录$datadir下，以主机名.err命名

2.查询日志

记录mysql中所有执行成功的SQL语句信息（除了慢查询日志中记录的查询信息），会增大服务器的压力。所以一般不开启查询日志。

2.1 查看方式

mysql> show variables like "%general_log%";

2.2 开启方式（默认关闭）

  • 临时开启
mysql> set global general_log=on;
  • 永久开启
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
general_log=ON
general_log_file=/var/log/mysql/mysql.log

注意：

  • 查询日志默认关闭
  • 默认的位置：在mysql数据目录$datadir下

3.慢查询日志

3.1 慢查询日志的作用

  • 将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件
  • 通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

3.2 查看方式

mysql> show variables like "%slow_query_log%";

3.3 开启方式（默认关闭）

修改mysql的主配置文件

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log=ON
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/var/log/mysql/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=5
#不使用索引的慢查询日志是否记录到索引
log_queries_not_using_indexes
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
min_examined_row_limit=100

注意：

  • 慢查询日志默认关闭
  • 开启后默认以主机名-slow.log命名
  • 默认的存放在数据目录$datadir下

3.4 模拟慢查询

  • 进入到一个库并创建一个新表
mysql> use world
mysql> create table tb01 select * from city;
  • 将tb01表中的数据插入到tb01中

多执行几次这个insert语句，直到看见查询时间超过我们设置5s即可。

mysql> insert into tb01 select * from tb01;
  • 使用mysqldumpslow命令分析慢查询日志
[root@db01 ~]# mysqldumpslow -s -c -t 10 /var/log/mysql/slow.log

参数说明：

-s 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t 是top n的意思，即为返回前面多少条的数据；

-g 后边可以写一个正则匹配模式，大小写不敏感的

4.二进制日志

4.1 二进制日志作用

  • 记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录
  • 记录所有的SQL语句
  • 记录对数据库数据进行修改的操作（增、删、改）
  • 可以恢复数据

4.2 二进制日志工作模式

  • STATEMENT

    语句模式，是MySQL5.6默认的模式。用来记录数据库的增、删、改等SQL语句。

    • 查看statement模式的binlog文件
    [root@db01 data]# mysqlbinlog mysql-bin.000002
    • 优缺点

    优点：易读、占用磁盘空间小。

    缺点：记录不太严谨。

  • ROW：行模式，是MySQL5.7默认模式。

    • 修改binlog为ROW模式
    [root@db01 data]# vim /etc/my.cnf
    [mysqld]
    binlog_format=row
    • 查看ROW模式下的binlog
    [root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002
    • 优缺点

    优点：记录更加严谨。

    缺点：不易读、占用磁盘空间较大。

  • MIXED：混合模式（STATEMENT、ROW模式混合）

4.3 二进制日志的管理

  • 开启二进制日志
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
log_bin=mysql-bin
server_id=1

注意:

在MySQL5.7中开启binlog必须要加上server_id。

  • 查看二进制日志

    • 物理查看
    [root@db01 ~]# ll  /usr/local/mysql/data/mysql-bin.*
    • 命令行查看
    mysql> show binary logs;

  • 刷新binlog

    • 刷新binlog，会生成一个新的binlog日志文件
    mysql> flush logs;
    • 查看binlog文件最大限制。
    mysql> show variables like "%max_binlog_size%";
    +-----------------+------------+
    | Variable_name   | Value      |
    +-----------------+------------+
    | max_binlog_size | 1073741824 |
    +-----------------+------------+

    注意：

    binlog文件达到1G时，会自动刷新生成一个新的binlog文件

  • 删除binlog

    • 删除指定天数前的binlog

    需要在mysql配置文件中加入如下参数

    expire_logs_days=7
    • 保留几天的binlog
    mysql> PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 day;
    • 删除指定binlog文件之前的所有binlog文件
    mysql> PURGE BINARY LOGS TO 'mysql-bin.000005';
    • 重置binlog
    mysql> reset master;

    二进制日志删除原则：在存储能力范围内，能多保留则多保留；基于上一次全备前的可以选择删除。

4.4 事件

  • 事件介绍

    • 在binlog中最小的记录单元为event
    • 一个事务会被查分成多个事件（event）

  • 事件（event）特性

    • 每个event都有一个开始位置（start position）和结束位置（stop position）。
    • 所谓的位置就是event对整个二进制的文件的相对位置。
    • 对于一个二进制日志中，前120个position是文件格式信息预留空间。
    • MySQL第一个记录的事件，都是从120开始的。

  • 查看binlog事件

mysql> show binlog events in 'mysql-bin.000001';

4.5 模拟二进制日志恢复数据

ROW模式下的二进制日志分析及数据恢复

#查看当前所在binlog位置点
mysql> show master status;

#创建一个binlog库
mysql> create database binlog;

#使用binlog库
mysql> use binlog

#创建binglog_table表
mysql> create table binlog_table1(id int);

#插入几条数据
mysql> insert into binlog_table1 values(1);
mysql> insert into binlog_table1 values(2);
mysql> insert into binlog_table1 values(3);

#删除id为1的行
mysql> delete from binlog_table1 where id=1;

#修改id=1为id=22
mysql> update binlog_table1 set id=22 where id=2;

#查看数据
mysql> select * from binlog_table1;

#删表
mysql> drop table binlog_table1;

#删库
mysql> drop database binlog;

恢复数据到delete之前

#查看binlog事件
mysql> show binlog events in 'mysql-bin.00001';

#使用mysqlbinlog来查看
[root@db01 data]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001
[root@db01 data]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 | grep -v SET
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000001
### UPDATE `binlog`.`binlog_table`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=22 /* INT meta=0 nullable=1 is_null=0 */
#分析
update binlog.binlog_table
set
@1=22 --------->@1表示binlog_table中的第一列,集合表结构就是id=22
where
@1=2  --------->@1表示binlog_table中的第一列,集合表结构就是id=2
#结果
update binlog.binlog_table set id=22 where id=2;

#截取二进制日志
查看二进制日志后，发现delete语句开始位置是941
[root@db01 ~]# mysqlbinlog --start-position=120 --stop-position=941 /usr/local/mysql/data/mysql-bin.000001 > /tmp/binlog.sql

#临时关闭binlog
mysql> set sql_log_bin=0;

#执行sql文件
mysql> source /tmp/binlog.sql

#查看删除的库
mysql> show databases;

#进binlog库
mysql> use binlog

#查看删除的表
mysql> show tables;

#查看表中内容
mysql> select * from binlog_table;

5.事务日志

InnoDB事务日志主要分为 redo log（重做日志，提供前滚操作） 和 undo log（回滚日志，提供回滚操作）。

为了最大程度上减少数据写入时io问题，在存储引擎修改表的数据时，会将数据从磁盘拷贝到内存中，然后修改内存中的数据拷贝，再将修改行为持久化到磁盘中（先写redo log buffer(日志缓冲区)(PS:这块我会在下文详细说明),再定期批量写入），而不用每次将修改的数据本身持久化到硬盘中。

Log Buffer（Redo Log Buffer）：重做日志缓冲区是用于保存将要写入重做日志磁 盘文件中的数据的内存缓冲区域。重做日志缓冲区的大小由innodb_log_buffer_size配置参 数定义。重做日志缓冲区中的内容会定期刷新到磁盘上的日志文件中。更大的重做日志缓 冲区允许运行更大的事务，这在一定程度上避免提交大事务之前需要将重做日志写入磁盘 中。因此，如果在应用场景中经常有大事务，则可以考虑增大重做日志缓冲区以减少磁盘 I/O操作。innodb_flush_log_at_trx_commit参数控制如何将重做日志缓冲区的内容写入日志 文件中（例如，设置为1时，每个事务提交时都需要执行一次将重做日志缓冲区的内容写 入日志文件中）。innodb_flush_log_at_timeout参数控制重做日志的刷新频率。

Undo Logs：用于存放事务修改之前的旧数据（undo log记录了有关如何撤销事务 对聚集索引记录的最新更改的信息），基于undo实现了MVCC和一致性非锁定读。 InnoDB总共支持128个回滚段，每个回滚段有1023个事务槽位，在并行事务场景中一个事 务槽位对应一个事务。其中32个回滚段位于临时表空间（Temporary Tablespace），也就 是说，对临时表操作的最大并行事务数大约为32×1023个；96个回滚段位于非临时表空间 （系统表空间至少一个，因为MySQL 5.7新增的在线undo truncate功能需要，Undo Tablespace最多95个），也就是说，对非临时表操作的最大并行事务数大约为96×1023 个。