MySQL日志管理工具
1️⃣ 错误日志
2️⃣ binlog二进制日志
3️⃣ slowlog 慢日志
一、 错误日志
1、查看方式
# 查看日志的路径
mysql> select @@datadir;
+--------------------+
| @@datadir |
+--------------------+
| /mysql/data/mysql/ |
+--------------------+
1 row in set (0.00 sec)
# 错误日志存放位置
[root@mysql-106 mysql]# ll /mysql/data/mysql/mysql-106.err
-rw-rw---- 1 mysql mysql 57902 Jul 20 09:14 /mysql/data/mysql/mysql-106.err
2、自定义错误日志
[root@mysql-106 logs]# cat /etc/my.cnf
[mysqld] # 注意在mysqld服务端添加配置
user=mysql
basedir=/mysql/softwares/mysql
datadir=/mysql/data/mysql
socket=/tmp/mysql.sock
log_error=/mysql/logs/mysql_error.log # 添加错误日志
[mysql] # 客户端
socket=/tmp/mysql.sock
# 创建相关的文件，并授权
[root@mysql-106 ~]# touch /mysql/logs/mysql_error.log
[root@mysql-106 ~]# chown mysql:mysql /mysql/logs/mysql_error.log
[root@mysql-106 ~]# ll /mysql/logs/mysql_error.log
-rw-r--r-- 1 mysql mysql 0 Jul 20 09:36 /mysql/logs/mysql_error.log
# 重启MySQL
[root@mysql-106 ~]# systemctl restart mysqld
二、binlog二进制日志
2.1、作用
1、 数据恢复，主从复制
2、 binlog日志主要记录对数据库操作（DDL,DCL,DML）的日志
3、 逻辑层性质日志
2.2、配置binlog二进制日志
默认： 8.0版本以前，没有开启。（生产环境一定要开启）
配置方法：
vim /etc/my.cnf
server_id=6 # 主机编号，主从中使用，5.7以后开启binlog要此参数
log_bin=/mysql/logs/binlog/mysql_bin_log #日志存放目录+日志名前缀，例如mysql_bin_log.0000001，mysql_bin_log.0000002
sync_binlog=1 # binlog日志刷盘策略，双一种的第二个1.每次事务提交立即刷写binlog到磁盘
binlog_format=row #binlog的记录格式为row模式
创建相关的文件及目录：
[root@mysql-106 logs]# cd /mysql/logs/
[root@mysql-106 logs]# mkdir binlog
[root@mysql-106 logs]# touch binlog/mysql_bin_log
[root@mysql-106 logs]# chown -R mysql:mysql binlog/
[root@mysql-106 logs]# ll binlog/
total 8
-rw-r--r-- 1 mysql mysql 0 Jul 20 10:16 mysql_bin_log
重启生效：
[root@mysql-106 ~]# systemctl restart mysqld
[root@mysql-106 logs]# ll binlog/
total 8
-rw-r--r-- 1 mysql mysql 0 Jul 20 10:16 mysql_bin_log
-rw-rw---- 1 mysql mysql 120 Jul 20 10:21 mysql_bin_log.000001
-rw-rw---- 1 mysql mysql 40 Jul 20 10:21 mysql_bin_log.index
注意：
1、日志的存放位置一定要有，并且权限是mysql。
2、 在生产环境要和数据盘分开，避免都坏掉(不同的磁盘)
例如：
/dev/sdb --/data/mysql
/dev/sdc --/data/binlog
2.3、binlog记录内容详解
binlog是SQL层的功能，记录的是变更SQL语句，不记录查询语句
记录SQL语句种类:
DDL: 原封不动的记录当前DDL（statement语句方式）。
DCL: 原封不动的记录当前DCL（statement语句方式）。
DML: 只记录已经提交的事务DML（如： insert , update ， delete）
DML三种记录方式：
binlog_format（binlog的记录方式）参数影响
1、 statement（5.6默认）SBR（statement based replication）: 语句模式原封不动的记录当前DML
2、 ROW（5.7默认值）RBR（ROW based replication）:记录数据行的变化（用户看不懂，需要工具分析）
3、 mixed（混合）MBR（mixed based replication）模式 : 以上两种模式的混合
面试题：
SBR与RBR模式的对比
statement : 可读性较高，日志量少，但是不够严谨
ROW : 可读性很低，日志量大，足够严谨
event（事件）的简介：
二进制日志的最小单元
对于DDL，DCL，一个语句就是一个event
对于DML语句来讲：只记录已提交的事务。
例如一下例子，就是被分为了4个event
position
start stop
begin 120 -340
DML1 340 -460
DML2 460 -550
commit 550 -760
开始标识: at 194
结束标识: end_log_pos 254
194? 254?
某个时间在binlog中的相对位置号
位置号（position）的作用是什么？
为了方便我们截取事件
2.4、binlog的查看
mysql> show variables like '%base%';
+------------------------+-------------------------------------------------------+
| Variable_name | Value |
+------------------------+-------------------------------------------------------+
| basedir | /mysql/softwares/mysql-5.6.51-linux-glibc2.12-x86_64/ |
| character_set_database | latin1 |
| collation_database | latin1_swedish_ci |
| log_bin_basename | /mysql/logs/binlog/mysql_bin_log |
| relay_log_basename | |
| skip_show_database | OFF |
+------------------------+-------------------------------------------------------+
# 查看binlog日志文件的位置
mysql> select @@log_bin_basename;
+----------------------------------+
| @@log_bin_basename |
+----------------------------------+
| /mysql/logs/binlog/mysql_bin_log |
+----------------------------------+
1 row in set (0.00 sec)
# 查看事务开启情况（如果为1，开启）
mysql> select @@log_bin;
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
1 row in set (0.01 sec)
# 文件查看
[root@mysql-106 ~]# ll /mysql/logs/binlog
total 8
-rw-r--r-- 1 mysql mysql 0 Jul 20 10:16 mysql_bin_log
-rw-rw---- 1 mysql mysql 120 Jul 20 10:21 mysql_bin_log.000001
-rw-rw---- 1 mysql mysql 40 Jul 20 10:21 mysql_bin_log.index
# .index存放着当前正在使用的日志的位置点
[root@mysql-106 ~]# cat /mysql/logs/binlog/mysql_bin_log.index
/mysql/logs/binlog/mysql_bin_log.000001
# 查看文件的类型
[root@mysql-106 ~]# file /mysql/logs/binlog/mysql_bin_log.000001
/mysql/logs/binlog/mysql_bin_log.000001: MySQL replication log
2.5、 二进制内置事件查看命令
# 打印当前在服役的二进制日志，有多少个
mysql> show binary logs;
# 查看当前正在使用的二进制日志
mysql> show master status;
# 查看二进制日志事件情况
mysql> show binlog events in 'mysql_bin_log.000001';
⭕️ 添加数据库有相应的变化
# 命令行的查看及过滤[root@mysql-106 ~]# mysql -uroot -p123 -e "show binlog events in 'mysql_bin_log.000001'" |grep DROP
2.6、内容查看
[root@mysql-106 binlog]# mysqlbinlog mysql_bin_log.000001 > /tmp/a.sql[root@mysql-106 binlog]# vim /tmp/a.sql# 命令翻译DML语句[root@mysql-106 binlog]# mysqlbinlog --base64-output=decode-rows -vvv mysql_bin_log.000001 > /tmp/a.sql
2.7、 日志截取恢复
注意： 只是一个小的案例，我们在生产环境中不会用这种方法。1、 日志恢复案例：mysql> flush logs; # 滚动一个新的日志2、 模拟数据环境：mysql> create database bindb charset utf8mb4;mysql> use bindbmysql> create table t1 (id int);mysql> begin;mysql> insert into t1 values(1),(2),(3),(4),(5);mysql> commit;mysql> begin;mysql> insert into t1 values(13),(32),(33),(43),(53);mysql> commit;mysql> begin;mysql> insert into t1 values(113),(322),(332),(434),(533);mysql> commit;mysql> begin;mysql> insert into t1 values(1513),(3242),(3232),(4434),(5334);mysql> commit;3、 删除数据库mysql> drop database bindb ;Query OK, 1 row affected (0.01 sec)4、数据恢复4.1 分析binlog日志起点: 226 mysql> show master status; mysql> show binlog events in 'mysql_bin_log.000002'; | mysql_bin_log.000002 | 226 | Query | 6 | 339 | create database bindb charset utf8mb4 |终点: 1489 | mysql_bin_log.000002 | 1489 | Query | 6 | 1584 | drop database bindb4.2 截取日志# 导出[root@mysql-106 binlog]# mysqlbinlog --start-position=226 --stop-position=1489 /mysql/logs/binlog/mysql_bin_log.000002 >/tmp/bin.sql# 恢复日志mysql> set sql_log_bin=0; # 先临时关闭binlog日志的记录Query OK, 0 rows affected (0.00 sec)mysql> source /tmp/bin.sql # 导入到数据库mysql> set sql_log_bin=1; # 开启binlog日志记录4.3 检查是否恢复mysql> select * from bindb.t1;+------+| id |+------+| 1 || 2 || 3 || 4 |
2.8、binlog维护操作
2.8.1、 日志滚动mysql> flush logs;mysql> select @@max_binlog_size; # 默认1G滚动一起+-------------------+| @@max_binlog_size |+-------------------+| 1073741824 |+-------------------+1 row in set (0.00 sec)[root@mysql-106 binlog]# mysqladmin -uroot -p123 flush-logs- 重启数据库也会自动滚动2.8.2、 日志的删除2.8.3、 自动删除机制mysql> select @@expire_logs_days; # 默认是0，单位是天，代表永不删除。设置自动删除的天数，一般设置两个全备周期+12.8.4、 手动删除purge binary logs to 'mysql_bin_log.000005' #删除1-52.8.5、 全部清空mysql> reset master注意！！！ 比较危险，在主库执行次操作，主从必宕。
三、binlog的GTID管理模式
3.1 GTID介绍
5.6版本新加的特性，5.7 8.0中做了加强 5.6中不开启，没有这个功能。 5.7中的GTID，即使不开也会有自动生成 SET @@SEISSION.GTID_NEXT='ANONYMOUS'# 有了Gtid就可以在多文件过滤时，过滤出我们想要的日志信息3.1.1 GTID（Global Transaction ID） 是对于一个已提交事务的编号，并且是一个全局唯一的编号。 它的官方定义如下： GTID = server_uuid:transaction_id a96dbf35-e08c-11eb-a5c5-000c29feac00:29
3.2 重要参数介绍及配置：
vim /etc/my.cnfgtid-mode = on # 打开功能log-slave-updates=ON # 5.6 加这个参数enforce-gtid-consistency=ON #校验GTID的一致性3.2.1 重启mysqld：[root@mysql-106 ~]# systemctl restart mysqld3.2.2 查看是否开启成功:mysql> select @@gtid_mode;+-------------+| @@gtid_mode |+-------------+| ON |+-------------+1 row in set (0.00 sec)3.2.3 查看UUID:mysql> select @@server_uuid;+--------------------------------------+| @@server_uuid |+--------------------------------------+| a96dbf35-e08c-11eb-a5c5-000c29feac00 |+--------------------------------------+1 row in set (0.00 sec)
⭕️ DDL和DML的记录方式
3.3 基于GTID进行查看binlog
3.3.1 具备GTID后，截取查看某些事务日志：--include-gtids # 包含的Gtid--exclude-gtids # 排除Gitd--ship-gtids3.3.2 例子：# 准备环境mysql> show master status; # 查看当前在用的binlog日志mysql> create database gtid_text3 charset utf8mb4;mysql> use gtid_text3mysql> create table t1(id int);mysql> begin;mysql> insert into t1 values(1),(2),(3);mysql> commit;mysql> flush logs; # 滚动日志mysql> show master status; # 查看当前binlog日志，你会发现Gtid是从最开始记录着mysql> create table t2(id int);mysql> begin;mysql> insert into t2 values(1),(2),(3);mysql> commit;mysql> flush logs;mysql> show master status;mysql> create table t3(id int);mysql> begin;mysql> insert into t3 values(1),(2),(3);mysql> commit;mysql> flush logs;mysql> show master status;mysql> drop database gtid_text3;
# 截取日志起点：4mysql> show master status;mysql> show binlog events in 'mysql_bin_log.000006';终点：15mysql> show master status;mysql> show binlog events in 'mysql_bin_log.000010';
确认截取gtid范围: 4-15文件截取范围: mysql_bin_log.000006 --到--- mysql_bin_log.000010 开始截取恢复：# 查询uuid,mysql> select @@server_uuid;mysqlbinlog --include-gtids='a96dbf35-e08c-11eb-a5c5-000c29feac00:4-15' mysql_bin_log.000006 mysql_bin_log.000007 mysql_bin_log.000008 mysql_bin_log.000009 mysql_bin_log.000010 >/tmp/gtid1.sql
# 恢复数据库mysql> set sql_log_bin=0; # 先临时关闭binlog日志mysql> source /tmp/gtid1.sql # 导入数据库
3.4 GTID的幂等性
从下图可以看出，我们的Gtid已经执行到16了，因为开启GTID后，binlog就具备了幂等性，重复GTID的事务不会在执行了，所以我们上边的执行会报错。那么怎么办呢？加上 --skip-gtids 这个参数，跳过gtid的检查
⭕️ 加--skip-gtids 和不加--skip-gtids对比
# 再次恢复[root@mysql-106 binlog]# mysqlbinlog --skip-gtids --include-gtids='a96dbf35-e08c-11eb-a5c5-000c29feac00:4-15' mysql_bin_log.000006 mysql_bin_log.000007 mysql_bin_log.000008 mysql_bin_log.000009 mysql_bin_log.000010 >/tmp/gtid2.sqlmysql> set sql_log_bin=0; # 临时关闭binlog日志mysql> source /tmp/gtid2.sql # 导入数据mysql> show databases; # 检查是否有gtid_text3这个数据库注意：mysql> set sql_log_bin=1; # 要开启binlog日志参考下图：
四、slowlog 慢日志
4.1 作用
1、记录这MySQL运行过程中较慢的语句，通过一个文本的文件记录；2、帮助我们进行语句优化工作日志；
4.2 配置slowlog慢日志
配置参数：# 默认慢日志是关闭的（0是关闭，1是开启）。mysql> select @@slow_query_log; # 查看文件存放的位置mysql> select @@slow_query_log_file;# 慢语句认定时间阈值，只要超过10秒就认为是慢语句mysql> select @@long_query_time;# 不走索引的语句记录mysql> select @@log_queries_not_using_indexes;# 配置my.cnfvim /etc/my.cnfslow_query_log=1slow_query_log_file=/mysql/logs/slowlog/mysql-106-slow.loglong_query_time=0.1log_queries_not_using_indexes=1# 重启数据库生效[root@mysql-106 binlog]# systemctl restart mysqld
4.3 模拟慢语句
执行一下SQLselect * from t100w limit 500000,10;select * from t100w limit 600000,5;select * from t100w limit 600000,2;select * from t100w limit 600000,2;select id ,count(num) from t100w group by id limit 10;select id ,count(num) from t100w group by id limit 5;select id ,count(num) from t100w group by id limit 2;select id ,count(num) from t100w group by id limit 2;select id ,count(k1) from t100w group by id limit 1;select id ,count(k1) from t100w group by id limit 1;select k2 ,sum(id) from t100w group by k2,k1 limit 1;select k2 ,sum(id) from t100w group by k2,k1 limit 1;select k2 ,sum(id) from t100w group by k2,k1 limit 1;select k1 ,sum(id) from t100w group by k2,k1 limit 1;select k1 ,count(id) from t100w group by k1 limit 10;
从上图可以看到存在一些问题：1、有很多同一类型的，我们优化一次就够了，不用反复的去看（怎么定位，轻重缓急？ 我们可以按次数去排序）2、没有轻重缓急，哪些是我们先要去处理的3、综上所述，这样查看很显然不是很方便；4、所以我们要借助一些工具，mysqldumpslow
4.4 慢语句的分析
[root@mysql-106 slowlog]# mysqldumpslow -s c -t 5 /mysql/logs/slowlog/mysql-106-slow.log -s 将统一类型的先聚合再统计，按次数排序-t 取排名靠前的
总结上述内容
1、错误日志
1.1 定位MySQL，工作过程汇总的故障，生产环境中建议不要和数据目录放在一起log_error=/mysql/logs/mysql_error.log
2、二进制日志
2.1 作用： 1、数据恢复 2、主从复制2.2 配置： log_bin=/mysql/logs/binlog/mysql_bin_log server_id=6 binlog_format=row sync_binlog=12.3 双一说明： # 每次事务提交，必然log buffer 中redo 落到磁盘。 innodb_flush_log_at_trx_commit=1 # 每次事务提交，必然保证binlog cache 中的日志落到磁盘。 sync_binlog=12.4 重点命令：# 事件查看命令 show master status; # 查看二进制日志正在使用的，以及gtid信息 show binlog events in '' # 查看二进制事件信息 show binlog events in '' limit 100 # 查看前多少行以上一般是同时使用的2.5 无gtid： mysql -uroot -p123 -e "show binlog events in 'mysql_bin_log.000001'" |grep DROP mysqlbinlog --start-position=xxx --stop-position=xxx /mysql/logs/binlog/mysql_bin_log.000002 >/tmp/bin.sql2.6 有gtid： mysqlbinlog --skip-gtids --include-gtids='xxxx:4-15' mysql_bin_log.000006 mysql_bin_log.000007 mysql_bin_log.000008 mysql_bin_log.000009 mysql_bin_log.000010 >/tmp/gtid1.sql 2.7 恢复日志set sql_log_bin=0; # 准备恢复前临时关闭binlog日志source /tmp/xxx.sql # 导入数据set sql_log_bin=1; # 导入完成一定要记得开启binlog日志2.8 滚动日志：flush logs;
3、慢日志
3.1 配置： vim /etc/my.cnf slow_query_log=1 slow_query_log_file=/mysql/logs/slowlog/mysql-106-slow.log long_query_time=0.1 log_queries_not_using_indexes=13.2 分析工具： mysqldumpslow -s c -t xxx