参数文件

当MySQL实例启动时，数据库会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存构造有多大等。在默认状况下，MySQL实例会依照肯定的程序在指定的地位进行读取，用户只需通过命令mysql --help | grep my.cnf来寻找即可。

简略地说，能够把数据库参数看成一个键/值（key/value）对。能够通过命令SHOWVARIABLES查看数据库中的所有参数，也能够通过LIKE来过滤参数名。从MySQL5.1版本开始，还能够通过information_schema架构下的GLOBAL_VARIABLES视图来进行查找。

MySQL数据库中的参数能够分为两类：动静（dynamic）参数和动态（static）参数。动静参数意味着能够在MySQL实例运行中进行更改，动态参数阐明在整个实例生命周期内都不得进行更改，就如同是只读（read only）的。

能够通过SET命令对动静的参数值进行批改，global和session关键字表明该参数的批改是基于以后会话还是整个实例的生命周期。有些动静参数只能在会话中进行批改，如autocommit；而有些参数批改完后，在整个实例生命周期中都会失效，如binlog_cache_size；而有些参数既能够在会话中又能够在整个实例的生命周期内失效，如read_buffer_size。

须要留神的是，对变量的全局值进行了批改，在这次的实例生命周期内都无效，但MySQL实例自身并不会对参数文件中的该值进行批改。也就是说，在下次启动时MySQL实例还是会读取参数文件。若想在数据库实例下一次启动时该参数还是保留为以后批改的值，那么用户必须去批改参数文件。要想晓得MySQL所有动静变量的可批改范畴，能够参考MySQL官网手册的DynamicSystem Variables的相干内容。

注：在mysql8中，能够通过set persist命令来长久化对全局变量的批改。

日志文件

谬误日志

谬误日志文件对MySQL的启动、运行、敞开过程进行了记录。MySQL DBA在遇到问题时应该首先查看该文件以便定位问题。该文件不仅记录了所有的错误信息，也记录一些正告信息或正确的信息。用户能够通过命令SHOW VARIABLES LIKE'log_error'来定位该文件。

慢查问日志

慢查问日志（slow log）可帮忙DBA定位可能存在问题的SQL语句，从而进行SQL语句层面的优化。例如，能够在MySQL启动时设一个阈值，将运行工夫超过该值的所有SQL语句都记录到慢查问日志文件中。DBA每天或每过一段时间对其进行查看，确认是否有SQL语句须要进行优化。

无关慢查问的一些配置项如下：

  • slow_query_log：指定是否开启慢查问日志
  • log_slow_queries：指定是否开启慢查问日志(该参数要被slow_query_log取代，做兼容性保留)
  • long_query_time：设定慢查问的阈值，超出次设定值的SQL即被记录到慢查问日志，缺省值为10s（从MySQL 5.1开始，long_query_time开始以微秒记录SQL语句运行的工夫）
  • slow_query_log_file：指定慢日志文件寄存地位，能够为空，零碎会给一个缺省的文件host_name-slow.log
  • min_examined_row_limit：查问查看返回少于该参数指定行的SQL不被记录到慢查问日志
  • log_queries_not_using_indexes: 不应用索引的查问是否记录到慢查问日志
  • log_throttle_queries_not_using_indexes: 每分钟记录到日志的未应用索引的语句数目，超过这个数目后只记录语句数量和破费的总工夫
  • log_output: 慢查问日志的存储形式，默认值是'FILE'，设为'TABLE'示意将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中

查问日志

查问日志记录了所有对MySQL数据库申请的信息，无论这些申请是否失去了正确的执行。默认文件名为：主机名.log，从MySQL 5.1开始，能够将查问日志的记录放入mysql架构下的general_log表中。

二进制日志

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

如果用户想记录SELECT和SHOW操作，那只能应用查问日志，而不是二进制日志。此外，二进制日志还包含了执行数据库更改操作的工夫等其余额定信息。总的来说，二进制日志次要有以下几种作用：

  • 复原（recovery）：某些数据的复原须要二进制日志，例如，在一个数据库全备文件复原后，用户能够通过二进制日志进行point-in-time的复原。
  • 复制（replication）：其原理与复原相似，通过复制和执行二进制日志使一台近程的MySQL数据库（个别称为slave或standby）与一台MySQL数据库（个别称为master或primary）进行实时同步。
  • 审计（audit）：用户能够通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻打。

通过配置参数log-bin[=name]能够启动二进制日志。如果不指定name，则默认二进制日志文件名为主机名，后缀名为二进制日志的序列号，所在门路为数据库所在目录（datadir）。

以下配置文件参数影响着二进制日志记录的信息和行为：

  • max_binlog_size
  • binlog_cache_size
  • sync_binlog
  • binlog-do-db
  • binlog-ignore-db
  • log-slave-update
  • binlog_format

