MySQL文件概述

---

---

 Mysql 数据库的文件包括：

1、参数文件：my.cnf

2、日志文件，包括错误日志、查询日志、慢查询日志、二进制日志

3、Mysql表文件：用来存放mysql表结构的文件，一般以.frm为后缀

4、Socket文件：当用unix域套接字方式进行连接时需要的文件

5、Pid文件：mysql实例的进程ID文件

6、存储引擎文件：每个存储引擎都有自己的文件夹来保存各种数据，这些存储引擎真正存储了数据和索引等数据。

---

参数文件

可以通过命令行“mysql --help | grep my.cnf”查看my.cnf文件的位置；

Mysql在启动时可以不需要参数文件，但是如果在默认的数据库目录下找不到mysql架构，则启动会失败；

Mysql的参数可以通过“show variables”来查看，由于从mysql5.1版本开始，可以通过information_schema架构下的GLOBAL_VARIABLES视图来进行查找，所以也可以这样查看

“select * from information_schema.global_variables”；

Mysql 的参数类型：分为动态（dynamic）和静态参数（static），动态参数意味着可以再mysql实例运行中进行更改；静态参数说明在整个实例声明周期内都不得进行更改，就好像是只读的。在动态参数中，有些参数修改可以是基于回话的也可以是基于整个实例的生命周期。Mysql5.1的动态参数在这里。

---

Mysql表文件

不论采用何种搜索引擎，mysql都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义文件。值得注意的是，frm还可以用来存放视图的定义。

---

Socket文件

通过使用命令“show variables like ‘socket’;”查看socket文件的目录

 

---

Pid 文件

当mysql实例启动时，会将自己的进程ID写入一个文件中，该文件即为pid文件，文件名为主机名.pid，通过命令“show variables like ‘pid_file’;”来查看。

 

---

日志文件  http://blog.itpub.net/26736162/viewspace-2142929/

1、错误日志，该文件对mysql的启动、运行、关闭过程进行了记录，在遇到问题时，首先应该查看此文件，可以通过“show variables like ‘log_error’;”来定位该文件。

 

2、慢查询日志，该文件记录了所有运行时间超过阀值的SQL语句，该阀值可以通过参数long_query_time来设置。默认值为10秒。

 

但是在默认情况下，mysql并不启动满查询日志，需要手工修改这个参数；

 

需要注意的两点内容：首先，慢查询日志记录的是大于阀值的SQL语句，而不是大于等于！！！其次，从mysql5.1开始，long_quey_time开始以微秒记录sql语句运行时间。

另一个和慢查询相关的参数是log_queries_not_using_index，如果运行的SQL语句没有使用索引，则mysql则同样会将这条SQL语句记录到慢查询日志文件中；

 

在慢查询日志文件逐渐增大时，可考虑使用工具mysqldumpslow工具帮助我们分析。

该工具的使用您可以使用 “man mysqldumpslow” 或者去这里了解。

Mysql5.1开始可以将慢查询的日志记录放入一张表中，该表在mysql.slow_log表中。

是否放在表中由参数‘log_output’来决定；参数log_output指定了慢查询输出的格式，默认为FILE,也可以将其设置为TABLE，就可以去Mysql.slow_log中去查询了。

 

同样也可以将查询日志的记录放入mysql架构下的general_log表。

3、查询日志

查询日志记录了所有对Mysql数据库请求的信息。

4、二进制日志

因为二进制日志太重要了，在这里暂且不提。

InnoDB存储引擎文件

这些文件包括表空间文件和重做日志文件。表空间的介绍请看Mysql InnoDB存储结构总结，重做日志也很重要，在这里暂且不提。

---

---

本章将分析构成MySQL数据库和InnoDB存储引擎表的各种类型文件，如下所示。

参数文件：告诉MySQL实例启动时在哪里可以找到数据库文件，并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。

日志文件：用来记录MySQL实例对某种条件做出响应时写入的文件。如错误日志文件、二进制日志文件、满查询日志文件、查询日志文件等。

socket文件：当用Unix域套接字方式进行连接时需要的文件。

pid文件：MySQL实例的进程ID文件。

MySQL表结构文件：用来存放MySQL表结构定义文件。

存储引擎文件：因为MySQL表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了数据和索引等数据。本章主要介绍与InnoDB有关的存储引擎文件。

3.1   参数文件

在第1章中已经介绍过了，当MySQL实例启动时，MySQL会先去读一个配置参数文件，用来寻找数据库的各种文件所在位置以及指定某些初始化参数，这些参数通常定义了某种内存结构有多大等设置。默认情况下，MySQL实例会按照一定的次序去取，你只需通过命令mysql --help | grep my.cnf来寻找即可。

MySQL参数文件的作用和Oracle的参数文件极其类似；不同的是，Oracle实例启动时若找不到参数文件，是不能进行装载（mount）操作的。MySQL稍微有所不同，MySQL实例可以不需要参数文件，这时所有的参数值取决于编译MySQL时指定的默认值和源代码中指定参数的默认值。但是，如果MySQL在默认的数据库目录下找不到mysql架构，则启动同样会失败，你可能在错误日志文件中找到如下内容：

1. 090922 16:25:52  mysqld started  
2. 090922 16:25:53  InnoDB: Started; log sequence number 8 2801063211  
3. InnoDB: !!! innodb_force_recovery is set to 1 !!!  
4. 090922 16:25:53 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist  
5. 090922 16:25:53  mysqld ended 

MySQL中的mysql架构中记录了访问该实例的权限，当找不到这个架构时，MySQL实例不会成功启动。

和Oracle参数文件不同的是，Oracle的参数文件分为二进制的参数文件（spfile）和文本类型的参数文件（init.ora），而MySQL的参数文件仅是文本的，方便的是，你可以通过一些常用的编辑软件（如vi和emacs）进行参数的编辑。

3.1.1   什么是参数

简单地说，可以把数据库参数看成一个键/值对。第2章已经介绍了一个对于InnoDB存储引擎很重要的参数innodb_buffer_pool_size。如我们将这个参数设置为1G，即innodb_buffer_pool_size=1G,，这里的“键”是innodb_buffer_pool_size，“值”是1G，这就是我们的键值对。可以通过show variables查看所有的参数，或通过like来过滤参数名。从MySQL 5.1版本开始，可以通过information_schema架构下的GLOBAL_VARIABLES视图来进行查找，如下所示。

