【精选】MySQL日志（log

默认情况下，所有的MySQL日志以文件的方式存放在数据库根目录下（除过在/etc/my.cnf指定某些日志具体的存放位置）：

MySQL的日志类型有以下几种：

一个高速、稳定、可靠的系统，缓存在其中必定起着至关重要的作用。MySQL日志处理也使用了缓存机制。MySQL日志最初存放在MySQL服务器的内存中，若超过指定的存储容量，内存中的日志则写（或者刷新flush）到外存中，以数据库表或者以文件的方式永远的保存在硬盘中。

MySQL的错误日志主要记录MySQL服务实例每次启动、停止的详细信息，以及MySQL实例运行过程中产生的警告或者错误信息。和其他的日志不同，MySQL的error日志必须开启，无法关闭。

默认情况下，错误日志的文件名为： 主机名.err。 但error 日志并不会记录所有的错误信息，只有MySQL服务实例运行过程中发声的关键错误（critical）才会被记录下来。

配置文件中写法：

MySQL普通查询日志记录MySQL服务实例所有的操作，如select、update、insert、delete等操作，无论该操作是否成功执行。还有MySQL客户机与MySQL服务端连接及断开的相关信息，无论连接成功还是失败。与MySQL普通查询日志有关的参数有三个。

可以通过 set @@global.general_log = 1的方式来开启普通查询日志。

但是通过这种方式修改MySQL的变量只会在当前的MySQL实例运行期间生效，一旦MySQL重启，则会重新恢复到默认的状态。

永久生效的方式是修改mysql 的my.cnf 文件。在配置文件后添加：

普通查询日志一旦开启，MySQL服务实例将自动创建普通查询日志文件，general_log_file参数设置了普通查询日志文件的物理位置。如下：

注意：由于普通查询日志几乎记录了MySQL的所有操作，对于数据访问频繁的数据库服务器而言，如果开启MySQL的普通查询日志将会大幅度的降低数据库的性能，因此建议关闭普通查询日志。只有在特殊时期，如需要追踪某些特殊的查询日志，可以临时打开普通的查询日志。

log_output参数设置了普通查询日志以及慢查询日志的内容存储到数据库表中。 可以使用set @@global.log_output='table'将普通查询日志及慢查询日志存入mysql系统数据库中的general 表 以及slow_log 表中。值得注意的是这两个表的存储引擎为CSV,此后查看新的普通查询日志内容时便可以使用SQL语句;

使用MySQL慢查询日志可以有效的跟踪 执行时间过长 或者 没有使用索引的查询语句。这种包括select 语句，update语句，delete语句，以及insert语句，为优化查询提供帮助。与普通查询日志不同的另一个区别在于，慢查询日志只包含成功执行过的查询语句。与MySQL慢查询日志有关的参数有5个。

slow_query_log 设置慢查询日志是否开启。

可以通过set @@global.slow_query_log = 1的方式来开启普通查询日志。

以上方式是临时性的，永久配置要在/etc/my.cnf配置文件中设置：

慢查询日志一旦开启，MySQL实例将自动创建慢查询日志文件。slowquerylog_file 所指定的文件，存放慢查询日志内容。

配置文件中写法：

long_query_time 设置了慢查询的时间阈值。默认阈值是10s。

修改：

或在配置文件中设置：

log_quries_not_using_indexes 是否将不使用索引的查询语句记录到慢查询日志中，无论查询速度有多快。

开启

或在配置文件中设置：

log_output参数可以设置慢查询日志的输出形式。默认为FILE,可以设置为TABLE

其中: lock_time表示该SQL执行时被锁阻塞的时间。 rows_send表示执行SQL后返回的内容行数。 rows_examined表示该SQL执行时实际扫描的记录条数。

但是使用TABLE来存储慢查询日志并不常见，业务量较大的情况下，对于系统的主服务会有影响。我们可以使用FILE 的方式来进行日志存储。安装MySQL的时候在MySQL的bin目录下已经默认安装了 mysqldumpslow 工具来进行慢查询的日志分析

常用法如下：

二进制日志：binary log。简称binLog。记录了对Mysql数据库执行更改的所有操作，但是不包括SELECT 和 SHOW这类操作。以二进制的形式保存在磁盘中。是属于Mysql Server层记录，任何存储引擎的 mysql 数据库都会记录binlog日志。并且binlog 还是 mysql 的逻辑日志。

逻辑日志：可以简单理解为记录的就是sql语句

物理日志：mysql 数据最终是保存在数据页的，物理日志记录的就是数据页变更

