InnoDB 文件日志系统

本章将分析构成MySQL数据库和InnoDB存储引擎表的各种类型文件。这些文件如下。

1.参数文件:告诉MySQL实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。

2.日志文件:用来记录MySQL实例对某种条件做出响应时写人的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。

3.socket文件:当用UNIX域套接字方式进行连接时需要的文件。

4.pid文件:MySQL实例的进程ID文件。

5.MySQL表结构文件:用来存放MySQL表结构定义文件。

6.存储引擎文件:因为MySQL表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。本章主要介绍与InnoDB有关的存储引擎文件。

1.MySQL参数文件

MySQL数据库中的参数可以分为两类:

动态（dynamic）参数

静态（static）参数

动态参数意味着可以在MySQL实例运行中进行更改，静态参数说明在整个实例生命周期内都不得进行更改，就好像是只读(read only)的。可以通过SET命令对动态的参数值进行修改。

2. MySQL 日志文件

日志文件记录了影响MySQL数据库的各种类型活动。MySQL数据库中常见的日志文件有:

1.错误日志（error log)

2.查询日志（log）

查询日志记录了所有对MySQL数据库请求的信息，无论这些请求是否得到了正确的执行。默认文件名为:主机名.log。通过上述查询日志会发现，查询日志甚至记录了对Access denied的请青>之，要能正确执行的SQL语句，查询日志也会进行记录。同样地，从MySQL 5.1开始，可以将查询日志的记录放人mysql架构下的general_log 表中，该表的使用方法和前面小节提到的slow_log基本一样，这里再赘述。这些日志文件可以帮助DBA对MySQL数据库的运行状态进行诊断，从而更好地进行数据库层面的优化。

3.慢查询日志（slow query log)

可以在MySQL启动时设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。DBA每天或每过一段时间对其进行检查，确认是否有SQL语句需要进行优化。该阈值可以通过参数long_query_time来设置，默认值为10，代表10秒。从MySQL 5.1开始， long_query_time开始以微秒记录SQL语句运行的时间，之前仅用秒为单位记录。而这样可以更精确地记录SQL的运行时间，供DBA分析。对DBA来说，一条SQL语句运行0.5秒和0.05秒是非常不同的，前者可能已经进行了表扫，后面可能是进行了索引。

另一个和慢查询日志有关的参数是 log_queries_not_using_indexes，如果运行的SQL语句没有使用索引，则MySQL数据库同样会将这条SQL 语句记录到慢查询日志文件。

4.二进制日志（binlog）

a

二进制日志（binary log）记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。

恢复（recovery):某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后， 用户可以通过二进制日志进行point-in-time的恢复。

复制（replication):其原理与恢复类似， 通过复制和执行二进制日志使一台远程的MySQL数据库（般称为slave或standby）与一台MySQL数据库（一般称为master或primary）进行实时同步。

审计(audit):用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

二进制日志文件在默认情况下并没有启动，需要手动指定参数来启动。可能有人会质疑，开启这个选项是否会对数据库整体性能有所影响。不错，开启这个选项的确会影响性能，但是性能的损失十分有限。根据MySQL官方手册中的测试表明，开启二进制日志会使性能下降1%。但考虑到可以使用复制（replication）和point-in-time的恢复，这些性能损失绝对是可以且应该被接受的。

参数max_binlog_size指定了单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件，后缀名+1，并记录到.index文件。从MySQL 5.0开始的默认值为1073 741824，代表1G（在之前版本中max_binlog_size默认大小为1.1G)。 当使用事务的表存储引擎（如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=[N]表示每写缓冲多少次就同步到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。sync_binlog 的默认值为0，如果使用InnoDB存储引擎进行复制，并且想得到最大的高可用性，建议将该值设为ON。不过该值为ON时，确实会对数据库的IO系统带来一定的影响。