1. mysql> select * from GLOBAL_VARIABLES where  VARIABLE_NAME like 'innodb_buffer%'\G;  
2. *************************** 1. row ***************************  
3. VARIABLE_NAME: INNODB_BUFFER_POOL_SIZE  
4. VARIABLE_VALUE: 1073741824  
5. 1 row in set (0.00 sec)  
6.  
7. mysql> show variables like 'innodb_buffer%'\G;  
8. *************************** 1. row ***************************  
9. Variable_name: innodb_buffer_pool_size  
10.         Value: 1073741824  
11. 1 row in set (0.00 sec) 

无论使用哪种方法，输出的信息基本上都一样的，只不过通过视图GLOBAL_ VARIABLES需要指定视图的列名。推荐使用show variables命令，因为这个命令使用更为简单，各版本的MySQL数据库都支持它。

Oracle的参数有所谓的隐藏参数（undocumented parameter），以供Oracle“内部人士”使用，SQL Server也有类似的参数。有些DBA曾问我，MySQL中是否也有这类参数。我的回答是：没有，也不需要。即使Oracle和SQL Server中都有些所谓的隐藏参数，在绝大多数情况下，这些数据库厂商也不建议你在生产环境中对其进行很大的调整。

3.1.2   参数类型

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

1. SET   
2. | [global | session] system_var_name= expr 
3. | [@@global. | @@session. | @@]system_var_name= expr 

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

1. mysql> set read_buffer_size=524288;  
2. Query OK, 0 rows affected (0.00 sec)  
3.  
4. mysql> select @@session.read_buffer_size\G;  
5. *************************** 1. row ***************************  
6. @@session.read_buffer_size: 524288  
7. 1 row in set (0.00 sec)  
8.  
9. mysql> select @@global.read_buffer_size\G;  
10. *************************** 1. row ***************************  
11. @@global.read_buffer_size: 2093056  
12. 1 row in set (0.00 sec) 

上面我将read_buffer_size的会话值从2MB调整为了512KB，你可以看到全局的read_buffer_size的值仍然是2MB，也就是说，如果有另一个会话登录到MySQL实例，它的read_buffer_size的值是2MB，而不是512KB。这里使用了set global|session来改变动态变量的值。我们同样可以直接使用set @@globl|@@session来更改，如下所示：

1. mysql> set @@global.read_buffer_size=1048576;  
2. Query OK, 0 rows affected (0.00 sec)  
3.  
4. mysql> select @@session.read_buffer_size\G;  
5. *************************** 1. row ***************************  
6. @@session.read_buffer_size: 524288  
7. 1 row in set (0.00 sec)  
8.  
9. mysql> select @@global.read_buffer_size\G;  
10. *************************** 1. row ***************************  
11. @@global.read_buffer_size: 1048576  
12. 1 row in set (0.00 sec) 

这次我们把read_buffer_size全局值更改为1MB，而当前会话的read_buffer_size的值还是512KB。这里需要注意的是，对变量的全局值进行了修改，在这次的实例生命周期内都有效，但MySQL实例本身并不会对参数文件中的该值进行修改。也就是说下次启动时，MySQL实例还是会读取参数文件。如果你想让数据库实例下一次启动时该参数还是保留为当前修改的值，则必须修改参数文件。要想知道MySQL所有动态变量的可修改范围，可以参考MySQL官方手册的第5.1.4.2节（Dynamic System Variables）的相关内容。

对于静态变量，如果对其进行修改，会得到类似如下的错误：

1. mysql> set global datadir='/db/mysql';  
2. ERROR 1238 (HY000): Variable 'datadir' is a read only variable 

3.2   日志文件

日志文件记录了影响MySQL数据库的各种类型活动。MySQL数据库中常见的日志文件有错误日志、二进制日志、慢查询日志、查询日志。这些日志文件为DBA对数据库优化、问题查找等带来了极大的便利。

3.2.1   错误日志

错误日志文件对MySQL的启动、运行、关闭过程进行了记录。MySQL DBA在遇到问题时应该首先查看该文件。该文件不但记录了出错信息，也记录一些警告信息或者正确的信息。总的来说，这个文件更类似于Oracle的alert文件，只不过在默认情况下是err结尾。你可以通过show variables like 'log_error'来定位该文件，如：

1. mysql> show variables like 'log_error';  
2. +---------------+-------------------------------------------+  
3. | Variable_name | Value                       |  
4. +---------------+-------------------------------------------+  
5. | log_error     | /usr/local/mysql/data/stargazer.err |  
6. +---------------+-------------------------------------------+  
7. 1 row in set (0.00 sec)  
8.  
9. mysql> system hostname  
10. stargazer 

可以看到错误文件的路径和文件名，默认情况下错误文件的文件名为服务器的主机名。如上面我们看到的，该主机名为stargazer，所以错误文件名为startgazer.err。当出现MySQL数据库不能正常启动时，第一个必须查找的文件应该就是错误日志文件，该文件记录了出错信息，能很好地指导我们找到问题，如果当数据库不能重启，通过查错误日志文件可以得到如下内容：

1. [root@nineyou0-43 data]# tail -n 50 nineyou0-43.err   
2. 090924 11:31:18  mysqld started  
3. 090924 11:31:18  InnoDB: Started; log sequence number 8 2801063331  
4. 090924 11:31:19 [ERROR] Fatal error: Can't open and lock privilege tables:   
5. Table 'mysql.host' doesn't exist  
6. 090924 11:31:19  mysqld ended 

这里，错误日志文件提示了你找不到权限库mysql，所以启动失败。有时我们可以直接在错误日志文件里得到优化的帮助，因为有些警告（warning）很好地说明了问题所在。而这时我们可以不需要通过查看数据库状态来得知，如：

1. 090924 11:39:44  InnoDB: ERROR: the age of the  last checkpoint is 9433712,  
2. InnoDB: which exceeds the log group capacity 9433498.  
3. InnoDB: If you are using big BLOB or TEXT rows,  you must set the  
4. InnoDB: combined size of log files at least 10  times bigger than the  
5. InnoDB: largest such row.  
6. 090924 11:40:00  InnoDB: ERROR: the age of the  last checkpoint is 9433823,  
7. InnoDB: which exceeds the log group capacity 9433498.  
8. InnoDB: If you are using big BLOB or TEXT rows,  you must set the  
9. InnoDB: combined size of log files at least 10 times bigger than the  
10. InnoDB: largest such row.  
11. 090924 11:40:16  InnoDB: ERROR: the age of the last checkpoint is 9433645,  
12. InnoDB: which exceeds the log group capacity 9433498.  
13. InnoDB: If you are using big BLOB or TEXT rows, you must set the  
14. InnoDB: combined size of log files at least 10  times bigger than the  
15. InnoDB: largest such row. 

