日志分类

总共7种日志。

种类	作用
错误日志	启停/关闭及运行错误
事务日志	redolog / undolog
查询日志	耗费性能，通常不开
慢查询日志	大于某个时长的操作
二进制日志	除select之外的操作
中继日志	主二进制-->从中继日志-->从重演
通用日志	审计哪个账号哪个事件做了哪个动作

错误日志

查看

mysql -uroot -p variables | grep log_error;

• 使用相对路径时，默认在数据目录下存放
• 未主动指定目录时，mysql会自己指定目录

配置错误日志

错误日志使用log_error以及log_warnings等参数进行定义

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
#绝对路径
log_error=/var/log/mysql.errlog
#相对路径
#log_error=mysql.errlog

警告信息

告警信息是否需要记录

mysql5.6
log_warnings=0 # 不记录警告
log_warnings=1 # 默认值，在错误日志记录警告
log_warnings=2 # 所有的告警信息都写入错误日志

怎么选：设置为1合适一些，不浪费性能，也能获得更详细的警告信息，是一个优化参数

mysql5.7
# 有三个可选值， 分别对应：
# log_error_verbosity=1：错误信息；
# log_error_verbosity=2：错误信息和告警信息；(推荐)
# log_error_verbosity=3（默认值就是3）：错误信息、告警信息和通知信息。

事务的日志

redo log 是重做日志，提供再写入操作，实现事务的持久性；
undo log 是回滚日志，提供回滚操作，保证事务的一致性。

redo日志与undo日志分别用于记录什么?
redo如何保证事务的持久性？
undo如何保证事务的一致性？
undo log是否是redo log的逆过程?

innodb 更新操作：write ahead log，预先持久化日志，再写入磁盘。

redolog

第一步：InnoDB 会先把记录从硬盘读入内存
第二部：修改数据的内存拷贝
第三步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值
第四步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加写的方式
第五步：定期将内存中修改的数据刷新到磁盘中(注意注意注意，不是从redo log file刷入磁盘，而是从内存刷入磁盘，redo log file只在崩溃恢复数据时才用)，如果数据库崩溃，则依据redo log buffer、redo log file进行重做，恢复数据,这才是redo log file的价值所在

为什么不直接写入磁盘，而要写入redolog
如何保障每次修改都能写入redolog file
脏页何时刷入磁盘

• redolog满了，就会触发脏页刷新到磁盘
• 系统内存不足，需要淘汰内存数据页时
• mysql认为空闲的时候
• mysql正常关闭之前

脏页输入带来哪些性能问题：脏页刷新磁盘会被数据库带来性能开销，导致数据库操作抖动。

刷盘参数：innodb_flush_log_at_trx_commit

redolog参数：

• innodb_log_files_in_group：日志文件数量
• innodb_log_file_size：日志文件大小

undolog

存储更改前一份的copy。

• 可重复读：读的就是undo
• rollback：用的也是undo

存储位置：回滚段，存储在表空间（innodb_undo_tablespace设置位置）

类型

insert undolog 和 update undolog

undolog是否是redolog的逆过程？其实从前文就可以得出答案了，undolog是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子，而redolog是物理日志记录的是数据页的物理变化，显然undolog不是redolog的逆过程。

数据在没有commit前，是随时从内存中写入到表数据块的，属于脏数据。数据
库崩溃后即使使用redo流程进行redo操作，但是脏数据还在，脏数据怎么处理，就只能靠undo流程，使用undo数据块的旧数据覆盖。

与备份的区别

redo/undo 无法支援删库删表这种管理员操作，它针对的时

一般查询日志

作用

默认是关闭的，一般不会开启，因为哪怕你开启事务一顿操作，最后不提交也会记录，生产上程序跑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 -pEgon@123 variables|grep general_log

或者
[root@db01 ~]# mysql -uroot -pEgon@123
mysql> show variables like '%gen%';

慢查询日志

慢日志作用

1）记录超时的sql语句（增删改查都会记录），默认关闭，需要手动打开。
2）通过对这些特殊的SQL语句分析并改进，提高数据库性能

配置慢日志

• 关键点：慢日志开关、慢日志文件路径、慢日志阈值

#默认慢日志是不开启的

