0基础学MySQL数据库—从小白到大牛(21)MySQL的数据目录

---

一、MySQL8的主要目录结构

[root@localhost /]# find / -name mysql
/etc/logrotate.d/mysql
/var/lib/mysql
/var/lib/mysql/mysql
/usr/bin/mysql
/usr/lib64/mysql

数据库文件的存放路径

MySQL数据库文件的存放路径：/var/lib/mysql/
MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中，这个目录就称为数据目录

[root@localhost /]# cd /var/lib/mysql
[root@localhost mysql]# ll
total 188872
-rw-r-----. 1 mysql mysql       56 Jul 25 00:55 auto.cnf
-rw-r-----. 1 mysql mysql      478 Jul 25 18:01 binlog.000001
-rw-r-----. 1 mysql mysql      157 Jul 25 18:01 binlog.000002
-rw-r-----. 1 mysql mysql       32 Jul 25 18:01 binlog.index
-rw-------. 1 mysql mysql     1680 Jul 25 00:56 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 25 00:56 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 25 00:56 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 25 00:56 client-key.pem
-rw-r-----. 1 mysql mysql   196608 Jul 25 18:03 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql  8585216 Jul 25 00:55 #ib_16384_1.dblwr
-rw-r-----. 1 mysql mysql     5948 Jul 25 00:56 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 25 18:01 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 25 18:03 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 25 00:55 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 25 18:01 ibtmp1
drwxr-x---. 2 mysql mysql      187 Jul 25 18:01 #innodb_temp
drwxr-x---. 2 mysql mysql      143 Jul 25 00:56 mysql
-rw-r-----. 1 mysql mysql 25165824 Jul 25 18:01 mysql.ibd
srwxrwxrwx. 1 mysql mysql        0 Jul 25 18:01 mysql.sock
-rw-------. 1 mysql mysql        5 Jul 25 18:01 mysql.sock.lock
drwxr-x---. 2 mysql mysql     8192 Jul 25 00:55 performance_schema
-rw-------. 1 mysql mysql     1680 Jul 25 00:56 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 25 00:56 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 25 00:56 server-cert.pem
-rw-------. 1 mysql mysql     1676 Jul 25 00:56 server-key.pem
drwxr-x---. 2 mysql mysql       28 Jul 25 00:56 sys
-rw-r-----. 1 mysql mysql 16777216 Jul 25 18:03 undo_001
-rw-r-----. 1 mysql mysql 16777216 Jul 25 18:03 undo_002

MySQL把数据都存到哪个路径下呢？其实数据目录对应着一个系统变量datadir我们在使用客户端与服务器建立连接之后查看这个系统变量的值就可以了：

mysql> show variables like 'datadir'; 
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.60 sec)

从结果中可以看出，在我的计算机上MySQL的数据目录就是/var/lib/mysql/

相关命令目录

[root@localhost /]# cd /usr/bin
[root@localhost bin]# ls

[root@localhost /]# cd /usr/sbin
[root@localhost bin]# ls

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

配置文件目录

[root@localhost bin]# cd /usr/share/mysql-8.0
[root@localhost mysql-8.0]# ll
total 1012
drwxr-xr-x. 2 root root     24 Jul 25 00:54 bulgarian
drwxr-xr-x. 2 root root   4096 Jul 25 00:54 charsets
drwxr-xr-x. 2 root root     24 Jul 25 00:54 czech
drwxr-xr-x. 2 root root     24 Jul 25 00:54 danish
-rw-r--r--. 1 root root  25575 Mar 23 06:52 dictionary.txt
drwxr-xr-x. 2 root root     24 Jul 25 00:54 dutch
drwxr-xr-x. 2 root root     24 Jul 25 00:54 english
drwxr-xr-x. 2 root root     24 Jul 25 00:54 estonian
drwxr-xr-x. 2 root root     24 Jul 25 00:54 french
drwxr-xr-x. 2 root root     24 Jul 25 00:54 german
drwxr-xr-x. 2 root root     24 Jul 25 00:54 greek
drwxr-xr-x. 2 root root     24 Jul 25 00:54 hungarian
-rw-r--r--. 1 root root   3999 Mar 23 06:52 innodb_memcached_config.sql
-rw-r--r--. 1 root root   2216 Mar 23 08:12 install_rewriter.sql
drwxr-xr-x. 2 root root     24 Jul 25 00:54 italian
drwxr-xr-x. 2 root root     24 Jul 25 00:54 japanese
drwxr-xr-x. 2 root root     24 Jul 25 00:54 korean
-rw-r--r--. 1 root root 618781 Mar 23 06:52 messages_to_clients.txt
-rw-r--r--. 1 root root 361398 Mar 23 06:52 messages_to_error_log.txt
-rw-r--r--. 1 root root   1977 Mar 23 08:12 mysql-log-rotate
drwxr-xr-x. 2 root root     24 Jul 25 00:54 norwegian
drwxr-xr-x. 2 root root     24 Jul 25 00:54 norwegian-ny
drwxr-xr-x. 2 root root     24 Jul 25 00:54 polish
drwxr-xr-x. 2 root root     24 Jul 25 00:54 portuguese
drwxr-xr-x. 2 root root     24 Jul 25 00:54 romanian
drwxr-xr-x. 2 root root     24 Jul 25 00:54 russian
drwxr-xr-x. 2 root root     24 Jul 25 00:54 serbian
drwxr-xr-x. 2 root root     24 Jul 25 00:54 slovak
drwxr-xr-x. 2 root root     24 Jul 25 00:54 spanish
drwxr-xr-x. 2 root root     24 Jul 25 00:54 swedish
drwxr-xr-x. 2 root root     24 Jul 25 00:54 ukrainian
-rw-r--r--. 1 root root   1248 Mar 23 08:12 uninstall_rewriter.sql