参数max_binlog_size指定了单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件，后缀名+1，并记录到.index文件。

当应用事务的表存储引擎（如InnoDB存储引擎）时，所有未提交（uncommitted）的二进制日志会被记录到一个缓存中去，等该事务提交（committed）时间接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定，默认大小为32K。此外，binlog_cache_size是基于会话（session）的，也就是说，当一个线程开始一个事务时，MySQL会主动调配一个大小为binlog_cache_size的缓存，因而该值的设置须要相当小心，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因而该值又不能设得太小。通过SHOW GLOBALSTATUS命令查看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。

然而，即便将sync_binlog设为1，还是会有一种状况导致问题的产生。当应用InnoDB存储引擎时，在一个事务收回COMMIT动作之前，因为sync_binlog为1，因而会将二进制日志立刻写入磁盘。如果这时曾经写入了二进制日志，然而提交还没有产生，并且此时产生了宕机，那么在MySQL数据库下次启动时，因为COMMIT操作并没有产生，这个事务会被回滚掉。然而二进制日志曾经记录了该事务信息，不能被回滚。这个问题能够通过将参数innodb_support_xa设为1来解决，尽管innodb_support_xa与XA事务无关，但它同时也确保了二进制日志和InnoDB存储引擎数据文件的同步。

注：二进制日志的写入机会是事务语句实现的时候，此时还未真正执行commit和开释锁。

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

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

如果以后数据库是复制中的slave角色，则它不会将从master获得并执行的二进制日志写入本人的二进制日志文件中去。如果须要写入，要设置log-slave-update。如果须要搭建master=>slave=>slave架构的复制，则必须设置该参数。

binlog_format参数非常重要，它影响了记录二进制日志的格局。在MySQL 5.1版本之前，没有这个参数。所有二进制文件的格局都是基于SQL语句（statement）级别的，因而基于这个格局的二进制日志文件的复制（Replication）和Oracle的逻辑Standby有点类似。同时，对于复制是有肯定要求的。如在主服务器运行rand、uuid等函数，又或者应用触发器等操作，这些都可能会导致主从服务器上表中数据的不统一（not sync）。另一个影响是，会发现InnoDB存储引擎的默认事务隔离级别是REPEATABLE READ。这其实也是因为二进制日志文件格式的关系，如果应用READ COMMITTED的事务隔离级别（大多数数据库，如Oracle，Microsoft SQL Server数据库的默认隔离级别），会呈现相似失落更新的景象，从而呈现主从数据库上的数据不统一。

MySQL 5.1开始引入了binlog_format参数，该参数可设的值有STATEMENT、ROW和MIXED。

  1. STATEMENT格局和之前的MySQL版本一样，二进制日志文件记录的是日志的逻辑SQL语句。
  2. 在ROW格局下，二进制日志记录的不再是简略的SQL语句了，而是记录表的行更改状况。基于ROW格局的复制相似于Oracle的物理Standby（当然，还是有些区别）。同时，对上述提及的Statement格局下复制的问题予以解决。从MySQL5.1版本开始，如果设置了binlog_format为ROW，能够将InnoDB的事务隔离根本设为READ COMMITTED，以取得更好的并发性。
  3. 在MIXED格局下，MySQL默认采纳STATEMENT格局进行二进制日志文件的记录，然而在一些状况下会应用ROW格局，可能的状况有：

1）表的存储引擎为NDB，这时对表的DML操作都会以ROW格局记录。
2）应用了UUID（）、USER（）、CURRENT_USER（）、FOUND_ROWS（）、ROW_COUNT（）等不确定函数。
3）应用了INSERT DELAY语句。
4）应用了用户定义函数（UDF）。
5）应用了长期表（temporary table）。

在通常状况下，咱们将参数binlog_format设置为ROW，这能够为数据库的复原和复制带来更好的可靠性。然而不能疏忽的一点是，这会带来二进制文件大小的减少，有些语句下的ROW格局可能须要更大的容量。而因为复制是采纳传输二进制日志形式实现的，因而复制的网络开销也有所增加。

二进制日志文件不能像谬误日志文件、慢查问日志文件那样用cat、head、tail等命令来查看。要查看二进制日志文件的内容，必须通过MySQL提供的工具mysqlbinlog。

InnoDB存储引擎文件

表空间文件

InnoDB采纳将存储的数据按表空间（tablespace）进行寄存的设计。在默认配置下会有一个初始大小为10MB，名为ibdata1的文件。该文件就是默认的表空间文件（tablespace file），用户能够通过参数innodb_data_file_path对其进行设置，用户能够通过多个文件组成一个表空间，同时制订文件的属性，如：

[mysqld]innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend

这里将/db/ibdata1和/dr2/db/ibdata2两个文件用来组成表空间。若这两个文件位于不同的磁盘上，磁盘的负载可能被均匀，因而能够进步数据库的整体性能。同时，两个文件的文件名后都跟了属性，示意文件idbdata1的大小为2000MB，文件ibdata2的大小为2000MB，如果用完了这2000MB，该文件能够主动增长（autoextend）。

