MySQL的數據目錄

歡迎來到藍隊云技術小課堂，每天分享一個技術小知識。

1. MySQL的主要目錄結構

# find / -name mysql
/etc/logrotate.d/mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql
/var/lib/mysql/mysql
/usr/bin/mysql
/usr/lib64/mysql
/usr/share/mysql

1.1 數據庫文件存放路徑

通過YUM安裝的MySQL數據庫文件的默認存放路徑：/var/lib/mysql

MySQL服務器程序在啟動時會到文件系統的某個目錄下加載一些文件，之后在運行過程中產生的數據也都會存儲到這個目錄下的某些文件中，這個目錄就是數據目錄。

MySQL把數據都存到哪個路徑下呢？其實數據目錄對應著一個系統變量 datadir，可以通過如下命令進行查看：

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

從結果中可以看出，在這里的MySQL的數據目錄就是 /var/lib/mysql/。

1.2. 相關命令目錄

相關命令目錄：/usr/bin（mysqladmin、mysqlbinlog、mysqldump等命令）和/usr/sbin。

# ll /usr/bin/mysql*
-rwxr-xr-x. 1 root root 10531233 Sep  7  2021 /usr/bin/mysql
-rwxr-xr-x. 1 root root  9329019 Sep  7  2021 /usr/bin/mysqladmin
-rwxr-xr-x. 1 root root 11332910 Sep  7  2021 /usr/bin/mysqlbinlog
-rwxr-xr-x. 1 root root  9704285 Sep  7  2021 /usr/bin/mysqlcheck
-rwxr-xr-x. 1 root root  7648822 Sep  7  2021 /usr/bin/mysql_config_editor
-rwxr-xr-x. 1 root root    26931 Sep  7  2021 /usr/bin/mysqld_multi
-rwxr-xr-x. 1 root root    27790 Sep  7  2021 /usr/bin/mysqld_safe
-rwxr-xr-x. 1 root root  9625156 Sep  7  2021 /usr/bin/mysqldump
-rwxr-xr-x. 1 root root     7844 Sep  7  2021 /usr/bin/mysqldumpslow
-rwxr-xr-x. 1 root root  9348792 Sep  7  2021 /usr/bin/mysqlimport
-rwxr-xr-x. 1 root root  9798674 Sep  7  2021 /usr/bin/mysql_install_db
-rwxr-xr-x. 1 root root  7537333 Sep  7  2021 /usr/bin/mysql_plugin
-rwxr-xr-x. 1 root root 17755032 Sep  7  2021 /usr/bin/mysqlpump
-rwxr-xr-x. 1 root root  9278592 Sep  7  2021 /usr/bin/mysql_secure_installation
-rwxr-xr-x. 1 root root  9290525 Sep  7  2021 /usr/bin/mysqlshow
-rwxr-xr-x. 1 root root  9403941 Sep  7  2021 /usr/bin/mysqlslap
-rwxr-xr-x. 1 root root  7851137 Sep  7  2021 /usr/bin/mysql_ssl_rsa_setup
-rwxr-xr-x. 1 root root  5160824 Sep  7  2021 /usr/bin/mysql_tzinfo_to_sql
-rwxr-xr-x. 1 root root 12548409 Sep  7  2021 /usr/bin/mysql_upgrade

安裝目錄下非常重要的bin目錄，它里面存儲了許多關于控制客戶端程序的命令（許多可執行文件，比如mysql、mysqldump等）。而數據目錄時用來存儲MySQL在運行過程中產生的數據。

1.3. 配置文件目錄

配置文件目錄：ll（命令及配置文件），/etc/mysql（如my.cnf）。

