MySQL：数据目录

MySQL数据目录

数据目录和安装目录的区别

• MySQL的安装目录里面有一个bin目录，里面存储了很多用来控制客户端和服务端的命令，比如mysqld等
• 数据目录是用来存储MySQL在运行过程中产生的目录的

如何确定MySQL中的数据目录

• MySQL服务器程序在启动时，会到文件系统的某个目录下加载一些数据，之后在运行过程中产生的数据也会存储到这个目录下的某些文件中，这个目录就叫做数据目录
• 系统变量data表明了数据目录的路径

> show variables like 'datadir'

+---------------+-------------------------------------------------------+
| Variable_name | Value                                                 |
+---------------+-------------------------------------------------------+
| datadir       | /home/oceanstar/workspace/mysql/mysql-data/3306/data/ |
+---------------+-------------------------------------------------------+
1 row in set (0.00 sec)

数据目录的结构

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

每次使用create database 数据库名创建一个数据库时，在文件系统中实际发生了什么呢？

每次新建一个数据库时，MySQL会做两件事：

• 在数据目录下创建一个与数据库同名的文件夹
• 在与该数据库同名的文件夹下创建一个叫做db.opt的文件（MySQL8.0没有发现这个东西）。这个文件中包含了该数据库的一些数据，比如该数据库的比较规则、字符集。

查看当前有哪些数据库：

[(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

• 这四个数据库是MySQL自带的：
  • information_schema ：
    • 这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些索引等。
    • 这些信息并不是真实的用户数据，而是一些描述性信息，有时候也叫做元数据
  • mysql：
    • 它存储了MySQL的用户账户和权限信息、一些存储过程和事件的定义信息、一些运行过程中产生的日志信息、帮助信息、时区信息等
  • performance_schema ：
    • 这个数据库主要保存着MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控
    • 它包含的信息有统计最近执行了哪些语句，在执行过程中的每个阶段花了多少时间，内存的使用情况等
  • sys：作用是通过视图的形式把information_schema和performance_schema 结合起来，让开发人员更方便的了解MySQL服务器的性能信息

看下当前数据目录中的内容：

$ ls 
 auto.cnf          client-key.pem       ibdata1      '#innodb_temp'        private_key.pem   sys
 ca-key.pem       '#ib_16384_0.dblwr'   ib_logfile0   mysql                public_key.pem    undo_001
 ca.pem           '#ib_16384_1.dblwr'   ib_logfile1   mysql.ibd            server-cert.pem   undo_002
 client-cert.pem   ib_buffer_pool       ibtmp1        performance_schema   server-key.pem
$ cd mysql

可以看到，除了information_schema 这个系统数据库外，其他的数据库在数据目录下都有对应的文件夹。

表在文件系统中的表示

数据都是以记录的形式插入到表中的。每个表的信息可以分为两种：

• 表结构的定义
  • 表结构指的是：该表的名称、表中有多少列、每个列的数据类型、约束条件等信息
  • 这些信息都体现在建表语句中。
  • 为了保存这些信息，该数据库文件夹中会创建一个专门用于描述表结构的文件，文件名为表名.frm（MySQL8.0没有发现这个东西）
  • 这个文件是以二进制格式存储的，如果直接打开会乱码
• 表中的数据

InnoDB是如何存储表数据的

InnoDB中：

• 它是使用页为基本单位来管理存储空间的，默认页大小是16KB
• 每个索引都是一棵B+树，该B+树的每个节点都是一个数据页。数据页之间没有必要是物理连续的，因为数据页之间有双向链表来维护这些表的顺序
• InnoDB的聚簇索引的叶子节点存储了用户的完整记录。也就是：索引即数据，数据即索引

为了管理这些页，引入了表空间(也叫做文件空间)的概念。这个表空间是抽象的，对应文件系统上一个或者多个真实文件，每一个表空间可以被划分成多个页，表数据就存放在表空间下的某些页中。

表空间可以分成多种类型

（1）系统表空间

• 这个表空间对应文件系统上一个盒子多个实际的文件
• 默认情况下，InnoDB会在数据目录下创建一个大小为12MB的名叫做ibdata1的文件，这个文件就是对应的系统表空间在文件系统上的表示
• 这个文件是自动扩展的，当不够用了就会自己增加文件大小

$ ls -l ibdata1
-rw-r----- 1 oceanstar oceanstar 12582912 8月   1 10:53 ibdata1

• 当然，我们可以在MySQL服务器启动时，在配置文件中指定这个ibdata1的名称、大小等，比如下面表示在MySQL启动之后，创建data1、data2这两个各自的512MB大小的文件作为系统表空间，其中的autoextend表示如果data1、data2文件不够用了，自动扩展data2的大小

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

（2）独立表空间

• 从MySQL5.6.6起，InnoDB不再默认把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间
• 在使用独立表空间来存储表数据时，会在该表所属数据库对应的子目录下创建一个叫做表名.ibd的文件来表示表文件

（3）其他表空间：

• 通用表空间
• undo表空间
• 临时表空间

什么时候使用独立表空间存储数据，什么时候使用系统表空间存储表数据

• 从MySQL5.6.6起，InnoDB默认把各个表的数据存储到独立表空间
• 从MySQL5.6.6起，可以通过启动项innodb_file_per_table指定是把表中数据存储到系统表空间还是独立表空间
  • 这个选项只对新建表有用
  • 如果想把已经存储到系统表空间的表转移到独立表空间中，语法：alter table 表名 TABLESPACE innodb_system
  • 如果想把已经存储到独立表空间的表转移到系统表空间中，语法：alter table 表名 TABLESPACE innodb_file_per_table

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

其他的文件

除了上面说的这些用户自己存储的数据外，数据目录下还包含了一些确保程序更好运行的额外文件：

• 服务器进程文件：每运行一个MySQL服务器程序，就意味着启动了一个进程。MySQL服务器会把这个进程ID写到这个文件中
• 服务器日志文件：在MySQL服务器运行期间，会产生各种日志
• SSL和RSA证书与密钥文件：主要是为了客户端和服务器安全通信而创建的一些文件

实践

我们自己来创建一个数据库：

[(none)]> create database test_data;
Query OK, 1 row affected (0.00 sec)

然后可以看到数据目录下会新建一个叫做test_data的文件夹，进入这个目录，发现是空的：

$ cd test_data/
$ ls

然后创建一个表：

[(none)]> use test_data;
Database changed
 [test_data]> create table test (c1 int);
Query OK, 0 rows affected (0.01 sec)

检查数据目录：

$ ls
test.ibd   # 存储test表中的数据