MySQL-02.MySQL的資料目錄和表檔案解析

长名06發表於2024-03-11

C-02.MySQL的資料目錄和表檔案解析

1.MySQL8的主要目錄結構

[root@LinuxCentOS7-132 dbtest1]# find / -name mysql
/etc/logrotate.d/mysql
/etc/selinux/targeted/active/modules/100/mysql
/etc/selinux/targeted/tmp/modules/100/mysql
/usr/bin/mysql
/usr/lib64/mysql
/usr/share/mysql
/usr/local/maven-repo/mysql
/var/lib/mysql
/var/lib/mysql/mysql

1.1 資料庫檔案的存放路徑

MySQL資料庫檔案的存放路徑 : /var/lib/mysql

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

從結果中可以看出，在我的計算機上MySQL的資料目錄就是/var/lib/mysql/。

1.2 MySQL相關命令目錄

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

1.3 配置檔案目錄

配置檔案目錄：/usr/share/mysql-8.0(命令及配置檔案)，/etc/mysql(如my.cnf)。

2.資料庫和檔案管理系統的關係

2.1 系統預設資料庫介紹

檢視當前DBMS的所有資料庫

mysql> SHOW DATABASES;

MySQL自帶的資料庫有4個

mysql： MySQL 系統自帶的核心資料庫，它儲存了MySQL的使用者賬戶和許可權資訊，一些儲存過程、事件的定義資訊，一些執行過程中產生的日誌資訊，一些幫助資訊以及時區資訊等。
information_schema：MySQL 系統自帶的資料庫，這個資料庫儲存著MySQL伺服器維護的所有其他資料庫的資訊，比如有哪些表、哪些檢視、哪些觸發器、哪些列、哪些索引。這些資訊並不是真實的使用者資料，而是一些描述性資訊，有時候也稱之為後設資料。在系統資料庫 information_schema中提供了一些以innodb_sys 開頭的表，用於表示內部系統表。
performance_schema：MySQL 系統自帶的資料庫，這個資料庫裡主要儲存MySQL伺服器執行過程中的一些狀態資訊，可以

用來監控 MySQL 服務的各類效能指標。包括統計最近執行了哪些語句，在執行過程的每個階段都花費了多長時間，記憶體的使用情況等資訊。
sys：MySQL 系統自帶的資料庫，這個資料庫主要是透過檢視的形式把information_schema和performance_schema結合起來，幫助系統管理員和開發人員監控 MySQL 的技術效能。

2.2 MySQL資料目錄下的內容

資料目錄，也就是MySQL資料庫檔案的存放路徑。

看一下MySQL8.0資料目錄下的內容

[root@LinuxCentOS7-132 mysql-8.0]# cd /var/lib/mysql
[root@LinuxCentOS7-132 mysql]# ll
總用量 191964
-rw-r-----. 1 mysql mysql       56 3月   6 22:13 auto.cnf
-rw-r-----. 1 mysql mysql     1456 3月   6 23:02 binlog.000001
-rw-r-----. 1 mysql mysql      179 3月   7 11:09 binlog.000002
-rw-r-----. 1 mysql mysql      179 3月   7 17:39 binlog.000003
-rw-r-----. 1 mysql mysql      179 3月   7 23:07 binlog.000004
-rw-r-----. 1 mysql mysql      852 3月  10 21:54 binlog.000005
-rw-r-----. 1 mysql mysql       80 3月  10 21:27 binlog.index
-rw-------. 1 mysql mysql     1676 3月   6 22:13 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 3月   6 22:13 ca.pem
-rw-r--r--. 1 mysql mysql     1112 3月   6 22:13 client-cert.pem
-rw-------. 1 mysql mysql     1680 3月   6 22:13 client-key.pem
drwxr-x---. 2 mysql mysql     4096 3月  10 22:25 dbtest1
-rw-r-----. 1 mysql mysql   196608 3月  10 21:56 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql  8585216 3月   6 22:13 #ib_16384_1.dblwr
-rw-r-----. 1 mysql mysql     3428 3月   7 23:07 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 3月  10 21:54 ibdata1
-rw-r-----. 1 mysql mysql 50331648 3月  10 21:56 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 3月   6 22:13 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 3月  10 21:27 ibtmp1
drwxr-x---. 2 mysql mysql     4096 3月  10 21:27 #innodb_temp
drwxr-x---. 2 mysql mysql     4096 3月   6 22:13 mysql
-rw-r-----. 1 mysql mysql 28311552 3月  10 21:54 mysql.ibd
srwxrwxrwx. 1 mysql mysql        0 3月  10 21:27 mysql.sock
-rw-------. 1 mysql mysql        5 3月  10 21:27 mysql.sock.lock
drwxr-x---. 2 mysql mysql     4096 3月   6 22:13 performance_schema
-rw-------. 1 mysql mysql     1680 3月   6 22:13 private_key.pem
-rw-r--r--. 1 mysql mysql      452 3月   6 22:13 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 3月   6 22:13 server-cert.pem
-rw-------. 1 mysql mysql     1680 3月   6 22:13 server-key.pem
drwxr-x---. 2 mysql mysql     4096 3月   6 22:13 sys
-rw-r-----. 1 mysql mysql 16777216 3月  10 21:54 undo_001
-rw-r-----. 1 mysql mysql 16777216 3月  10 21:56 undo_002