# ll /usr/share/mysql/
total 2072
drwxr-xr-x. 2 root root      24 Dec 31 14:00 bulgarian
drwxr-xr-x. 2 root root    4096 Dec 31 14:00 charsets
drwxr-xr-x. 2 root root      24 Dec 31 14:00 czech
drwxr-xr-x. 2 root root      24 Dec 31 14:00 danish
-rw-r--r--. 1 root root   25575 Sep  7  2021 dictionary.txt
drwxr-xr-x. 2 root root      24 Dec 31 14:00 dutch
drwxr-xr-x. 2 root root      24 Dec 31 14:00 english
-rw-r--r--. 1 root root  530464 Sep  7  2021 errmsg-utf8.txt
drwxr-xr-x. 2 root root      24 Dec 31 14:00 estonian
-rw-r--r--. 1 root root 1068800 Sep  7  2021 fill_help_tables.sql
drwxr-xr-x. 2 root root      24 Dec 31 14:00 french
drwxr-xr-x. 2 root root      24 Dec 31 14:00 german
drwxr-xr-x. 2 root root      24 Dec 31 14:00 greek
drwxr-xr-x. 2 root root      24 Dec 31 14:00 hungarian
-rw-r--r--. 1 root root    3999 Sep  7  2021 innodb_memcached_config.sql
-rw-r--r--. 1 root root    2221 Sep  7  2021 install_rewriter.sql
drwxr-xr-x. 2 root root      24 Dec 31 14:00 italian
drwxr-xr-x. 2 root root      24 Dec 31 14:00 japanese
drwxr-xr-x. 2 root root      24 Dec 31 14:00 korean
-rw-r--r--. 1 root root     773 Sep  7  2021 magic
-rw-r--r--. 1 root root     844 Sep  7  2021 mysql-log-rotate
-rw-r--r--. 1 root root    2171 Sep  7  2021 mysql_security_commands.sql
-rw-r--r--. 1 root root  288342 Sep  7  2021 mysql_sys_schema.sql
-rw-r--r--. 1 root root    1214 Sep  7  2021 mysql_system_tables_data.sql
-rw-r--r--. 1 root root  155031 Sep  7  2021 mysql_system_tables.sql
-rw-r--r--. 1 root root   10862 Sep  7  2021 mysql_test_data_timezone.sql
drwxr-xr-x. 2 root root      24 Dec 31 14:00 norwegian
drwxr-xr-x. 2 root root      24 Dec 31 14:00 norwegian-ny
drwxr-xr-x. 2 root root      24 Dec 31 14:00 polish
drwxr-xr-x. 2 root root      24 Dec 31 14:00 portuguese
drwxr-xr-x. 2 root root      24 Dec 31 14:00 romanian
drwxr-xr-x. 2 root root      24 Dec 31 14:00 russian
drwxr-xr-x. 2 root root      24 Dec 31 14:00 serbian
drwxr-xr-x. 2 root root      24 Dec 31 14:00 slovak
drwxr-xr-x. 2 root root      24 Dec 31 14:00 spanish
drwxr-xr-x. 2 root root      24 Dec 31 14:00 swedish
drwxr-xr-x. 2 root root      24 Dec 31 14:00 ukrainian
-rw-r--r--. 1 root root    1243 Sep  7  2021 uninstall_rewriter.sql

2. 數據庫和文件系統的關系

像InnoDB、MyISAM這樣的存儲引擎都是把表存儲在磁盤上的，操作系統用來管理磁盤的結構被稱為文件系統，所以用專業一點的話來表述就是：像InnoDB、MyISAM這樣的存儲引擎都是把表存儲在文件系統上的。當我們想讀取數據的時候，這些存儲引擎會從文件系統中把數據讀出來返回給我們，當我們想寫入數據的時候，這些存儲引擎會把這些數據又寫回文件系統。

2.1. 查看默認數據庫

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

可以看到有4個數據庫是屬于MySQL自帶的系統數據庫。

• mysql：

MySQL系統自帶的核心數據庫，它存儲了MySQL的用戶賬戶和權限信息，一些存儲過程、事件的定義信息，一些運行過程中產生的日志信息，一些幫助信息以及時區信息等。

• information_schema：

MySQL 系統自帶的數據庫，這個數據庫保存著MySQL服務器維護的所有其他數據庫的信息，比如有哪些表、哪些視圖、哪些觸發器、哪些列、哪些索引。這些信息并不是真實的用戶數據，而是一些描述性信息，有時候也稱之為元數據。在系統數據庫 information_schema 中提供了一些以 innodb_sys 開頭的表，用于表示內部系統表。

