mysql 日志协议_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;