第3章 文件

第3章 文件

构成MySQL数据库和InnoDB存储引擎表的各种类型文件有:

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

3.1 参数文件

MySQL的参数文件是文本类型的，也就是可以通过一些常用的编辑软件(vi、emacs)对参数进行编辑。
当MySQL实例启动时，MySQL会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存结构等设置。默认情况下，MySQL实例会按照一定的次序去读取，可以通过命令mysql --help | grep my.cnf得到读取的顺序。
MySQL实例启动时若找不到参数文件，这会使用编译MySQL时指定的默认值和源代码中指定参数的默认值。但是，如果MySQL在默认的数据库目录下找不到mysql架构，则启动会失败，同时会在错误日志中找到相关信息。

什么是参数
简单地说，可以把数据库参数看成一个键值对。
可以通过show variables查看所有的参数，通过like来过滤参数名。

参数类型
MySQL参数文件中的参数可以分为两类：动态(dynamic)参数和静态(static)参数。动态参数意味着可以在MySQL实例运行中进行更改；静态参数说明在整个实例生命周期内都不得进行更改，就好像是只读(read only)的。可以通过SET命令对动态的参数值进行修改，SET的语法如下:

SET
| [global | session] system_var_name = expr
| [@@global. | @@session. | @@ ]system_var_name = expr

这里可以看到global和session关键字，它们表明该参数是基于当前会话还是整个实例的生命周期。有些动态参数只能在会话中进行修改，如autocommit；有些参数修改完后，在整个实例生命周期中都会生效，如binlog_cache_size；而有些参数既可以在会话又可以在整个实例的生命周期内生效，如read_buffer_size。

set read_buffer_size = 524288;//这个设置了当前会话的参数
select global read_buffer_size = 2093056;
select @@global.read_buffer_size = 2093056;//和上面相同

对变量的全局值进行修改，在这次的实例生命周期内都有效，但MySQL实例本身并不会对参数文件中的该值进行修改。也就是说，下次启动时，MySQL实例还是会读取参数文件。

3.2 日志文件

日志文件记录了影响MySQL数据库的各种类型活动。MySQL数据库中常见的日志文件有错误日志、二进制日志、慢查询日志、查询日志。

错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。该文件不但记录了出错信息，也记录了一些警告信息或者正确的信息。

show variables like 'log_error';
system hostname

通过上面的命令可以看到错误文件的路径和文件名，默认情况下错误文件的文件名为服务器的主机名。当出现MySQL数据库不能正常启动时，第一个必须查找的文件应该就是错误日志文件，该文件记录了出错信息。

慢查询日志

慢查询能为SQL语句的优化带来很好的帮助。
可以设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中，该阈值可以通过参数long_query_time来设置，默认值为10.000000，以微秒记录，这里代表10秒。
可以通过slow_query_log变量，查看是否开启了慢查询日志。
另一个和慢查询有关的参数是log_queries_not_using_indexes，如果运行的SQL语句没有使用索引，则MySQL数据库同样会将这条SQL语句记录到慢查询日志文件中。
随着MySQL数据库服务器运行时间的增加，可能会有越来越多的SQL查询被记录到了慢查询日志文件中，这时要分析就显得不容易了，MySQL这时提供了mysqldumpslow命令:

[root@xxx data]# mysqldumpslow xxx-slow.log
[root@xxx data]# mysqldumpslow -s al -n 10 xxx-slow.log#锁定时间最长的10条SQL语句

MySQL5.1开始可以将查询的日志记录放入一张表中，这使我们的查询更加直观。
参数log_output指定了慢查询输出的格式，默认为FILE,当把它设置为TABLE时，就可以查询mysql架构下的slow_log表了。参数log_output是动态的，并且是全局的。可以在线进行修改。

mysql> show create table mysql.slow_log;
mysql> show variables like 'log_output';
mysql> set global log_output = 'TABLE';
mysql> select sleep(10);
mysql> select *from mysql.slow_log\G;

查询slow_log表的定义会发现，该表使用的是CSV引擎，对大数据量下的查询效率可能不高，可以把slow_log表的引擎转换到MyISAM，用来进一步提高查询的效率。但此时需要将慢查询关闭！！！

mysql> set global slow_query_log = off;

查询日志

查询日志记录了所有对MySQL数据库的请求信息，不论这些请求是否得到了正确执行。默认文件名为：主机名.log。查询日志的记录也可能在mysql架构下的general_log表。

