2.MySQL日志 - HelloWorld开发者社区

MySQL日志分类

　　MySQL日志主要包含：错误日志、查询日志、慢查询日志、重做日志、回滚日志、二进制日志
错误日志：
　　用来记录 MySQL 服务器运行过程中的错误信息，比如，服务器启动关闭信息、运行错误信息、时间调度器运行一个事件时产生的信息、在服务器上启动进程产生的信息。
　　错误日志可以自己配置，log-error：配置是否启用错误日志功能和错误日志的存储位置、log-warning：配置是否将警告信息也定义至错误日志中
　　错误日志存储在数据库的数据文件目录中，名称为 hostname.err，其中 hostname 为服务器主机名。在 MySQL 5.5.7 之前，数据库管理员可以删除很长时间之前的错误日志，以节省服务器上的硬盘空间， MySQL 5.5.7 之后，服务器将关闭此项功能，只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的。命令为：

mv hostname.err hostname.err.old mysqladmin flush-logs

查询日志：

　　查询日志在 MySQL 中被称为 general log(通用日志)，查询日志里的内容不要被“查询日志”误导，认为里面只存储 select 语句，其实不然，查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录，具体原因如下:

insert 查询为了避免数据冲突，如果此前插入过数据，当前插入的数据如果跟主键或唯一键的数据重复那肯定会报错；
update 时也会查询因为更新的时候很可能会更新某一块数据；
delete 查询，只删除符合条件的数据；

　　因此都会产生日志，在并发操作非常多的场景下，查询信息会非常多，那么如果都记录下来会导致 IO 非常大，影响 MySQL 性能，因此如果不是在调试环境下，是不建议开启查询日志功能的。
　　查询日志的开启有助于帮助我们分析哪些语句执行密集，执行密集的 select 语句对应的数据是否能够被缓存，同时也可以帮助我们分析问题，所以，我们可以根据自己的实际情况来决定是否开启查询日志。
　　查询日志模式是关闭的，可以通过以下命令开启查询日志：

set global generallog=1 set global logoutput='table';

　　general_log=1 为开启查询日志，0 为关闭查询日志，这个设置命令即时生效，不用重启 MySQL 服务器。

慢查询日志：
　　慢查询会导致 CPU、IOPS、内存消耗过高，当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。开启慢查询日志，可以让 MySQL 记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中。使用如下命令记录当前数据库的慢查询语句：

set global slowquerylog='ON';

　　使用 set global slowquerylog='ON' 开启慢查询日志，只是对当前数据库有效，如果 MySQL 数据库重启后就会失效。所以如果要永久生效，就要修改配置文件 my.cnf，设置 slowquerylog=1 并重启 MySQL 服务器。

redo log（重做日志）：
　　为了最大程度的避免数据写入时，因为 IO 瓶颈造成的性能问题，MySQL 采用了这样一种缓存机制，先将数据写入内存中，再批量把内存中的数据统一刷回磁盘。为了避免将数据刷回磁盘过程中，因为掉电或系统故障带来的数据丢失问题，InnoDB 采用 redo log 来解决此问题。
　　redo log是固定大小的，比如配置一组4个文件，每个文件大小1G，那么该日志就可以记录4GB的操作。写到末尾又从头循环写。如下图所示：

2.MySQL日志

　　write pos是当前记录位置，一边写一边后移，check point是当前要擦除的位置，也是往后推移并循环，擦除记录前要把记录更新到数据文件。

　　两者之间是日志文件还空着的部分，可以用来记录新的操作，当write pos追上check point表示文件满了，此时不能再执行新的更新，需要停下先擦除一些记录，将check point推进一下。有了redo log，InnoDB可以保证即使数据库发生异常重启，之前提交的记录都不会丢失。这种能力称为crash-safe。
undo log（回滚日志）：
　　用于存储日志被修改前的值，从而保证如果修改出现异常，可以使用 undo log 日志来实现回滚操作。 undo log 和 redo log 记录物理日志不一样，它是逻辑日志，可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录，当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。undo log 默认存放在共享表空间中，在 MySQL 5.6 中，undo log 的存放位置还可以通过变量 innodbundodirectory 来自定义存放目录，默认值为“.”表示 datadir 目录。
bin log（二进制日志）：
　　是一个二进制文件，主要记录所有数据库表结构变更，比如，CREATE、ALTER TABLE 等，以及表数据修改，比如，INSERT、UPDATE、DELETE 的所有操作，bin log 中记录了对 MySQL 数据库执行更改的所有操作，并且记录了语句发生时间、执行时长、操作数据等其它额外信息，但是它不记录 SELECT、SHOW 等那些不修改数据的 SQL 语句。
　　binlog 默认是关闭状态，可以在 MySQL 配置文件（my.cnf）中通过配置参数 log-bin = [base-name] 开启记录 binlog 日志，如果不指定 base-name，则默认二进制日志文件名为主机名，并以自增的数字作为后缀，比如：mysql-bin.000001，所在目录为数据库所在目录（datadir）。通过以下命令来查询 binlog 是否开启：

