Mysql中的日志

MySQL中有以下日志文件，分别是：
1：重做日志（redo log）
　　2：回滚日志（undo log）
　　3：二进制日志（binlog）
　　4：错误日志（errorlog）
　　5：慢查询日志（slow query log）
　　6：一般查询日志（general log）
　　7：中继日志（relay log）。
Binlog是通用的。
Redo Log，Undo Log则是工作于数据引擎层，只有InnoDB引擎才会有的。

Redo Log（重做日志）☆

什么是Redo Log（InnoDB引擎功能）
默认开启，记录事务执行后的状态，用来恢复未写入data file的已成功事务更新的数据。以恢复操作为目的，在数据库发生意外时根据redo log进行重做。
（Mysq把记录从磁盘页读到内存进行修改成功后，还没来得及刷到磁盘中，数据库宕机了，会导致数据丢失。为了避免这个修改丢失，内存中更新完后写入到redo log buffer 中，然后由redo log buffer 写入到redo log中。）

Redo Log工作机制

产生：随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生Redo Log写入Log Buffer，而不是随着事务的提交就立刻写入磁盘文件。
释放：等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）redo log是循环写的。

Redo Log的作用

Redo Log 是为了实现事务的持久性。防止在发生故障的时间点，尚有脏页未写入表的 data file 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

Redo Log写入机制

Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

Redo Log相关参数配置

1）查看Redo Log 相关参数: show variables like ‘%innodb_log%’;

2）查看重做日志缓存区Innodb_log_buffer（默认8M）：
show variables like ‘%innodb_log_buffer_size%’;

Redo Log 的持久化

Redo Buffer 持久化到 Redo Log （innodb日志缓冲区的日志刷到磁盘）的策略有三种，可通过 Innodb_flush_log_at_trx_commit 设置：
0：每秒提交 Redo buffer ->OS cache -> flush cache to disk，可能丢失一秒内的事务数据。由后台Master线程每隔 1秒执行一次执行刷新Innodb_log_buffer到重做日志文件。
1（默认值）：每次事务提交执行 Redo Buffer -> OS cache -> flush cache to disk，最安全，性能最差的方式。
2：每次事务提交执行 Redo Buffer -> OS cache，然后由后台Master线程再每隔1秒执行OScache -> flush cache to disk 的操作。
（当重做日志缓存可用空间 少于一半时，重做日志缓存被刷新到重做日志文件）
一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据。

对应的物理文件：
　　默认情况下，对应的物理文件位于数据库的data目录下的ib_logfile1&ib_logfile2
　　innodb_log_group_home_dir 指定日志文件组所在的路径，默认./ ，表示在数据库的数据目录下。
　　innodb_log_files_in_group 指定重做日志文件组中文件的数量，默认2

关于文件的大小和数量，由以下两个参数配置：
　　innodb_log_file_size 重做日志文件的大小。
　　innodb_mirrored_log_groups 指定了日志镜像文件组的数量，默认1

Undo Log（撤销/回滚日志）☆

默认开启，撤销日志以撤销操作为目的，返回指定某个状态的操作：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库的影响。mysql数据库的事务的原子性就是通过undo log实现的。

Undo Log产生和销毁：

产生：Undo Log在事务开始前产生，将当前是的版本生成undo log，undo 也会产生 redo 来保证undo log的可靠性；
销毁：事务提交之后，并不会立刻删除undo log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。

Undo Log存储：

undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollback segment回滚段，内部包含1024个undo log segment。

Undo Log日志查看：

查看Undo Log的命令show variables like ‘%innodb_undo%’;

MySQL5.7之后的独立undo 表空间配置参数如下：
　　innodb_undo_directory = /data/undospace/ –undo独立表空间的存放目录 innodb_undo_logs = 128 –回滚段为128KB innodb_undo_tablespaces = 4 –指定有4个undo log文件
　　如果undo使用的共享表空间，这个共享表空间中又不仅仅是存储了undo的信息，共享表空间的默认为与MySQL的数据目录下面，其属性由参数innodb_data_file_path配置。

Undo Log作用:

1）Undo Log可以实现事务的原子性
Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。
2）实现多版本并发控制（MVCC）
Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照，其他并发事务可以用它进行快照读。

参考：MySQL系列之事务日志Undo log学习笔记

Binlog（二进制日志）☆

什么是Binlog（MySQL Server自带功能）
Binlog 默认没有开启，用于记录数据库表结构变更以及表数据变更的二进制日志（比如 insert、update、delete、create、truncate等），但不包含查询语句（比如 select、show），简单说Binlog记录了数据库所有的写操作。

Binlog用在哪些场景

Binlog日志可以使用在数据恢复和主从复制中。

1.用于数据恢复

binlog记录了数据库的数据变更，可以用于数据恢复，数据恢复通过mysqlbinlog工具完成，工具可以查看每条语句文本；记录中字段Position用于记录binlog日志的指针，需要恢复时，只要指定—start-position和—stop-position，或者指定—start-datetime和—stop-datetime，就可以恢复指定区间的数据。

2.用于主从复制

比如一主二从结构，需要master开启binlog日志，从库订阅master的binlog日志，当master发生数据变更时，从库会把主库中执行过的写操作都执行一遍保证主从数据一致。

Binlog的文件存储模式

Binlog的文件模式有STATEMENT（statement-based replication, SBR）、ROW（row-based replication, RBR）和MIXED（mixed-based replication, MBR）。