mysql> use information_schema;
Database changed
mysql> show tables like 'innodb_%';
+-----------------------------------------+
| Tables_in_information_schema (INNODB_%) |
+-----------------------------------------+
| INNODB_BUFFER_PAGE                      |
| INNODB_BUFFER_PAGE_LRU                  |
| INNODB_BUFFER_POOL_STATS                |
| INNODB_CACHED_INDEXES                   |
| INNODB_CMP                              |
| INNODB_CMPMEM                           |
| INNODB_CMPMEM_RESET                     |
| INNODB_CMP_PER_INDEX                    |
| INNODB_CMP_PER_INDEX_RESET              |
| INNODB_CMP_RESET                        |
| INNODB_COLUMNS                          |
| INNODB_DATAFILES                        |
| INNODB_FIELDS                           |
......

• performance_schema

  MySQL 系統自帶的數據庫，這個數據庫里主要保存MySQL服務器運行過程中的一些狀態信息，可以用來監控 MySQL 服務的各類性能指標 。包括統計最近執行了哪些語句，在執行過程的每個階段都花費了多長時間，內存的使用情況等信息。

• sys

  MySQL 系統自帶的數據庫，這個數據庫主要是通過 視圖 的形式把 information_schema 和 performance_schema 結合起來，幫助系統管理員和開發人員監控 MySQL 的技術性能。

2.2. 數據庫在文件系統中的表示

使用 CREATE DATABASE 數據庫名 語句創建一個數據庫的時候，在文件系統上實際發生了什么呢？ 其實很簡單，每個數據庫都對應數據目錄下的一個子目錄，或者說對應一個文件夾，每當新建一個數據庫時，MySQL會幫我們做這兩件事兒：

1、在數據目錄下創建一個和數據庫名同名的字目錄。

2、在與該數據庫名同名的字目錄下創建一個名為db.opt的文件（僅限MySQL5.7及之前版本），這個文件中包含了該數據庫的各種屬性，比如該數據庫的字符集和比較規則。

首先看下這里的數據目錄下的內容：

# cd /var/lib/mysql
# ll
total 122956
-rw-r-----. 1 mysql mysql       56 Dec 31 14:05 auto.cnf
-rw-------. 1 mysql mysql     1676 Dec 31 14:05 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec 31 14:05 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Dec 31 14:05 client-cert.pem
-rw-------. 1 mysql mysql     1680 Dec 31 14:05 client-key.pem
drwxr-x---. 2 mysql mysql       54 Jan  2 14:39 dbdemo1
drwxr-x---. 2 mysql mysql       56 Jan  2 15:03 dbdemo2
-rw-r-----. 1 mysql mysql      562 Jan  2 14:55 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jan  2 15:03 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jan  2 15:03 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Dec 31 14:05 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jan  2 14:55 ibtmp1
drwxr-x---. 2 mysql mysql     4096 Dec 31 14:05 mysql
srwxrwxrwx. 1 mysql mysql        0 Jan  2 14:55 mysql.sock
-rw-------. 1 mysql mysql        6 Jan  2 14:55 mysql.sock.lock
drwxr-x---. 2 mysql mysql     8192 Dec 31 14:05 performance_schema
-rw-------. 1 mysql mysql     1676 Dec 31 14:05 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Dec 31 14:05 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Dec 31 14:05 server-cert.pem
-rw-------. 1 mysql mysql     1676 Dec 31 14:05 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Dec 31 14:05 sys
drwxr-x---. 2 mysql mysql     4096 Dec 31 15:21 testdb

這個數據目錄下的文件和子目錄比較多，除了 information_schema 這個系統數據庫外，其它的數據庫都在數據目錄下都有對應的子目錄。

以我的 testdb 數據庫為例，在MySQL5.7中打開：

