MySQL 5.7 物理文件体系结构的简单整理说明

 

本文出处:http://www.cnblogs.com/wy123/p/7102128.html 
(保留出处并非什么原创作品权利,本人拙作还远远达不到,仅仅是为了链接到原文,因为后续对可能存在的一些错误进行修正或补充,无他)

 

本文的数据库版本是MySQL5.7.18,简单介绍一下MySQL数据文件目录的物理结构和作用,从中可以窥见MySQL的整体上的物理文件结构以及逻辑功能。
可以从整体结构上了解到MySQL的物理体系架构(本人学习的思路往往是被与已了解的事物对照学习,或者快速了解其轮廓,再逐步细化整个知识体系)
鉴于MySQL中任何一项逻辑性或者物理性文件都具有可配置性,另外就是由于开源,MySQL在每个大版本中都有一些改进的东西,不能根据某一项或者默认配置生搬硬套。

 

如下图是MySQL(5.7.18)在Linux系统yum默认安装的数据文件目录,可以看到有如下几类文件。

 

1,数据库路径:可以看到,系统数据库和用户自定义的数据库都是一个路径,展开具体的路径之后是具体的每个数据库自己的对象)
2,logbin二进制日志文件:如果开启了二进制日志,会有若干个二进制日志文件(如图的mysql-bin.000042,mysql-bin.000043)与其对应的描述文件(mysql-bin.index)
3,redo重做日志文件:ib_logfile0,ib_logfile1,是支持事务性引擎的redo日志文件
4,共享表空间:ibdata1,如果指定innodb表为非独立文件的,用户自定义库中的表的数据就存储在共享表空间中。
  即便是innnodb表指定为独立表空间,用户自定义库中的某些元数据信息(比如存储过程等信息都存储在共享表空间中)。
  同时,共享表空间还负责存储undo数据的存储的作用(undo数据也即事物性操作的数据的修改之前的值)。
  不过,而从5.6开始,用户可以把undo log存储到独立的tablespace中,并拆分成多个Undo log文件。
5,临时表空间:ibtemp1,存储临时对象的空间,比如临时表对象等。
6,errorlog:error_log.log,记录启动、运行或停止MySQL服务器过程,以及MySQL运行过程中一些较为严重的错误信息
7,mysql.sock:作用是MySQL服务器本身的客户端连接的时候,发起本地连接时可用
8,slow_log:截图的MySQL服务中尚未配置慢查询日志,如果配置了MySQL的慢查询日志,MySQL会将运行过程中的慢查询日志记录到slow_log文件中
9,general_log:同上面的8,截图的服务器尚未配置MySQL的通用查询日志,如果配置了通用查询日志,MySQL将运行过程中的所有sql都记录在此文件中。
10,另外一个是最终的MySQL的配置文件,my.cnf,YUM安装的MySQL的配置文件my.cnf默认在etc目录下

  

 

  上述文件可以分类之后用结构化的方式展现出来,如下,也即上述描述的一系列文件结构的归类展现
  需要说明的是,上述列举的一系列文件中尚未包括一些文件,比如启用复制的时候的中继日志文件等等(粗浅拙图,大神轻喷)

 

    

