>$ mysql --help | grep my.cnf

?参数文件：告诉MySQL实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。
?日志文件：用来记录MySQL实例对某种条件做出响应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。
?socket文件：当用UNIX域套接字方式进行连接时需要的文件。
?pid文件：MySQL实例的进程ID文件。
?MySQL表结构文件：用来存放MySQL表结构定义文件。
?存储引擎文件：因为MySQL表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。

参数文件

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

日志文件

?错误日志（error log）
?二进制日志（binlog）
?慢查询日志（slow query log）
?查询日志（log）

错误日志

>$ show variables like "log_error"

慢查询日志

>$ show variables like "long_query_time"

>$ show variables like "slow_query_log"

查询日志

查询日志甚至记录了对Access denied的请求，即对于未能正确执行的SQL语句，查询日志也会进行记录。

二进制日志

二进制日志（binary log）记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然而，若操作本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志。

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

套接字文件

在UNIX系统下本地连接MySQL可以采用UNIX域套接字方式，这种方式需要一个套接字（socket）文件。套接字文件可由参数socket控制。一般在/tmp目录下，名为mysql.sock：
（很少看到解释）

pid文件

当MySQL实例启动时，会将自己的进程ID写入一个文件中——该文件即为pid文件。该文件可由参数pid_file控制，默认位于数据库目录下，文件名为主机名.pid

表结构定义文件

不论表采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。

InnoDB存储引擎文件

InnoDB采用将存储的数据按表空间（tablespace）进行存放的设计。在默认配置下会有一个初始大小为10MB，名为ibdata1的文件。该文件就是默认的表空间文件（tablespace file），用户可以通过参数innodb_data_file_path对其进行设置。

若设置了参数innodb_file_per_table，则用户可以将每个基于InnoDB存储引擎的表产生一个独立表空间。独立表空间的命名规则为：表名.ibd。通过这样的方式，用户不用将所有数据都存放于默认的表空间中。

单独的表空间文件仅存储该表的数据、索引和插入缓冲BITMAP等信息，其余信息还是存放在默认的表空间中。

InnoDB表存储引擎文件

重做日志文件

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

每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组下至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。为了得到更高的可靠性，用户可以设置多个的镜像日志组（mirrored log groups），将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性。

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

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

首先，二进制日志会记录所有与MySQL数据库有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志。而InnoDB存储引擎的重做日志只记录有关该存储引擎本身的事务日志。
其次，记录的内容不同，无论用户将二进制日志文件记录的格式设为STATEMENT还是ROW，又或者是MIXED，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志。而InnoDB存储引擎的重做日志文件记录的是关于每个页（Page）的更改的物理情况。
此外，写入的时间也不同，二进制日志文件仅在事务提交前进行提交，即只写磁盘一次，不论这时该事务多大。而在事务进行的过程中，却不断有重做日志条目（redo entry）被写入到重做日志文件中。

重做日志写入过程

重做日志条目是由4个部分组成：
?redo_log_type占用1字节，表示重做日志的类型
?space表示表空间的ID，但采用压缩的方式，因此占用的空间可能小于4字节
?page_no表示页的偏移量，同样采用压缩的方式
?redo_log_body表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析

二进制日志的作用非常关键，可以用来进行point in time的恢复以及复制（replication）环境的搭建。

问题

二进制文件、undo、redo，这些文件有什么关系

• 首先，范围不同。二进制文件记录所有与MySQL相关的日志记录，包括InnoDB，MyISAM，Heap等存储引擎的日志。而InnoDB的重做日志只记录InnoDB相关的事务日志。
• 其次，内容不同。二进制文件记录的是关于一个事务的具体操作内容，而InnoDB的重做日志记录每个数据页（page）更改的物理情况。
• 写入的时间不同。二进制文件在事务提交之前记录，在事务进行过程中，不断有重做日志条目写入重做日志文件中。

表空间文件

.frm是描述了表的结构，.MYD保存了表的数据记录，*.MYI则是表的索引
假设我们使用系统默认的参数启动，则会有两个tablespace，

