程序员scholar
  • MySQL
  • MySQL高级
scholar
2023-08-29
目录

第02章_MySQL的数据目录

友情提示

转载须知: 以下所有文章整理于B站宋红康老师的《MySQL数据库入门到大牛》。MySQL (opens new window)

# 1. MySQL8的主要目录结构

find / -name mysql
1

image-20240224074706961

# 1.1 数据库文件的存放路径

MySQL数据库文件的存放路径: /var/lib/mysql/

  • MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件,之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中,这个目录就称为数据目录。
  • MySQL把数据都存到哪个路径下呢?其实数据目录对应着一个系统变量datadir,我们在使用客户端与服务器建立连接之后查看这个系统变量的值就可以了：
show variables like 'datadir'; # /var/lib/mysql/
1

image-20240224074830688

# 1.2 相关命令目录

相关命令目录：/usr/bin 和 /usr/sbin。

[root@yisu-6579123d39065 bin]# cd /usr/bin
[root@yisu-6579123d39065 bin]# find . -name "mysqladmin*"
./mysqladmin
[root@yisu-6579123d39065 bin]# find . -name "mysqldump*"
./mysqldump
./mysqldumpslow
[root@yisu-6579123d39065 bin]#
1
2
3
4
5
6
7

安装目录下非常重要的bin目录,它里边存储了许多关于控制客户端程序和服务器程序的命令(许多可执行文件，比如 mysql， mysqld， mysqld_safe 等）。而数据目录是用来存储MysQL在运行过程中产生的数据，注意区分开二者。

# 1.3 配置文件目录

**配置文件目录：/usr/share/mysql-8.0（命令及配置文件），/etc/my.cnf **

image-20240224080204750

# 2. 数据库和文件系统的关系

像InnoDB、 MyISAM这样的存储引擎都是把表存储在磁盘上的,操作系统用来管理磁盘的结构被称为 文件系统,所以用专业一点的话来表述就是:像InnoDB,MyISAM这样的存储引擎都是把表存储在文件系统上的。当我们想读取数据的时候,这些存储引擎会从文件系统中把数据读出来返回给我们,当我们想写入数据的时候,这些存储引擎会把这些数据又写回文件系统。

# 2.1 查看默认数据库

mysql> show databases;
1

image-20240224080644005

可以看到有4个数据库是属于MySQL自带的系统数据库。这些数据库为MySQL的运行提供了核心的支持，包括用户权限的管理、系统的元数据、性能监控数据以及帮助理解和优化MySQL服务器的工具。

# 1. mysql数据库

  • 用途：存储了系统的用户账号信息、权限信息、插件信息等。是MySQL权限和安全性管理的核心数据库。
  • 重要表：
    • user：存储用户账户信息和全局权限。
    • db：存储数据库级的权限。
    • tables_priv：存储表级的权限。
    • columns_priv：存储列级的权限。
    • proc_priv：存储存储过程和函数的权限。

# 2. information_schema数据库

  • 用途：提供了关于MySQL服务器中所有其他数据库的信息，如表的结构、数据类型、访问权限等的元数据。information_schema是一个虚拟数据库，其内容是动态生成的，不存储在磁盘上。
  • 重要视图：
    • TABLES：提供关于数据库中所有表的信息。
    • COLUMNS：提供关于表列的详细信息。
    • STATISTICS：提供关于表索引的信息。
    • SCHEMATA：提供关于数据库的信息。

# 3. performance_schema数据库

  • 用途：用于监控MySQL服务器在运行时的性能参数和统计信息，帮助分析数据库的瓶颈。performance_schema提供了丰富的性能数据，但默认情况下可能未完全启用所有的监控工具。
  • 重要表：
    • events_waits_summary_global_by_event_name：提供等待事件的汇总信息。
    • file_summary_by_event_name：提供文件I/O事件的汇总信息。
    • table_locks_waits_summary_by_table：提供表锁等待的汇总信息。

# 4. sys数据库

  • 用途：是一个对performance_schema数据进行了封装和扩展的数据库，目的是让用户更容易地查询和分析性能数据。它通过视图和函数提供了更易于理解和使用的接口来访问performance_schema的数据。
  • 重要视图和函数：
    • sys.schema_table_statistics：提供表级别的统计信息。
    • sys.session：提供当前MySQL服务器会话的信息。
    • sys.statement_analysis：提供SQL语句执行的分析结果。

# 2.2 数据库在文件系统中的表示

看一下我的计算机上的数据目录下的内容：

[root@yisu-6579123d39065 lib]# cd /var/lib/mysql
[root@yisu-6579123d39065 mysql]# ll
总用量 91600
-rw-r----- 1 mysql mysql       56 2月  24 00:58 auto.cnf
-rw-r----- 1 mysql mysql     3347 2月  24 03:24 binlog.000001
-rw-r----- 1 mysql mysql       16 2月  24 01:01 binlog.index
-rw------- 1 mysql mysql     1680 2月  24 00:58 ca-key.pem
-rw-r--r-- 1 mysql mysql     1108 2月  24 00:58 ca.pem
-rw-r--r-- 1 mysql mysql     1108 2月  24 00:58 client-cert.pem
-rw------- 1 mysql mysql     1680 2月  24 00:58 client-key.pem
drwxr-x--- 2 mysql mysql        6 2月  24 03:04 dbtest1
-rw-r----- 1 mysql mysql   196608 2月  24 03:47 #ib_16384_0.dblwr
-rw-r----- 1 mysql mysql  8585216 2月  24 00:58 #ib_16384_1.dblwr
-rw-r----- 1 mysql mysql     5927 2月  24 00:58 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 2月  24 03:45 ibdata1
-rw-r----- 1 mysql mysql 12582912 2月  24 01:01 ibtmp1
drwxr-x--- 2 mysql mysql     4096 2月  24 01:01 #innodb_redo
drwxr-x--- 2 mysql mysql     4096 2月  24 01:01 #innodb_temp
drwxr-x--- 2 mysql mysql      137 2月  24 00:58 mysql
-rw-r----- 1 mysql mysql 26214400 2月  24 03:45 mysql.ibd
srwxrwxrwx 1 mysql mysql        0 2月  24 01:01 mysql.sock
-rw------- 1 mysql mysql        6 2月  24 01:01 mysql.sock.lock
drwxr-x--- 2 mysql mysql     8192 2月  24 00:58 performance_schema
-rw------- 1 mysql mysql     1676 2月  24 00:58 private_key.pem
-rw-r--r-- 1 mysql mysql      452 2月  24 00:58 public_key.pem
-rw-r--r-- 1 mysql mysql     1108 2月  24 00:58 server-cert.pem
-rw------- 1 mysql mysql     1680 2月  24 00:58 server-key.pem
drwxr-x--- 2 mysql mysql       27 2月  24 00:58 sys
-rw-r----- 1 mysql mysql 16777216 2月  24 03:47 undo_001
-rw-r----- 1 mysql mysql 16777216 2月  24 03:44 undo_002
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

这个数据目录下的文件和子目录比较多，除了 information_schema 这个系统数据库外，其他的数据库在 数据目录 下都有对应的子目录。以我的 dbtest1数据库为例，在MySQL8.3 中打开

image-20240224082608961

# 2.3 表在文件系统中的表示

# 2.3.1 InnoDB存储引擎模式

# (1) 表结构

为了保存表结构，InnoDB在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件

表名.frm
1

比方说我们在 dbtest1数据库下创建一个名为 test 的表

mysql> USE dbtest1;
Database changed
mysql> CREATE TABLE test (
-> c1 INT
-> );
Query OK, 0 rows affected (0.03 sec)
1
2
3
4
5
6

那在数据库 dbtest1对应的子目录下就会创建一个名为 test.frm 的用于描述表结构的文件。.frm文件的格式在不同的平台上都是相同的。这个后缀名为.frm是以 二进制格式 存储的，我们直接打开是乱码的；

注意

  • 在 MySQL 8.0 之前的版本中，MySQL 使用 .frm 文件来存储表的定义（即表的结构信息），每个表都会对应一个 .frm 文件。但从 MySQL 8.0 开始，这种机制被改进了。
  • MySQL 8.0 引入了一个新的数据字典架构，用于存储所有的系统信息，包括表的结构信息。所以，在 MySQL 8.0 及更高版本中，表的定义不再存储在单独的 .frm 文件中，而是存储在 MySQL 的内部数据字典中。数据字典是内置于 MySQL 服务器的，以更高效和集中的方式管理数据库对象（如表、视图、存储过程等）的元数据。此外，表的结构信息和一些元数据也通过序列化字典信息（SDI）存储在与表相关联的 .ibd 文件中。

这个改变带来了几个好处：

  1. 性能提升：通过集中存储元数据，MySQL 可以更快地访问和管理表的定义信息，从而提高数据库的整体性能。
  2. 更好的可靠性：以前，如果 .frm 文件损坏，可能会导致数据不一致或表无法使用。现在，由于元数据存储在数据字典中，这些信息被更好地保护起来，减少了数据损坏的风险。
  3. 简化管理：在旧版本中，数据库的迁移和备份需要处理多种不同的文件（如 .frm、.ibd 等）。数据字典的引入简化了这些操作，因为表的定义信息现在是集中管理的。
# (2) 表中数据和索引

① 系统表空间（system tablespace）

默认情况下，InnoDB会在数据目录下创建一个名为ibdata1、大小为12M的自拓展文件，这个文件就是对应的系统表空间在文件系统上的表示。怎么才12M？注意这个文件是 自扩展文件，当不够用的时候它会自己增加文件大小。

image-20240224091704069

当然,如果你想让系统表空间对应文件系统上多个实际文件,或者仅仅觉得原来的ibdata1这个文件名难听,那可以在MySQL启动时配置对应的文件路径以及它们的大小,比如我们这样修改一下my.cnf配置文件:

[server]
innodb_data_file_path=data1:512M;data2:512M:autoextend
1
2

这样在MySQL启动之后就会创建这两个512M大小的文件作为 系统表空间，其中的autoextend 表明这两个文件如果不够用会自动扩展data2文件的大小。

需要注意的一点是,在一个MySQL服务器中,系统表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中,我们表中的数据都会被默认存储到这个系统表空间。

② 独立表空间(file-per-table tablespace)

在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据和索引的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同。

表名.ibd
1

image-20240224092131210

③ 系统表空间与独立表空间的设置

我们可以自己指定使用系统表空间还是独立表空间来存储数据，这个功能由启动参数innodb_file_per_table控制

[server] 
innodb_file_per_table=0 # 0：代表使用系统表空间； 1：代表使用独立表空间
1
2

④ 其他类型的表空间

随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间（general tablespace）、临时表空间（temporary tablespace）等。

# (3) 解析.ibd文件

.frm在MySQL8中不存在了，表的结构信息和一些元数据真的合并到.ibd文件了吗？

这就需要解析ibd文件。Oracle 官方将frm文件的信息及更多信息移动到叫做序列化字典信息（Serialized DictionaryInformation, SDI) , SDI被写在ibd文件内部。MySQL 8.0属于Oracle旗下,同理。