下面对从类别上对各个文件进行一个简单的说明:

  系统数据库

  在MySQL5.7.18中,系统数据库包括information_schema,mysql,sys,performance_schema
  1,information_schema库,提供了数据库的元数据信息,是数据库的数据,比如数据库的名字,数据库中的表名,字段名,字段类型等,可以说是数据库的数据字典信息。
    这个库中的信息并非物理地保存在表中,而是动态地去读取其他文件得到的,比如上面一开始提到的共享表空间,对于用户数据中的对象,比如表结构等,都保存在共享表空间中,
    information_schema库中的一些信息可以认为是直接映射到共享表空间中的信息的。因此第一个截图中,并没有information_schema的路径(文件夹)
  2,performance_schema库,是数据库性能相关的信息的数据,记录的是数据库服务器的性能参数。
    1)保留进程等待信息,包括锁,互斥变量,文件信息等。
    2)保存历史事件汇总信息,为MySQL服务器性能评估提供参考信息
    3)配置型选项,来决定是否记录一些与性能相关的信息,比如profile信息等,参考http://www.cnblogs.com/wy123/p/6979499.html
  3,sys库,可以根据sys库中的数据快速了解系统的运行信息,方便地查询出来数据库的信息,在性能瓶颈,自动化吧运维等方面都有很大的帮助
    sys库中的信息是通过视图的方式,将information_schema和performance_schema库中的数据结合起来,可以得到更加直观和容易理解的信息
    4,mysql库,存储了系统的用户权限信息及帮助信息,新建的用户,用户的权限信息的都存储在MySQL库。
    比如在修改MySQL的root密码的时候,都要先use mysql这个系统库,然后再执行用户,授权等操作。

  用户数据库

  用户数据库实际上是一个目录,目录中保存了数据库中的表以及数据信息,如下截图是一个典型的数据库目录下的文件信息。
  对于innodb引擎的表,一个表分别对应两个文件,一个是*.frm,存储的是表结构信息,一个是*.ibd,存储的是表中的数据,从大小也可以看出来*.ibd较大而*.frm较小。
  另外一个文件是db.opt,保存的是数据库的配置信息,比如编码信息等。
  对于innodb表,如果是独立的表空间的话,数据库中的表结构以及数据都存储在数据库的路径下(而不是在共享表空间中ibdata1文件中)
  但是数据中的其他对象,包括undo信息,也即数据被修改之后,事务提交之间的版本信息,仍然存储在共享表空间的ibdata1文件中

     test_database1对应的数据库物理文件

  

  test_database1对应的逻辑数据库如下

 

 基于ibdata1文件的共享表空间

 对于innodb,innodb_file_per_table选项决定了是否启动独立表空间,MySQL5.7中是默认启动的,也就是说MySQL的用户数据库将使用独立表空间来存储数据,

 

正如截图看到的,本测试服务器的共享表空间中只有一个文件,如下通过show variables like 'innodb_data%';命令可以查询共享表空间的文件信息,实际上共享表空间可以配置成多个物理文件。

  

  关于共享表空间和独立表空间都有各自的优缺点,本文不在抄了,也可以将数据文件从共享表空间转移到独立表空间。
  不过从当前(MySQL5.7.18)来看,MySQL默认innodb引擎默认是独立的表空间,让MySQL数据文件住上“单间”而不是集体宿舍,可见独立的表空间还是有一定优势的。

 

     基于ibtmp1文件的临时表空间

   临时表空间是存储全局级,回话级,事物级,检索级临时表对象的地方,有参数innodb_temp_data_file_path可以看到临时表空间的信息。

   

  有关临时表空间更多的信息,请参考:https://yq.aliyun.com/ziliao/89528

  

  基于ib_logfileN的重做日志

  redo日志默认情况下有两个文件,也即:ib_logfile0和ib_logfile1,如果在数据库启动的过程中没有这两个文件,系统会默认自动生成这两个文件。
  默认情况下,ib_logfile0和ib_logfile1是两个独立的日志文件(可以配置的更多个ib_logfile文件),但是redo日志的写入在逻辑上对于ib_logfile0和ib_logfile1是连续的。
  重做日志是MySQL事物处理的核心文件,事务处理的核心之一是一致性,也就是说要么全做,要么全不做。
  事物性操作都是基于一个或者多个表中部分数据的操作,为了保证一致性,在确保事物的一致性的时候,需要事物提交的时候直接或者间接写盘操作。
  MySQL事物操作是logwrite-ahead操作,也即先写日志(具体怎么写日志取决于innodb_flush_log_at_trx_commit的配置),相当于间接写盘操作。
  目的是将对数据库具体的数据文件的分散随机写入(多个表的数据写入数据文件)转换成基于日志的顺序写入操作,而数据文件是异步写盘,
  如果数据文件写盘异常,可以通过redo日志来“重做”,据此来提高事物性操作的效率。
  redo日志空间的使用,在逻辑上相当于一个环形空间,redo日志不断向前推进写入记录,后台的定时执行的checkpoint将事务修改过尚未写盘的记录异步写入数据文件之后,日志空间可重用。

   

 

  基于mysql-bin.n的二进制日志

  bin-log日志记录数据中发生的写入性操作(增删改),但不记录查询操作,语句以事件的方式保存,描述了数据的更改过程,此日志对发生灾难时数据恢复起到了极为重要的作用。
  对应的物理文件如截图

  

  MySQL默认情况下并没有开启二进制日志,需要在my.cnf中配置log_bin的路径,重启MySQL之后会自动开启log_bin二进制日志记录功能。
  这其中包括了一系列关于log_bin的配置。