设置innodb_data_file_path参数后，所有基于InnoDB存储引擎的表的数据都会记录到该共享表空间中。若设置了参数innodb_file_per_table，则用户能够将每个基于InnoDB存储引擎的表产生一个独立表空间。独立表空间的命名规定为：表名.ibd。通过这样的形式，用户不必将所有数据都寄存于默认的表空间中。

须要留神的是，这些独自的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是寄存在默认的表空间中。下图显示了InnoDB存储引擎对于文件的存储形式：

重做日志文件

在默认状况下，在InnoDB存储引擎的数据目录下会有两个名为ib_logfile0和ib_logfile1的文件。在MySQL官网手册中将其称为InnoDB存储引擎的日志文件，不过更精确的定义应该是重做日志文件（redo log file）。重做日志文件对于InnoDB存储引擎至关重要，它们记录了对于InnoDB存储引擎的事务日志。

当实例或介质失败（media failure）时，重做日志文件就能派上用场。例如，数据库因为所在主机掉电导致实例失败，InnoDB存储引擎会应用重做日志复原到掉电前的时刻，以此来保证数据的完整性。

每个InnoDB存储引擎至多有1个重做日志文件组（group），每个文件组下至多有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。为了失去更高的可靠性，用户能够设置多个的镜像日志组（mirrored log groups），将不同的文件组放在不同的磁盘上，以此进步重做日志的高可用性。在日志组中每个重做日志文件的大小统一，并以循环写入的形式运行。InnoDB存储引擎先写重做日志文件1，当达到文件的最初时，会切换至重做日志文件2，再当重做日志文件2也被写满时，会再切换到重做日志文件1中。

下列参数影响着重做日志文件的属性：

  • innodb_log_file_size
  • innodb_log_files_in_group
  • innodb_mirrored_log_groups
  • innodb_log_group_home_dir

参数innodb_log_file_size指定每个重做日志文件的大小。在InnoDB1.2.x版本之前，重做日志文件总的大小不得大于等于4GB，而1.2.x版本将该限度扩充为了512GB。

参数innodb_log_files_in_group指定了日志文件组中重做日志文件的数量，默认为2。参数innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1，示意只有一个日志文件组，没有镜像。若磁盘自身曾经做了高可用的计划，如磁盘阵列，那么能够不开启重做日志镜像的性能。最初，参数innodb_log_group_home_dir指定了日志文件组所在门路，默认为./，示意在MySQL数据库的数据目录下。

重做日志文件的大小设置对于InnoDB存储引擎的性能有着十分大的影响。一方面重做日志文件不能设置得太大，如果设置得很大，在复原时可能须要很长的工夫；另一方面又不能设置得太小了，否则可能导致一个事务的日志须要屡次切换重做日志文件。此外，重做日志文件太小会导致频繁地产生async checkpoint，导致性能的抖动。

重做日志有一个capacity变量，该值代表了最初的检查点不能超过这个阈值，如果超过则必须将缓冲池（innodb buffer pool）中脏页列表（flush list）中的局部脏数据页写回磁盘，这时会导致用户线程的阻塞。

同样是记录事务日志，和之前介绍的二进制日志有什么区别？

首先，二进制日志会记录所有与MySQL数据库无关的日志记录，包含InnoDB、MyISAM、Heap等其余存储引擎的日志。而InnoDB存储引擎的重做日志只记录无关该存储引擎自身的事务日志。

其次，记录的内容不同，无论用户将二进制日志文件记录的格局设为STATEMENT还是ROW，又或者是MIXED，其记录的都是对于一个事务的具体操作内容，即该日志是逻辑日志。而InnoDB存储引擎的重做日志文件记录的是对于每个页（Page）的更改的物理状况。

此外，写入的工夫也不同，二进制日志文件仅在事务提交前进行提交，即只写磁盘一次，不管这时该事务多大。而在事务进行的过程中，却一直有重做日志条目（redo entry）被写入到重做日志文件中。

从重做日志缓冲往磁盘写入时，是按512个字节，也就是一个扇区的大小进行写入。因为扇区是写入的最小单位，因而能够保障写入必然是胜利的。因而在重做日志的写入过程中不须要有doublewrite。

从日志缓冲写入磁盘上的重做日志文件是按肯定条件进行的，主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不管事务是否曾经提交。另一个触发写磁盘的过程是由参数innodb_flush_log_at_trx_commit管制，示意在提交（commit）操作时，解决重做日志的形式。

参数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数据库产生宕机而操作系统及服务器并没有产生宕机时，因为此时未写入磁盘的事务日志保留在文件系统缓存中，当复原时同样能保证数据不失落。