# cd testdb
# ll
total 1236
-rw-r-----. 1 mysql mysql   8658 Dec 31 15:21 countries.frm
-rw-r-----. 1 mysql mysql 114688 Dec 31 15:21 countries.ibd
-rw-r-----. 1 mysql mysql     61 Dec 31 15:21 db.opt
-rw-r-----. 1 mysql mysql   8716 Dec 31 15:21 departments.frm
-rw-r-----. 1 mysql mysql 147456 Dec 31 15:21 departments.ibd
-rw-r-----. 1 mysql mysql   3033 Dec 31 15:21 emp_details_view.frm
-rw-r-----. 1 mysql mysql   8982 Dec 31 15:21 employees.frm
-rw-r-----. 1 mysql mysql 180224 Dec 31 15:21 employees.ibd
-rw-r-----. 1 mysql mysql   8660 Dec 31 15:21 job_grades.frm
-rw-r-----. 1 mysql mysql  98304 Dec 31 15:21 job_grades.ibd
-rw-r-----. 1 mysql mysql   8736 Dec 31 15:21 job_history.frm
-rw-r-----. 1 mysql mysql 147456 Dec 31 15:21 job_history.ibd
-rw-r-----. 1 mysql mysql   8688 Dec 31 15:21 jobs.frm
-rw-r-----. 1 mysql mysql 114688 Dec 31 15:21 jobs.ibd
-rw-r-----. 1 mysql mysql   8790 Dec 31 15:21 locations.frm
-rw-r-----. 1 mysql mysql 131072 Dec 31 15:21 locations.ibd
-rw-r-----. 1 mysql mysql   8610 Dec 31 15:21 order.frm
-rw-r-----. 1 mysql mysql  98304 Dec 31 15:21 order.ibd
-rw-r-----. 1 mysql mysql   8614 Dec 31 15:21 regions.frm
-rw-r-----. 1 mysql mysql 114688 Dec 31 15:21 regions.ibd

2.3. 表在文件系統中的表示

我們的數據其實都是以記錄的形式插入到表中的，每個表的信息其實可以分為兩種：

1. 表結構的定義

2. 表中的數據

表結構就是該表的名稱，表里邊有多少列，每個列的數據類型，約束條件和索引，使用的字符集和比較規則等各種信息，這些信息都體現在我們的建表語句中了。

2.3.1. InnoDB存儲引擎模式

1.表結構

為了保存表結構，InnoDB在數據目錄下對應的數據庫子目錄下創建了一個專門用于描述表結構的文件，文件名是這樣：

表名.frm

比如說我們在 testdb01 數據庫下創建一個名為 test的表：

mysql> use testdb01;
Database changed
mysql> create table test(c1 int);
Query OK, 0 rows affected (0.03 sec)

那在數據庫 testdb01對應的子目錄下就會創建一個名為 test.frm 的用于描述表結構的文件。.frm文件的格式在不同的平臺上都是相同的。這個后綴為.frm是以二進制格式存儲的，直接打開是亂碼的。

2.表中數據和索引

• InnoDB其實是使用頁為基本單位來管理存儲空間的，默認的頁大小為16KB。

• 對于InnoDB存儲引擎來說，每個索引都對應一顆B+樹，該B+樹都每個節點都是一個數據頁，數據頁之間不必要是物理連續的，因為數據頁之間有雙向鏈表來維護著這些頁的順序。

• InnoDB的聚簇索引的葉子節點存儲了完整的用戶記錄，也就是所謂的索引即數據，數據即索引。

為了更好的管理這些頁，InnoDB提出了一個表空間或者文件空間（英文名：table space或者file space）的概念，這個表空間是一個抽象的概念，它可以對應文件系統上一個或多個真實文件（不同表空間對應的文件數量可能不同）。每一個表空間可以被劃分為很多個頁，我么的表數據就存放在某個表空間下的某些頁里。這里表空間有幾種不同的類型：

① 系統表空間(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

比如：我們使用了獨立表空間去存儲 testdb01 數據庫下的 test 表的話，那么在該表所在數據庫對應的 testdb01 目錄下會為 test 表創建這兩個文件：

test.frm
test.ibd

其中 test.ibd 文件就用來存儲 test 表中的數據和索引。

③ 系統表空間與獨立表空間的設置

我們可以自己指定使用 系統表空間 還是 獨立表空間 來存儲數據，這個功能由啟動參數 innodb_file_per_table 控制，比如說我們想刻意將表數據都存儲到 系統表空間 時，可以在啟動 MySQL服務器的時候這樣配置：

[server]
innodb_file_per_table=0 # 0：代表使用系統表空間； 1：代表使用獨立表空間

默認情況：

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.01 sec)

