MySql日志管理
## 1.MySQL常用日志种类
```bsh
1 错误日志（Error log）
#当数据库启动、运行、停止时产生该日志 ==>error.log
2 普通查询日志（General query log） #==>nginx access.log
客户端连接数据库执行语句时产生该日志
3 二进制日志（Binary log） *****
当数据库内容[发生改变时产生该日志]，也被用来实现主从复制功能
4 慢查询日志（Slow query log） *****
SQL语句在数据库查询超过指定时间产生该日志
```
## 2.错误日志
```bsh
1 介绍
记录数据库从启动以来,状态\报错\警告等.
2 查询和配置
mysql> show variables like '%log_error';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| log_error | ./db01.err |
+---------------+------------+
1 row in set (0.00 sec)
[root@db01 ~]# ll /data/3306/data/db01.err
-rw-r----- 1 mysql mysql 23653 4月 21 10:57 /data/3306/data/db01.err
配置:my.cnf
[mysqld_safe]
log-error = /data/3306/data/oldgboy.err
[root@db01 ~]# systemctl restart mysqld
默认路径:/data/3306/data/oldgboy.err
mysql> show variables like '%log_error';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| log_error | /data/3306/data/oldgboy.err |
+---------------+-----------------------------+
1 row in set (0.01 sec)
3 错误日志怎么用
每天定时巡检,主要关注[ERROR]和[WARNING]。
4 错误日志切割
[root@db01 data]# cd /data/3306/data/
[root@db01 data]# mv oldboy.err oldboy-$(date +%F).err
[root@db01 data]# mysqladmin -uroot -poldboy123 flush-logs
[root@db01 data]# ls|grep oldboy
oldboy-2022-04-21.err
oldboy.err
5 排查错误
新手安装数据库时，遇到数据库无法启动时的排查方法为：
1)先把错误日志文件清空，然后重新启动MySQL服务，
再查看日志文件报有什么错误，并根据错误日志进行处理。
2)如果无法解决，则删除数据文件，重新初始化数据库。
3)遇到错误，关键字百度。
```
## 3.普通日志(access.log) 了解
```bsh
1 介绍
默认是关闭的,因为会大量消耗磁盘IO.
可以记录MySQL中发生过的所有操作日志.一般用来调试.
mysql> show variables like '%general%';
+------------------+--------------------------+
| Variable_name | Value |
+------------------+--------------------------+
| general_log | OFF |
| general_log_file | /data/3306/data/db01.log |
+------------------+--------------------------+
2 如何配置
[mysqld]
general_log = ON
general_log_file = /data/3306/data/oldboy.log
[root@oldboy ~]# cat /data/3306/data/oldboy.log
/usr/local/mysql/bin/mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
2021-12-12T03:15:48.572994Z 8 Connect root@localhost on using Socket
2021-12-12T03:15:48.573597Z 8 Query select @@version_comment limit 1
2021-12-12T03:15:49.916995Z 8 Query show variables like '%general_log%'
2021-12-12T03:15:54.772789Z 8 Query show variables like '%general_log%'
2021-12-12T03:16:02.494293Z 8 Query show databases
2021-12-12T03:16:03.967361Z 8 Quit
3 普通查询日志作用
审计\调试
真正要审计,在客户端用堡垒机,跳板机审计.
实际生产工作中极少使用普通查询日志,最多临时开一下..
```
## 4.二进制日志 (binlog)
```bsh
1.介绍
以event形式,记录MySQL数据库中【变更类】的SQL操作日志(DDL\DCL\DML).
举例：
select,show不会记录
crate,drop会记录
2.作用
1)数据增量恢复
2)主从复制
3.参数及配置
mysql> show variables like '%log_bin%';
+---------------------------------+------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------+
| log_bin | ON |
| log_bin_basename | /data/3306/data/binlog |
| log_bin_index | /data/3306/data/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+------------------------------+
#4个价参
log_bin=1 #开关,是否开启binlog日志记录。
log_bin_basename=/data/3306/data/binlog #路径和名字。
log_bin_index =/data/3306/data/binlog.index #binlog文件名列表。
sql_log_bin = 1 #是否记录binlog,临时使用.
mysql> show variables like '%binlog%';
+------------------------------------------------+----------------------+
| Variable_name | Value |
+------------------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_format | ROW |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+------------------------------------------------+----------------------+
其他binlog参数：
sync_binlog=1 # 数据安全参数,数据落盘。性能差.
max_binlog_size # 最大binlog文件最大1G
max_binlog_ _size # 最大binlog缓存大小
binlog_cache_size # 当前binlog缓存大小.
binlog_format=row/statement/mixed # binlog日志记录格式,默认ROW格式,早期statement格式.
```
#### binlog_format格式有几种,区别.
```bsh
格式区别:
1.行模式row(RBR)(8.0默认)
日志记录每一行数据的真实变化，所以日志量会比较大,消耗IO大，但是记录足够准确.
2.statement(SBR)(5.6以前默认):
记录发生的语句本身,不会按行记录.日志量相对少.记录有可能不准确.
3.mixed混合模式
企业选择:row
注意:binlog_format #只影响DML语句,DDL和DCL都是Statement模式记录
```
## 5.binlog应用
#### 1)查看和分析binlog
```bsh
1）查看二进制日志文件和大小.
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 179 | No |
| binlog.000002 | 179 | No |
| binlog.000003 | 179 | No |
| binlog.000004 | 179 | No |
2）查看当前库记录二进制的文件及位置点.
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000020 | 490 | | | |
+---------------+----------+--------------+------------------+-------------------+
3）查看某个日志具体信息
mysql> show binlog events in 'binlog.000020';
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------+
| binlog.000020 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.26, Binlog ver: 4 |
| binlog.000020 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000020 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000020 | 235 | Query | 1 | 324 | BEGIN |
| binlog.000020 | 324 | Query | 1 | 459 | update oldboy.stu set sname="oldgirl" where age>20 |
| binlog.000020 | 459 | Xid | 1 | 490 | COMMIT /* xid=33 */ |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------+
4）查看分析binlog
mysqlbinlog # 解析binlog命令
--base64-output=decode-rows # 行模式查看
-vvv # 输出
binlog.000018
例子：
mysqlbinlog --base64-output=decode-rows -vvv binlog.000018
#按时间截取日志参数
start-datetime (No default value)
stop-datetime (No default value)
#按位置截取日志参数（推荐）
start-position 4
stop-position 18446744073709551615
#gtid参数
--skip-gtids ##忽略GTID
--include-gtids ##包含GTID
--exclude-gtids ##排除GTID
```
#### 2)数据损坏模拟和恢复--binlog应用增量恢复
```bsh
故障模拟:
mysql> flush logs; ##从新文件,从0位置开始.
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000020 | 156 | | | |
+---------------+----------+--------------+------------------+-------------------+
mysql> drop database oldboy;
mysql> create database oldboy;
mysql> use oldboy;
mysql> create table t1 (id int);
mysql> insert into t1 values(1);
mysql> insert into t1 values(2);
mysql> insert into t1 values(3);
mysql> select * from oldboy.t1;
mysql> drop table t1; #破坏语句
恢复过程:
mysql> show master status; ###确认日志文件命名为binlog.000021
mysql> show binlog events in 'binlog.000021';
```
```bsh
恢复方法1: 将记录的binlog文件解析成sql文件,然后编辑sql文件,删除破坏的drop命令.
cd /data/3306/data
mysqlbinlog --skip-gtids binlog.000021 >/tmp/tmp.sql
#删除破坏的drop语句
[root@db01 data]# sed -n '/^DROP TABLE/p' /tmp/tmp.sql
DROP TABLE `t1` /* generated by server */
[root@db01 data]# sed -i '/^DROP TABLE/d' /tmp/tmp.sql
[root@db01 data]# sed -n '/^DROP TABLE/p' /tmp/tmp.sql
还原:
mysql -uroot -poldboy123 oldboy </tmp/tmp.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
错误坑:如果解析不加--skip-gtids,会报错.
ERROR 1790 (HY000) at line 198: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.
```
```bsh
恢复方法2: 指定位置点恢复,恢复drop命令位置点以前的数据,drop语句不恢复.
截取传统binlog日志(GTID)
cd /data/3306/data
mysqlbinlog --skip-gtids --stop-position=1706 binlog.000021 >/tmp/bin.sql
mysqlbinlog --skip-gtids --start-position=156 --stop-position=1706 binlog.000021>/tmp/bin.sql
mysql> set sql_log_bin=0; # 临时不记录binlog
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1; # 开启binlog.
mysql> use oldboy;
mysql> show tables;
+------------------+
| Tables_in_oldboy |
+------------------+
| t1 |
+------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
位置点记录binlog问题:位置是相对的,将来恢复可能是多个binlog文件,或者多台mysql的binlog文件。
```
## 6.GTID
#### 1)介绍
```bsh
GTID全称Global Transaction ID，全局事务ID，5.6开始起用的功能。
在整个binlog中每行日志加一个唯一ID值，不管有多少个binlog,都是连续生成的.
具备幂等性(运行过的就不会在运行了).
1ced0886-23d4-11eb-9768-000c29f4772b:1
server_uuid : NO.
server_uuid是自动生成的，如需改变/data/3306/data/auto.cnf删除。
```
#### 2)配置
```bsh
mysql> show variables like "%gtid_%";
set global gtid_mode=ON;
set global enforce_gtid_consistency=ON; #强制一致性。
[root@db01 data]# cat /data/3306/data/auto.cnf
[auto]
server-uuid=3d5846bb-bec3-11ec-bd1d-000c296ebb8f
```
#### 3)基于GTID截取日志
```bsh
截取1-6:6个事务
--skip-gtids ##忽略GTID
--include-gtids ##包含GTID
--exclude-gtids ##排除GTID
#假设1-6是drop语句
mysqlbinlog --skip-gtids --include-gtids='3d5846bb-bec3-11ec-bd1d-000c296ebb8f:1-5' /data/3306/data/binlog.000023 >/tmp/gtid.sql
#恢复1-8,排除6
mysqlbinlog --skip-gtids --include-gtids='3d5846bb-bec3-11ec-bd1d-000c296ebb8f:1-8' --exclude-gtids='3d5846bb-bec3-11ec-bd1d-000c296ebb8f:6' /data/3306/data/binlog.000023 >/tmp/gtid1.sql
注意:如果需要截取的日志需要在原库恢复,需要加--skip-gtids参数,跳过导出文件gtid的记录.
#恢复13-16 #17是drop语句
mysqlbinlog --skip-gtids --include-gtids='3d5846bb-bec3-11ec-bd1d-000c296ebb8f:13-16' /data/3306/data/binlog.000023 >/tmp/gtid.sql
#恢复13-17,排除17
mysqlbinlog --skip-gtids --include-gtids='3d5846bb-bec3-11ec-bd1d-000c296ebb8f:13-17' --exclude-gtids='3d5846bb-bec3-11ec-bd1d-000c296ebb8f:17' /data/3306/data/binlog.000023 >/tmp/gtid1.sql
注意:如果需要截取的日志需要在原库恢复,需要加--skip-gtids参数,跳过导出文件gtid的记录.
```
#### 4)binlog日志切割
```bsh
日志切换文件的触发机制:4种.
1)mysql内部命令刷新,启动新binlog记录日志
mysql> flush logs;
2)命令行mysqladmin刷新
[root@oldboy data]# mysqladmin -uroot -poldboy123 flush-logs
3）文件大小达到1G,自动切割
mysql> select @@max_binlog_size;
4）重启数据库
```
#### 5)binlog日志删除
```bsh
1.什么删除策略？ 每天/每周/每月,保留3天/7天??
生成环境中binlog删除策略:
1)取决于数据库的备份频率
每天全备,只留一天binlog日志就够了,建议3-7天。
每周一个备份，只留7天binlog日志就够了,建议7-15天。
PB,EB数据,一般会每周一个备份甚至一个月一次全备,binlog也要备份。
2.删除方法
a. 设置自动过期时间
mysql> show variables like '%_logs_%';
+----------------------------+---------+
| Variable_name | Value |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 | ##默认30天，8.0新参数
| expire_logs_days | 0 | ###8.0以前老的参数8.0可能不生效
+----------------------------+---------+
binlog_expire_logs_seconds=2592000 ###30天过期，默认值是一个月.
binlog_expire_logs_seconds=3600*24*7
mysql> select 3600*24*7;
+-----------+
| 3600*24*7 |
+-----------+
| 604800 |
+-----------+
b.手工清理
show BINARY LOGS;
##基于文件清理,删除14以前,不含14.
PURGE BINARY LOGS TO 'binlog.000014';
##按时间清理
PURGE BINARY LOGS BEFORE '2022-04-21 23:15';
c.全部重置
reset master;
[root@db01 data]# ls -l bin*
-rw-r----- 1 mysql mysql 156 4月 22 10:24 binlog.000001
-rw-r----- 1 mysql mysql 16 4月 22 10:24 binlog.index
能用rm -f在binlog日志目录下删除么? 尽量不要..
如果非要删,最后reset master;
```
##### 单机如何备份binlog
```bsh
1.rsync定时每分钟
2.rsync+sersync实时，基于文件系统。
3.mysqlbinlog --read-from-remote-server --raw --host=10.0.0.51 --port=3306 --user=repl --password=oldboy123 --stop-never binlog.000001 --result-file=/opt/
解释如下：
--read-from-remote-server：用于备份远程服务器的binlog。如果不指定该选项，则会查找本地的binlog。
--raw：binlog日志会以二进制格式存储在磁盘中，如果不指定该选项，则会以文本形式保存。
--user：复制的MySQL用户，只需要授予REPLICATION SLAVE权限。
--stop-never：mysqlbinlog可以只从远程服务器获取指定的几个binlog，也可将不断生成的binlog保存到本地。指定此选项，代表只要远程服务器不关闭或者连接未断开，mysqlbinlog就会不断的复制远程服务器上的binlog。
mysql-bin.000001：代表从哪个binlog开始复制。
实践：
mysql> create user repl@'10.0.0.%' identified by 'oldboy123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to repl@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在B机器上执行；
mysqlbinlog --read-from-remote-server --raw --host=10.0.0.51 --port=3306 --user=repl --password=oldboy123 --stop-never binlog.000001 --result-file=/opt/ &
```
## 7.慢查询日志
#### 1)介绍
```bsh
记录执行较慢的select查询语句.
DBA工作中，找出慢的SQL并优化，是DBA核心工作。
为什么要记录?
找到慢查询语句,让数据库运行效率更高.
```
#### 2)配置
```bsh
[root@db01 ~]# cat /etc/my.cnf
#by oldboy weixin:oldboy0102
[mysqld]
#慢查询
slow_query_log = ON #<==慢查询开启开关
long_query_time = 1 #<==记录大于1秒的SQL语句。
log_queries_not_using_indexes = ON #<==没有使用到索引的SQL语句。
slow-query-log-file = /data/3306/data/slow.log #<==记录SQL语句的文件。
min_examined_row_limit = 800 #<==记录结果集大于800行的SQL语句。
在线修改；
mysql> set global slow_query_log=on;
mysql> set global long_query_time=0.01;
mysql> set global log_queries_not_using_indexes=1;
mysql> select @@slow_query_log;
mysql> select @@long_query_time;
mysql> select @@log_queries_not_using_indexes;
```
#### 3)分析慢日志
```bsh
mysqlsla 和 mysqldumpslow慢查询分析工具外，还有一些第三方分析工具，
如：pt-query-diges、 myprofi、mysql-explain-slow-log、mysqllogfilter等等
```
```bsh
mysqldumpslow分析方法:
[root@oldboy data]# mysqldumpslow -s c -t 3 oldboy-slow.log
Reading mysql slow query log from oldboy-slow.log
Count: 4 Time=0.00s (0s) Lock=0.00s (0s) Rows=10.0 (40), root[root]@localhost
select * from t100w where num<N limit N
Count: 3 Time=0.55s (1s) Lock=0.00s (0s) Rows=20.0 (60), root[root]@localhost
select count(*) from t100w where num<N group by k2,k1 limit N
Count: 3 Time=0.53s (1s) Lock=0.00s (0s) Rows=13.3 (40), root[root]@localhost
select count(*) from t100w where num<N group by num,k1 limit N
mysqlsla分析方法：
Count : 23 (8.52%)
Time : 102 s total, 4.434783 s avg, 3 s to 7 s max (6.79%)
95% of Time : 88 s total, 4.190476 s avg, 3 s to 6 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 1 avg, 1 to 1 max (0.02%)
Rows examined : 11.53k avg, 5.70k to 17.60k max (1.07%)
Database : bbsdb
Users :
oldboy@ 172.16.1.51 : 86.96% (20) of query, 11.11% (30) of all users
bbs@ 172.16.1.52 : 13.04% (3) of query, 2.96% (8) of all users
Query abstract:
SELECT MIN(BBS_HI_id) AS BBS_HI_id FROM t_******** WHERE BBS_HI_isteammate=N AND BBS_HI_editor_user_id_encrypt='S';
Query sample:
select min(BBS_HI_ID) AS BBS_HI_ID from t_******** where BBS_HI_ISTEAMMATE=1 and BBS_HI_EDITOR_USER_ID_ENCRYPT='asdfEWERADFS';
```
```bsh
SQL优化流程:
运维[dba]-运维总监--研发项目程序员--核心架构师--研发经理--研发总监--CTO
```
```bsh
慢查询日志切割:
Date=`date +%F -d -1day`
cd /data/3306/data/slow &&\
mv slow.log slow.log_$Date &&\
mysqladmin -uroot -poldboy123 flush-log
慢查询切割脚本
#!/bin/bash
#Author: oldboy
#Organization: www.oldboyedu.com
#Created Time : 2018-03-19 23:47:21
export PATH=/data/3306/data/slow/bin:/sbin:/bin:/usr/sbin:/usr/bin
Date=`date +%F -d -1day`
#cut slow log # 切割
cd /data/3306/data/slow &&\
mv slow.log slow.log_$Date &&\
mysqladmin -uroot -poldboy123 flush-log
#analyze slow log # 分析
#Time=`date +%F`
#Path=/usr/local/bin/mysqlsla
#cd /data/3306/data/slow &&\
#$Path/mysqlsla -lt slow slow.log_$Date >analyzed_slow_$Date.log 2>&1
#rsync analyzed_slow to backup server同步到备份服务器
#备份服务器发邮件
#send analyzed slow log to adminstrator on backup server by mail.
自动化运维：
devops平台:
ELK日志手机，慢查询日志收集到ELK平台.
当然还可以把日志收集到数据库中或使用ELK等流行的工具收集慢查询日志，
最后分析后可视化展现，
```