MySQL的数据目录（Linux）
1：MySQL8.0的主要目录结构
我们在安装完MySQL后，就得具体看看MySQL的一些目录结构和基本的文件；具体操作如下
①：使用全局文件查询命令：find / -name mysql
/etc/logrotate.d/mysql
/usr/bin/mysql
/usr/lib64/mysql
/var/lib/mysql
/var/lib/mysql/mysql
②：创建的数据库对应磁盘文件的存放路径（注：这个是MySQL的数据目录）
我们可以在MySQL服务里面执行：SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+
从结果中可以看出，在我的计算机上MySQL的数据目录就是 /var/lib/mysql/
InnoDB、MyISAM这样的存储引擎都是把表存储在磁盘文件上的。当我们想读取数据的时候，
这些存储引擎会从磁盘文件中把数据读出来返回给我们，当我们想写入数据的时候，这些存储引擎会把这些数据又写回磁盘文件上。
③：MySQL可执行命令目录
可以猜测，安装后的MySQL存储的可执行相关命令在这里面：/usr/bin
命令执行：ls /usr/bin | grep mysql
mysql、mysqladmin、mysqlbinlog、mysqlcheck、mysql_upgrade....
④：配置文件目录
MySQL配置文件：/etc/mysql/my.cnf
用于存放字符集或语言等信息：/usr/share/mysql-8.0
二：MySQL数据库与磁盘文件系统的关系
1：查看默认数据库信息
①：查询安装MySQL后默认的几个数据库：
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
可以看到有4个数据库是属于MySQL自带的系统数据库。
②：自带的数据库介绍说明
mysql：
MySQL系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，
一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
information_schema：
MySQL系统自带的数据库，这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、
哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据；如下部分：
CHARACTER_SETS表：
提供了 mysql 可用字符集的信息，SHOW CHARACTER SET; 命令从这个表获取结果
SCHEMATA表：
当前 mysql 实例中所有数据库的信息。SHOW DATABASES; 命令从这个表获取数据
TABLES表：
存储数据库中的表信息（包括视图），包括表属于哪个数据库，表的类型、存储引擎、创建时间等信息。
SHOW TABLES FROM XX; 命令从这个表获取结果
COLUMNS表：
存储表中的列信息，包括表有多少列、每个列的类型等。SHOW COLUMNS FROM schemaname.tablename命令从这个表获取结果
STATISTICS表：
表索引的信息。SHOW INDEX FROM schemaname.tablename; 命令从这个表获取结果。
USER_PRIVILEGES表：
用户权限表。内容源自 mysql.user 授权表。是非标准表。
SCHEMA_PRIVILEGES表：
方案权限表。给出了关于方案（数据库）权限的信息。内容来自 mysql.db 授权表。是非标准表。
TABLE_PRIVILEGES表：
表权限表。给出了关于表权限的信息。内容源自 mysql.tables_priv 授权表。是非标准表。
COLUMN_PRIVILEGES表：
列权限表。给出了关于列权限的信息。内容源自 mysql.columns_priv 授权表。是非标准表。
performance_schema：
MySQL系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以
用来监控MySQL服务的各类性能指标。包括统计最近执行了哪些语句，在执行过程的每个阶段都
花费了多长时间，内存的使用情况等信息。
sys：
MySQL系统自带的数据库，这个数据库主要是通过视图的形式把information_schema和
performance_schema结合起来，帮助系统管理员和开发人员监控MySQL的技术性能。
2：数据库在磁盘文件中的表示
我们每创建一个数据库后，都会在磁盘的 /var/lib/mysql 目录下自动创建一个对应我们刚才创建的数据库文件夹；为了更好测试，我们创建一个数据库
①：Innodb格式 -- 创建数据库表 CREATE DATABASE IF NOT EXISTS demo_db1 CHARACTER SET UTF8mb3 COLLATE utf8mb3_general_ci; USE demo_db1; -- 老师表 CREATE TABLE `teacher_innodb`( `tid` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID', `tname` varchar(5) NOT NULL COMMENT '姓名', `tsex` enum('男','女') DEFAULT '男' COMMENT '性别', `tage` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄', `tsalary` decimal(6, 1) DEFAULT '0.0' COMMENT '工资', `taddress` varchar(10) DEFAULT NULL COMMENT '住址' )ENGINE=Innodb CHARACTER SET UTF8mb3 COLLATE utf8mb3_general_ci; -- 学生表 CREATE TABLE `student_innodb`( `sid` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID', `sname` varchar(5) NOT NULL COMMENT '姓名', `ssex` enum('男','女') DEFAULT '男' COMMENT '性别', `sage` tinyint(11) unsigned NOT NULL DEFAULT '20' COMMENT '年龄', `scredit` double DEFAULT '60.0' COMMENT '学分', `smoney` decimal(4, 1) DEFAULT '0.0' COMMENT '零花钱', `saddress` mediumtext COMMENT '住址', `senrol` date DEFAULT '2020-12-12' COMMENT '入学时间', `tid` int(11) DEFAULT NULL, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teacher_innodb` (`tid`) ON DELETE SET NULL ON UPDATE CASCADE )ENGINE=Innodb CHARACTER SET UTF8mb3 COLLATE utf8mb3_general_ci; -- 插入数据 INSERT INTO teacher_innodb VALUES(1,"张老师",'男',22,6000.20,'安徽六安'); INSERT INTO student_innodb VALUES(1,'鲍大莉','女',25,98,999.9,'安徽六安','2022-02-02',1); ②：MyISAM格式建表示例 -- 老师表 CREATE TABLE `teacher_myisam`( `tid` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID', `tname` varchar(5) NOT NULL COMMENT '姓名', `tsex` enum('男','女') DEFAULT '男' COMMENT '性别', `tage` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄', `tsalary` decimal(6, 1) DEFAULT '0.0' COMMENT '工资', `taddress` varchar(10) DEFAULT NULL COMMENT '住址' )ENGINE=MyISAM CHARACTER SET UTF8mb3 COLLATE utf8mb3_general_ci;
我们在MySQL5.7和MySQL8.0分别执行这两个SQL脚本信息，并对其磁盘的文件进行基本分析说明
①：数据库创建后对应的具体磁盘文件 -- 进入目录并展示磁盘文件信息 cd /var/lib/mysql ll -- 打印文件信息（部分文件；d开头的都是文件夹） -rw-r----- 1 mysql mysql 56 1月 3 22:17 auto.cnf -rw-r----- 1 mysql mysql 45339 1月 4 12:43 binlog.000001 -rw-r----- 1 mysql mysql 64 1月 4 12:44 binlog.index drwxr-x--- 2 mysql mysql 4096 1月 4 16:04 demo_db1 -rw-r----- 1 mysql mysql 12582912 1月 4 16:04 ibdata1 drwxr-x--- 2 mysql mysql 4096 1月 3 22:18 mysql -rw-r----- 1 mysql mysql 25165824 1月 4 16:04 mysql.ibd srwxrwxrwx 1 mysql mysql 0 1月 4 12:44 mysql.sock drwxr-x--- 2 mysql mysql 4096 1月 3 22:17 performance_schema drwxr-x--- 2 mysql mysql 4096 1月 3 22:18 sys -- 其实这个数据目录下的文件和子目录比较多，除了information_schema这个系统数据库外， -- 其它的数据库在数据目录下都有对应的子目录 ②：以我们之前创建的 demo_db1 数据库在MySQL5.7中打开此文件夹 ll ./demo_db1 -- 打印的文件信息 -rw-r----- 1 mysql mysql 61 1月 4 21:09 db.opt -rw-r----- 1 mysql mysql 8889 1月 4 21:09 student_innodb.frm -rw-r----- 1 mysql mysql 114688 1月 4 21:09 student_innodb.ibd -rw-r----- 1 mysql mysql 8772 1月 4 21:09 teacher_innodb.frm -rw-r----- 1 mysql mysql 98304 1月 4 21:09 teacher_innodb.ibd -rw-r----- 1 mysql mysql 8772 1月 4 21:09 teacher_myisam.frm -rw-r----- 1 mysql mysql 0 1月 4 21:09 teacher_myisam.MYD -rw-r----- 1 mysql mysql 1024 1月 4 21:09 teacher_myisam.MYI ③：以我们之前创建的 demo_db1 数据库在MySQL8.0中打开此文件夹 ll ./demo_db1 -- 打印的文件信息 -rw-r----- 1 mysql mysql 131072 1月 4 16:04 student_innodb.ibd -rw-r----- 1 mysql mysql 114688 1月 4 16:04 teacher_innodb.ibd -rw-r----- 1 mysql mysql 6037 1月 4 16:04 teacher_myisam_377.sdi -rw-r----- 1 mysql mysql 0 1月 4 16:04 teacher_myisam.MYD -rw-r----- 1 mysql mysql 1024 1月 4 16:04 teacher_myisam.MYI 总结：发现两个版本的数据库里面的文件不同（下面详细介绍）
3：表在磁盘文件的表示
我们的数据其实都是以记录的形式插入到表中的，每个表的信息其实可以分为两种：表结构的定义、表中的数据
InnoDB存储引擎模式
Ⅰ：表结构 为了保存表结构，InnoDB在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件 ，文件名是这样： 表名.frm 比如我们在MySQL5.7下查询我们之前创建的数据库 demo_db1 它对应的子目录下就会创建有名为 student_innodb.frm 和 teacher_innodb.frm 的用于描述表结构的文件。.frm文件的格式在不同的平台上都是相同的。 这个后缀名为.frm是以 二进制格式 存储的，我们直接打开是乱码的。 Ⅱ：表中的数据和索引 ①：系统表空间（system tablespace） 默认情况下，InnoDB会在数据目录下创建一个名为ibdata1、大小为12M的文件，这个文件就是对应的系统表空间在磁盘文件上的表示。 怎么才12M？注意这个文件是自扩展文件，当不够用的时候它会自己增加文件大小；当然，如果你想让系统表空间对应磁盘文件上有多 个实际文件，或者仅仅觉得原来的ibdata1这个文件名难听，那可以在MySQL启动时配置对应的文件路径以及它们的大小，比如我们 这样修改一下my.cnf 配置文件： [server] innodb_data_file_path=data1:512M;data2:512M:autoextend -- 注：这样在MySQL启动之后就会创建这两个512M大小的文件作为系统表空间， -- 其中的autoextend表明这两个文件如果不够用会自动扩展data2文件的大小。 ②：独立表空间(file-per-table tablespace) 在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间， 也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建 一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个 .ibd 的扩展名而已，所以完整的文件名称长这样： 表名.ibd 比如我们在MySQL5.7下查询我们之前创建的数据库 demo_db1 ；因为使用了独立表空间存储，所以它对应的子目录下就会创建 以student_innodb表和teacher_innodb来说对应的文件： student_innodb.frm（表结构描述文件）、student_innodb.ib（表数据和索引文件） teacher_innodb.frm（表结构描述文件）、teacher_innodb.ib（表数据和索引文件） ③：系统表空间与独立表空间的设置 我们可以自己指定使用 系统表空间 还是 独立表空间 来存储数据，这个功能由启动参数innodb_file_per_table 控制， 比如说我们想刻意将表数据都存储到 系统表空间 时，可以在启动MySQL服务器的时候这样配置： [server] innodb_file_per_table=0 # 参数说明 0(OFF)：代表使用系统表空间； 1(ON)：代表使用独立表空间 默认情况（使用独立表空间）： SHOW VARIABLES LIKE 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ ④：其他类型的表空间 随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间， 比如通用表空间（general tablespace）、临时表空间（temporary tablespace）等。 Ⅲ：总结（必看） MySQL5.7和MySQL8.0都使用的是独立表空间 ①：在MySQL5.7下的表空间磁盘文件表示形式（以demo_db1里的student_innodb表说明） > MySQL5.7在创建表的时候首先会在数据库存放路径创建 ./demo_db1 文件夹 > 在 ./demo_db1 文件夹目录下生成一个 db.opt 文件用于保存数据库的相关配置。比如：字符集、比较规则。 > 在 ./demo_db1 文件夹目录下生成 student_innodb.frm 文件描述表结构，字段长度等。 > 在 ./demo_db1 文件夹目录下生成 student_innodb.ibd 文件存储数据信息和索引信息。 [root@ser286522571205 /]# ll /var/lib/mysql/demo_db1/ -rw-r----- 1 mysql mysql 61 1月 4 21:09 db.opt -rw-r----- 1 mysql mysql 8889 1月 4 21:09 student_innodb.frm -rw-r----- 1 mysql mysql 114688 1月 4 21:09 student_innodb.ibd ②：在MySQL8.0下的表空间磁盘文件表示形式（以demo_db1里的student_innodb表说明） > MySQL8.0在创建表的时候首先会在数据库存放路径创建 ./demo_db1 文件夹 > 在 ./demo_db1 文件夹目录下生成 student_innodb.ibd 文件存储（这个文件合并了frm和ibd文件） -- 注：因为MySQL8.0中不再单独提供b.frm和db.opt，而是合并在b.ibd文件中。 [root@VM-4-10-centos demo_db1]# ll /var/lib/mysql/demo_db1/ -rw-r----- 1 mysql mysql 131072 1月 4 16:04 student_innodb.ibd 注：MySQL8.0里可以直接查看xx.ibd文件，但是需要借助Oracle提供的ibd2sdi工具（查看命令：ll | grep ibd2sdi） 在 ./demo_db1 数据库文件下执行：ibd2sdi --dump-file=生成文件名称.txt xxx.ibd 如：ibd2sdi --dump-file=student_innodb.txt student_innodb.ibd [root@VM-4-10-centos demo_db1]# ll -rw-r----- 1 mysql mysql 131072 1月 4 16:04 student_innodb.ibd -rw------- 1 root root 23785 1月 4 23:26 student_innodb.txt 查看txt文件太乱了，所有格式化信息： -- 查询jq安装包 yum list | grep jq -- 安装jq.x86_64 yum install jq.x86_64 -- 执行命令生成一个json文件（下面2行是一条语句） ibd2sdi student_innodb.ibd |jq '.[]?|.[]?|.dd_object?|({table:.name?}, (.columns?|.[]?|{name:.name,type:.column_type_utf8}))' > student_innodb.json -- 生成的文件类似这样 {"table": "student_innodb"} {"name": "sid","type": "int"} {"name": "sname","type": "varchar(5)"}
MyISAM存储引擎模式
Ⅰ：表结构 在存储表结构方面，MyISAM和InnoDB一样，也是在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件： 表名.frm Ⅱ：表中的数据和索引 在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是 使用不同的文件来存储数据文件和索引文件，同时表数据都存放在对应的数据库子目录下。 我们之前在 demo_db1 数据库下创建了一个 teacher_myisam 表；这时候就会生成3个文件 teacher_myisam.frm 存储表结构 teacher_myisam.MYD 存储数据 (MYData) teacher_myisam.MYI 存储索引 (MYIndex) Ⅲ：总结（必看） 如果是MyISAM存储引擎的表结构时虽然都会生成3个文件，但是MySQL5.7和MySQL8.0略微不同： MySQL5.7 中：b.frm ：描述表结构文件，字段长度等。 -rw-r----- 1 mysql mysql 8772 1月 4 21:09 teacher_myisam.frm -rw-r----- 1 mysql mysql 0 1月 4 21:09 teacher_myisam.MYD -rw-r----- 1 mysql mysql 1024 1月 4 21:09 teacher_myisam.MYI MySQL8.0 中：b.xxx.sdi ：描述表结构文件，字段长度等。 -rw-r----- 1 mysql mysql 6037 1月 4 16:04 teacher_myisam_377.sdi -rw-r----- 1 mysql mysql 0 1月 4 16:04 teacher_myisam.MYD -rw-r----- 1 mysql mysql 1024 1月 4 16:04 teacher_myisam.MYI MySQL5.7和MySQL8.0都会生成的文件 b.MYD (MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式) b.MYI (MYIndex)：存放索引信息文件
.
作者：蚂蚁小哥
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须在文章页面给出原文链接，否则保留追究法律责任的权利。
