乐趣区

关于mysql:MySQL原理文件

jiezi

参数文件

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

退出移动版