[root@localhost mysql-8.0]# cd /etc
[root@localhost etc]# ls

二、数据库和文件系统的关系

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

查看默认数据库

查看一下在我的计算机上当前有哪些数据库：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (1.30 sec)

• mysql
  MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
• information_schema
  MySQL 系统自带的数据库，这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。在系统数据库information_schema中提供了一些以innodb_sys开头的表，用于表示内部系统表。
• performance_schema
  MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。
• sys
  MySQL 系统自带的数据库，这个数据库主要是通过视图的形式把 information_schema 和performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

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

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

[root@localhost etc]# cd /var/lib/mysql
[root@localhost mysql]# ls
auto.cnf       binlog.index  client-cert.pem    #ib_16384_1.dblwr  ib_logfile0  #innodb_temp  mysql.sock          private_key.pem  server-key.pem  undo_001
binlog.000001  ca-key.pem    client-key.pem     ib_buffer_pool     ib_logfile1  mysql         mysql.sock.lock     public_key.pem   sys             undo_002
binlog.000002  ca.pem        #ib_16384_0.dblwr  ibdata1            ibtmp1       mysql.ibd     performance_schema  server-cert.pem  test

[root@localhost mysql]# cd ./test
[root@localhost test]# ls

5.7有db.opt

8.0就没有db.opt，改存放于具体表中。也没有frm文件，因为把两个文件合二为一为ibd文件
具体详见下方介绍

db.opt 用于存放数据库下信息，存放字符集或者使用规则

.frm 用于存储表结构

.ibd 5.7当中文件数据存放在这里，也叫独立表空间

ibdata1 也叫系统表空间，用于存放数据

① 系统表空间（system tablespace）

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

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

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

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

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

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

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

表名.ibd

比如：我们使用了 独立表空间 去存储 atguigu 数据库下的 test 表的话，那么在该表所在数据库对应的 atguigu 目录下会为 test 表创建这两个文件：

test.frm 
test.ibd

其中 test.ibd 文件就用来存储 test 表中的数据和索引。

④ 其他类型的表空间

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

.frm在mysql8中不存在了。那去哪里了呢？

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

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

这个工具不需要下载，MySQL8自带的有，只要你配好环境变量就能到处用。

(1)查看表结构
到存储ibd文件的目录下，执行下面的命令：

ibd2sdi --dump-file=表名.txt   表名.ibd

ibd2sdi --dump-file=emp.txt   emp.ibd
vim ./emp.txt

这样iibd2sdi就会把ibd里存储的表结构以json的格式保存在.txt中

小结

如果表InnoDB ，data\a中会产生1个或者2个文件：

b.frm ：描述表结构文件，字段长度等
如果采用系统表空间 模式的，数据信息和索引信息都存储在 ibdata1 中
如果采用独立表空间 存储模式，data\a中还会产生 b.ibd 文件（存储数据信息和索引信息）
此外：
① MySQL5.7 中会在data/a的目录下生成 db.opt 文件用于保存数据库的相关配置。比如：字符集、比较规则。而MySQL8.0不再提供db.opt文件。

② MySQL8.0中不再单独提供b.frm，而是合并在b.ibd文件中。

如果表b采用MyISAM，data\a中会产生3个文件：

MySQL5.7 中： b.frm ：描述表结构文件，字段长度等。
MySQL8.0 中 b.xxx.sdi ：描述表结构文件，字段长度等
b.MYD (MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式)
b.MYI (MYIndex)：存放索引信息文件

视图在文件系统中的表示

我们知道MySQL中的视图其实是虚拟的表，也就是某个查询语句的一个别名而已，所以在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。和表一样，描述视图结构的文件也会被存储到所属数居库对应的子目录下边，只会存储一个视图名.frm的文件。如下图中的：emp_details_view.frm

其他的文件

除了我们上边说的这些用户自己存储的数据以外，数据目录下还包括为了更好运行程序的一些额外文件，主要包括这几种类型的文件：

• 服务器进程文件
  我们知道每运行一个MySQL服务器程序，都意味着启动一个进程。MySQL服务器会把自己的进程ID写入到一个文件中。
• 服务器日志文件
  在服务器运行过程中，会产生各种各样的日志，比如常规的查询日志、错误日志、二进制日志、redo日志等。这些日志各有各的用途。
• 默认/自动生成的SSL和RSA证书和密销文件
  主要是为了客户端和服务器安全通信而创建的一些文件。