show variables like 'log_%';

详细解释bin log

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

STATEMENT 格式的 binlog 记录的是数据库上执行的原生 SQL 语句。这种格式的优点是简单，简单地记录和执行这些语句，能够让主备保持同步，在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。另一个好处是二进制日志里的时间更加紧凑，所以相对而言，基于语句的复制模式不会使用太多带宽，同时也节约磁盘空间。并且通过 mysqlbinlog 工具容易读懂其中的内容。缺点就是同一条 SQL 在主库和从库上执行的时间可能稍微或很大不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的 SQL。比如，使用 INSERT INTO TB1 VALUE(CUURENT_DATE()) 这一条使用函数的语句插入的数据复制到当前从服务器上来就会发生变化，存储过程和触发器在使用基于语句的复制模式时也可能存在问题；另外一个问题就是基于语句的复制必须是串行化的，比如：InnoDB 的 next-key 锁等，并不是所有的存储引擎都支持基于语句的复制；
ROW 格式是从 MySQL 5.1 开始支持基于行的复制，也就是基于数据的复制，基于行的更改。这种方式会将实际数据记录在二进制日志中，它有其自身的一些优点和缺点，最大的好处是可以正确地复制每一行数据，一些语句可以被更加有效地复制，另外就是几乎没有基于行的复制模式无法处理的场景，对于所有的 SQL 构造、触发器、存储过程等都能正确执行；它的缺点就是二进制日志可能会很大，而且不直观，所以，你不能使用 mysqlbinlog 来查看二进制日志，也无法通过看二进制日志判断当前执行到那一条 SQL 语句。现在对于 ROW 格式的二进制日志基本是标配了，主要是因为它的优势远远大于缺点，并且由于 ROW 格式记录行数据，所以可以基于这种模式做一些 DBA 工具，比如数据恢复，不同数据库之间数据同步等；
MIXED 也是 MySQL 默认使用的二进制日志记录方式，但 MIXED 格式默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。比如用到 UUID()、USER()、CURRENTUSER()、ROWCOUNT() 等无法确定的函数。

binlog 的作用如下：

恢复（recovery）：某些数据的恢复需要二进制日志。比如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point-in-time 的恢复；
复制（replication）：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL数据库（一般称为 slave 或者 standby）与一台 MySQL 数据库（一般称为 master 或者 primary）进行实时同步；
审计（audit）：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击。

　　除了上面介绍的几个作用外，binlog 对于事务存储引擎的崩溃恢复也有非常重要的作用，在开启 binlog 的情况下，为了保证 binlog 与 redo 的一致性，MySQL 将采用事务的两阶段提交协议。当 MySQL 系统发生崩溃时，事务在存储引擎内部的状态可能为 prepared（准备状态）和 commit（提交状态）两种，对于 prepared 状态的事务，是进行提交操作还是进行回滚操作，这时需要参考 bin log，如果事务在 bin log 中存在，那么将其提交；如果不在 binlog 中存在，那么将其回滚，这样就保证了数据在主库和从库之间的一致性。
　　在InnoDB引擎下，一条更新语句的执行流程：

update T set c=c+1 where ID=2 //c原来为0
1、执行器先找引擎取ID=2这一行，ID是主键因此引擎直接用树搜索找到这行。如果该数据所在数据页存在内存中，则直接返回给执行器，否则，会先从磁盘读入内存，然后再返回。
2、执行器拿到数据，将值加1，得到新的数据，再调用引擎接口写入这行新数据。
3、引擎将这行新数据更新到内存，同时将这个更新操作记录到redo log，此时redo log处于prepare（准备）状态。然后告知执行器执行完成，随时可以提交事务。
4、执行器生成这个操作的bin log，并将其写入磁盘。
5、执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成commit状态，更新完成。

　　在上面过程中，redo log状态拆分成了两个步骤，分为了两阶段提交。这是为了让两份日志之间逻辑一致，保证数据的一致性

两阶段提交的作用：
　　当先写redo log，后写bin log：当redo log做完，而binlog还没有写完。MySQL进程异常重启。通过redo log数据将恢复，c的值就是1。但由于bin log没有写入，导致备份日志的时候，bin log没有这条更新语句，会导致更新丢失，恢复的c是0，与原库不同。
　　当先写bin log，后写redo log：当bin log写完之后，进程崩溃，但redo log中恢复没有记录到修改，因此c是0。但bin log已经记录了更新操作。备份恢复时就多出了一个更新事务，恢复出来的c是1，与原库值不同。
bin log 与 redo log的区别：

redo log是InnoDB引擎特有的，bin log是Server层实现的，所有引擎都可以使用。
redo log是物理日志，记录的是"在某个数据页上做了什么修改"，big log是逻辑日志，记录是这个语句的原始逻辑，比如"给ID=2这一行的c字段加1"。
redo log是循环写，空间固定会用完。bin log是追加写入，文件写到一定大小后会切换到下一个，不覆盖原来的日志。

　　最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统，也就是 redo log 来实现 crash-safe 能力。

参考文献：