第一个tablespace包括一个文件ibdata1，这个也可以称为系统表空间，
另外一个space包括两个日志文件(iblogfile0, iblogfile1)

ibdata1文件是InnoDB存储引擎的共享表空间文件，该文件中主要存储着下面这些数据：

• data dictionary
• double write buffer
• insert buffer/change buffer
• rollback segments
• undo space
• Foreign key constraint system tables

无论是否启用了 innodb_file_per_table = 1，ibdata1文件都必须存在，因为它必须存储上述 InnoDB 引擎所依赖&必须的数据，尤其是上面加粗标识的 rollback segments 和 undo space，它俩是引起 ibdata1 文件大小增加的最大原因

一些表空间的基础知识

共享表空间ibdata和用户表空间是不一样的，因为它需要存储更多全局的一些信息，例如doublewrite,undo等等，所以共享表空间拥有更多的段

每个表空间都有一个唯一space id，因为很多地方都需要使用到这个id，例如内存数据刷到磁盘时，需要使用这个space id来寻找表空间文件．InnoDB总有一个"系统空间"，即共享表空间，这个表系统表空间的space id始终为0．

页空间结构

一个表空间文件被分为一个个16kb的页，每个页都有一个32位序号(page number)，通常称为偏移量，即离表空间初始位置的偏移量．因为每个页大小为16kb，所以第0个页的偏移量为0，第一个页的偏移量为16384等等．因为32位的最大值为2^{32，所以一个表空间的最大值为2}32*16kb=64TB．

共享表空间

共享表空间space id为0，包含了很多分配在固定偏移量上的页，用来存储和InnoDB操作相关的大量信息．系统表空间和其他空间一样，也有FSP_HDR,IBUF_BITMAP和INODE页，并且分配在前三个页，但是第四个页之后，和独立表空间就不太一样了

ibdata1 File Review

下面是分配的页:
Page 3，type SYS:保存和插入缓存相关的信息
Page 4,type INDEX:插入缓存的索引结构的root页，插入缓存也是一个B+树；
Page 5,type TRX_SYS: 记录和InnoDB事务操作相关的信息，例如最近一次的事务id,MySQL二进制日志信息以及两次写缓冲区的位置信息；
Page 6,type SYS: 第一个回滚段(rollback segment)页，回滚段例外的页或者整个区当需要的时候才会被分配出来存储回滚段信息；
Page 7,type SYS: 存储和数据字典相关的信息，包含了数据字典中所有表格root页面的个数．当我们要访问数据字典表页时，就需要这些信息．
Pages 64-127: 第一个double write buffer页块（64块，即一个extent）．double write buffer是InnoDB的一个恢复机制；
Pages 128-191:double write buffer的第二个页块．
系统表空间其他页则在索引，回滚段和undo logs等等需要时分配．

单独表空间

从MySQL5.6开始，默认就是一个表，一个表空间文件．以.ibd后缀的文件就是单独表空间文件，有如下结构

IBD File Overview

单独表空间文件结构很简单，没有共享表空间存储数据类型种类多；前三个页分别是FSP_HDR,IBUF_BITMAP和INODE页，从第四个页开始存储索引页．在InnoDB中，B+树内部节点页和叶子节点页统称为索引页(index pages)．第四个页固定为主键的root页，其他页则可能是内部节点页，主键叶子节点，辅助索引root页，辅助索引内部节点页和辅助索引叶子节点页．

因为大部分InnoDB系统信息都保存在共享表空间中，所以单独表空间(per-table space)大部分都是type INDEX页保存表数据．

InnoDB是支持MVCC的，它和ORACLE类似，采用 undo log、redo log来实现MVCC特性的。在事务中对一行数据进行修改时，InnoDB 会把这行数据的旧版本数据存储一份在undo log中，如果这时候有另一个事务又要修改这行数据，就又会把该事物最新可见的数据版本存储一份在undo log中，以此类推，如果该数据当前有N个事务要对其进行修改，就需要存储N份历史版本（和ORACLE略有不同的是，InnoDB的undo log不完全是物理block，主要是逻辑日志，这个可以查看 InnoDB 源码或其他相关资料）。这些 undo log 需要等待该事务结束后，并再次根据事务隔离级别所决定的对其他事务而言的可见性进行判断，确认是否可以将这些 undo log 删除掉，这个工作称为 purge（purge 工作不仅仅是删除过期不用的 undo log，还有其他，以后有机会再说）。