看一下MySQL5.7.26資料目錄下的內容

[root@LinuxCentOS7-131 mysql]# cd /var/lib/mysql
[root@LinuxCentOS7-131 mysql]# ll
總用量 188532
-rw-r-----. 1 mysql mysql       56 11月 15 2022 auto.cnf
-rw-------. 1 mysql mysql     1679 11月 15 2022 ca-key.pem
-rw-r--r--. 1 mysql mysql     1107 11月 15 2022 ca.pem
-rw-r--r--. 1 mysql mysql     1107 11月 15 2022 client-cert.pem
-rw-------. 1 mysql mysql     1679 11月 15 2022 client-key.pem
-rw-r-----. 1 mysql mysql      481 3月   7 17:39 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 3月  10 21:40 ibdata1
-rw-r-----. 1 mysql mysql 50331648 3月  10 21:40 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 11月 15 2022 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 3月  10 21:40 ibtmp1
-rw-r-----. 1 mysql mysql      177 3月   6 23:03 LinuxCentOS7-131-relay-bin.000001
-rw-r-----. 1 mysql mysql      177 3月   7 09:46 LinuxCentOS7-131-relay-bin.000002
-rw-r-----. 1 mysql mysql      177 3月   7 14:04 LinuxCentOS7-131-relay-bin.000003
-rw-r-----. 1 mysql mysql      177 3月  10 21:40 LinuxCentOS7-131-relay-bin.000004
-rw-r-----. 1 mysql mysql      144 3月  10 21:40 LinuxCentOS7-131-relay-bin.index
-rw-r-----. 1 mysql mysql      139 3月  10 21:40 master.info
drwxr-x---. 2 mysql mysql     4096 11月 15 2022 mysql
srwxrwxrwx. 1 mysql mysql        0 3月  10 21:40 mysql.sock
-rw-------. 1 mysql mysql        5 3月  10 21:40 mysql.sock.lock
drwxr-x---. 2 mysql mysql     4096 2月  29 09:40 nacos_config
drwxr-x---. 2 mysql mysql     4096 11月 15 2022 performance_schema
-rw-------. 1 mysql mysql     1679 11月 15 2022 private_key.pem
-rw-r--r--. 1 mysql mysql      451 11月 15 2022 public_key.pem
drwxr-x---. 2 mysql mysql     4096 11月 19 2022 reggie
-rw-r-----. 1 mysql mysql       75 3月   6 23:02 relay-log.info
drwxr-x---. 2 mysql mysql     4096 11月 19 2022 rw
-rw-r--r--. 1 mysql mysql     1107 11月 15 2022 server-cert.pem
-rw-------. 1 mysql mysql     1675 11月 15 2022 server-key.pem
-rw-r-----. 1 mysql mysql      177 3月   6 23:02 SlaveStudyCentOs7-relay-bin.000140
-rw-r-----. 1 mysql mysql       37 3月   6 22:47 SlaveStudyCentOs7-relay-bin.index
drwxr-x---. 2 mysql mysql    12288 11月 15 2022 sys
drwxr-x---. 2 mysql mysql     4096 11月 18 2022 wind

總結：這個資料目錄下的檔案和子目錄比較多，除了information_schema這個系統資料庫外，其他的資料庫在資料目錄下都有對應的子目錄。

2.3 某個資料庫目錄下的內容

