mysql日志类型
MySQL 中有六种日志文件，分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。
undo log 和 redo log
undo log 和 redo log 其实都不是 MySQL 数据库层面的日志，而是 InnoDB 存储引擎的日志。二者的作用联系紧密，事务的隔离性由锁来实现，原子性、一致性、持久性通过数据库的 redo log 或 redo log 来完成。redo log 又称为重做日志，用来保证事务的持久性，undo log 用来保证事务的原子性和 MVCC。
redo log
功能
事务在运行过程中，都是在内存的Buffer Pool修改页面，事务提交后，这些被修改后的脏页并不会立刻刷盘（立刻刷盘开销太大，一方面是一个页面可能就修改了一点点，将整个页面刷盘不值当，另一方面是一个事务会涉及不同的页面，如果将这些页面都刷盘会产生很多的随机IO）
但如果不采取其他措施，那么在事务提交后MySQL发生故障，导致内存中数据丢失，那么这个已提交事务作出的更改也会丢失，所以这里就需要引入redo日志，对任意页面进行修改的操作都会生成redo日志，在事务提交时，只要保证生成的redo日志成功落盘即可，这样，即使MySQL发生故障导致内存中的数据丢失，也可以根据已落盘的redo日志恢复数据
和大多数关系型数据库一样，InnoDB 记录了对数据文件的物理更改，并保证总是日志先行，也就是所谓的 WAL，即在持久化数据文件前，保证之前的 redo 日志已经写到磁盘。由于 redo log 是顺序整块写入，所以性能要更好。
重做日志两部分组成：一是内存中的重做日志缓冲(redo log buffer)，是易失的；二是重做日志文件(redo log file)，是持久的。redo log 记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。
写入过程
在一条语句进行执行的时候，InnoDB 引擎会把新记录写到 redo log 日志中，然后更新内存，更新完成后就算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将 redo log 中的内容更新到磁盘中。（InnoDB 引擎写入顺序：redolog ->更新内存 ->磁盘数据页）
更详细的步骤，需要了解两个关键词：checkpoint 和 LSN(Log Sequence Number)，前者检查点简单来说就是把脏页刷到磁盘的时间点，这个时间点之前的数据都已经保存到了持久存储。而 LSN 是 InnoDB 使用的一个版本标记的计数，它是一个单调递增的值。数据页和 redo log 都有各自的 LSN。每次把 redo log 中的内容写入到实际的数据页之后，就会把 LSN 也同步过去。如果发生了宕机，我们可以根据数据页中的 LSN 值和 redo log 中 LSN 的值判断需要恢复的 redo log 的位置和大小。redo log 同样也有自己的缓存，所以也涉及到刷盘策略，是通过innodb_flush_log_at_trx_commit这个参数控制的。
当对应事务的脏页写入到磁盘之后，redo log 的使命也就完成了，重做日志占用的空间就可以重用（被覆盖）。
undo log
undo log 有两个作用：提供回滚和多版本并发控制下的读(MVCC)，也即非锁定读
在数据修改的时候，不仅记录了redo，还记录了相对应的 undo，如果因为某些原因导致事务失败或回滚了，可以借助该 undo 进行回滚。
undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。
有时候应用到行版本控制的时候，也是通过 undo log 来实现的：当读取的某一行被其他事务锁定时，它可以从 undo log 中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。
undo log 是采用段(segment)的方式来记录的，每个 undo 操作在记录的时候占用一个 undo log segment。
另外，undo log 也会产生 redo log，因为 undo log 也要实现持久性保护。
当事务提交的时候，InnoDB 不会立即删除 undo log，因为后续还可能会用到 undo log，如隔离级别为 repeatable read 时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即 undo log 不能删除。
当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表，由 purge 线程判断是否有其他事务在使用 undo 段中表的上一个事务之前的版本信息，决定是否可以清理 undo log 的日志空间。
在 MySQL 5.7 之前，undo log 存储在共享表空间中，因此有可能大大增加表空间的占用，5.7 之后可以通过配置选择存储在独立的表空间中。
错误日志：
它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。
和mysql启动相关的信息，mariadb默认路径/var/log/mariadb/mariadb.log,源码包装mysql在数据目录下（data）生成 主机名.err
手动指定错误日志目录：log-err=filename
查询日志
客户端所有的语句，包括查询，默认不开启，一般调试时开启
日志输出方式
log_output=[table,file,none]，默认是file
查看查询日志
show variables like "general_log%";
开启查询日志
set global general_log=1;
记录在了 主机名-master.log 中
慢查询日志（slow query log）
记录执行时间超过long_query_time设定值并且扫描行数不小于min_examined_row_limit（对于查询扫描行数小于此参数的SQL，将不会记录到慢查询日志中）的SQL语句查看慢查询时间限定值
精确度为微妙
查看扫描行（最小测试行限定）数值
配置文件/etc/my.cnf中的mysqld段指定slow_query_log_file=路径文件名，如果不指定，默认还是在数据目录下，格式为 主机名-slow.log
show variables like "slow%";
开启慢查询日志
set global slow_query_log=ON;
实验生成慢查询日志
指定小于时长为多少慢查询日志就纪录
set long_query_time=0.000001;
一般是0.1，为了做实验而已哈
show variables like "long%";
执行查询命令
进入数据文件目录查看生成的慢日志
mysqldumpslow localhost-slow.log
mysqldump --help可以了解更多信息
二进制日志（binlog）
数据库主从同步的时候是通过同步 binlog 实现的。
binlog 主要有以下作用：
- 复制：MySQL 主从复制在 Master 端开启 binlog，Master 把它的二进制日志传递给 slaves 并回放来达到 master-slave 数据一致的目的
- 数据恢复：通过 mysqlbinlog 工具恢复数据
- 增量备份
设置和查看
二进制日志：修改相关的SQL语句（update insert delete create drop alter）
三种格式
基于语句：记录数据执行的命令
基于行：录数据每行变化后的结果，更加精准，磁盘IO更大
混合模式：
开启方式：
vim /etc/my.cnf
[mysqld]下加入log-bin=mysql-bin
再/var/lib/mysql/目录或者是源码包指定二进制安装位置目录会出现mysql-bin.000001
同时生成mysql-bin.index用来生成二进制日志索引
查看内容：
查看是否开启二进制日志
show variables like "sql_log_bin";
查看系统中所有二进制文件
show master logs;
show binary logs;
在mysql端查看二进制日志内容
show binlog events in "mysql-bin.000004"\G
查看二进制日志缓存大小
show variables like "binlog_cache_size";
查看二进制日志格式
show variables like "binlog_format";
查看二进制状态
show master status；
清除二进制日志
reset master;
指定删除二进制日志
删除此二进制日志之前的bin-log日志
purge master logs to "mysql-bin.000001";
指定日期
purge master logs before "日期";
查看二进制过期时间
show variables like "expire_logs_days";
0为永不过期
设置二进制日志过期时间
set global expire_logs_days=3;
刷新二进制日志
flush logs;
实验：使用二进制日志进行数据恢复
完全恢复
mysqlbinlog mysql-bin.000001 | mysql -uroot -p123
一般bin-log日志记录了最后一步误操作过程，不建议使用
按照执行位置编号
格式：
mysqlbinlog --start-position=起始位置 --stop-position=终止位置 mysql-bin.000002| mysql -uroot -p123
查看是否清除
show binlog events in "mysql-bin.000001";
创建库表，插入数据
create database aaa;
use aaa
create table bbb(name char(20));
insert into bbb values('zhangsan'),('lisi');
select * from bbb;
删除表bbb
drop table bbb;
查看二进制日志记录事件
如图可知，当插入语句时，会触发一个事务，所以终止位置一定是要包含commit（序号618）
恢复数据库时选择开始节点开始执行（245），更早（4）也可以
在外部使用mysqlbinlog命令查看
mysqlbinlog mysql-bin.000001
恢复时先把库删了，因为命令不会自动使用use选择库，无法直接从建表开始
drop database aaa;
恢复库
mysqlbinlog --start-position=245 --stop-position=618 mysql-bin.000001| mysql
成功恢复
按照时间恢复
mysqlbinlog mysql-bin.000001
格式：mysqlbinlog --start-datetime ‘YY-MM-DD HH:MM:SS’--stop-datetime ‘YY-MM-DD HH:MM:SS’二进制日志 | mysql -uroot -p123
例：
mysqlbinlog --start-datetime "2020-8-31 19:03:00" --stop-datetime "2020-8-31 19:07:00" mysql-bin.000001 | mysql -uroot -p123
实验：使用日期重新尝试进行数据库恢复
删除所有bin-log日志
reset master;
查看是否清除
show binlog events in "mysql-bin.000001";
创建库表，插入数据
create database aaa;
use aaa
create table bbb(name char(20));
insert into bbb values('zhangsan'),('lisi');
select * from bbb;
删库
drop database aaa;
在外部使用mysqlbinlog命令查看
mysqlbinlog mysql-bin.000001
mysqlbinlog mysql-bin.000001
根据日志可知，初始时间为2020-09-13 22:32:54，结束时间为2020-09-13 22:33:42
执行命令
mysqlbinlog --start-datetime "2020-09-13 22:32:54" --stop-datetime "2020-09-13 22:33:43" mysql-bin.000001 | mysql
挑选时间为结束时间靠后一点（我这里靠后1秒），在删除库之前和结束时间点之后即可，当然大部分情况初始时间可以不填写，直接填写结束时间更快捷
插入的数据会重新记录在二进制日志中，所以先关闭bin-log日志功能再进行插入
show variables like "sql_log_bin";
set sql_log_bin=OFF;
中继日志（Relay Log）
中继日志（relay log）只在主从服务器架构的从服务器上存在。从服务器（Slave）为了与主服务器(Master)保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步，起到了中介作用。
查看中继日志配置
show variables like "%relay_log%";
中继日志文件配置
文件名格式: 文件名.有序自增数字， 例: 主机名-relay-bin.000001, 主机名-relay-bin.000002 等
索引文件格式: 例: 主机名-relay-bin.index, 包含所有中继日志文件的名称
发生以下任何事件时, 会重新生成中继日志文件
每次复制 I/O 线程启动时
服务器刷新日志（flush logs 或 mysqladmin flush-logs）
relay log 文件过大时, 有两种情况
如果 max_relay_log_size 值大于0, 则表示中继日志文件的最大大小, 默认 0
如果 max_relay_log_size 值等于0, 则由 max_binlog_size 确定最大中继日志大小
元数据日志
元数据日志记录了影响表分区的数据定义语句生成的元数据操作
分区元数据操作的记录被写入MySQL 数据目录中的 ddl_log.log 文件
ddl_log.log 在实际需要记录元数据语句之前不会创建它，并在成功启动mysqld 后将其删除。因此，此文件可能不会出现在以完全正常方式运行的 MySQL 服务器上
例如: drop table 和 alter table 生成的元数据操作
第三方备份工具
xtrabackup：
innobackupex：对innodb做增量备份，对myisaum做全量备份
mysqlcheck表维护工具
mysqlpump 并行导出工具
mysqlimport 数据导入工具
发布者：LJH，转发请注明出处：https://www.ljh.cool/6375.html