binlog 的主要使用场景有两个，分别是 主从复制 和 数据恢复

主从复制：在 Master 端开启 binlog，然后将 binlog 推送到各个 Slave 从端，Slave 端重放 binlog 从而达到主从数据一致

数据恢复：通过使用 mysqlbinlog 工具来恢复数据

二进制日志和前面提到的几种日志不同，二进制不能直接通过cat或者less 文本查看器查看。需要借助专业的工具。

二进制日志主要记录数据库的变化情况，因此可以用作主从库的同步。内容主要包括数据库所有的更新操作，use语句、insert语句、delete语句、update语句、create语句、alter语句、drop语句。用一句更简洁易懂的话概括就是：所有涉及数据变动的操作，都要记录进二进制日志中。

使用 show variables like ‘log_bin’\G 来查看二进制日志是否开启。

log_bin 默认是不开启的，并且是个只读的变量，需要在my.cnf中配置，并配置MySQL日志的格式。然后重启MySQL。

重启MySQL后，在data目录会发现生成了一个binlog.000001的文件。实际上每次MySQL重启，在目录下都会生成一个这样的文件，文件名依次递增。此外，MySQL还会在该目录下创建一个二进制日志的索引文件，可以通过命令show variables like ‘log_bin_index’\G来查看索引文件的位置，然后使用cat命令看下。会发现，里面记录着二进制文件的相对路径。

MySQL二进制日志相关参数

max_binlog_size maxbinlogsize 单个二进制日志文件的大小。如果超过该值，则生成新的文件，后缀名+1；

binlog_cache_size binlogcachesize 内存中存放二进制日志的缓存大小

sync_binlog sync_binlog 缓存中写入几次二进制日志，开始同步刷新到外存（硬盘）中。

log_slave_updates logslvaeupdates 用于主从复制

binlog 日志有三种格式，分别为 STATMENT、ROW 和 MIXED

（1）STATEMENT

该日志格式在日志文件中记录的都是SQL语句（statement），每一条对数据进行修改的SQL都会记录在日志文件中，通过Mysql提供的mysqlbinlog工具，可以清晰的查看到每条语句的文本。主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次。

（2）ROW

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句。比如，执行SQL语句 ：update tb_book set status=‘1’ , 如果是STATEMENT 日志格式，在日志中会记录一行SQL文件； 如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW 格式的日志中会记录每一行的数据变更。

（3）MIXED

这是目前MySQL默认的日志格式，即混合了STATEMENT 和 ROW两种格式。默认情况下采用STATEMENT，但是在一些特殊情况下采用ROW来进行记录。MIXED 格式能尽量利用两种模式的优点，而避开他们的缺点。

由于日志以二进制方式存储，不能直接读取，需要用MySQL 中自带的工具mysqlbinlog工具来查看

使用二进制日志恢复数据

获取当前使用的二进制日志文件

对于比较繁忙的系统，由于每天生成日志量大 ，这些日志如果长时间不清楚，将会占用大量的磁盘空间。下面讲解几种清理日志的常见方法

原则上要先将准备清理的日志通过物理备份的方式备份到其他存储设备上，永久留存。，然后清理

第一种：

第二种：

直接在MySQL的配置文件my.cnf 中设置 expire_logs_days 参数来设置二进制文件的过期天数，过期的二进制文件将会被自动删除。建议在删除前另启一个周期计划任务，定期去备份二进制任务。免得一些数据若干天才发现出现了差错，而二进制日志被自动删除了。

第三种：

执行删除日志指令，会删除所有二进制日志文件（慎用）

重启mysql服务，会使二进制日志文件截断，产生新的二进制日志文件进行记录；执行 flush logs 也会如此。

MySQL会最大程度的利用缓存，从而提高数据的访问效率。那么换一句话来说，任何高性能的系统都必须利用到缓存，从各个层面来讲，缓存都发挥了巨大的作用。

再上升到一个高度提炼一下：缓存和队列是实现高性能的必走之路。那么对于数据库来说这个却是个很棘手的问题，要保证数据更高效率的读取和存储，所以要利用到缓存。但是要保证数据的一致性，则必须保证所有的数据都必须准确无误的存储到数据库中，及时发生意外，也要保证数据可恢复。

我们知道InnoDB是一个事务安全的存储引擎，而一致性是事务ACID中的一个重要特性。InnoDB存储引擎主要是通过InnoDB事务日志实现数据一致性的，InnoDB事务日志包括重做（redo）日志，以及回滚（undo）日志

InnoDB事务日志与前面提到的日志不同，InnoDB事务日志由InnoDB存储引擎自行维护，而且内容不能被数据库管理员读取