3.2.2   慢查询日志（1）

前一小节提到可以通过错误日志得到一些关于数据库优化的信息帮助，而慢查询能为SQL语句的优化带来很好的帮助。可以设一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志文件中。该阈值可以通过参数long_query_time来设置，默认值为10，代表10秒。

默认情况下，MySQL数据库并不启动慢查询日志，你需要手工将这个参数设为ON，然后启动，可以看到如下结果：

1. mysql> show variables like '%long%';  
2. +-----------------+------------+  
3. | Variable_name   | Value |  
4. +-----------------+------------+  
5. | long_query_time | 10    |   
6. +-----------------+------------+  
7. 1 row in set (0.00 sec)  
8.  
9. mysql> show variables like 'log_slow_queries';  
10. +------------------+------------+  
11. | Variable_name | Value |  
12. +------------------+------------+  
13. | log_slow_queries | ON|   
14. +------------------+------------+  
15. 1 row in set (0.01 sec) 

这里需要注意两点。首先，设置long_query_time这个阈值后，MySQL数据库会记录运行时间超过该值的所有SQL语句，但对于运行时间正好等于long_query_time的情况，并不会被记录下。也就是说，在源代码里是判断大于long_query_time，而非大于等于。其次，从MySQL 5.1开始，long_query_time开始以微秒记录SQL语句运行时间，之前仅用秒为单位记录。这样可以更精确地记录SQL的运行时间，供DBA分析。对DBA来说，一条SQL语句运行0.5秒和0.05秒是非常不同的，前者可能已经进行了表扫，后面可能是走了索引。下面的代码中，是在MySQL 5.1中将long_query_time设置为了0.05：

1. mysql> show variables like 'long_query_time';  
2. +-----------------+--------------+  
3. | Variable_name   | Value|  
4. +-----------------+--------------+  
5. | long_query_time | 0.050000 |  
6. +-----------------+--------------+  
7. 1 row in set (0.00 sec) 

另一个和慢查询日志有关的参数是log_queries_not_using_indexes，如果运行的SQL语句没有使用索引，则MySQL数据库同样会将这条SQL语句记录到慢查询日志文件。首先，确认打开了log_queries_not_using_indexes:

1. mysql> show variables like 'log_queries_not_using_indexes';  
2. +-------------------------------+------------------+  
3. | Variable_name         | Value|  
4. +-------------------------------+------------------+  
5. | log_queries_not_using_indexes | ON  |  
6. +-------------------------------+------------------+  
7. 1 row in set (0.00 sec)  
8. update 'low_game_schema'.'item' set SLOT='8' where GUID='2222249168632297608' and is_destroy='0'; 

这里详细记录了SQL语句的信息，如上述SQL语句运行的账户和IP、运行时间、锁定的时间、返回行等。我们可以通过慢查询日志来找出有问题的SQL语句，对其进行优化。随着MySQL数据库服务器运行时间的增加，可能会有越来越多的SQL查询被记录到了慢查询日志文件中，这时要分析该文件就显得不是很容易了。MySQL这时提供的mysqldumpslow命令，可以很好地解决这个问题：

1. [root@nh122-190 data]# mysqldumpslow nh122-190-slow.log  
2. Reading mysql slow query log from nh122-190-slow.log  
3. Count: 11  Time=10.00s (110s)  Lock=0.00s (0s)   Rows=0.0 (0), dbother[dbother]@localhost  
4.   insert into test.DbStatus select now(),(N-com_ select)/(N-uptime),(N-com_insert)/(N-uptime),(N-com_ update)/(N-uptime),(N-com_delete)/(N-uptime),N-(N/N), N-(N/N),N.N/N,N-N/(N*N),GetCPULoadInfo(N) from test. CheckDbStatus order by check_id desc limit N  
5. Count: 653 Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 9YOUgs_SC[9YOUgs_SC]@ [192.168.43.7]  
6.   select custom_name_one from 'low_game_schema'.'role_ details' where role_id='S' 
7. rse and summarize the MySQL slow query log. Options are  
8.   --verbose    verbose  
9.   --debug      debug  
10.   --help       write this text to standard output  
11.  
12.   -v           verbose  
13.   -d           debug  
14.   -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default  
15.                 al: average lock time  
16.                 ar: average rows sent  
17.                 at: average query time  
18.                  c: count  
19.                  l: lock time  
20.                  r: rows sent  
21.                  t: query time    
22.   -r           reverse the sort order (largest last instead of first)  
23.   -t NUM       just show the top n queries  
24.   -a           don't abstract all numbers to N and strings to 'S'  
25.   -n NUM       abstract numbers with at least n digits within names  
26.   -g PATTERN   grep: only consider stmts that include this string  
27.   -h HOSTNAME  hostname of db server for *-slow.log  filename (can be wildcard),  
28.                default is '*', i.e. match all  
29.   -i NAME      name of server instance (if using mysql. server startup script)  
30.   -l           don't subtract lock time from total time 

3.2.2   慢查询日志（2）

如果我们想得到锁定时间最长的10条SQL语句，可以运行：

1. [root@nh119-141 data]# /usr/local/mysql/bin /mysqldumpslow -s al -n 10 david.log   
2. Reading mysql slow query log from david.log  
3. Count: 5  Time=0.00s (0s)  Lock=0.20s (1s)  Rows=4.4 (22), Audition [Audition]@[192.168.30.108]  
4.   SELECT OtherSN, State FROM wait_friend_info WHERE UserSN = N  
5.  
6. Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)   Rows=1.0 (1), audition-kr[audition-kr]@[192.168.30.105]  
7.   SELECT COUNT(N) FROM famverifycode WHERE  UserSN=N AND verifycode='S' 
8. ...... 

MySQL 5.1开始可以将慢查询的日志记录放入一张表中，这使我们的查询更加直观。慢查询表在mysql架构下，名为slow_log。其表结构定义如下：

