姜咏康的博客
0%

深入学习 MySQL (二)：MySQL 文件系统

发表于 更新于 分类于

本章将分析构成 MySQL 数据库和 InnoDB 引擎的各种类型的文件。从物理角度来看，这些文件大致分为以下几类：

  • 套接字文件：当客户端使用 UNIX 域套接字连接时所需要的文件
  • 配置文件：启动时加载，配置 MySQL 和 存储引擎的初始设置
  • 表结构文件：用来存放用户定义的数据表结构
  • 表空间文件：存放表中的记录、索引等数据
  • 日志文件：记录 MySQL 在运行过程中发生的一些事件

套接字文件

在 UNIX 系统下本机连接 MySQL 实例可以采用套接字文件的方式，这种方式需要套接字文件。查看本机套接字文件所在路径：

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

配置文件

MySQL 实例在启动时，会先去读取配置文件，用来查找数据库各种文件所在的位置以及指定某些初始化参数。MySQL 会按照一定的顺序去查找配置文件，并且以最后一个查找到的配置文件为准。这点，与 Linux Shell 配置文件的优先级类似。

1
2
3
4
5
6
# 查看配置文件的查找顺序与优先级
$ mysql --help
...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
...

当然，这默认情况下这些文件中都是空空如也，每一个配置项无须我们一一配置，MySQL 都已经帮我们设置好了默认值，我们只需要配置自己需要作出修改的参数。如何查看所有的配置项以及默认值？官方推荐的做法是使用 show variables 命令，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show variables;
+--------------------------------------------+------------------------------+
| Variable_name                              | Value                        |
+--------------------------------------------+------------------------------+
| activate_all_roles_on_login                | OFF                          |
| auto_generate_certs                        | ON                           |
| auto_increment_increment                   | 1                            |
| auto_increment_offset                      | 1                            |
| autocommit                                 | ON                           |
| automatic_sp_privileges                    | ON                           |
| avoid_temporal_upgrade                     | OFF                          |
| back_log                                   | 151                          |
| basedir                                    | /usr/                        |
| ...                                        |                              |
+--------------------------------------------+------------------------------+

上面截取了部分默认参数配置，如果需要对其进行修改，可以在配置文件中进行修改。但是 MySQL 实例需要重启才能生效，那么有没有办法可以在实例运行中进行修改参数呢？答案是可以的，但是仅限于部分参数。

MySQL 中的参数类型可以分为两类：

  • 动态参数：可以在实例运行时进行修改，即时生效
  • 静态参数：仅可以在配置文件中进行设置，实例需要重启才能生效

例如修改缓存区大小，这个就是属于动态参数：

1
2
3
4
5
6
7
8
9
10
mysql> set session read_buffer_size = 524288;  -- 增大缓冲区
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'read_buffer_size'; -- 查看是否设置成功
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 524288 |
+------------------+--------+
1 row in set (0.00 sec)

如果试图在修改一个静态参数，那么可能得到以下报错：

1
2
mysql> set session basedir = '/';
ERROR 1238 (HY000): Variable 'basedir' is a read only variable

表结构文件

MySQL 的数据存储是按照表进行的，每个表都与之对应的表结构文件，文件中记录了表中的字段、类型、索引等信息。表结构文件的后缀名为 .frm。表结构文件与创建表时选择的存储引擎无关，选择了任意类型的存储引擎，都会在创建表时生成 .frm 文件。

表空间文件

InnoDB 存储引擎提供了两种表空间的存储方式：

  • 共享表空间
  • 独占表空间

在共享表空间的模式下，整个数据库的表数据和索引都被存放在同一个文件中，默认位于 $datadir/ibdata1。
在独占表空间的模式下，MySQL 会为每一张表生成一个独立的表空间，后缀名为 .ibd, 默认位于 $datadir/$db_name/$table_name.ibd。可以通过设置 innodb_file_per_table=ON 来开启独占表空间模式。

日志文件

日志记录 MySQL 在运行过程中发生的一些事件，查看分析日志文件可以对实例的运行状态进行诊断，从而可以更好的管理数据库。日志文件大致可以分为以下几类：

  • 错误日志
  • 查询日志
  • 慢查询日志
  • 二进制日志

错误日志 对 MySQL 的启动、运行、关闭过程进行了记录。当数据库实例发生异常时，我们首先应该检查的就是错误日志。查看日志文件的位置：

1
2
3
4
5
6
7
mysql> show variables like 'log_error';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| log_error     | ./vincentdeMacBook-Pro.local.err |
+---------------+----------------------------------+
1 row in set (0.00 sec)

