MYSQL---日志

1.日志的概述

日志是MySQL数据库的重要组成部分。日志文件中记录着MySQL数据库运行期间发生的变化；也就是说用来记录MySQL数据库的客户端连接状况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。

MySQL有不同类型的日志文件，用来存储不同类型的日志，分为二进制日志、错误日志、查询日志和慢查询日志，这也是常用的4种。MySQL 8又新增两种支持的日志：中继日志和事务日志。使用这些日志文件，可以查看MySQL内部发生的事情

慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化

查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助

错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护

二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复

中继日志：用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作

事务日志：记录数据定义语句执行的元数据操作

除二进制日志外，其他日志都是文本文件。默认情况下，所有日志创建于 MySQL数据目录中

日志的弊端

日志功能会降低MySQL数据库的性能

日志会占用大量的磁盘空间

2.错误日志(error log)

它记录了mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志

在MySQL数据库中，错误日志功能是默认开启的。而且，错误日志无法被禁止。

默认情况下，错误日志存储在MySQL数据库的数据文件夹下/var/log/，名称默认为 mysqld.log （Linux系统）

show variables like '%log_err%';

错误日志记录信息

1.服务器启动和关闭过程中的信息

未必是错误信息，比如mysql是如何去初始化存储引擎的过程记录在错误日志里等等

2.服务器运行过程中的错误信息

比如sock文件找不到，无法加载mysql数据库的数据文件，如果忘记初始化mysql或data dir路径找不到，或权限不正确等都会记录在此

3.事件调度器运行一个事件时产生的信息

一旦mysql调度启动一个计划任务的时候，它也会将相关信息记录在错误日志中

4.在从服务器上启动从服务器进程时产生的信息

在复制环境下，从服务器进程的信息也会被记录进错误日志

删除/刷新日志

在mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。mysql数据库中，可以使用mysqladmin命令开启新的错误日志。

mysqladmin –u root –p flush-logs

在mysql5.5.7之后：服务器将关闭此项功能。只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的：

mv stu18.magedu.com.err  stu18.magedu.com.err.old
mysqladmin flush-logs

3.二进制日志(bin log)

MySQL的二进制日志（binary log）是一个二进制文件，主要记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的所有操作。二进制日志（binary log）中记录了对MySQL数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录SELECT、SHOW等那些不修改数据的SQL语句

bin log主要应用场景：

一是用于数据恢复

二是用于数据复制

在MYSQL8版本中，默认二进制日志是开启着的

show variables like '%log_bin%';

日志格式

binlog格式分为: STATEMENT、ROW和MIXED三种

STATEMENT

STATEMENT格式的binlog记录的是数据库上执行的原生SQL语句，对数据进行修改的sql都记录在日志文件中

ROW

基于行的日志记录，记录的是每一行的数据变更（默认）

MIXED

MIXED也是MySQL默认使用的二进制日志记录方式，但MIXED格式默认采用基于语句的复制，混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下回自动切换为ROW进行记录

show variables like '%binlog_format%';

查看日志

当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件

MySQL服务重新启动一次，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同；如果日志长度超过了 max_binlog_size 的上限（默认是1GB），就会创建一个新的日志文件

binlog的相关参数

max_binlog_size

可以通过max_binlog_size参数来限定单个binlog文件的大小（默认1G），如果当前binlog文件的大小达到了参数指定的阈值，会创建一个新的binlog文件作为当前活跃的binlog文件，后续所有对数据库的修改都会记录到新的binlog文件中。

对于binlog文件的大小，有个需要注意的地方是，binlog文件可能会大于max_binlog_size参数设定的阈值。由于一个事务所产生的所有事件必须记录在同一个binlog文件中，所以即使binlog文件的大小达到max_binlog_size参数指定的大小，也要等到当前事务的所有事件全部写入到binlog文件中才能切换，这样就会出现binlog文件的大小大于max_binlog_size参数指定的大小的情况。

binlog_cache_size

当使用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中去，等该事务提交（committed）时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32K。此外，binlog_cache_size是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要相当小心，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。通过SHOW GLOBAL STATUS命令查看binlog_cache_use、binlog_cache_disk_use的状态，可以判断当前binlog_cache_size的设置是否合适。binlog_cache_use记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。

sync_binlog

在MySQL 5.7之前版本默认情况下，二进制日志并不是在每次写的时候同步的磁盘（用户可以理解为缓冲写）。因此，当数据库所在的操作系统发生宕机时，可能会有最后一部分数据没有写入二进制文件中，这会给恢复和复制带来问题。参数sync_binlog=[N]中的N表示每提交多少个事务就进行binlog刷新到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，每次事务提交时就会刷新binlog到磁盘；sync_binlog为0表示刷新binlog时间点由操作系统自身来决定，操作系统自身会每隔一段时间就会刷新缓存数据到磁盘；sync_binlog为N表示每N个事务提交会进行一次binlog刷新。如果使用Innodb存储引擎进行复制，并且想得到最大的高可用性，需要将此值设置为1。不过该值为1时，确时会对数据库IO系统带来一定的开销。

