mysql日志文件

错误日志

• 错误日志对mysql的启动，运行和关闭过程进行了记录。当mysql遇到问题的时候，应该首先查看mysql错误日志；
• 可以通过show variables like 'log_error';来定位错误日志文件；

慢查询日志

• 慢查询日志可以帮助mysql使用人员定位可能存在问题的sql语句，从而进行sql语句层面的优化；
• 可以通过show variables like 'long_query_time';来设置阈值；

查询日志

• 查询日志记录了所有对mysql数据库请求的信息，无论这些信息是否得到了正确的执行。默认文件名是：主机名.log

二进制日志

• 二进制日志（binlog）记录了对mysql数据库执行更改的所有操作，不包括select或者show这类操作。

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

• 缓冲：当使用事务的表存储引擎（例如InnoDB存储引擎）时，所有未提交的二进制日志会被记录到一个缓冲中去，等该事务提交时直接将缓冲中的二进制日志写入二进制文件中，该缓冲的大小由binlog_cache_size决定。默认32k，此外，该参数是基于会话的。该参数不能设置过大，也不能设置过小，导致一次完整事务不能记录在该缓冲中，这是就需要写入到临时文件中。可以通过show global status like 'binlog_cache%'命令来查看当前的binlog_cache_size是否合适。如果查询出来的binlog_cache_disk_use为0或者远远小于binlog_cache_use的值，说明当前的binlog_cache_size完全够用；

• 同步写：在默认情况下，二进制日志并不是在每次写的时候同步到磁盘的。因此，当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入到二进制日志文件中，这会给恢复和复制带来问题。参数sync_binlog=[N]表示每次写缓冲多少次就同步到磁盘。如果将N设置为1，就表示采用同步写的方式来写二进制文件。不过这种设置会对数据库的IO造成一定的影响。
  但是，即使将sync_binlog设置为1，还是会有一种情况导致问题的发生，那就是当使用InnoDB存储引擎的时候，在一个事务发出commit动作之前，由于sync_binlog设置为1，因此会将二进制日志立即写入到磁盘，如果这时已经写入到了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在mysql数据库下次启动的时候，由于commit操作并没有发生，因此这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。这时可以通过设置innodb_support_xa为1来解决。它确保了二进制日志和InnoDB存储引擎数据文件的同步。

• 日志格式：
  – STATEMENT格式：和之前的mysql版本一样，二进制日志记录的是日志的逻辑sql语句；
  – ROW格式：二进制日志记录的不再是简单的sql语句，而是记录表的行更改情况。配合READ COMMITTED的事务隔离可以获得更好的并发性；
  – MIXED格式：mysql默认采用STATEMENT格式进行二进制日志的记录，但是在一些情况，例如使用NDB作为存储引擎，或者使用UUID()等一些函数，或者使用INSERT DELAY语句等，会使用ROW格式；

当用户执行了一条update语句，使用STATEMENT格式的二进制日志会增加大概200字节，而使用ROW日志格式的二进制日志会增加大概13MB左右，要知道一个该表的大小不过17MB左右。这时因为MYSQL数据库不再将逻辑的SQL操作记录到二进制日志中，而是记录对于每行的更改。因此使用ROW格式来记录二进制日志，会对磁盘空间有一定要求，同是复制的网络开销也会有所增加。

ROW和STATEMENT对比：

• Row ：
  日志中会记录每一行数 据被修改的形式，然后在slave端再对相同的数据进行修改
  优点：在row level模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条被修改。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定的情况下的存储过程或function，以及trigger的调用和触发无法被正确复制的问题
  缺点：row level，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，会产生大量的日志内容。

• Statement
  每一条会修改数据的sql都会记录到master的bin-log中。slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql来再次执行
  优点：statement level下的优点首先就是解决了row level下的缺点，不需要记录每一行数据的变化，减少bin-log日志量，节约IO，提高性能，因为它只需要在Master上锁执行的语句的细节，以及执行语句的上下文的信息。
  缺点：由于只记录语句，所以，在statement level下 已经发现了有不少情况会造成MySQL的复制出现问题，主要是修改数据的时候使用了某些定的函数或者功能的时候会出现。

• Mixed
  在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志格式，也就是在Statement和Row之间选择一种。如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。

企业场景如何选择binlog模式
1、互联网公司，使用MySQL的功能相对少（存储过程、触发器、函数）
选择默认的语句模式，Statement Level（默认）
2、公司如果用到使用MySQL的特殊功能（存储过程、触发器、函数）
则选择Mixed模式
3、公司如果用到使用MySQL的特殊功能（存储过程、触发器、函数）又希望数据最大化一直，此时最好选择Row level模式

事务日志

事务日志分为两种，redo log和undo log。这两种日志分别有不同的作用。

redo log

redo log是为了防止数据库宕机导致数据页被破坏，适用于崩溃恢复，保证了数据库ACID中的持久性。同时它可以降低数据库刷新数据页这种消耗较高的写磁盘的操作，从而提高数据库的运行效率。

redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo log file)。 mysql 每执行一条 DML 语句，先将记录写入 （redo log buffer )，后续某个时间点再一次性将多个操作记录写到 redo log file 。这种 先写日志，再写磁盘 的技术就是 MySQL
里经常说到的 WAL(Write-Ahead Logging) 技术。

mysql 支持三种将 redo log buffer 写入 redo log file 的时机，可以通过innodb_flush_log_at_trx_commit 参数配置，各参数值含义如下：

参数值	含义
0（延迟写）	事务提交时不会将 redo log buffer 中日志写入到 os buffer ，而是每秒写入 os buffer 并调用 fsync() 写入到 redo log file 中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
1（实时写，实时刷）	事务每次提交都会将 redo log buffer 中的日志写入 os buffer 并调用 fsync() 刷到 redo log file 中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
2（实时写，延迟刷）	每次提交都仅写入到 os buffer ，然后是每秒调用 fsync() 将 os buffer 中的日志写入到 redo log file 。

前面说过， redo log 实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此 redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。

启动 innodb 的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为 redo log 记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如 binlog )要快很多。 重启 innodb 时，首先会检查磁盘中数据页的 LSN ，如果数据页的 LSN 小于日志中的 LSN ，则会从 checkpoint 开始恢复。 还有一种情况，在宕机前正处于checkpoint 的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的 LSN 大于日志中的 LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

redo log与binlog区别

	redo log	binlog
文件大小	redo log 的大小是固定的。	binlog 可通过配置参数 max_binlog_size 设置每个 binlog 文件的大
实现方式	redo log 是 InnoDB 引擎层实现的，并不是所有引擎都有。	binlog 是 Server 层实现的，所有引擎都可以使用 binlog 日志
记录方式	redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。	binlog通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上
适用场景	redo log 适用于崩溃恢复(crash-safe)	binlog 适用于主从复制和数据恢复

由 binlog 和 redo log 的区别可知： binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。但只有 redo log 也不行，因为 redo log 是 InnoDB
特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog 和 redo log
二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

undo log

数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。
实际上， 原子性 底层就是通过 undo log 实现的。 undo log 主要记录了数据的逻辑变化，比如一条 INSERT语句，对应一条 DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。同时， undo log 也是MVCC(多版本并发控制)实现的关键，