这是一个相对路径，默认的文件名是 主机名.local.err。查看一下错误日志的文件内容：

1
2
3
4
5
6
7
$ less /usr/local/var/mysql/vincentdeMacBook-Pro.local.err
2019-06-17T06:10:22.6NZ mysqld_safe Logging to '/usr/local/var/mysql/vincentdeMacBook-Pro.local.err'.
2019-06-17T06:10:22.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql
2019-06-17T06:10:22.983791Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-06-17T06:10:22.984135Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2019-06-17T06:10:22.984193Z 0 [Note] /usr/local/opt/mysql@5.7/bin/mysqld (mysqld 5.7.25) starting as process 26665 ...
2019-06-17T06:10:22.987211Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql/ is case insensitive

查询日志 记录了所有对 MySQL 数据库请求的信息，无论这些请求有没有正确执行。查看日志文件的默认配置：

1
2
3
4
5
6
7
8
-- 查询日志是否开启
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
-- 查询日志存放位置
mysql> show variables like 'general_log_file';
+------------------+-----------------------------------------------+
| Variable_name    | Value                                         |
+------------------+-----------------------------------------------+
| general_log_file | /usr/local/var/mysql/vincentdeMacBook-Pro.log |
+------------------+-----------------------------------------------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
-- 日志文件的存储方式
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.01 sec)

慢查询日志 用来定位查询时间过长的 SQL 语句。默认配置下并不会开启，开启后查询时间超过 10s 的 SQL 语句都会被记录在慢查询日志中。查看慢查询日志的配置：

1
2
3
4
5
6
7
8
-- 慢查询日志是否开启
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
-- 慢查询日志阈值
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
-- 慢查询日志存储位置
mysql> show variables like 'slow_query_log_file';
+---------------------+----------------------------------------------------+
| Variable_name       | Value                                              |
+---------------------+----------------------------------------------------+
| slow_query_log_file | /usr/local/var/mysql/vincentdeMacBook-Pro-slow.log |
+---------------------+----------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
-- 是否记录未使用索引的查询语句
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

二进制日志 记录了对 MySQL 数据库执行更改的所有操作。但是不包括 SELECT SHOW 这类读的操作。二进制日志的主要作用是 数据恢复 与 数据同步。默认配置下二进制日志是关闭的，在配置文件中添加 log-bin 可以开启。

1
2
[mysqld]
log-bin=binlog

二进制日志默认的位置在 datadir 文件夹内，如：

1
2
3
4
5
6
7
$ ls -al /usr/local/var/mysql
total 389160
drwxr-xr-x   32 vincent  admin      1024 12 25 14:30 .
drwxrwxr-x    7 vincent  admin       224  6 17  2019 ..
-rw-r-----    1 vincent  admin        56  6 17  2019 auto.cnf
-rw-r-----    1 vincent  admin       728 12 25 14:35 bin_log.000001 # 二进制日志文件
-rw-r-----    1 vincent  admin        17 12 25 14:30 bin_log.index  # 二进制日志索引文件

以默认的 InnoDB 为例，所有未提交的事务产生的二进制日志文件会先写入到内存中，什么时间将内存中的二进制日志文件刷新到磁盘由 sync_binlog 参数的配置相关。配置 sync_binlog=N 则表示每缓存 N 次之后写入磁盘，即每提交 N 次事务后写入磁盘。

查看二进制日志文件的内容，必须使用 MySQL 提供的 mysqlbinlog 工具去查看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
$ mysqlbinlog -v --base64-output=decode-rows /usr/local/var/mysql/bin_log.000001
#191225 17:28:16 server id 1  end_log_pos 1689 CRC32 0x5b9f6991 	Update_rows: table id 239 flags: STMT_END_F
### UPDATE `classicmodels`.`customers`
### WHERE
###   @1=103
###   @2='Atelier graphique'
###   @3=''
###   @4='Carine '
###   @5='40.32.2555'
###   @6='54, rue Royale'
###   @7=NULL
###   @8='Nantes'
###   @9=NULL
###   @10='44000'
###   @11='France'
###   @12=1370
###   @13=21000.00
### SET
###   @1=103
###   @2='Atelier graphique'
###   @3='Schmitt'
###   @4='Carine '
###   @5='40.32.2555'
###   @6='54, rue Royale'
###   @7=NULL
###   @8='Nantes'
###   @9=NULL
###   @10='44000'
###   @11='France'
###   @12=1370
###   @13=21000.00
# at 1689
#191225 17:28:16 server id 1  end_log_pos 1720 CRC32 0xd13f3d52 	Xid = 117
COMMIT/*!*/;
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*/;

参考文章