1. mysql> show create table mysql.slow_log;  
2. *************************** 1. row ***************************  
3.        Table: slow_log  
4. Create Table: CREATE TABLE 'slow_log' (  
5.   'start_time' timestamp NOT NULL DEFAULT  CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
6.   'user_host' mediumtext NOT NULL,  
7.   'query_time' time NOT NULL,  
8.   'lock_time' time NOT NULL,  
9.   'rows_sent' int(11) NOT NULL,  
10.   'rows_examined' int(11) NOT NULL,  
11.   'db' varchar(512) NOT NULL,  
12.   'last_insert_id' int(11) NOT NULL,  
13.   'insert_id' int(11) NOT NULL,  
14.   'server_id' int(11) NOT NULL,  
15.   'sql_text' mediumtext NOT NULL  
16. ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' 
17. 1 row in set (0.00 sec) 

参数log_output指定了慢查询输出的格式，默认为FILE，你可以将它设为TABLE，然后就可以查询mysql架构下的slow_log表了，如：

1. mysql> show variables like 'log_output';  
2. +---------------+---------+  
3. | Variable_name | Value |  
4. +---------------+---------+  
5. | log_output    | FILE |  
6. +---------------+---------+  
7. 1 row in set (0.00 sec)  
8.  
9. mysql> set global log_output='TABLE';  
10. Query OK, 0 rows affected (0.00 sec)  
11.  
12. mysql> show variables like 'log_output';  
13. +---------------+---------+  
14. | Variable_name | Value |  
15. +---------------+---------+  
16. | log_output  | TABLE |  
17. +---------------+---------+  
18. 1 row in set (0.00 sec)  
19.  
20. mysql> select sleep(10);  
21. +-----------+  
22. | sleep(10)|  
23. +-----------+  
24. |       0 |  
25. +-----------+  
26. 1 row in set (10.01 sec)  
27. mysql> select * from mysql.slow_log\G;  
28. *************************** 1. row ***************************  
29.     start_time: 2009-09-25 13:44:29  
30.      user_host: david[david] @ localhost []  
31.     query_time: 00:00:09  
32.      lock_time: 00:00:00  
33.      rows_sent: 1  
34.  rows_examined: 0  
35.             db: mysql  
36. last_insert_id: 0  
37.      insert_id: 0  
38.      server_id: 0  
39.       sql_text: select sleep(10)  
40. 1 row in set (0.00 sec) 

参数log_output是动态的，并且是全局的。我们可以在线进行修改。在上表中我设置了睡眠（sleep）10秒，那么这句SQL语句就会被记录到slow_log表了。

查看slow_log表的定义会发现，该表使用的是CSV引擎，对大数据量下的查询效率可能不高。我们可以把slow_log表的引擎转换到MyISAM，用来进一步提高查询的效率。但是，如果已经启动了慢查询，将会提示错误：

1. mysql> alter table mysql.slow_log engine=myisam;  
2. ERROR 1580 (HY000): You cannot 'ALTER' a log table if logging is enabled  
3.  
4. mysql> set global slow_query_log=off;  
5. Query OK, 0 rows affected (0.00 sec)  
6.  
7. mysql> alter table mysql.slow_log engine=myisam;  
8. Query OK, 1 row affected (0.00 sec)  
9. Records: 1  Duplicates: 0  Warnings: 0 

不能忽视的是，将slow_log表的存储引擎更改为MyISAM后，对数据库还是会造成额外的开销。不过好在很多关于慢查询的参数都是动态的，我们可以方便地在线进行设置或者修改。

3.2.3   查询日志

查询日志记录了所有对MySQL数据库请求的信息，不论这些请求是否得到了正确的执行。默认文件名为：主机名.log。我们查看一个查询日志：

1. [root@nineyou0-43 data]# tail nineyou0-43.log   
2. 090925 11:00:24   44 Connect     zlm@192.168.0.100 on   
3. 44 Query       SET AUTOCOMMIT=0 
4.                44 Query       set autocommit=0 
5.                44 Quit         
6. 090925 11:02:37 45 Connect   Access denied for  user 'root'@'localhost' (using password: NO)  
7. 090925 11:03:51 46 Connect   Access denied for  user 'root'@'localhost' (using password: NO)  
8. 090925 11:04:38  23 Query    rollback 

通过上述查询日志你会发现，查询日志甚至记录了对access denied的请求。同样，从MySQL 5.1开始，可以将查询日志的记录放入mysql架构下的general_log表，该表的使用方法和前面小节提到的slow_log基本一样，这里不再赘述。

3.2.4   二进制日志（1）

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

恢复（recovery）。某些数据的恢复需要二进制日志，如当一个数据库全备文件恢复后，我们可以通过二进制日志进行point-in-time的恢复。

复制（replication）。其原理与恢复类似，通过复制和执行二进制日志使得一台远程的MySQL数据库（一般称为slave或者standby）与一台MySQL数据库（一般称为master或者primary）进行实时同步。

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

1. mysql> show variables like 'datadir';  
2. +---------------+----------------------------+  
3. | Variable_name | Value     |  
4. +---------------+----------------------------+  
5. | datadir       | /usr/local/mysql/data/ |   
6. +---------------+----------------------------+  
7. 1 row in set (0.00 sec)  
8.  
9. mysql> system ls -lh /usr/local/mysql/data/;  
10. total 2.1G  
11. -rw-rw----  1 mysql mysql 6.5M Sep 25 15:13 bin_log.000001  
12. -rw-rw----  1 mysql mysql   17 Sep 25 00:32 bin_log.index  
13. -rw-rw----  1 mysql mysql 300M Sep 25 15:13 ibdata1  
14. -rw-rw----  1 mysql mysql 256M Sep 25 15:13 ib_logfile0  
15. -rw-rw----  1 mysql mysql 256M Sep 25 15:13 ib_logfile1  
16. drwxr-xr-x  2 mysql mysql 4.0K May  7 10:08 mysql  
17. drwx------  2 mysql mysql 4.0K May  7 10:09 test 

这里的bin_log.00001即为二进制日志文件，我们在配置文件中指定了名称，所以没有用默认的文件名。bin_log.index为二进制的索引文件，用来存储过往生产的二进制日志序号，通常情况下，不建议手工修改这个文件。

二进制日志文件在默认情况下并没有启动，需要你手动指定参数来启动。可能有人会质疑，开启这个选项是否会对数据库整体性能有所影响。不错，开启这个选项的确会影响性能，但是性能的损失十分有限。根据MySQL官方手册中的测试表明，开启二进制日志会使得性能下降1%。但考虑到可以使用复制（replication）和point-in-time的恢复，这些性能损失绝对是可以并且是应该被接受的。

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

这里的bin_log.00001即为二进制日志文件，我们在配置文件中指定了名称，所以没有用默认的文件名。bin_log.index为二进制的索引文件，用来存储过往生产的二进制日志序号，通常情况下，不建议手工修改这个文件。

二进制日志文件在默认情况下并没有启动，需要你手动指定参数来启动。可能有人会质疑，开启这个选项是否会对数据库整体性能有所影响。不错，开启这个选项的确会影响性能，但是性能的损失十分有限。根据MySQL官方手册中的测试表明，开启二进制日志会使得性能下降1%。但考虑到可以使用复制（replication）和point-in-time的恢复，这些性能损失绝对是可以并且是应该被接受的。

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

max_binlog_size

binlog_cache_size

sync_binlog

binlog-do-db

binlog-ingore-db

log-slave-update

binlog_format

参数max-binlog-size指定了单个二进制日志文件的最大值，如果超过该值，则产生新的二进制日志文件，后缀名+1，并记录到.index文件。从MySQL 5.0开始的默认值为

1 073 741 824，代表1GB（之前的版本max-binlog-size默认大小为1.1GB）。

3.2.4   二进制日志（2）

当使用事务的表存储引擎（如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_size的设置是否合适。Binlog_cache_use记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。现在来看一个数据库的状态：

1. mysql> show variables like 'binlog_cache_size';  
2. +-------------------+-------+  
3. | Variable_name   | Value |  
4. +-------------------+-------+  
5. | binlog_cache_size | 32768 |   
6. +-------------------+-------+  
7. 1 row in set (0.00 sec)  
8.  
9. mysql> show global status like 'binlog_cache%';  
10. +-----------------------+--------------+  
11. | Variable_name  | Value |  
12. +-----------------------+---------------+  
13. | binlog_cache_disk_use | 0     |   
14. | binlog_cache_use     | 33553 |   
15. +-----------------------+---------------+  
16. 2 rows in set (0.00 sec) 

使用缓冲次数33 553次，临时文件使用次数为0。看来，32KB的缓冲大小对于当前这个MySQL数据库完全够用，所以暂时没有必要增加binlog_cache_size的值。

默认情况下，二进制日志并不是在每次写的时候同步到磁盘（我们可以理解为缓冲写）。因此，当数据库所在操作系统发生宕机时，可能会有最后一部分数据没有写入二进制日志文件中。这会给恢复和复制带来问题。参数sync_binlog=[N]表示每写缓冲多少次就同步到磁盘。如果将N设为1，即sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志。该默认值为0，如果使用InnoDB存储引擎进行复制，并且想得到最大的高可用性，建议将该值设为ON。不过该值为ON时，确实会对数据库的IO系统带来一定的影响。

但是，即使将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表示需要写入或者忽略写入哪些库的日志。默认为空，表示需要将所有库的日志同步到二进制日志。

如果当前数据库是复制中的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数据库的默认隔离级别）会出现类似丢失更新的现象，从而出现主从数据库上的数据不一致。

3.2.4   二进制日志（3）

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

（1）STATEMENT格式和之前的MySQL版本一样，二进制日志文件记录的是日志的逻辑SQL语句。

（2）在ROW格式下，二进制日志记录的不再是简单的SQL语句了，而是记录表的行更改情况。基于ROW格式的复制类似于Oracle的物理Standby（当然，还是有些区别）。同时，对于上述提及的Statement格式下复制的问题给予了解决。MySQL 5.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参数还有对于存储引擎的限制，如表3-1所示。

表3-1   存储引擎二进制日志格式支持情况

binlog_format是动态参数，因此可以在数据库运行环境下进行更改，例如，我们可以将当前会话的binlog_format设为ROW，如：

1. mysql> set @@session.binlog_format='ROW';  
2. Query OK, 0 rows affected (0.00 sec)  
3.  
4. mysql> select @@session.binlog_format;  
5. +-------------------------------+  
6. | @@session.binlog_format |  
7. +-------------------------------+  
8. | ROW                 |   
9. +-------------------------------+  
10. 1 row in set (0.00 sec) 

当然，也可以将全局的binlog_format设置为你想要的格式。不过通常情况下，这个操作可能会带来问题，运行时，请确保更改后不会对你的复制带来影响。如：

1. mysql> set global binlog_format='ROW';  
2. Query OK, 0 rows affected (0.00 sec)  
3.  
4. mysql> select @@global.binlog_format;  
5. +------------------------------+  
6. | @@global.binlog_format |  
7. +------------------------------+  
8. | ROW             |   
9. +------------------------------+  
10. 1 row in set (0.00 sec)  

通常情况下，我们将参数binlog_format设置为ROW，这可以为数据库的恢复和复制带来更好的可靠性。但是不能忽略一点的是，这会带来二进制文件大小的增加，有些语句下的ROW格式可能需要更大的容量。比如我们有两张一样的表，大小都为100W，执行UPDATE操作，观察二进制日志大小的变化：

1. mysql> select @@session.binlog_format\G;  
2. *************************** 1. row ***************************  
3. @@session.binlog_format: STATEMENT  
4. 1 row in set (0.00 sec)  
5.  
6. mysql> show master status\G;  
7. *************************** 1. row ***************************  
8.             File: test.000003  
9.         Position: 106  
10.     Binlog_Do_DB:   
11. Binlog_Ignore_DB:   
12. 1 row in set (0.00 sec)  
13.  
14. mysql> update t1 set username=upper(username);   
15. Query OK, 89279 rows affected (1.83 sec)  
16. Rows matched: 100000  Changed: 89279  Warnings: 0  
17.  
18. mysql> show master status\G;  
19. *************************** 1. row ***************************  
20.             File: test.000003  
21.         Position: 306  
22.     Binlog_Do_DB:   
23. Binlog_Ignore_DB:   
24. 1 row in set (0.00 sec) 

3.2.4   二进制日志（4）

可以看到，在binlog_format格式为STATEMENT下，执行UPDATE语句二进制日志大小只增加了200字节（306-106）。如果我们使用ROW格式，同样来操作t2表，可以看到：

1. mysql> set session binlog_format='ROW';  
2. Query OK, 0 rows affected (0.00 sec)  
3.  
4. mysql> show master status\G;  
5. *************************** 1. row ***************************  
6.             File: test.000003  
7.         Position: 306  
8.     Binlog_Do_DB:   
9. Binlog_Ignore_DB:   
10. 1 row in set (0.00 sec)  
11.  
12. mysql> update t2 set username=upper(username);   
13. Query OK, 89279 rows affected (2.42 sec)  
14. Rows matched: 100000  Changed: 89279  Warnings: 0  
15.  
16. mysql> show master status\G;  
17. *************************** 1. row ***************************  
18.             File: test.000003  
19.         Position: 13782400  
20.     Binlog_Do_DB:   
21. Binlog_Ignore_DB:   
22. 1 row in set (0.00 sec) 

这时你会惊讶地发现，同样的操作在ROW格式下竟然需要13 782 094字节，二进制日志文件差不多增加了13MB，要知道t2表的大小也不超过17MB。而且执行时间也有所增加（这里我设置了sync_binlog=1）。这就是因为，这时MySQL数据库不再将逻辑的SQL操作记录到二进制日志，而是记录对于每行的更改记录信息。

上面的这个例子告诉我们，将参数binlog_format设置为ROW，对于磁盘空间要求有了一定的增加。而由于复制是采用传输二进制日志方式实现的，因此复制的网络开销也有了增加。

二进制日志文件的文件格式为二进制（好像有点废话），不能像错误日志文件，慢查询日志文件用cat、head、tail等命令来查看。想要查看二进制日志文件的内容，须通过MySQL提供的工具mysqlbinlog。对于STATEMENT格式的二进制日志文件，使用mysqlbinlog后，看到就是执行的逻辑SQL语句，如：

1. [root@nineyou0-43 data]# mysqlbinlog --start-position=203 test.000004  
2. /*!40019 SET @@session.max_insert_delayed_threads=0*/;  
3. ....  
4. #090927 15:43:11 server id 1  end_log_pos 376    Query   thread_id=188   exec_time=1     error_code=0 
5. SET TIMESTAMP=1254037391/*!*/;  
6. update t2 set username=upper(username) where id=1 
7. /*!*/;  
8. # at 376  
9. #090927 15:43:11 server id 1  end_log_pos 403   Xid = 1009 
10. COMMIT/*!*/;  
11. DELIMITER ;  
12. # End of log file  
13. ROLLBACK /* added by mysqlbinlog */;  
14. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 

update t2 set username=upper（username） where id=1，这个可以看到日志的记录以SQL语句的方式（为了排版的方便，省去了一些开始的信息）。在这个情况下，mysqlbinlog和Oracle LogMiner类似。但是如果这时使用ROW格式的记录方式，则会发现mysqlbinlog的结果变得“不可读”（unreadable），如：

1. [root@nineyou0-43 data]# mysqlbinlog  --start-position=1065 test.000004  
2. /*!40019 SET @@session.max_insert_delayed_threads=0*/;  
3. ......  
4. # at 1135  
5. # at 1198  
6. #090927 15:53:52 server id 1  end_log_pos 1198   Table_map: 'member'.'t2' mapped to number 58  
7. #090927 15:53:52 server id 1  end_log_pos 1378  Update_rows: table id 58 flags: STMT_END_F  
8.  
9. BINLOG '  
10. EBq/ShMBAAAAPwAAAK4EAAAAADoAAAAAAAAABm1lbWJ lcgACdDIACgMPDw/+CgsPAQwKJAAoAEAA  
11. /gJAAAAA  
12. EBq/ShgBAAAAtAAAAGIFAAAQADoAAAAAAAEACv////8A /AEAAAALYWxleDk5ODh5b3UEOXlvdSA3  
13. Y2JiMzI1MmJhNmI3ZTljNDIyZmFjNTMzNGQyMjA1NA FNLacPAAAAAABjEnpxPBIAAAD8AQAAAAtB  
14. TEVYOTk4OFlPVQQ5eW91IDdjYmIzMjUyYmE2YjdlOW M0MjJmYWM1MzM0ZDIyMDU0AU0tpw8AAAAA  
15. AGMSenE8EgAA  
16. '/*!*/;  
17. # at 1378  
18. #090927 15:53:52 server id 1  end_log_pos 1405  Xid = 1110 
19. COMMIT/*!*/;  
20. DELIMITER ;  
21. # End of log file  
22. ROLLBACK /* added by mysqlbinlog */;  
23. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 

3.2.4   二进制日志（5）

我们看不到执行的SQL语句，反而是一大串我们看不到的字符。其实只要加上参数-v或者-vv，就能清楚地看到执行的具体信息了，-vv会比-v多显示出更新的类型，这次我们加上-vv选项，得到：

1. [root@nineyou0-43 data]# mysqlbinlog -vv  - -start-position=1065 test.000004  
2. ......  
3. BINLOG '  
4. EBq/ShMBAAAAPwAAAK4EAAAAADoAAAAAAAAABm1lbWJlcgA CdDIACgMPDw/+CgsPAQwKJAAoAEAA  
5. /gJAAAAA  
6. EBq/ShgBAAAAtAAAAGIFAAAQADoAAAAAAAEACv////8A/A EAAAALYWxleDk5ODh5b3UEOXlvdSA3  
7. Y2JiMzI1MmJhNmI3ZTljNDIyZmFjNTMzNGQyMjA1NAFNLa cPAAAAAABjEnpxPBIAAAD8AQAAAAtB  
8. TEVYOTk4OFlPVQQ5eW91IDdjYmIzMjUyYmE2YjdlOWM0MjJ mYWM1MzM0ZDIyMDU0AU0tpw8AAAAA  
9. AGMSenE8EgAA  
10. '/*!*/;  
11. ### UPDATE member.t2  
12. ### WHERE  
13. ###   @11=1 /* INT meta=0 nullable=0 is_null=0 */  
14. ###   @2='david' /* VARSTRING(36) meta=36 nullable=0 is_null=0 */  
15. ###   @3='family' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */  
16. ###   @4='7cbb3252ba6b7e9c422fac5334d22054' /*  VARSTRING(64) meta=64 nullable=0 is_null=0 */  
17. ###   @5='M' /* STRING(2) meta=65026 nullable=0 is_null=0 */  
18. ###   @6='2009:09:13' /* DATE meta=0 nullable=0 is_null=0 */  
19. ###   @7='00:00:00' /* TIME meta=0 nullable=0 is_null=0 */  
20. ###   @8='' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */  
21. ###   @9=0 /* TINYINT meta=0 nullable=0 is_null=0 */  
22. ###   @10=2009-08-11 16:32:35 /* DATETIME meta=0  nullable=0 is_null=0 */  
23. ### SET  
24. ###   @11=1 /* INT meta=0 nullable=0 is_null=0 */  
25. ###   @2='DAVID' /* VARSTRING(36) meta=36 nullable=0 is_null=0 */  
26. ###   @3=family /* VARSTRING(40) meta=40 nullable=0 is_null=0 */  
27. ###   @4='7cbb3252ba6b7e9c422fac5334d22054' /* VA RSTRING(64) meta=64 nullable=0 is_null=0 */  
28. ###   @5='M' /* STRING(2) meta=65026 nullable=0 is_null=0 */  
29. ###   @6='2009:09:13' /* DATE meta=0 nullable=0 is_null=0 */  
30. ###   @7='00:00:00' /* TIME meta=0 nullable=0 is_null=0 */  
31. ###   @8='' /* VARSTRING(64) meta=64 nullable=0 is_null=0 */  
32. ###   @9=0 /* TINYINT meta=0 nullable=0 is_null=0 */  
33. ###   @10=2009-08-11 16:32:35 /* DATETIME meta=0  nullable=0 is_null=0 */  
34. # at 1378  
35. #090927 15:53:52 server id 1  end_log_pos 1405  Xid = 1110 
36. COMMIT/*!*/;  
37. DELIMITER ;  
38. # End of log file  
39. ROLLBACK /* added by mysqlbinlog */;  
40. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 

现在mysqlbinlog向我们解释了具体做的事情。可以看到，一句简单的update t2 set username=upper（username）where id=1语句记录为了对于整个行更改的信息，这也解释了为什么前面我们更新了10万行的数据，在ROW格式下，二进制日志文件会增大了13MB。

3.3   套接字文件

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

1. mysql> show variables like 'socket'\G;  
2. *************************** 1. row ***************************  
3. Variable_name: socket  
4.       Value: /tmp/mysql.sock  
5. 1 row in set (0.00 sec)  
6. 3.4   pid文件 

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

1. mysql> show variables like 'pid_file'\G;  
2. *************************** 1. row ***************************  
3. Variable_name: pid_file  
4.        Value: /usr/local/mysql/data/xen-server.pid  
5. 1 row in set (0.00 sec) 

3.4   pid文件

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

3.5   表结构定义文件

因为MySQL插件式存储引擎的体系结构的关系，MySQL对于数据的存储是按照表的，所以每个表都会有与之对应的文件（对比SQL Server是按照每个数据库下的所有表或索引都存在mdf文件中）。不论采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，这个文件记录了该表的表结构定义。

frm还用来存放视图的定义，如我们创建了一个v_a视图，那么对应地会产生一个v_a.frm文件，用来记录视图的定义，该文件是文本文件，可以直接使用cat命令进行查看：

1. [root@xen-server test]# cat v_a.frm   
2. TYPE=VIEW 
3. query=select 'test'.'a'.'b' AS 'b' from 'test'.'a'  
4. md5=4eda70387716a4d6c96f3042dd68b742 
5. updatable=1 
6. algorithm=0 
7. definer_user=root 
8. definer_host=localhost 
9. suid=2 
10. with_check_option=0 
11. timestamp=2010-08-04 07:23:36  
12. create-version=1 
13. source=select * from a  
14. client_cs_name=utf8 
15. connection_cl_name=utf8_general_ci 
16. view_body_utf8=select 'test'.'a'.'b' AS 'b' from 'test'.'a' 

3.6   InnoDB存储引擎文件

之前介绍的文件都是MySQL数据库本身的文件，和存储引擎无关。除了这些文件外，每个表存储引擎还有其自己独有的文件。这一节将具体介绍和InnoDB存储引擎密切相关的文件，这些文件包括重做日志文件、表空间文件。

3.6.1   表空间文件

InnoDB存储引擎在存储设计上模仿了Oracle，将存储的数据按表空间进行存放。默认配置下，会有一个初始化大小为10MB、名为ibdata1的文件。该文件就是默认的表空间文件（tablespace file）。你可以通过参数innodb_data_file_path对其进行设置。格式如下：

1. innodb_data_file_path=datafile_spec1[;datafile_spec2]... 

你也可以用多个文件组成一个表空间，同时制定文件的属性，如：

1. [mysqld]  
2. 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，这样不用将所有数据都存放于默认的表空间中。下面这台服务器设置了innodb_file_per_table，可以看到：

1. mysql> show variables like 'innodb_file_per_table'\G;  
2. *************************** 1. row ***************************  
3. Variable_name: innodb_file_per_table  
4.         Value: ON  
5. 1 row in set (0.00 sec)  
6.  
7. mysql> system ls -lh /usr/local/mysql/data/member/*  
8. -rw-r-----  1 mysql mysql 8.7K 2009-02-24  / usr/local/mysql/data/member/ Profile.frm  
9. -rw-r-----  1 mysql mysql 1.7G  9月 25 11:13 /usr/local/mysql/data/member/ Profile.ibd  
10. -rw-rw----  1 mysql mysql 8.7K  9月 27 13:38  /usr/local/mysql/data/member/t1.frm  
11. -rw-rw----  1 mysql mysql  17M  9月 27 13:40  /usr/local/mysql/data/member/t1.ibd  
12. -rw-rw----  1 mysql mysql 8.7K  9月 27 15:42 /usr/local/mysql/data/member/t2.frm  
13. -rw-rw----  1 mysql mysql  17M  9月 27 15:54 /usr/local/mysql/data/member/t2.ibd 

表Profile、t1、t2都是InnoDB的存储引擎，由于设置参数innodb_file_per_table=ON，因此产生了单独的.ibd表空间文件。需要注意的是，这些单独的表空间文件仅存储该表的数据、索引和插入缓冲等信息，其余信息还是存放在默认的表空间中。图3-1显示了InnoDB存储引擎对于文件的存储方式：

图3-1   InnoDB表存储引擎文件

3.6.2   重做日志文件（1）

默认情况下会有两个文件，名称分别为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中。图3-2显示了一个拥有3个重做日志文件的重做日志文件组。

图3-2   日志文件组

参数innodb_log_file_size、innodb_log_files_in_group、innodb_mirrored_log_groups、innodb_log_group_home_dir影响着重做日志文件的属性。参数innodb_log_file_size指定了重做日志文件的大小；innodb_log_files_in_group指定了日志文件组中重做日志文件的数量，默认为2；innodb_mirrored_log_groups指定了日志镜像文件组的数量，默认为1，代表只有一个日志文件组，没有镜像；innodb_log_group_home_dir指定了日志文件组所在路径，默认在数据库路径下。以下显示了一个关于重做日志组的配置：

1. mysql> show variables like 'innodb%log%'\G;  
2. *************************** 1. row ***************************  
3. Variable_name: innodb_flush_log_at_trx_commit  
4.         Value: 1  
5. *************************** 2. row ***************************  
6. Variable_name: innodb_locks_unsafe_for_binlog  
7.         Value: OFF  
8. *************************** 3. row ***************************  
9. Variable_name: innodb_log_buffer_size  
10.         Value: 8388608  
11. *************************** 4. row ***************************  
12. Variable_name: innodb_log_file_size  
13.         Value: 5242880  
14. *************************** 5. row ***************************  
15. Variable_name: innodb_log_files_in_group  
16.         Value: 2  
17. *************************** 6. row ***************************  
18. Variable_name: innodb_log_group_home_dir  
19.         Value: ./  
20. *************************** 7. row ***************************  
21. Variable_name: innodb_mirrored_log_groups  
22.         Value: 1  
23. 7 rows in set (0.00 sec) 

重做日志文件的大小设置对于MySQL数据库各方面还是有影响的。一方面不能设置得太大，如果设置得很大，在恢复时可能需要很长的时间；另一方面又不能太小了，否则可能导致一个事务的日志需要多次切换重做日志文件。在错误日志中可能会看到如下警告：

1. 090924 11:39:44  InnoDB: ERROR: the age of the  last checkpoint is 9433712,  
2. InnoDB: which exceeds the log group capacity 9433498.  
3. InnoDB: If you are using big BLOB or TEXT rows, you must set the  
4. InnoDB: combined size of log files at least 10  times bigger than the  
5. InnoDB: largest such row.  
6. 090924 11:40:00  InnoDB: ERROR: the age of the  last checkpoint is 9433823,  
7. InnoDB: which exceeds the log group capacity 9433498.  
8. InnoDB: If you are using big BLOB or TEXT rows,  you must set the  
9. InnoDB: combined size of log files at least 10  times bigger than the  
10. InnoDB: largest such row.  
11. 090924 11:40:16  InnoDB: ERROR: the age of the  last checkpoint is 9433645,  
12. InnoDB: which exceeds the log group capacity 9433498.  
13. InnoDB: If you are using big BLOB or TEXT rows,  you must set the  
14. InnoDB: combined size of log files at least 10  times bigger than the  
15. InnoDB: largest such row. 

3.6.2   重做日志文件（2）

上面错误集中在InnoDB: ERROR: the age of the last checkpoint is 9433645,InnoDB: which exceeds the log group capacity 9433498。这是因为重做日志有一个capacity变量，该值代表了最后的检查点不能超过这个阈值，如果超过则必须将缓冲池（innodb buffer pool）中刷新列表（flush list）中的部分脏数据页写回磁盘。

也许有人会问，既然同样是记录事务日志，那和我们之前的二进制日志有什么区别？首先，二进制日志会记录所有与MySQL有关的日志记录，包括InnoDB、MyISAM、Heap等其他存储引擎的日志。而InnoDB存储引擎的重做日志只记录有关其本身的事务日志。其次，记录的内容不同，不管你将二进制日志文件记录的格式设为STATEMENT还是ROW，又或者是MIXED，其记录的都是关于一个事务的具体操作内容。而InnoDB存储引擎的重做日志文件记录的关于每个页（Page）的更改的物理情况（如表3-2所示）。此外，写入的时间也不同，二进制日志文件是在事务提交前进行记录的，而在事务进行的过程中，不断有重做日志条目（redo entry）被写入重做日志文件中。

表3-2   重做日志结构

在第2章中已经提到，对于写入重做日志文件的操作不是直接写，而是先写入一个重做日志缓冲（redo log buffer）中，然后根据按照一定的条件写入日志文件。图3-3很好地表示了这个过程。

图3-3   重做日志写入过程

上面提到了从日志缓冲写入磁盘上的重做日志文件是按一定条件的，那这些条件有哪些呢？第2章分析了主线程（master thread），知道在主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已经提交。另一个触发这个过程是由参数innodb_ flush_log_at_trx_commit控制，表示在提交（commit）操作时，处理重做日志的方式。

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

3.7   小结

本章介绍了与MySQL数据库相关的一些文件，并了解了文件可以分为MySQL数据库文件以及和各存储引擎有关的文件。与MySQL数据库有关的文件中，错误文件和二进制日志文件非常重要。当MySQL数据库发生任何错误时，DBA首先就应该去查看错误文件，从文件提示的内容中找出问题的所在。当然，错误文件不仅记录了错误的内容，也记录了警告的信息，通过一些警告也有助于DBA对于数据库和存储引擎的优化。

二进制日志的作用非常关键，可以用来进行point in time的恢复以及复制（replication）环境的搭建。因此，建议在任何时候时都启用二进制日志的记录。从MySQL 5.1开始，二进制日志支持STATEMENT、ROW、MIX三种格式，用来更好地同步数据库。DBA应该十分清楚三种不同格式之间的差异。

本章的最后介绍了和InnoDB存储引擎相关的文件，包括表空间文件和重做日志文件。表空间文件是用来管理InnoDB存储引擎的存储，分为共享表空间和独立表空间。重做日志非常重要，用来记录InnoDB存储引擎的事务日志，也因为重做日志的存在，才使得InnoDB存储引擎可以提供可靠的事务。

---

---

About Me

............................................................................................................................................. ● 本文整理自网络 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群号：230161599（满）、618766405 ● 微信群：可加我微信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友（646634621），注明添加缘由 ● 于 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................................. ● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。    小麦苗的微信公众号      小麦苗的DBA宝典QQ群1     小麦苗的DBA宝典QQ群2        小麦苗的微店 .............................................................................................................................................