但是，即使将sync_binlog设为1，还是会有一种情况导致问题的发生。当使用InnoDB存储引擎时，在一个事务发出COMMIT动作之前，由于sync_binlog为1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在MySQL数据库下次启动时，由于COMMIT操作并没有发生，这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。对于这个问题，MySQL使用了两阶段提交来解决的，简单说就是对于已经写入到binlog文件的事务一定会提交成功，而没有写入到binlog文件的事务就会进行回滚，从而保证二进制日志和InnoDB存储引擎数据文件的一致性，保证主从复制的安全。

binlog-do-db&binlog-ignore-db

参数binlog-do-db和binlog-ignore-db表示需要写入或者忽略写入哪些库的二进制日志。默认为空，表示需要同步所有库的日志到二进制日志。

log-slave-update

如果当前数据库是复制中的slave角色，则它不会将master取得并执行的二进制日志写入自己的二进制日志文件中去。如果需要写入，要设置log-slave-update。如果需要搭建master–>slave–>slave架构的复制，则必须设置该参数。

binlog-format

binlog_format参数十分重要，用来设置二进制日志的记录格式,详情参考(6.5 binlog格式)

log_bin_trust_function_creators

默认为OFF，这个参数开启会限制存储过程、Function、触发器的创建

删除日志

对于比较繁忙的业务系统，每天生成的binlog数据巨大，如果长时间不清除，将占用大量磁盘空间

也可以在mysql的配置文件中配置二进制日志的国企时间，设置了之后，二进制日志过期会自动删除

show variables like '%binlog_expire_logs_seconds%';

4.查询日志(general log)

查询日志里的内容不要被"查询日志"误导，认为里面只存储select语句，其实不然，查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录

insert查询为了避免数据冲突，如果此前插入过数据，当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错

update时也会查询因为更新的时候很可能会更新某一块数据

delete查询，只删除符合条件的数据

因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致IO非常大，影响MySQL性能，因此如果不是在调试环境下，是不建议开启查询日志功能的

查询日志配置

参数general_log用来控制开启、关闭MySQL查询日志

参数general_log_file用来控制查询日志的位置

如果你要判断MySQL数据库是否开启了查询日志，可以使用下面命令。general_log为ON表示开启查询日志，OFF表示关闭查询日志

show variables like '%general%';

打开查询日志

如果要开启查询日志，可以设置以下配置：

修改MySQL的配置文件/etc/my.cnf文件，添加内容如下

如果开启了查询日志，参数log_output控制着查询日志的存储方式， log_output可以设置为以下4种值:

FILE : 表示日志存储在文件中

TABLE : 表示日志存储在mysql库中的general_log表中

FILE, TABLE : 表示将日志同时存储在文件和general_log表中，改值会徒增很多IO压力，一般不会这样设置

NONE : 表示不记录日志，即使general_log设置为ON，如果log_output设置为NONE，也不会记录查询日志

log_output不仅控制查询日志的输出，也控制着慢查询日志的输出，即: log_output设置为FILE，就表示查询日志和慢查询日志都存放在文件中，设置为TABLE，查询日志和慢查询日志都存放在mysql库中的general_log表中

查看log_output设置:

show variables like 'log_output';

关闭查询日志

可以在my.cnf里添加,1开启（0关闭）,这样要重启才能生效

general-log = 0

然后重启mysql

5.慢查询日志(slow query log)

慢查询会导致CPU，IOPS，内存消耗过高。当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能

慢查询日志记录的慢查询不仅仅是执行比较慢的SELECT语句，还有INSERT，DELETE，UPDATE，CALL等DML操作，只要超过了指定时间，都可以称为"慢查询"，被记录到慢查询日志中

默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中

慢查询日志记录了所有执行时间超过参数long_query_time设置值并且扫描记录数不小于min_examined_row_limit的所有的SQL语句的日志，默认未开启。long_query_time默认为10秒，最小为0，精度可以到微妙

show variables like "%slow%";

set global slow_query_log='ON';也可以打开慢查询日志，只是对当前数据库有效，如果MySQL数据库重启后就会失效。所以如果要永久生效，就要修改配置文件 my.cnf

默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询。可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes

6. 中继日志(relay log)

中继日志（relay log）只在主从服务器架构的从服务器上存在。从服务器（slave）为了与主服务器(Master)保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步

relay log是复制过程中产生的日志，很多方面都跟binary log差不多，区别是: relay log是从库服务器I/O线程将主库服务器的二进制日志读取过来记录到从库服务器本地文件，然后从库的SQL线程会读取relay-log日志的内容并应用到从库服务器上

中继日志作用

中继日志用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作

