一、日志简介
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;