binlog_format参数十分重要，它影响了记录二进制日志的格 式 。在MySQL 5.1版本之前，没有这个参数。所有二进制文件的格式都是基于SQL语句(statement）级别的，因此基于这个格式的二进制日志文件的复制（Replication）和Oracle的逻辑Standby有点相似。同时，对于复制是有一定要求的。 如在主服务器运行rand、者使用触发器等操作，这些都可能会导致主从服务器上表中数据的不一致（not sync)。另一个影响是，会发现InnoDB存储引擎的默认事务隔离级别是REPEATABLEREAD。这其实也是因为二进制日志文件格式的关系，如果使用READ COMMITTED的事务隔离级别（大多数数据库，如Oracle，Microsoft sQL Server数据库的默认隔离级别)，会出现类似丢失更新的现象，从而出现 主从数据库上的数据不一致，因为statement是按顺序记录每条sql语句，不针对具体得数据行，比如使用limit 10 删除前十条数据但是没具体指定哪十条，sesson完全可能删了不同得数据，因为这个时候从库和主库复制延迟问题，删除会变得错乱。

MySQL5.1开始引入了binlog_format参数，该参数可设的值有STATEMENT[逻辑复制]、ROW[物理复制]和MIXED[混合复制]。

(1）STATEMENT格式和之前的MySQL版本-样，二进制日志文件记录的是日志的逻辑SQL语句。

(2） 在ROW格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表的某一行更改情况。基于ROW格式的复制类似于Oracle的物理Standby（当然，还是有些区别)。 同时，对上述提及的Statement格式下复制的问题予以解决。从MySQL 5.1版本开始，如果设置了binlog_format为ROW，可以将InnoDB的事务隔离基本设为READCOMMITTED，以获得更好的并发性。

(3）在MIXED格式下，MySQL默认采用STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式，可能的情况有:

    1）表的存储引擎为NDB，这时对表的DML操作都会以ROW格式记录。

    2）使用了UUID)、USER()、CURRENT_USER()、FOUND_ROWSO、ROW_COUNTO 等不确定函数。

    3）使用了INSERTDELAY语句。

    4）使用了用户定义函数（UDF)。

    5）使用了临时表（temporary table)。

3.InnoDB存储文件

【重做日志文件】

也叫redolog，当实例或介质失败（media failure）时，重做日志文件就能派上用场。 例如，数据库 由于所在主机掉电导致实例失败，InnoDB存储引擎会使用重做日志恢复到掉电前的时 刻，以此来保证数据的完整性，这个日志重用就是用来实现崩溃恢复。-每个InnoDB存储引擎至少有1个重做日志文件组（group)，每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。为了得到更高的可靠性，用户可以设置多个的镜像日志组（mirrored log groups)，将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致，并以循环写人的方式运行。InnoDB存储引擎先写重做日志文件1，当达到文件的最后q会切换至重做日志文件2，再当重做日志文件2也被写满时，会再切换到重做日志文件1中。

参数innodb_log_file_size指定每个重做日志文件的大小。在InnoDB1.2.x版本之前， 重做日志文件总的大小不得大于等于4GB，而1.2.x版本将该限制扩大为了512GB。 重做日志文件不能设置得太大，如果设置得很大，在恢复时可能需要很长的时间a 方面又不能设置得太小了，否则可能导致一个事务的日志需要多次切换重做日志文件。 此外，重做日志文件太小会导致频繁地发生async checkpoint，导致性能的抖动。例如，

参数innodb_flush_log_at_trx_commit的有效值有0、1、2。

0代表当提交事务时，并不将事务的重做日志写人磁盘上的日志文件，而是等待主线程每秒的刷新。1和2不同的地方在于:1表示在执行commit时将重做日志缓冲同步写到磁盘，即伴有fsync的调用。2表示将重做日志异步写到磁盘，即写到文件系统的缓存中。因此不能完全保证在执行commit时肯定会写入重做日志文件，只是有这个动作发生。 因此为了保证事务的ACID中的持久性，必须将innodb_flush_log_at_trx_commit设

置为1，也就是每当有事务提交时，就必须确保事务都已经写入重做日志文件。那么当 数据库因为意外发生宕机时，可以通过重做日志文件恢复，并保证可以恢复已经提交的 事务。而将重做日志文件设置为0或2，都有可能发生恢复时部分事务的丢失。不同之处在于，设置为2时，当MySQL数据库发生宕机而操作系统及服务器并没有发生宕机时，由于此时未写人磁盘的事务日志保存在文件系统缓存中，当恢复时同样能保证数据