那么问题来了，如果当前有个事务中需要读取到大量数据的历史版本，而该事务因为某些原因无法今早提交或回滚，而该事务发起之后又有大量事务需要对这些数据进行修改，这些新事务产生的 undo log 就一直无法被删除掉，形成了堆积，这就是导致 ibdata1 文件大小增大最主要的原因之一。这种情况最经典的场景就是大量数据备份，因此我们建议把 备份工作放在专用的 slave server 上，不要放在 master server 上 。

另一种情况是，InnoDB的 purge 工作因为本次 file i/o 性能是在太差或其他的原因，一直无法及时把可以删除的 undo log 进行purge 从而形成堆积，这是导致 ibdata1 文件大小增大另一个最主要的原因。这种场景发生在服务器硬件配置比较弱，没有及时跟上业务发展而升级的情况。

比较少见的一种是在早期运行在32位系统的MySQL版本中存在bug，当发现待 purge 的 undo log 总量超过某个值时，purge 线程直接放弃抵抗，再也不进行 purge 了，这个问题在我们早期使用32位MySQL 5.0版本时遇到的比较多，我们曾经遇到这个文件涨到100多G的情况。后来我们费了很大功夫把这些实例都迁移到64位系统下，终于解决了这个问题。

最后一个是，选项 innodb_data_file_path 值一开始就没调整或者设置很小，这就必不可免导致 ibdata1 文件增大了。Percona官方提供的 my.cnf 参考文件中也一直没把这个值加大，让我百思不得其解，难道是为了像那个经常被我吐槽的xx那样，故意留个暗门，好方便后续帮客户进行优化吗？（我心理太阴暗了，不好不好~~）

稍微总结下，导致ibdata1文件大小暴涨的原因有下面几个：

• 有大量并发事务，产生大量的undo log；
• 有旧事务长时间未提交，产生大量旧undo log；
• file i/o性能差，purge进度慢；
• 初始化设置太小不够用；
• 32-bit系统下有bug。

InnoDB 还没有办法对 ibdata1 文件表空间进行回收/收缩，一旦 ibdata1 文件的肚子被搞大了，只能把数据先备份后恢复再次重新初始化实例才能恢复原先的大小，或者把依次把各个独立表空间文件备份恢复到一个新实例中，除此外，没什么更好的办法了。
相应的建议对策是：

• 升级到5.6及以上（64-bit），采用独立undo表空间，5.6版本开始就支持独立的undo表空间了，再也不用担心会把 ibdata1 文件搞大；
• 初始化设置时，把 ibdata1 文件至少设置为1GB以上；
• 增加purge线程数，比如设置 innodb_purge_threads = 8；
• 提高file i/o能力，该上SSD的赶紧上；
• 事务及时提交，不要积压；
• 默认打开autocommit = 1，避免忘了某个事务长时间未提交；
• 检查开发框架，确认是否设置了 autocommit=0，记得在事务结束后都有显式提交或回滚。

该FLUSH TABLES … FOR EXPORT语句确保对命名表的更改已刷新到磁盘，以便在实例运行时可以进行二进制表副本。当FLUSH TABLES … FOR EXPORT运行时， InnoDB产生了.cfg在同一个数据库的目录表文件。该.cfg文件包含导入表空间文件时用于模式验证的元数据。

这个命令值得一提的是，保持当前的窗口，不要关闭，如果关闭，cfg文件就会自动删除，可以看到命令运行后生成了cfg文件。

InnoDB表空间
MySQL · 引擎特性 · InnoDB 文件系统之文件物理结构
MySQL日志——Undo | Redo