MySQL 日志管理

一、日志简介

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;
点赞

发表回复