15--mysql日志管理
目录
一、日志分类
# 日志种类 # 作用
错误日志: 记录 MySQL 服务器启动、关闭及运行错误等信息
事务日志: 1、redo log重做日志 2、undo log回滚日志
查询日志: 记录查询的信息
慢查询日志: 记录执行时间超过指定时间的操作
二进制日志: 又称binlog日志，以二进制文件的方式记录数据库中除 SELECT 以外的操作
中继日志: 备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放
通用日志: 审计哪个账号、在哪个时段、做了哪些事件
二、错误日志
首次启动会提示错误日志位置 查询错误日志存放路径：
[root@db01 ~]# mysqladmin -uroot -p'cdan@123' variables | grep -w log_error
或者
[root@db01t ~]# mysql -uroot -pcdan@123
mysql> show variables like '%log_error%';
配置错误日志（默认就是启用的）
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
#绝对路径
log_error=/var/log/mysql.errlog
#相对路径
#log_error=mysql.errlog
#如果自己指定日志路径
[root@localhost ~]# touch /var/log/mysql.errlog
[root@localhost ~]# chmod 640 /var/log/mysql.errlog
[root@localhost ~]# chown mysql.mysql /var/log/mysql.errlog
[root@localhost ~]# systemctl restart mysqld
MySQL中，其中log_error定义是否启用错误日志的功能和错误日志的存储位置，此外还可以用参数控制 是否将告警信息（warning messages）也写入错误日志。在不同的版本中改控制参数有所不同
在MySQL 5.6中用log_warnings参数
log_warnings=0， 表示不记录告警信息。
log_warnings=1(默认值)， 表示告警信息写入错误日志。
log_warnings大于1，表示各类告警信息，例如有关网络故障的信息和重新连接信息写入错误日志。
mysql5.7新增的log_error_verbosity参数
# 它有三个可选值， 分别对应：
# log_error_verbosity=1：错误信息；
# log_error_verbosity=2：错误信息和告警信息；(推荐)
# log_error_verbosity=3（默认值就是3）：错误信息、告警信息和通知信息。
三 、事务的日志
事务日志主要分为redo log日志和undo log日志。
详见上篇 red log刷盘策略 和 undo log 表空间 的博客
四、 一般查询日志
1.默认是关闭的
一般不会开启，因为哪怕你开启事务一顿操作，最后不提交也会记录，生产上程序跑sql很多，会非常
非常占地方，从来都不启动，要看操作去binlog
2.开启
[root@db01 ~]# vim /etc/my.cnf
general_log=on
general_log_file=/var/log/select.log
#可以使用set global general_log=on;设置
[root@localhost ~]# touch /var/log/select.log
[root@localhost ~]# chmod 640 /var/log/select.log
[root@localhost ~]# chown mysql.mysql /var/log/select.log
[root@localhost ~]# systemctl restart mysqld
3.查看一般查询日志
[root@db01 ~]# mysqladmin -uroot -pcdan@123 variables|grep general_log
或者
[root@db01 ~]# mysql -uroot -pcdan@123
mysql> show variables like '%gen%';
五 、慢查询日志
5.1 慢日志作用
1）将mysql服务器中影响数据库性能的相关SQL语句(不论是什么语句，增删改查)记录到日志文件
2）通过对这些特殊的SQL语句分析并改进，提高数据库性能
5.2 配置慢日志
#默认慢日志是不开启的
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
#指定是否开启慢查询日志
slow_query_log = 1
#指定慢日志文件存放位置（默认在data）
slow_query_log_file=/var/log/slow.log
#设定慢查询的阀值(默认10s)
long_query_time=0.05
#不使用索引的慢查询日志是否记录到日志
log_queries_not_using_indexes=ON
#查询检查返回少于该参数指定行的SQL不被记录到慢查询日志，少于100行的sql语句查询慢的话不记
录，一般不使用
#min_examined_row_limit=100 # 鸡肋
执行下述命令
touch /var/log/slow.log
chmod 640 /var/log/slow.log
chown mysql.mysql /var/log/slow.log
systemctl restart mysqld
六 、二进制日志
6.1 二进制日志简介
二进制日志即binlog日志，记录了mysql数据库所有的dml,ddl语句事件（不包含select）。记录增删 改，也可以记录SQL语句及行记录变化，还可以记录这些操作事件；总之会记录所有修改操作的SQL语 句。
不要混淆以下三种日志：
1）general log：记录数据库里的所有SQL操作记录 （
2）redo log：只记录innodb存储引擎的修改日志 （
3）binlog：只记录server层面内部的修改情况。--select /show 不记录
6.2 开启binlog日志的好处
(1）数据恢复：可以基于时间点恢复，以及根据其进行增量与差异备份
（2）mysql主从复制，通过binlog实现数据复制
6.3 二进制日志的工作模式
（1）语句模式：binlog_format=statement（Mysql5.7.6之前的默认级别）
# 语句模式介绍
记录对数据库做出修改的sql语句,不记录该sql的上下文信息
例如
1、开启binlog日志后，我们在某个库下自定义函数，若想定义成功需要先临时设置配置项
set global log_bin_trust_function_creators=TRUE;
2、然后自定义函数
delimiter //
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END //
delimiter ;
3、最后执行一条插入语句
insert into emp(name) values(concat("egon",f1(1,2)));
如果使用statement模式，那么这条insert语句将会被记录到二进制日志中,而sql语句中依赖的f1的定义是不会记录下来的，f1只存在于当前库。
# 优点
不需要记录细到每一行数据的更改变化，因此，binlog日志量小，IO压力小，性能较高
例如:
一条语句修改了100万行，该模式下只需要记录下该条语句即可
# 缺点
日志中记录的sql语句可能有上下文依赖，此时脱离了当前数据库环就无法运行了，因此该模式下容易出现主从不一致的问题。
例如：
主库记录的某条sql语句引用了主库中的函数、触发器、存储过程等特殊功能
在从库上接收了该sql之后，可能就无法正确运行，从而主从库数据不一致的问题。
ps：row模式是基于每一行来记录变化的，所以不会出现类似的问题。
# 应用场景
sql语句对mysql内置功能依赖比较少：不使用存储过程/触发器/函数，可以使用该模式，否则还是推荐行级模式
（2）***行级模式：binlog_format=row，（mysql5.7.6之后+8.0的默认级别）
# 行级模式介绍
记录每一行数据修改的细节，即哪一条记录被修改了，修改成什么样了
例如：执行语句（f1参照上例，此处略）
insert into t1(name) values(concat("egon",f1(1,2)));
如果使用row模式，那么日志中会记录插入了一条新记录，记录中的name字段值为'egon3'
# 优点
相当于把上下文依赖都记录了下来，可以更方便查看每一条数据修改的细节，并且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题，即该模式下主从复制强一致，数据最安全。
# 缺点
日志量大
例如
一条语句修改了100万行，语句模式下只需要记录一条语句即可
而行级模式却修改记录下100万行的修改记录，binlog日志的量可能会大得惊人。
# 应用
sql语句对mysql内置功能依赖比较多，希望数据最安全，复制强一致的场景推荐行级模式
（3）混合模式：binlog_format=mixed，一般不用
# 混合模式介绍
结合 row level 与statement level的优点
混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行
级模式记录，这些特殊情况包含但不限于以下情况。
•当函数中包含UUID()时。
•当表中有自增列（AUTO_INCREMENT）被更新时。
•当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。
•当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。
# 应用场景
看上去这种方式似乎比较美好，但是在生产环境中，为了保险起见，一般会使用row模式。
6.4 binlog日志相关概念
二进制日志文件，顾名思义，它是二进制的，所以我们不能直接使用cat命令进行查看，而是需要通过一 些别的命令查看其内容，而且，二进制日志文件，有”事件”和”位置”的概念，什么是事件呢？
(1) 事件events
通俗的讲，我们可以把binlog中的每一条记录当做一个”事件”，因为binlog记录了所有对数据库进行的修 改，所以，我们可以认为，数据库的修改被记录到二进制日志中，这些记录每一条都可以理解为一个”事 件”。
(2) 位置position
由于二进制日志文件是二进制的，所以，我们可以把整个二进制文件想象成一个字节序列。假设，二进 制日志文件刚开始是空的，从第1个字节开始记录，假设记录第一个”事件”（第一条记录），需要15个字 节，那么第一个事件的开始”位置”就是1，结束”位置”就是15，由于前15个字节已经被第一个事件占用， 那么当我们想要通过二进制日志记录第二个事件时，则需要从第15个字节向后开始记录，假设记录第二 个”事件”需要20个字节，那么第二个事件在binlog中的起始”位置”就是15，结束”位置”就是35，以此类 推。
6.5 二进制日志相关参数
(1) server_id
服务ID,主从库必须不一样，建议数字为:ip+端口，5.7.3以后版本,必须指定
(2) log-bin
此变量用于控制是否开启二进制日志，而且这是一个只读变量，默认值为OFF当我们启动数据库以后，在当前数据库连接中查看此变量的值，此变量值可能为OFF，表示不记录二进制日志，如果想要记录二进制日志，只需将此值设置为二进制日志的文件名即可，但是需要注意的是，我们无法在当前会话中使用set命令设置log_bin的值，因为它是一个只读变量，我们只能通过修改my.cnf的方式，设置log_bin的值，假设，我们编辑my.cnf文件，设置log_bin的值为mybinlog，那么，在mysql的数据目录中，将会自动生成一个以mybinlog为文件名前缀的二进制日志文件，如果想要再次禁用binlog，只需要将log_bin这一行配置从my.cnf文件中注释即可，或者将其删除，重启mysql服务后，再次查看log_bin的值，其值为OFF，注意，不要直接在my.cnf文件中将log_bin的值设置为ON或者OFF，如果这样做，你将会看到以ON或者OFF为文件名前缀的二进制日志文件。换句话说，如果my.cnf配置文件中没有log_bin的配置，则表示未开启二进制日志，如果my.cnf中存在log_bin的配置，那么则表示开启了二进制日志，同时二进制日志文件的名称将会以log_bin对应的值为文件名前缀，同时，二进制日志文件的后缀名会进行自动编号，每次日志滚动后，后缀名编号自动加1。
示例：
log-bin=/var/lib/mysql/mybinlog #绝对路径
# log-bin=mybinlog #也可以用相对路径， 会在$datadir下产生mysqlbinlog-00000N
(3) log_bin_index
不设置的话，会根据log_bin值名称自动生成mybinlog.index
log_bin_index=var/lib/mysql/mybinlog.index
(4) sql_log_bin
默认为ON
此变量用于标识当前会话中的操作是否会被记录于二进制日志，此变量值设置为ON，则表示在当前数据库连接中，对数据库进行修改的语句将会被记录到binlog中，此变量值设置为OFF，则表示在当前数据库连接中，对数据库进行的修改的语句将不会被记录到binlog中，在主从复制结构中，这些语句由于没有被binlog记录，所以也不会同步到从节点中。换句话说，即使在my.cnf配置文件中设置了log_bin的值，当前会话中，如果sql_log_bin的值设置为OFF，当前会话的操作也不会记录在二进制日志中。而且需要注意的是，sql_log_bin是一个会话界别的变量，只能在当前会话中使用set sql_log_bin
命令进行设置，不能使用set global sql_log_bin命令进行设置，因为它是会话级别的变量，而且，sql_log_bin也不能配置在my.cnf文件中，否则可能会无法启动mysql。
(5) binlog_format
此变量的值决定了二进制日志的记录方式，此变量的值可以设置为statement、row、mixed，分别表示以语句的形式记录二进制日志，以数据修改的形式记录二进制日志，以混合的方式记录二进制日志，安全保险起见，推荐使用row的方式记录。
(6) max_binlog_size
设置单个二进制日志文件的最大大小，以字节为单位，超过此值大小，则二进制日志文件会自动滚动，
比如设置为500M为524288000。
(7) sync_binlog
在存储引擎章节中我们提过：
innodb_flush_log_at_trx_commit控制redo日志的刷盘策略，建议值为1
sync_binlog控制binlog日志的刷盘策略，建议值为1
当我们把innodb_flush_log_at_trx_commit设置为1的时候，表示事务提交时，事务日志立马从内存刷写到磁盘中的事务日志文件中，而sync_binlog对于二进制日志的作用，就像innodb_flush_log_at_trx_commit对于事务日志的作用，由于二进制日志一开始存在于内存（binlog_cache）中，如果将sync_binlog设置为1，则表示每1次事务提交之后，都会立即将内存中的二进制日志立即同步到磁盘中的二进制日志文件中，如果将sync_binlog设置为0，则表示当事务提交之后，mysql不会立即将内存中的binlog刷写到磁盘中的binlog日志文件中，而是由文件系统决定什么时候刷写，这可能取决于文件系统的缓存机制，当此值设置为0时，一旦操作系统宕机，那么将丢失未从内存中同步到磁盘中的binlog，所以，当此值设置为0时，安全性最差，但是性能最高，当此值设置为1时，安全性最高，性能最差，除了将此值设置为0或1，还能设置为N，假设将此值设置为3，则表示每3次事务提交后，将binlog从内存刷写到磁盘一次，值设置的越大，有可能丢失的日志数据将会越多，当然，性能会越好，在追求安全的情况下，推荐设置为1，但是听说，此值设置为0和设置为1时在性能上的差距还是比较明显的，如果设置为0或N，最好为操作系统准备带有备用电源的缓存。
(8) 其他参数
#打开才能查看详细记录，默认为off
binlog_rows_query_log_events=on
#表示自动删除10天以前的日志
expire_logs_days=10
# full,minimal,noblob分别表示binlog中内容全记录，只记录被操作的，和不记录二进制
binlog_row_image=full #（full,minimal,noblob），
6.5 查看binlog配置项
show variables like '%log_bin%';
show variables like '%binlog%';
show variables like '%binlog_format%';
show variables like '%server%';
show variables like 'expire_logs_days'; -- 过期日志天数
# 或者
[root@db01 ~]# mysqladmin -uroot -pcdan@123 variables |grep -w log_bin
6.6 开启binlog
1.默认是关闭的
2.配置开启binlog
vim /etc/my.cnf
[mysqld]
server_id=1
log-bin=/var/lib/mysql/mybinlog
binlog_format='row' #(row,statement,mixed)，不建议随意去修改binlog工作模式
binlog_rows_query_log_events=on
max_binlog_size=100M
[root@db02 ~]# systemctl restart mysqld
[root@db02 ~]# cd /var/lib/mysql
[root@db02 mysql]# ll #此时查看多了binlog日志文件
-rw-r----- 1 mysql mysql 154 Jul 19 16:02 mybinlog.000001
[root@db02 ~]# mysqlbinlog mybinlog.000001 #查看binlog日志文件
6.7 查看binlog日志
查看日志日志名、状态、事件
show binary logs;
show master logs;
show master status;
show binlog events in 'mybinlog.000002';
show binlog events in 'mybinlog.000001' limit 3;
查看日志内容
# 1、执行下述sql
create database if not exists test;
use test;
create table user(name varchar(10));
begin;
insert into user values("egon1"),("egon2"),("egon3");
update user set name="EGON" where name="egon1";
commit;
show master status; -- binlog的当前position
# 查看binlog，看看有没有记录，二进制文件我们看不了，有专门的命令
查看全部：
# mysqlbinlog mybinlog.000002
按时间：
# mysqlbinlog mybinlog.000002 --start-datetime="2022-11-05 10:02:56"
# mysqlbinlog mybinlog.000002 --stop-datetime="2022-11-05 11:02:54"
# mysqlbinlog mybinlog.000002 --start-datetime="2022-11-05 10:02:56" --stop-datetime="2022-11-05 11:02:54"
按字节数：
# mysqlbinlog mybinlog.000002 --start-position=337
# mysqlbinlog mybinlog.000002 --stop-position=662
# mysqlbinlog mybinlog.000001 --start-position=745 --stop-position=749
# mysqlbinlog mybinlog.000001 --base64-output=decode-rows -vvv ----start-position=337 --stop-position=662|less
如果是行级模式，想要看懂详细内容则需要加上额外参数，但是仅用于看懂内容，如果要用于还原数
据，还是应该去掉额外的参数并将内容定位到文件中
#mysqlbinlog --base64-output=decode-rows -vvv mybinlog.000002 # 仅用于查看，不
能用于日后的数据恢复
#mysqlbinlog mybinlog.000002 --start-position=100 > /tmp/1.sql # /tmp/1.sql
可用于日后的数据恢复
6.8 binlolg可以用来恢复数据
#修改数据
begin;
update user set name="XXX" where name="egon2";
commit;
#发现自己修改错了
select * from user;
#回滚，回滚不了，已经提交了
rollback;
select * from user;
#一怒之下删表
drop table user;
#恢复数据:查看binlog数据的起始点与要恢复到的位置点，导出成SQL
mysqlbinlog mybinlog.000002 ----start-position=120 --stop-position=772 > /tmp/binlog.sql
mysql -uroot -pEgon@123 < /tmp/binlog.sql
6.9 刷新与清除二进制日志
(1) 刷新binlog
#刷新binlog：关闭当前的二进制日志文件并创建一个新文件
1）手动执行命令刷新
flush logs;
或者
mysqladmin -uroot -p flush-logs;
或者
mysql -uroot -pEgon@123 -e 'flush logs'
2）重启数据库时会刷新
3）二进制日志上限（max_binlog_size）;当binlog达到1G,自动刷新
(2) 清除binlog
# 清除二进制日志原则
在存储能力范围内，能多保留则多保留
基于上一次全备前的可以选择删除
1) 删除所有binlog，相当于重置
reset master;
2) 删除指定binlog名之前的所有binlog(保留指定的binlog)
purge binary logs to 'mybinlog.00003'; -- mybinlog.00003之前的都删除掉
3）删除日期之前的日志：手动执行
PURGE {MASTER | BINARY} LOGS BEFORE 'date' --用于删除日期之前的日志，BEFORE变量的
date自变量可以为'YYYY-MM-DD hh:mm:ss'格式
如：（MASTER 和BINARY 在这里都是等效的）
PURGE MASTER LOGS TO 'mybinlog.00003';
purge binary logs before '2021-07-13 19:11:00';
还可以做减法：如只保留3天的
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;
4）删除日期之前的日志：修改配置参数，让mysql自动执行
删除7天前的binlog
#临时生效
SET GLOBAL expire_logs_days = 7;
#永久生效
[root@db01 data]# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7
优化sql总结
如何定位运行速度慢的sql
- 开启慢查询日志
如何分析sql
- explain工具
如何优化sql
1.逻辑（能连表就不要子查询）-，再看rows是否变低
2.对范围大的sql建议：分段取、缓存
3.建立索引
- 尽量给占用空间小，并且重复度低
- 不要让索引字段参与运算
- 尽量减少范围（能分段取就不要一下就取）
- 能覆盖索引就不要回表（尽量用主键字段作为查询条件）
- 多条sql语句用的都是and连接的多个条件，而条件中都出现了某一个字段，可以创建联合索引