包括:
  1)log-bin的路径配置:log-bin=/var/lib/mysql/mysql-bin
  2)二进制日志的格式:binlog_format = MIXED
  3)设置日志文件的失效期:expire_logs_days = n,参数为expire_logs_days,set global expire_log_days=n,N天前的日志自动删除;
  4)二进制日志缓存大小,binlog_cache_size = ***:二进制日志缓存大小,是每一个连接进来的线程分配的大小,不是整个服务器的大小;
  5)最大缓存大小:max_binlog_cache_size=***
  6)单个文件最大大小,:max_binlog_size = 100m,单个文件最大大小,超过此大小则再分配一个文件,但是一个事务必须在一个文件中,所以可能会稍大点;
具体配置的详细信息如下截图所示。

  

  开启log_bin之后,可以在系统变量中查询到相关的配置。

  

     在数据库发生灾难的时候,可以借助完整备份和差异备份进行大部分数据的恢复,但是log_bin记录了数据库中所有发生的事件,
  因此在完整备份和差异备份还原的基础上,借助l最后一次差异备份(前提是有完整备份)的log_bin,可以将数据库恢复至具体的某一个时间点。
     关于数据中的操作(增删改)记录到log_bin的时机,有sync_binlog参数进行控制。
  sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,
  此时(sync_binlog=0)就增加了不可控性(事物提交后还有多少日志尚未写入log_bin文件)。
  sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
  在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。
  因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候,是最安全但是性能损耗最大的设置。
  因为当设置为1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响(如果此时宕机,可以借助重做日志进行重做)。

  sync_binlog也是一个系统变量,可以通过如下命令查看配置信息。

  

  详情参考这里:http://www.cnblogs.com/ggjucheng/archive/2012/11/15/2771535.html

 

  undo表空间

  redo重做日志在提高事物性操作的效率的同时,也通过redo重做机制保证了事物的可靠性。
  如果事物回滚,则需要依赖undo日志进行回滚操作,MySQL在进行事物操作的同时,会记录事务性操作修改数据之前的信息,就是undo日志,确保可以回滚到事物发生之前的状态
  默认情况下,MySQL将undo日志记录在共享表空间中(上文提到的bdata1共享文件),如果事物成功提交,记录在共享表空间的undo日志会被后台进程做puege清理
  当然这个undo日志也有差别,如果是update操作之后提交事务,undo日志还需要为MVCC提供服务器,如果是insert操作,事物提交之后可以直接清理回滚日志记录。

 

  不过,而从5.6开始,用户可以把undo log存储到独立的tablespace中,并拆分成多个Undo log文件。

  详情参考:http://mysqllover.com/?p=873

  

 总结:

  本位粗浅地分析了MySQL物理文件的结构以及对应的逻辑功能,多数配置是按照默认情况下来进行展现的,从中可以对MySQL的物理结构以及逻辑功能进行一个简单又粗浅的说明(很粗很浅,欢迎指正)。
  但终究是窥探管中窥豹仅见一斑而已,路曼曼其修远。中间经历了各种翻书,各种查资料,各种抄(学习不就是一个模仿和创造的过程,自我安慰一下)。
  再次说明,MySQL每一步都是可配置化的,配置不同,甚至每个MySQL的发型版本,某些地方都不尽一致,切勿照搬。