二进制日志

二进制日志记录了对数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，如果想记录SELECT和SHOW操作，那只能使用查询日志，而不是二进制日志。此外，二进制日志还包括了执行数据库更改操作的时间和执行时间等信息。二进制日志主要有以下两种作用：

• 恢复(recovery)。某些数据的恢复需要二进制日志。如当一个数据库全备文件恢复后，可以通过二进制日志进行point-in-time的恢复。
• 复制(replication)。通过复制和执行二进制日志使得一台远程的MySQL数据库(一般称为slave或者standby)与一台MySQL数据库(一般称为master或者primary)进行实时同步。

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

mysql> show variables like 'datadir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| datadir       | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.09 sec)

mysql> system ls -lh /usr/local/mysql/data/;

其中bin_log.00001即为二进制日志文件，我们在配置文件中指定了名称，所有没有用默认的文件名。bin_log.index为二进制的索引文件，用来存储过往生产的二进制日志序号。
二进制日志文件在默认情况下没有启动，需要手动指定参数来启动。
以下配置文件的参数影响着二进制日志记录的信息和行为：

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

参数max_binlog_size指定了单个二进制日志文件的最大值，如果超过这个值，则产生新的二进制日志文件，后缀名加1，并记录到.index文件，从MySQL5.0开始的默认值为1GB。

当使用事务的表存储引擎(如InnoDB存储引擎)时，所有未提交(uncommitted)的二进制日志会被记录到一个缓存中，等该事务提交时(committed)时直接将缓存中的二进制日志写入二进制日志文件，而该缓存的大小由binlog_cache_size决定，默认大小为32KB。此外，binlog_cache_size是基于会话(session)的，也就是说，当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值的设置需要谨慎，不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓存中
的日志写入一个临时文件中，因此该值也不能设置太小。通过show global status命令查看binlog_cache_use、binlog_cache_disk_use的状态，可以判断当前设置是否合适。binlog_cache_use记录了使用缓存写二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。

默认情况下，二进制日志不是每次写的时候同步到磁盘，因此，当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中。这会给恢复和复制带来问题。参数sync_binlog=[N]表示每写缓冲多少次就同步到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。该值默认是0，如果使用InnoDB存储引擎进行复制，并且想得到最大的高可用性，建议将该值设为ON。
但是，即使将sync_binlog设为1，还是会有一种情况会导致问题的发生。当使用InnoDB存储引擎时，在一个事务发出COMMIT动作之前，由于sync_binlog设为1，因此会将二进制日志立即写入磁盘。如果这时已经写入二进制文件，但是提交还没有发生，并且此时发生了宕机，那么在MySQL数据库下次启动时，因为COMMIT操作并没有发生，所以这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。这个问题可以通过innodb_support_xa设为1来解决，虽然innodb_support_xa与XA事务有关，但它同时也确保了二进制日志和InnoDB存储引擎数据文件的同步。

参数binlog-do-db和binlog-ignore-db表示需要写入或者忽略写入哪些库的日志。默认为空，表示需要将所有库的日志同步到二进制日志。
如果当前数据库是复制中的salve角色，则它不会将从master取得并执行的二进制日志写入自己的二进制日志文件中。如果需要写入，则需要设置log-slave-update。如果你需要搭建master=>slave=>slave架构的复制，则必须设置该参数。

binlog_format影响了二进制日志的格式。该参数可设的值有STATEMENT、ROW和MIXED。

• STATEMENT，二进制日志文件记录的是日志的逻辑SQL语句。
• ROW，记录表的行更改情况，可以将InnoDB的事务隔离级别设为READ COMMITTED，以获得更好的并发性。
• MIXED，MySQL默认采用STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式，可能的情况有:
  1）表的存储引擎为NDB，这时对于表的DML操作都会以ROW格式记录。
  2）使用了UUID0、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数。
  3）使用了INSERT DELAY语句。
  4）使用用户定义函数(UDF)。
  5）使用了临时表(temporary table)。

binlog_format是动态参数，因此可以在数据库运行环境下进行更改。
ROW模式下，记录同样的信息会占用更多的空间，因为MySQL数据库不再将逻辑的SQL操作记录到二进制日志，而是记录对于每行的更改记录信息。
二进制日志文件的文件格式为二进制，不能像错误日志文件，慢查询日志文件用cat、head、tail等命令来查看。想要查看二进制日志文件的内容，需要通过MySQL提供的工具mysqlbinlog，对于STATEMENT格式的二进制日志文件，使用mysqlbinlog后，看到就是执行的逻辑SQL语句。