为了从IBD文件中提取SDI信息，Oracle提供了一个应用程序 ibdEsdi。

ibd2sdi官方文档

  • 这个工具不需要下载，MySQL8自带的有，只要你配好环境变量就能到处用。
  • 查看表结构到存储ibd文件的目录下，执行下面的命令：
# student替换为具体的表名
ibd2sdi --dump-file=student.txt student.ibd
1
2

结果如图所示：

image-20240224100648999

image-20240224101059105

# 2.3.2 MyISAM存储引擎模式

# 1. 表结构

在存储表结构方面， MyISAM 和 InnoDB 一样，也是在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件

表名.frm
1
# 2. 表中数据和索引

在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。

test.frm 存储表结构 #MySQL8.0 改为了 b.xxx.sdi
test.MYD 存储数据 (MYData) 
test.MYI 存储索引 (MYIndex
1
2
3

举例：创建一个 student_myisam表，使用 ENGINE 选项显式指定为MYISAM引擎。因为 InnoDB 是默认引擎。







 

CREATE TABLE `student_myisam` (
`id` bigint NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`age` int DEFAULT NULL,
`sex` varchar(2) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=MYISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb3;
1
2
3
4
5
6
7

image-20240224102538698

image-20240224102233725

# 2.3.3 两种存储引擎小结

1、如果表b采用 InnoDB引擎 ，表b所在数据库下会产生1个或者2个文件：

  • b.frm ：描述表结构文件，字段长度等
  • 如果采用 系统表空间 模式的，数据信息和索引信息都存储在 ibdata1 中
  • 如果采用 独立表空间 存储模式，表b所在数据库下还会产生 b.ibd 文件（存储数据信息和索引信息）

此外：

① MySQL5.7 中会在表b所在数据库下生成 db.opt 文件用于保存数据库的相关配置。比如：字符集、比较

规则。而MySQL8.0不再提供db.opt文件。

② MySQL8.0中不再单独提供b.frm，而是将对应的表结构信息合并在b.ibd文件中。

2、如果表b采用 MyISAM引擎 ，表b所在数据库下中会产生3个文件：

  • MySQL5.7 中： b.frm ：描述表结构文件，字段长度等。

  • MySQL8.0 中 b.xxx.sdi ：描述表结构文件，字段长度等

  • b.MYD (MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式)

  • b.MYI (MYIndex)：存放索引信息文件

公众号封面

编辑此页 (opens new window)
上次更新: 2024/12/28, 18:32:08
第01章_Linux下安装MySQL
第03章_用户与权限管理

← 第03章_用户与权限管理→

Theme by Vdoing | Copyright © 2019-2025 程序员scholar