2.3.1 mysql 8.0 檢視某個資料庫在檔案系統中的結構

InnoDB儲存引擎下

[root@LinuxCentOS7-132 mysql]# cd ./dbtest1;
[root@LinuxCentOS7-132 dbtest1]# ll
總用量 80
-rw-r-----. 1 mysql mysql 114688 3月  10 21:54 t.ibd

dbtest1資料庫下，只建立了一張t表

mysql> use dbtest1;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| t                 |
+-------------------+
1 row in set (0.00 sec)

MyISAM儲存引擎下

在mysql 8.0下，建立一個dbtest2資料庫，新建一張MyIASM儲存引擎的表。用於檢視錶對於的檔案結構。

mysql> create database dbtest2
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> use dbtest2;
Database changed
mysql> 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;
Query OK, 0 rows affected, 1 warning (0.01 sec)

檢視dbtest2目錄下檔案

[root@LinuxCentOS7-132 mysql]# cd dbtest2;
[root@LinuxCentOS7-132 dbtest2]# ll
總用量 12
-rw-r-----. 1 mysql mysql 4330 3月  10 23:43 student_myisam_362.sdi
-rw-r-----. 1 mysql mysql    0 3月  10 23:43 student_myisam.MYD
-rw-r-----. 1 mysql mysql 1024 3月  10 23:43 student_myisam.MYI

2.3.2 mysql 5.7 檢視某個資料庫在檔案系統中的結構

InnoDB儲存儲存引擎下

[root@LinuxCentOS7-131 mysql]# cd wind
[root@LinuxCentOS7-131 wind]# ll
總用量 112
-rw-r-----. 1 mysql mysql    65 11月 18 2022 db.opt
-rw-r-----. 1 mysql mysql  8586 11月 18 2022 user.frm
-rw-r-----. 1 mysql mysql 98304 11月 19 2022 user.ibd

.opt檔案，存放的是當前資料庫的資訊，使用的字符集，比較規則等。

wind資料庫下，只建立了一張user表

mysql> use wind;
Database changed
mysql> show tables;
+----------------+
| Tables_in_wind |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

MyISAM儲存引擎下

在mysql 5.7下，建立一個dbtest2資料庫，新建一張MyIASM儲存引擎的表。用於檢視錶對於的檔案結構。

mysql> create database rw2;
Query OK, 1 row affected (0.00 sec)

mysql> use rw2;
Database changed
mysql> 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;
Query OK, 0 rows affected (0.00 sec)

檢視rw2目錄下檔案

[root@LinuxCentOS7-131 mysql]# cd rw2
[root@LinuxCentOS7-131 rw2]# ll
總用量 20
-rw-r-----. 1 mysql mysql   61 3月  10 23:45 db.opt
-rw-r-----. 1 mysql mysql 8642 3月  10 23:46 student_myisam.frm
-rw-r-----. 1 mysql mysql    0 3月  10 23:46 student_myisam.MYD
-rw-r-----. 1 mysql mysql 1024 3月  10 23:46 student_myisam.MYI

2.4 表在檔案系統中的表示

2.4.1 Innodb儲存引擎模式

1.表結構

為了儲存表結構， InnoDB 在資料目錄下對應的資料庫子目錄下建立了一個專門用於描述表結構的檔案，檔名是這樣：表名.frm

例如：2.3.2 中wind資料庫下的user.frm。

.frm檔案的格式在不同的平臺上都是相同的。這個字尾名為.frm是以二進位制格式儲存的，我們直接開啟是亂碼的。

2.表資料和索引

1.系統表空間(system tablespace)

預設情況下，InnoDB會在資料目錄下建立一個名為ibdata1、大小為12M的檔案，這個檔案就是對應的系統表空間在檔案系統上的表示。怎麼才12M？注意這個檔案是自擴充套件檔案，當不夠用的時候它會自己增加檔案大小。

當然，如果你想讓系統表空間對應檔案系統上多個實際檔案，或者僅僅覺得原來的 ibdata1 這個檔名難聽，那可以在MySQL啟動時配置對應的檔案路徑以及它們的大小，比如我們這樣修改一下my.cnf 配置檔案：

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

2.獨立表空間(file-per-table tablespace)