1）ROW：

记录数据库整个表每一行具体做了哪些修改的日志信息，这样他可以完全实现主从同步和数据恢复，通常如果用于主从复制建议使用ROW模式。不过这个文件模式日志量很大，尤其是alter table操作会让日志量暴涨。查看命令：mysqlbinlog -vv mysqlbinlog.00002(日志文件名)

2）STATEMENT：

日志文件中记录的都是SQL语句，他的日志量比较少，不过有时候并不能完全恢复数据（因为有些修改，比如一些可变的参数并不能完全通过SQL语句表达）。查看命令：mysqlbinlog mysqlbinlog.00001(日志文件名)

3）MIXED：

则相当于以上两种模式的混合，也就是一般情况使用STATEMENT模式，对于STATEMENT模式无法复制的操作使用ROW模式。
通常如果用于主从复制建议使用ROW模式。

Binlog的操作

1）查看Binlog是否开启: show variables like ‘log_bin’;

2）设置Binlog开启状态：Binlog是服务层的属性，开启Binlog需要在my.conf（Linux）或者my.ini（Windows）文件中配置binlog开启，然后重启MySQL服务后，即可看到Binlog开启:
#log-bin=ON
#log-bin-basename=mysqlbinlog
# 打开binlog，指定log-bin名字
log-bin=mysqlbinlog
# 指定ROW(行)模式
binlog-format=ROW

3）使用show binlog events命令

show binary logs; //等价于show master logs;
show master status;
show binlog events;
show binlog events in 'mysqlbinlog.000001';

4）使用mysqlbinlog命令
命令：mysqlbinlog mysqlbin.00001(日志文件名)
会记录下每条变更的sql语句，还有执行开始时间，结束时间，事务id等等信息。

mysqlbinlog "文件名"
mysqlbinlog "文件名" > "test.sql"

5）使用 binlog 恢复数据

//按指定时间恢复
mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stopdatetime="
2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234
//按事件位置号恢复
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002
| mysql -uroot -p1234

mysqldump：定期全部备份数据库数据。mysqlbinlog可以做增量备份和恢复操作。

6）删除Binlog文件

purge binary logs to 'mysqlbinlog.000001'; //删除指定文件
purge binary logs before '2020-04-28 00:00:00'; //删除指定时间之前的文件
reset master; //清除所有文件
purge master logs to 'mysqlbinlog.000002'； // 删除00002编号之前的所有日志。

可以通过设置配置文件中：—expire_logs_days=1 参数来启动自动清理功能。默认值为0表示没启用。设置为1表示超出1天binlog文件会自动删除掉。

Binlog文件结构
Binlog文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是Logevent。不同的修改操作对应的不同的log event。比较常用的log event有：Query event、Row event、Xid event等。binlog文件的内容就是各种Log event的集合。Log Event的结构如下图：

Binlog写入机制
1）Binlog以事件机制写入，它会根据记录模式和操作触发event事件生成日志事件(log event)。
2）将事务执行过程中产生log event写入缓冲区，每个事务线程都有一个缓冲区。
3）Log Event保存在一个binlog_cache_mngt数据结构中，在该结构中有两个缓冲区，一个是stmt_cache，用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。
4）事务在提交阶段将产生的log event写入到外部binlog文件中。
5）不同事务以串行方式将log event写入binlog文件中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log event。

Error Log（错误日志）

错误日志，默认开启，记录MySQL启动和停止,以及服务器在运行过程中发生的错误的相关信息。

通过 show variables like ‘log_error%’;

指定日志路径两种方法:
编辑my.cnf 写入 log-error=[path]
通过命令参数错误日志 mysqld_safe –user=mysql –log-error=[path] &

General Query Log（通用查询日志）

通用查询日志，默认关闭，记录所有的操作语句（包括CRAD语句），无论这些查询或是命令是否正确甚至是否包含语法错误。

通过show variables like ‘general%’;

通用日志通常在需要采样分析的时候可以开启，不建议直接修改配置文件，而是通过命令设置开启：
开启命令：set global general_log=1; 或者 set @@global.general_log=1;
关闭命令：set global general_log=0;
修改文件路径：先要停止服务service mysql stop；再修改路径mysqld —general_log_file=/tmp/suse11b.log；最后设置开启set global general_log=1;都完成后重新连接数据库服务就可以了。

#不建议直接修改配置文件
#开启查询日志 0：关闭，1：开启
general_log=1
#设置日志的文件名，没指定默认为：host_name.log
general_log_file=file_name

Slow Query Log（慢查询日志）

慢查询日志，默认开启，记录所有执行时间超时的查询SQL，默认是10秒。如果我们需要改变时长，可以修改long_query_time参数。

show variables like ‘%slow_query%’; //是否开启

show variables like ‘%long_query_time%’; //慢查询设置的时长

命令开启：set global slow_query_log=1;

Q & A

Binlog和Redolog区别

1）Redo Log是属于InnoDB引擎功能，Binlog是属于MySQL Server自带功能，并且是以二进制文件记录。
2）Redo Log属于物理日志，记录该数据页更新状态内容，Binlog是逻辑日志，记录更新过程。
3）Redo Log日志是循环写，会覆盖，日志空间大小是固定，Binlog是追加写入，写完一个写下一个，不会覆盖使用。
4）Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力。

参考：

简书-MySQL中的日志