中继日志是连接mastert(主服务器)和slave(从服务器)的信息，它是复制的核心，I/O线程将来自master的binlog存储到中继日志中，中继日志充当缓冲，这样master不必等待slave执行完成就可以发送下一个binlog

show variables like '%relay%';

7.事务日志

数据库数据存放的文件称为data file；日志文件称为log file；数据库数据是有缓存的，如果没有缓存，每次都写或者读物理disk，那性能就太低下了。数据库数据的缓存称为data buffer，日志（redo）缓存称为log buffer；既然数据库数据有缓存，就很难保证缓存数据（脏数据）与磁盘数据的一致性

为了保证事务的ACID特性，就不得不说MySQL InnoDB引擎的事务日志: 重做日志redo和回滚日志undo

注: 在数据库的世界里，数据从来都不重要，日志才是最重要的，有了日志就有了一切

看查日志文件设置状态

show variables like 'innodb_%';

innodb_log_files_in_group:DB 中设置几组事务日志，默认是2；
innodb_log_group_home_dir 事务日志存放目录，不设置；

1.重做日志(redo log)

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的，并且是事务的记录是顺序追加的，性能非常高(磁盘的顺序写性能比内存的写性能差不了太多)

InnoDB用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以重放日志并且恢复已经提交的事务

持久化

事务被提交，数据一定会被写入到数据库中并持久存储起来，通常来说当事务已经被提交之后，就无法再次回滚了

重做日志实现持久化

与原子性一样，事务的持久性也是通过日志来实现的，MySQL 使用重做日志（ redo log ）实现事务的持久性，重做日志由两部分组成，一是内存中的重做日志缓冲区，因为重做日志缓冲区在内存中，所以它是易失的；另一个就是在磁盘上的重做日志文件，它是持久的

重做日志执行

在MySQL中事务执行 commit 提交了之后，但是服务器宕机了，数据还没有写入磁盘，在MySQL重启服务之后会重新执行这个重做日志写入数据

redo log参数

innodb_log_files_in_group

redo log 文件的个数，命名方式如：ib_logfile0，iblogfile1... iblogfilen。默认2个，最大100个

innodb_log_file_size

文件设置大小，默认值为 48M，最大值为512G，注意最大值指的是整个 redo log系列文件之和，即（innodb_log_files_in_group * innodb_log_file_size ）不能大于最大值512G

innodb_log_group_home_dir

文件存放路径

innodb_log_buffer_size

Redo Log 缓存区，默认8M，可设置1-8M。延迟事务日志写入磁盘，把redo log 放到该缓冲区，然后根据 innodb_flush_log_at_trx_commit参数的设置，再把日志从buffer 中flush 到磁盘中

2.回滚日志(undo log)

undo log有两个作用：提供回滚和多个行版本控制(MVCC)

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取

回滚日志实现原子性

想要保证事务的原子性，就需要在异常发生时，对已经执行的操作进行回滚，而在 MySQL 中，恢复机制是通过回滚日志（ undo log ）实现的，所有事务进行的修改都会先记录到这个回滚日志中，然后再对数据库中的对应行进行写入。注意：系统发生崩溃、数据库进程直接被杀死后，当用户再次启动数据库进程时，还能够立刻通过查询回滚日志将之前未完成的事务进行回滚，这也就需要回滚日志必须先于数据持久化到磁盘上，是我们需要先写日志后写数据库的主要原因。在日志文件中：在事务中使用的每一条 insert into 都对应了一条 delete ，每一条 update 也对应一条相反的 update 语句

回滚日志执行

1.手动执行回滚命令时会执行。

2.如果程序在事务执行之后，提交命令执行之前出现了异常，在下次 MySQL 服务重启的时候会执行

undo log相关参数

show global variables like '%undo%';

show global variables like '%truncate%';

innodb_max_undo_log_size

控制最大undo tablespace文件的大小，当启动了innodb_undo_log_truncate 时，undo tablespace 超过innodb_max_undo_log_size 阀值时才会去尝试truncate。该值默认大小为1G，truncate后的大小默认为10M。

innodb_undo_tablespaces

设置undo独立表空间个数，范围为0-128，默认为0，0表示表示不开启独立undo表空间且 undo日志存储在ibdata 文件中。该参数只能在最开始初始化MySQL实例的时候指定，如果实例已创建，这个参数是不能变动的，如果在数据库配置文件 .cnf 中指定innodb_undo_tablespaces 的个数大于实例创建时的指定个数，则会启动失败，提示该参数设置有误。

innodb_undo_log_truncate

InnoDB的purge线程，根据innodb_undo_log_truncate设置开启或关闭innodb_max_undo_log_size的参数值，以及truncate的频率来进行空间回收和 undo file 的重新初始化。

innodb_purge_rseg_truncate_frequency

用于控制purge回滚段的频度，默认为128。假设设置为n，则说明，当Innodb Purge操作的协调线程 purge事务128次时，就会触发一次History purge，检查当前的undo log 表空间状态是否会触发truncate