在MySQL5.6.6以及之後的版本中，InnoDB並不會預設的把各個表的資料儲存到系統表空間中，而是為每一個表建立一個獨立表空間，也就是說我們建立了多少個表，就有多少個獨立表空間。使用獨立表空間來儲存表資料的話，會在該表所屬資料庫對應的子目錄下建立一個表示該獨立表空間的檔案，檔名和表名相同，只不過新增了一個.ibd的副檔名而已，所以完整的檔名稱長這樣：表名.ibd

例如，2.3.2 wind資料庫下的user.ibd檔案。

user.ibd檔案就用來儲存user表中的資料和索引。

3.系統表空間和獨立表空間的設定

我們可以自己指定使用系統表空間 還是獨立表空間來儲存資料，這個功能由啟動引數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.00 sec)

4.其他型別的表空間

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

3.MySQL8中的修改

注意在2.3.1中dbtest1資料庫目錄中，對於t表只有一個.ibd檔案。

.opt檔案和.frm檔案都不存在了，這是因為在mysql8.0後，Oracle官方將frm檔案的資訊以及更多的資訊移動到叫做序列化字典資訊(Serialized Dictionary Information，SDI)，SDI被寫在了ibd檔案內部。一張表對於一個.ibd檔案。

Oracle提供了一個ibd2sdi指令

這個工具不需要下載，MySQL8自帶

檢視錶結構

ibd2sdi --dump-file=t.txt t.ibd ，使用時，t替換為表名

[root@LinuxCentOS7-132 dbtest1]# ll
總用量 80
-rw-r-----. 1 mysql mysql 114688 3月  10 21:54 t.ibd
[root@LinuxCentOS7-132 dbtest1]# ibd2sdi --dump-file=t.txt t.ibd
[root@LinuxCentOS7-132 dbtest1]# ll
總用量 92
-rw-r-----. 1 mysql mysql 114688 3月  10 21:54 t.ibd
-rw-------. 1 root  root   11100 3月  10 23:35 t.txt

2.4.2 MyISAM儲存引擎模式

1.表結構

在儲存表結構方面，MyISAM和InnoDB一樣，也是在資料目錄下對應的資料庫子目錄下建立了一個專門用於描述表結構的檔案：

表名.frm

2.表中資料和索引

在MyISAM中的索引全部都是二級索引，該儲存引擎的資料和索引是分開存放的。所以在檔案系統中也是使用不同的檔案來儲存資料檔案和索引檔案，同時表資料都存放在對應的資料庫子目錄下。假如student_myisam表使用MyISAM儲存引擎的話，那麼在它所在資料庫對應的rw2目錄下會為student_myisam表建立這三個檔案：

test.frm 儲存表結構
test.MYD 儲存資料 (MYData)
test.MYI 儲存索引 (MYIndex)

3.MySQL8中的修改

在mysql8中，對於儲存表結構的frm和資料庫的opt檔案都寫入到了表的sdi檔案中。

2.5 小結

舉例：資料庫a，表b。

1、如果表b採用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檔案中。

2、如果表b採用MyISAM，data\a中會產生3個檔案：

MySQL5.7 中：b.frm：描述表結構檔案，欄位長度等。MySQL8.0 中b.xxx.sdi：描述表結構檔案，欄位長度等
b.MYD(MYData)：資料資訊檔案，儲存資料資訊(如果採用獨立表儲存模式)
b.MYI(MYIndex)：存放索引資訊檔案

2.6 檢視在檔案系統中的表示

我們知道MySQL中的檢視其實是的表，也就是某個查詢語句的一個別名而已，所以在儲存檢視的時候是不需要儲存真實的資料的，只需要把它的結構儲存起來就行了。和表一樣，描述檢視結構的檔案也會被儲存到所屬資料庫對應的子目錄下邊，只會儲存一個檢視名.frm的檔案。

在5.7下是這樣的

在rw資料庫中建立一個檢視，然後檢視rw目錄檔案

mysql> use rw;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------+
| Tables_in_rw |
+--------------+
| user         |
+--------------+
1 row in set (0.01 sec)

mysql> select * from user;
Empty set (0.01 sec)

mysql> create view user_view as select * from user;
Query OK, 0 rows affected (0.00 sec)

