MySQL笔记系列之二文件

文件

MySQL数据库和InnoDB存储引擎由以下几种类型文件：参数文件，日志文件，socket文件，pid文件，MySQL表结构文件，存储引擎文件。

参数文件（配置文件）

当MySQL实例启动时，MySQL会先去读取一个配置参数文件，用来寻找数据库的各种文件所在的位置以及指定某些初始化参数，默认情况下，MySQL会按照一定的次序去读取文件。MySQL在启动时，如果没有参数文件，这时的参数值取决于编译MySQL时指定的默认值和源码中指定参数的默认值。但，如果MySQL在默认的数据库目录下找不到mysql架构，则启动失败。Oracle与MySQL不同，没有参数文件，则启动失败。

⑴参数

可以把参数看做是变量和值，即类似与map结构的键值对，参数可以通过在MySQL实例中通过show variables variable_name来查看其信息。

⑵参数类型

参数文件中的参数分为动态参数和静态参数。动态参数意味着可以在MySQL实例运行中进行修改，静态参数说明在整个实例声明周期中都不得进行更改。其次，注意global和session关键字，表明该参数的修改是基于当前会话还是整个实例的生命周期，有些动态参数只能在会话中进行修改，有些参数修改，则在整个实例声明周期中都会生效。当前会话可以理解为本次mysql启动到结束这一时间段。

日志文件

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

⑴错误日志

对MySQL的启动，运行，关闭过程进行了记录，当MySQL出错时，我们应该查看错误日志。默认是以err结尾。可通过show variables like 'log_error"来定位该文件：

mysql> show variables like 'log_error';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| log_error     | E:\mysqlData\DESKTOP-KFBCOS0.err |
+---------------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

⑵慢查询日志

慢查询为SQL语句的优化带来帮助，可以设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询日志中。阈值通过参数long_query_time来设置。默认为10，即10秒。

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

参数long_queries_not_using_indexes,如果运行的SQL语句没有使用索引，则MySQL数据库同样会将这条语句记录到慢查询日志文件中。log_queries_not_using_indexes也是需要手动打开，如果没有打开的话，

show VARIABLES like 'log_queries_not_using_indexes';

log_queries_not_using_indexes	OFF

手动设置为ON：

set global log_queries_not_using_indexes=ON;

默认情况下，MySQL是不启动慢查询日志的，需要手动设置或者在my.cnf配置文件中添加参数。

启动慢查询：

在MySQL实例中使用以下查询：

show VARIABLES like '%slow%';

log_slow_admin_statements	OFF
log_slow_slave_statements	OFF
slow_launch_time	10
slow_query_log	OFF
slow_query_log_file	E:\mysqlData\DESKTOP-KFBCOS0-slow.log

通过set命令设置slow_query_log参数的值为ON,

set global slow_query_log=ON;

也可以直接写到配置文件中 my.cnf

slow_query_log_file=/mysql/log/nagiosdb-slow.log

slow_launch_time=5

注意：设置long_query_time值后，MySQL数据库会记录运行时间大于该值的所有SQL语句，等于不会被记录，MySQL5.1之后，开始以微妙记录SQL语句运行时间。

MySQL5.1之后，将慢查询的日志记录放在一张表中，在mysql架构下，slow_log。参数log_output指定了慢查询输出的格式，默认为FILE，可进行设置 set global log_output='TABLE';参数log_ouput是动态，全局的，可在线进行修改。

select * from mysql.slow_log;

⑶查询日志

记录了对MySQL数据库请求的信息，默认文件名为：主机名.log，从5.1开始，查询日志的记录放在mysql架构下的general_log表，使用方式和slow_log基本一致。

⑷二进制日志

记录了对数据库执行更改的所有操作，但不包括SELECT,SHOW，因为他们没有对数据进行修改。二进制日志的作用有恢复和复制。如果二进制文件没有启动，可以手动方式启动，

查询二进制日志的状态

show variables like '%log_bin%';

log_bin	ON
log_bin_basename	E:\mysqlData\binlog
log_bin_index	E:\mysqlData\binlog.index
log_bin_trust_function_creators	OFF
log_bin_use_v1_row_events	OFF
sql_log_bin	ON

如果long_bin变量对应的值为OFF，即没有开启二进制日志，需要设置为ON，如果是ON，则已经开启，同时我们也可以看到二进制日志的名字以及所在的位置为：log_bin_basename所对应的目录。在该目录下的bin_log.000001文件是二进制文件、根据MySQL的官方手册的测试表明，开启二进制日志会使得性能下降1%。

二进制日志的其他变量：

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