[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

慢日志经验

慢日志主是用于优化sql语句。

• 需要全表扫描的语句，无法优化
• 子查询语句可以改为链表查询，看rows是否降下来
• 查询条件添加索引列，如联合索引

哪些语句会被记录到慢日志

• select * 不带条件，默认就会记录到慢日志
• 超过慢日志阈值的增删改查操作

二进制日志

二进制日志简介

区分清楚：redo存放修改的数据，binlog存放这条修改的语句。

general log：所有的sql操作
redo log：存储引擎的修改数据
binlog：只记录修改操作指令

二进制日志作用

1、数据恢复：基于时间点、位置点
2、备份：增量、差异
3、mysql 主从复制：通过binlog实现数据复制。

二进制日志的工作模式

记录语句

配置：binlog_format=statement，记录sql语句。适用于不依赖内置功能的sql。

思考：binlog 日志记录了语句，如果语句中调用了函数，但binlog默认只记录修改语句，并不记录函数定义语句，怎么办？

• 开启 set global log_bin_trust_function_createors=true;

优点：只记录语句，不管具体的行数据，日志量小，IO压力小，性能较高。

缺点：sql语句可能有上下文依赖，脱离了当前数据库就无法运行，特别容易出现主从不一致的问题。

• 主库某条sql引用了函数、触发器、存储过程，从库接收了该sql，无法执行成功，特别容易导致主从不一致。

记录行（mysql8默认）

配置：binlog_format=row，记录每一行数据的修改细节。

优点：记录的是修改后的数据细节，不依赖任何上下文。
缺点：每一行都记录，日志量大。

适用于mysql内置功能依赖多，希望数据安全的场景，现在mysql8默认就是row。

混合模式 mixed

语句和行的混合：默认记录语句，遇到调用函数、存储过程等上下文时记录为行。

应用场景：看似美好，但通常为了保险用row，很少用mixed。

binlog日志概念

事件：events

• 一行就是一个事件

位置：position，事件在日志中记录的位置。

二进制日志相关参数

server_id：服务ID,主从库必须不一样
log-bin：二进制日志开启/关闭（无法set，只能改配置，后面跟日志文件名）
    log_bin=./binlog
sql_log_bin：小开关，当前session中的操作是否记录到binlog
    优先级高于 log-bin 参数
    只能set sql_log_bin off/on；不能用global，不能配置到my.cnf。
binlog_format：row/statement/mixed
binlog_row_image=full/minimal/noblob：全部，被操作的，不记录
max_binlog_size：binlog到达这个值就滚动。
sync_binlog：刷盘策略（0内存，1硬盘）
    innodb_flush_log_at_trx_commit：redo刷盘策略
    sync_binlog：binlog刷盘策略。

#打开才能查看详细记录，默认为off
binlog_rows_query_log_events=on 

#表示自动删除10天以前的日志
expire_logs_days=10 

# full,minimal,noblob分别表示binlog中内容全记录，只记录被操作的，和不记录二进制
binlog_row_image=full #（full,minimal,noblob），

查看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 -p variables |grep -w log_bin

配置

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 

查看binlog日志

show binary logs;
show master logs;
show master status;
show binlog events in 'mybinlog.000002';
show binlog events in 'mybinlog.000002' limit 3;

binlog日志需要用mysqlbinlog工具查看。

• 按时间查看：--start-datetime / --stop-datetime
• 按位置点查看：--start-position / --stop-datetime
• 二进制转明文：--base64-output=decode-rows

备份与恢复

#修改数据
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 --stop-position=772 > /tmp/binlog.sql

mysql -uroot -pEgon@123 < /tmp/binlog.sql

刷新与清除二进制日志

刷新binlog

• flush logs
• 重启数据库
• binlog满了

#刷新binlog：关闭当前的二进制日志文件并创建一个新文件
1）手动执行命令刷新
flush logs;  
或者
mysqladmin -uroot -p flush-logs;
或者
mysql -uroot -pEgon@123 -e 'flush logs'

2）重启数据库时会刷新
3）二进制日志上限（max_binlog_size）;当binlog达到1G,自动刷新

清除binlog

• 删除所有：reset master;
• 指定名称删除：purge binary logs to 'bin name'
• 指定时间删除：purge binary logs before 'date'
• 指定过期自动删除：SET GLOBAL expire_logs_days = 7;

# 清除二进制日志原则
在存储能力范围内，能多保留则多保留
基于上一次全备前的可以选择删除

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