[root@xxx data]# mysqlbinlog --start-position=1065 test.000004

但是如果使用ROW格式的记录方式时，需要添加-v或-vv(显示更多信息)。

[root@xxx data]# mysqlbinlog -vv --start-position=1065 test.000004

3.3 套接字文件

Unix系统下本地连接MySQL可以采用Unix域套接字方式，这种方式需要一个套接字(socket)文件。套接字文件可由参数socket控制。一般在/tmp目录下，名为mysql.sock。

mysql> show variables like 'socket';

3.4 pid文件

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

3.5 表结构定义文件

因为MySQL插件式存储引擎的体系结构的关系，MySQL对于数据的存储是按照表的，所以每个表都会有与之对应的文件，不论采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。
frm还可以用来存放视图的定义。

3.6 InnoDB存储引擎文件

表空间文件

InnoDB存储引擎将存储的数据按表空间进行存放。默认配置下，会有一个初始化大小为10MB，名为ibdata1的文件，该文件就是默认的表空间文件(tablespace file),也可以通过参数innodb_file_path进行设置。格式如下；

innodb_data_file_path=datafile_spac1[;datafile_space2]...

也可以通过用多个文件组成一个表空间，同时制定文件的属性。如:

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

这里将/db/ibdata1和/dr2/db/ibdata2两个文件用来组成表空间。若两个文件位于不同的磁盘上，则可以对性能带来一定程度的提升。两个文件的文件名后都跟了属性，表示文件大小。但是如果文件满了，则可以自动增长(auto extend)。
通过设置参数innodb_file_per_table，可以将每个基于InnoDB存储引擎的表单独产生一个表空间，文件名为表名.ibd，这样不用将所有数据都存放于默认的表空间中。但是，单独的表空间文件仅存储该表的数据、索引和插入缓冲等信息，其余信息还是存放在默认的表空间中。

重做日志文件

默认情况下会有两个文件，名称分别为ib_logfile0和ib_logfile1。它们记录了对于InnoDB存储引擎的事务日志。
重做日志文件的主要目的是，万一实例或者介质失败(media failure)，就可以使用重做日志文件保证数据的完整性。
每个InnoDB存储引擎至少有一个重组日志文件组(group)。每个文件组至少有2个重做日志文件，如默认的ib_logfile0和ib_logfile1。为了得到更高的可靠性，可以设置多个镜像日志组(mirrored log groups)，将不同的文件组放在不同的磁盘上。日志组中每个重做日志文件大小一致，并以循环方式使用。InnoDB存储引擎先写重做日志1，当达到文件的最后时，会切换至重做日志2，当重做日志2页被写满时，会再切换到重做日志1中。
参数innodb_log_file_size指定了重做日志文件的大小；innodb_log_files_in_group指定了日志文件组中重做日志的数量，默认是2；innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认是1，表示没有镜像。innodb_log_group_home_dir指定了日志文件组所在路径，默认在数据库路径下。
二进制日志与重做日志文件的不同。首先，二进制日志会记录所有与MySQL有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志。而InnoDB存储引擎的重做日志只记录有关其本身的事务日志。其次，记录的内容不同，不管二进制日志文件记录的格式是什么，其记录的都是关于一个事务的具体操作内容。而InnoDB存储引擎的重做日志文件记录的是关于每个页(Page)的更改的物理情况。此外，写入的时间也不同，二进制日志文件是在事务提交前进行记录的，而在事务进行的过程中，不断有重做日志条目(redo entry)被写入重做日志文件中。

对于写入重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲(redo log buffer)中，然后根据按照一定的条件写入日志文件。这些条件:主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已经提交。另一个触发这个过程是由参数innodb_flush_log_at_trx_commit控制，表示在提交(commit)操作时，处理重做日志的方式。
参数innodb_flush_log_at_trx_commit可设的值有0、1、2。

• 0，代表可提交事务时，并不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒的刷新。
• 1，在commit时将重做日志缓冲同步写入磁盘。
• 2，重做日志异步写到磁盘，即不能完全保证commit时肯定会写入重做日志文件，只是有这个动作。