binlog_cache_size：当事务的表存储引擎时，所有未提交的二进制日志会被记录到一个缓存中，等该事务提交时直接将缓存中的二进制日志写入二进制日志文件中，缓冲的大小由binlog_cache_size决定，默认大小为32KB,同时binlog_cache_size是基于会话的，即当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入到一个临时文件中，因此该值不能设置的太小。

show variables like 'binlog_cache_size';

binlog_cache_size	32768

binlog_cache_use记录了使用缓存写二进制日志的次数；

binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。

show global status like 'binlog_cache%';
Binlog_cache_disk_use	0
Binlog_cache_use	10518

可以看出：使用缓冲次数为10518次，临时文件使用次数为0。

sync_binlog = N：表示每次缓冲多少次就同步到磁盘,如果将N设置为1，则sync_binlog=1表示采用同步写磁盘的方式来写二进制日志，这个时候写操作不使用操作系统的缓冲来写二进制日志。该值默认为0.

show VARIABLES like 'sync_binlog';
sync_binlog	1

binlog_format参数影响二进制日志的格式，从MySQL5.1开始引入binlog_format参数，该参数可设的值有STATEMENT,ROW,MIXED.

STATEMENT格式，二进制日志文件记录的是日志的逻辑SQL语句

ROW格式下，二进制日志不在是简单的SQL语句，而是记录表的行更改情况，从MySQL5.1开始，如果设置binlog_format为ROW,可以将InnoDB的事务隔离级别设为READ COMMITTED。

MIXED格式下，MySQL默认采用STATEMENT格式进行二进制日志文件的记录。

注：binlog_format是动态变量，可以在实例运行中进行修改，例如：set @@session.binlog_format='ROW'; set @@global.binlog_format='ROW';同时，二进制日志文件的ROW格式，会带来二进制文件的大小的增加，也增加网络开销。

mysqlbinlog工具（注意不是在MySQL实例中运行此工具，此工具独立于MySQL实例），是为了查看二进制日志文件的内容，二进制日志不能像错误日志文件，慢查询日志文件那样进行查询，对于STATEMENT格式的二进制文件，使用mysqlbinlog后，便可以看到执行的逻辑SQL语句，对于ROW格式的二进制日志，我们需要在后面加上参数-vv或-v来显示更新的内容。

C:\Users\liumin>mysqlbinlog binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'binlog.000001' not found (OS errno 2 - No such file or directory)
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

套接字文件

Unix系统下本地连接MySQL可以使用Unix域套接字方式，套接字文件由参数socket控制，一般在/tmp目录下，名为mysql.sock;

show VARIABLES like 'socket';
socket	MySQL

pid文件

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

show VARIABLES like 'pid_file';
pid_file	E:\mysqlData\DESKTOP-KFBCOS0.pid

表结构定义文件

MySQL数据的存储是按照表的，每个表都有与之对应的文件，不论采用何种存储引擎，MySQL都有一个以frm为后缀名的文件，该文件记录该表的表结构定义。frm还用来存放视图的定义，该文件是文本文件，可使用文本查看工具进行查看。

InnoDB存储引擎文件

之前都是MySQL数据库本身的文件，与存储引擎无关，但，每个存储引擎都有自己独有的文件。

表空间文件：

表空间文件将存储的数据按表空间进行存放。InnoDB有一个默认的表空间文件，默认大小为10MB,名为ibdata1的文件，自动增长，

show VARIABLES like 'innodb_data_file_path';
innodb_data_file_path	ibdata1:12M:autoextend

可以用多个文件组成一个表空间，同时制定文件的属性：（在配置文件中修改，也可以在MySQL实例上修改）

[mysqld]

innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M：autoextend

故对于所有基于InnoDB存储引擎的表的数据都会记录到上面设置的文件中。

但，通过设置参数innodb_file_per_table，可以将每个基于InnoDB存储引擎的表单独产生一个表空间，文件名为.ibd，这样就不用将所有数据都存放于默认的表空间。注意：单独的表空间文件仅存储该表的数据，索引，插入缓冲，其余信息还是存放在默认的表空间中，这就是为什么刚才的默认表空间为12M的缘故。

show VARIABLES like 'innodb_file_per_table';
innodb_file_per_table	ON

重做日志文件

MySQL官方手册将其称为InnoDB存储引擎的日志文件，默认情况下会有两个文件，名为ib_logfile0,ib_logfile1.重做日志文件的目的是用来恢复数据。每个InnoDB存储引擎至少有一个重做日志文件组，每个文件组至少有2个重做日志文件，默认为ib_logfile0,ib_logfile1.当然可以设置多个，也可以将不同的文件组放在不同的磁盘上，日志组中的每个重做日志文件的大小一致，并以循环方式使用。InnoDB存储引擎先写重做日志1号文件，当达到要求，然后切换到重做日志2号文件，当2写完后，在切换到下一个，指到最后一个写完，在切换到1号文件。