MySQL–日志

日志种类

错误日志

若是mysql突然抽风打开不了，看这个日志准没错。Ciallo～(∠・ω< )⌒☆

记录mysql数据库的一般状态信息及报错信息，是我们对于数据库常规报错处理的常用日志。

默认位置：

$MYSQL_HOME/data/

开启方式：

MySQL安装完后默认开启

[root@servera ~]# vim /etc/my.cnf
# 编辑配置文件
[mysqld]
log_error=/application/mysql/data/error_log.err
# 如果错误日志文件不在，需要手动创建，不然mysql启动会报错
mysql> show variables like 'log_error';
# 查看方式

一般查询日志

记录mysql所有执行成功的SQL语句信息，可以做审计用，但是我们很少开启。

默认位置：

$MYSQL_HOME/data/

开启方式：

MySQL安装完之后默认不开启

[root@db01 ~]# vim /etc/my.cnf
[mysqld]
general_log=on
general_log_file=/application/mysql/data/general_log.log
# 编辑配置文件
mysql> show variables like '%gen%';
# 查看方式

二进制日志

1.记录已提交的DML事务语句，并拆分为多个事件（event）来进行记录

事件介绍：
在binlog中最小的记录单元为event
一个事务会被拆分成多个事件（event）
事件（event）特性：
每个event都有一个开始位置（start position）和结束位置（stop position）。
所谓的位置就是event对整个二进制的文件的相对位置。
对于一个二进制日志中，前120个position是文件格式信息预留空间。
MySQL第一个记录的事件，都是从120开始的。

2.记录所有DDL、DCL等语句

3.二进制日志会记录所有对数据库发生修改的操作

二进制日志模式
statement：语句模式
row：行模式，即数据行的变化过程
mixed：以上两者的混合模式。
企业推荐使用row模式

二进制日志模式优缺点
statement模式
优点：简单明了，容易被看懂，就是sql语句，记录时不需要太多的磁盘空间
缺点：记录不够严谨
row模式
优点：记录更加严谨
缺点：有可能会需要更多的磁盘空间，不太容易被读懂

binlog的作用
数据的备份恢复
数据的复制

二进制日志的管理操作

开启方式

[root@servera data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=row
##server_id=1    #注意:在mysql5.7中开启binlog必须要加上server-查看id。

查看二进制日志

[root@servera data]# ll /application/mysql/data/
-rw-rw---- 1 mysql mysql 285 April 26 2023 mysql-bin.000001
#物理查看
mysql> show binary logs;
mysql> show master status;
#命令行查看
mysql> show binlog events in 'mysql-bin.000001';
#查看binlog事件

刷新binlog日志

mysql> flush logs;

删除二进制日志

根据存在时间删除日志：

#临时生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7

使用purge命令删除：

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;

根据文件名删除：

PURGE BINARY LOGS TO 'mysql-bin.000001';

用reset master：

mysql> reset master;
#将binlog文件从1从头开始

注意：要是直接 rm -rf 删除可能会导致mysql内部报错哦。

row模式下二进制日志数据恢复实战

创库删库：

mysql> show master status;
# 查看binlog信息
mysql> create database binlog;
# 创建一个binlog库
mysql> use binlog
# 使用binlog库
mysql> create table binlog_table(id int);
# 创建binglog_table表
mysql> show master status;
# 查看binlog信息
mysql> insert into binlog_table values(1);
# 插入数据1
mysql> show master status;
# 查看binlog信息
mysql> commit;
# 提交
mysql> show master status;
# 查看binlog信息
mysql> insert into binlog_table values(2);
# 插入数据2
mysql> insert into binlog_table values(3);
#插入数据3
mysql> show master status;
# 查看binlog信息
mysql> commit;
# 提交
mysql> delete from binlog_table where id=1;
# 删除数据1
mysql> show master status;
# 查看binlog信息
mysql> commit;
# 提交
mysql> update binlog_table set id=22 where id=2;
# 更改数据2为22
mysql> show master status;
# 查看binlog
mysql> commit;
# 提交
mysql> show master status;
# 查看binlog信息
mysql> select * from binlog_table;
# 查看数据
mysql> drop table binlog_table;
# 删表
mysql> drop database binlog;
# 删库跑路

查看binlog事件，找到删除命令的具体位置，恢复数据库

mysql> show binlog events in 'mysql-bin.000001';
# 查看binlog事件
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000001
[root@db01 data]# mysqlbinlog /application/mysql/data/mysql-bin.000001|grep
-v SET
[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysqlbin.000001
# 使用mysqlbinlog来查看
# 查看二进制日志后，发现删除开始位置是858
[root@db01 data]# mysqlbinlog --start-position=120 --stop-position=858
/application/mysql/data/mysql-bin.000001 > /tmp/binlog.sql
#建立一个新的没有删除命令的sql文件，之后导入数据库完成恢复工作
mysql> set sql_log_bin=0;
#临时关闭binlog
mysql> source /tmp/binlog.sql
#执行sql文件
mysql> show databases;
#查看删除的库
mysql> use binlog
#进binlog库
mysql> show tables;
#查看删除的表
mysql> select * from binlog_table;
#查看表中内容

慢查询日志

是将mysql服务器中影响数据库性能的相关SQL语句记录到日志文件

通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的

默认位置：MYSQL_HOME/data/MYSQLHOME/data/$hostname-slow.log

开启方式（默认没有开启）

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

分析慢查询日志

模拟慢查询语句

mysql> use world
#进入world库
mysql> show tables
#查看表
mysql> create table t1 select * from city;
#将city表中所有内容加到t1表中
mysql> desc t1;
#查看t1的表结构
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
mysql> insert into t1 select * from t1;
#将t1表所有内容插入到t1表中（多插入几次）
mysql> delete from t1 where id>2000;
#删除t1表中id>2000的数据
[root@db01 ~]# cat /application/mysql/data/slow.log
#查看慢日志

使用mysqldumpslow命令来分析慢查询日志

[root@servera ~]# mysqldumpslow -s c -t 10 /application/mysql/data/slow.log
#输出记录次数最多的10条SQL语句
[root@servera ~]# mysqldumpslow -s r -t 10 /application/mysql/data/slow.log
#得到返回记录集最多的10个查询
[root@servera ~]# mysqldumpslow -s t -t 10 -g "left join"
/application/mysql/data/slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句

参数说明:
-s：是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记
录数来排序，ac、at、al、ar，表示相应的倒叙；

-t：是top n的意思，即为返回前面多少条的数据；

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

第三方推荐（扩展）

[root@servera ~]# wget https://downloads.percona.com/downloads/perconatoolkit/3.3.1/binary/redhat/7/x86_64/percona-toolkit-3.3.1-1.el7.x86_64.rpm
[root@servera ~]# yum localinstall -y percona-toolkit-3.3.1-1.el7.x86_64.rpm

使用percona公司提供的pt-query-digest工具分析慢查询日志

[root@servera ~]# pt-query-digest /application/mysql/data/slow.log

the journey of you