Redo log 是重做日志，属于 InnoDB储存引擎的日志。是物理日志，日志记录的内容是数据页的更改，这个页"做了什么改动"。

redo log 包括两部分： 一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo logfile)。

mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志，再写磁盘 的技术就是 MySQL 里经常说到的 WAL(Write-Ahead Logging) 技术。

在计算机操作系统中，用户空间( user space )下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间( kernel space )缓冲区( OS Buffer )。

因此， redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer ，然后再通过系统调用 fsync() 将其刷到 redo log file 中，过程如下： mysql 支持三种将 redo log buffer 写入 redo log file 的时机，可以通过innodb_flush_log_at_trx_commit 参数配置，各参数值含义如下： redo log与binlog区别

由 binlog 和 redo log 的区别可知：binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。（CrashSafe指MySQL服务器宕机重启后，能够保证：所有已经提交的事务的数据仍然存在；所有没有提交的事务的数据自动回滚） 但只有 redo log 也不行，因为 redo log 是 InnoDB特有的。

为什么 binlog 没有 crash-safe 功能？

redo log 和 binlog 有一个很大的区别就是，一个是循环写，一个是追加写。也就是说 redo log 只会记录未刷盘的日志，已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志，保存的是全量日志。

当数据库 crash 后，想要恢复未刷盘但已经写入 redo log 和 binlog 的数据，binlog 是无法恢复的。虽然 binlog 拥有全量的日志，但没有一个标志让 innodb 判断哪些数据已经刷盘，哪些数据还没有。

保证 crash-safe 为啥要用两个日记，不能用一个日记吗（Redo log 或 Binglog）?

只有 binlog 日志，没有 redo log 是不能做到故障恢复的。那么针对只有 redo log日志，没有 binlog 日志，这也是不行的，因为 redo log 是 innodb 持有的，且日志上的记录落盘后会被抹掉。因此需要 binlog 和 redo log 两者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

在默认情况下重做日志的值记录在ib_logfile0 以及ib_logfile1重做日志中

回滚日志主要记录已经部分完成并且写入硬盘的未完成事务，默认情况情况下，回滚日志的信息记录在表空间文件，共享表空间文件ibdata1或者独享表空间中。

数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。

实际上， 原子性 底层就是通过 undo log 实现的。undo log主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。

undo log 作用：

binlog、redo log、undo log三种日志。binlog是属于mysql Server层的，属于整个mysql的，而redo log、undo log是属于innodb存储引擎独有的，redo log、undo log是事务日志，binlog是二进制日志负责记录对mysql数据库有修改的sql操作

MySQL服务器崩溃后，重新启动MySQL服务时，由于重做日志（redo）与回滚日志（undo）日志的存在，InnoDB通过回滚日志（undo）日志将所有已部分完成并写入硬盘的未完成事务进行回滚操作（rollback）。然后将重做日志（undo）日志中的事务全部重新执行一遍即可恢复所有的数据。但是数据量过大，为了缩短恢复的时间InnoDB引入了Checkpoint 机制。

当事务需要修改某条记录是，InnoDB首先将该数据所在的数据块从外存中读取到硬盘中，事务提交后，InnoDB修改数据页中的记录，这时缓存的数据页已经和外存中的数据块已经不一样了，此时缓存中的数据页称为脏页（dirty page）,脏页刷新到外存中，变为干净页（clean page）。

日志序列号（LSN）是日志空间中每条日志的结束点，用字节偏移量表示，在Checkpoin和恢复时使用。

Checkpoint 机制原理 假设在某个时间点，所有的脏页（dirty page）都被刷新到了硬盘上，这个时间点之前的所有重做日志（redo）就不需要重做了.系统就就下这个时间点重做日志的结尾位置作为Checkpoint,Checkpoint之前的重做日志也就不需要再重做了，可以放心的删除掉。为了更好的利用重做日志（redo）的空间,InnoDb采用轮循的策略使用重做日志空间，因此InnoDB的重做日志文件至少为2个。通过Checkpoint 机制，通过重做日志（redo）将数据库崩溃时已经完成但还没有来得及将缓存中已经修改但还未完全写入外存的事务进行重做（undo）操作，即可保证数据的一致性，也可以缩短恢复时间。

InnoDB重做日志(redo)的参数

InnoDB回滚日志（undo）的参数

备份的时候可以使用flush logs，关闭当前的所有日志文件，然后产生新的日志文件。关闭日志文件后，可以采用物理方式备份。 另外flush logs可以添加具体的日志类型：