說明：innodb_file_per_table參數到修改只對新建的表起作用，對于已經分配了表空間的表并不起作用。

如果我們想把已經保存在系統表空間中的表轉移到獨立表空間，可以使用下面的語法：

ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table;

或者把已經存在獨立表空間的表轉移到系統表空間，可以使用下面的語法：

ALTER TABLE 表名 TABLESPACE [=] innodb_system;

④ 其他類型的表空間

隨著MySQL的發展，除了上述兩種老牌表空間之外，現在還新提出了一些不同類型的表空間，比如通用 表空間（general tablespace）、臨時表空間（temporary tablespace）等。

3.疑問

.frm在MySQL8中不存在了，那去哪里了呢？

這就需要解析ibd文件，Oracle官方將frm文件的信息以及更多信息移動到叫做序列化字典信息（Serialized Dictionary Information，SDI）SDI被寫在ibd文件內部。MySQL8.0屬于Oracle旗下，同理。

為了從IBD文件中提取SDI信息，Oracle提供了一個應用程序ibd2sdi。

ibd2sdi官方文檔

這個工具不需要下載， MySQL8自帶的有，只要你配置好環境變量就能使用。

（1）查看表結構

到存儲ibd文件到目錄下，執行下面的命令：

ibd2sdi --dump-file=test.txt test.ibd

說明：--dump-file參數指定輸出的文件路徑。如這里輸出到test.txt中，輸出到文件內容為json格式，查看該文件部分內容如下：

圖中標記部分從上到下分別表示

• 表名

• 列

• 列名

• 列的長度

通過上面的測試結果可以發現，MySQL8把之前版本的frm文件合并到ibd文件中了。

2.3.2. MyISAM存儲引擎模式

1.表結構

在存儲表結構方面， MyISAM 和 InnoDB 一樣，也是在數據目錄下對應的數據庫子目錄下創建了一個專門用于描述表結構的文件：

表名.frm

2.表中數據和索引

在MyISAM中的索引全部都是 二級索引 ，該存儲引擎的 數據和索引是分開存放 的。所以在文件系統中也是使用不同的文件來存儲數據文件和索引文件，同時表數據都存放在對應的數據庫子目錄下。假如 test 表使用MyISAM存儲引擎的話，那么在它所在數據庫對應的 testdb01 目錄下會為 test 表創建這三個文件：

test.frm    存儲表結構
test.MYD    存儲數據（MYData）
test.MYI    存儲索引（MYIndex）

其中test.MYD代表表的數據文件，也就是我們插入的用戶記錄。采用獨立表存儲模式，每個表對應一個MYD文件；test.MYI代表表的索引文件，我們為該表創建的索引都會放到這個文件中。

舉例：創建一個MyISAM表，使用ENGINE選項顯著指定引擎。因為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;

在MySQL8版本中：

（1）進入數據庫目錄

包含三個文件：

student_myisam_404.sdi  -- 存儲元數據
student_myisam.MYD      -- 存儲數據
student_myisam.MYI      -- 存儲索引

對于InnoDB表，SDI與InnoDB用戶表空間中的數據一起存儲。對于MyISAM和其它存儲引擎，它被寫入數據目錄中的.sdi文件。

在MySQL 8.0中，MyISAM存儲引擎不提供分區支持。在以前版本的 MySQL 中創建的分區 MyISAM 表不能在MySQL8.0中使用。

在MySQL5.7版本中：

（1）進入數據庫目錄

包含三個文件：

student_myisam.frm      -- 存儲元數據
student_myisam.MYD      -- 存儲數據
student_myisam.MYI      -- 存儲索引

可以發現，在之前的數據庫版本中，MyISAM引擎已存在frm文件，但是在MySQL8之后也和InnoDB引擎一樣去掉了，放入了sdi文件中。

藍隊云官網上擁有完善的技術支持庫可供參考，大家可自行查閱，更多技術問題，可以直接咨詢。同時，藍隊云整理了運維必備的工具包免費分享給大家使用，需要的朋友可以直接咨詢。

更多技術知識，藍隊云期待與你一起探索。