[root@LinuxCentOS7-131 mysql]# cd rw;
[root@LinuxCentOS7-131 rw]# ll
總用量 116
-rw-r-----. 1 mysql mysql    65 11月 19 2022 db.opt
-rw-r-----. 1 mysql mysql  8650 11月 19 2022 user.frm
-rw-r-----. 1 mysql mysql 98304 11月 19 2022 user.ibd
-rw-r-----. 1 mysql mysql   560 3月  10 23:56 user_view.frm

MySQL8.0下

未生存檢視名.frm檔案

mysql> use dbtest1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_dbtest1 |
+-------------------+
| t                 |
+-------------------+
1 row in set (0.00 sec)

mysql> create view t_view as select * from t;
Query OK, 0 rows affected (0.00 sec)

[root@LinuxCentOS7-132 dbtest1]# ll
總用量 92
-rw-r-----. 1 mysql mysql 114688 3月  10 21:54 t.ibd
-rw-------. 1 root  root   11100 3月  10 23:35 t.txt

2.7 其他檔案

除了我們上邊說的這些使用者自己儲存的資料以外，資料目錄下還包括為了更好執行程式的一些額外檔案，主要包括這幾種型別的檔案

伺服器程序檔案

我們知道每執行一個 MYSQL伺服器程式,都意味著啟動一個程序。MSQL伺服器會把自己的程序ID寫入到一個檔案中。

伺服器日誌檔案

在伺服器執行過程中，會產生各種各樣的日誌,比如常規的查詢日誌、錯誤日誌、二進位制日誌、redo日誌等。這些日誌各有各的用途，後面講解。

預設/自動生成的SSL和RSA證書和金鑰檔案

主要是為了客戶端和伺服器安全通訊而建立的一些檔案。

只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。

Java XML檔案解析書目錄
2017-06-08
JavaXML
mysql資料目錄下的常見檔案
2013-03-04
MySql
更改MySQL資料檔案存放目錄位置
2016-04-01
MySql
8.var目錄下的檔案和目錄詳解
2020-04-04
Linux 只複製目錄，不復制目錄下的資料檔案
2019-12-03
Linux
PHP遍歷目錄和檔案
2011-12-21
PHP
Linux檔案和目錄管理
2024-04-17
Linux
資料檔案丟失的恢復（改變目錄）
2014-08-26
如何統計 Linux 中檔案和資料夾/目錄的數量
2018-04-01
Linux
SQLServeronLinux的檔案和目錄結構
2016-12-26
SQLServerLinux
Linux檔案系統-目錄和檔案管理
2018-02-02
Linux
將ORACLE資料庫資料檔案遷移到其他目錄(ZT)
2010-04-24
Oracle資料庫
沒有目錄建目錄，沒有檔案建檔案
2014-05-28
.Net引用根目錄子資料夾下的dll檔案
2023-01-03
Tomcat目錄和檔案講解
2017-11-26
Tomcat
Linux的檔案屬性和目錄配置
2024-04-11
Linux
表空間和資料檔案的管理
2018-10-03
表空間&資料檔案和控制檔案(zt)
2007-06-23
解析Linux系統根檔案系統的目錄樹
2006-10-09
Linux
dir 顯示目錄檔案和子目錄列表(轉)
2007-08-16
yii框架，掃描目錄下檔案入資料庫
2019-02-16
框架資料庫
QT選擇目錄等常用檔案/資料夾操作
2017-01-09
QT
如何對報表資料新增目錄
2019-11-21
無緩衝檔案IO和目錄操作
2022-01-22
【ZIP】打包過濾指定目錄和檔案
2018-12-02
LINUXdu檢視目錄和檔案大小
2018-02-24
Linux
在 Linux 中如何歸檔檔案和目錄
2021-09-09
Linux
表空間和資料檔案管理
2014-04-26
約束前端專案中的目錄和檔名
2022-04-13
前端
c++ Builder 關於檔案和目錄的操作
2009-04-24
C++UI
18、檔案與目錄
2018-01-01
清理bdump目錄檔案
2012-04-16
解析Linux系統根檔案系統的目錄樹(轉)
2007-08-15
Linux
利用拷貝data目錄檔案的方式遷移mysql資料庫
2017-12-05
MySql資料庫
關於檔案系統在建立目錄檔案和普通檔案時的區別
2024-04-24
一個自動遞增生成目錄和檔案的cop檔案類
2013-08-19
資料庫映象和全文目錄
2008-11-13
資料庫
adb命令管理Linux 檔案和目錄
2018-08-27
Linux