共計 7284 個字符，預計需要花費 19 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

這篇文章主要介紹 MySQL 物理文件有哪些，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

1. 數據庫的數據存儲文件

MySQL 數據庫會在 data 目錄下面建立一個以數據庫為名的文件夾，用來存儲數據庫中的表文件數據。不同 的數據庫引擎，每個表的擴展名也不一樣，例如：MyISAM 用“.MYD”作為擴展名，Innodb 用“.ibd”，Archive 用“.arc”，CSV   用“.csv“。

1. .FRM 文件

8.0 之前無論是那種存儲引擎，創(chuàng)建表之后就一定會生成一個以表明命名的 .frm 文件。frm 文件主要存放與表相關的數據信息，主要包括表結構的定義信息。當數據庫崩潰時，用戶可以通過 frm 文件來恢復數據表結構。

2. .MYD 文件

“.MYD”文件是 MyISAM 存儲引擎專用，存放 MyISAM 表的數據。每一個 MyISAM 表都會有一個“.MYD”文件與 之對應，同樣存放于所屬數據庫的文件夾 下，和“.frm”文件在一起。

3. .MYI 文件

“.MYI”文件也是專屬于 MyISAM 存儲引擎的，主要存放 MyISAM 表的索引相關信息。對于 MyISAM 存儲來說，可以被 cache 的內容主要就是來源 于“.MYI”文件中。每一個 MyISAM 表對應一個“.MYI”文件，存放于位置 和“.frm”以及“.MYD”一樣。

4. .ibd 文件與 .ibdata 文件

這兩種文件都是存放 Innodb 數據的文件，之所以有兩種文件來存放 Innodb 的數據（包括索引），是因為 Innodb 的數據存儲方式能夠通過配置來決定是使用共享表空間存放存儲數據，還是獨享表空間存放存儲數據。獨享表空間存儲方式使用“.ibd”文件來存放數據，且每個表一個“.ibd”文件，文件存放在和 MyISAM 數據相同的位置。如果選用共享存儲表空間來存放數據，則會使用 ibdata 文件來存放，所有表共同使用一個（或者多個，可自行配置）ibdata 文件。

ibdata 文件可以通過 innodb_data_home_dir（數據存放目錄）和 innodb_data_file_path (配置每個文件的名稱) 兩個參數配置組成 innodb_data_file_path 中可以一次配置多個 ibdata 文件 #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend 配置方式共享表空間以及獨占表空間都是針對數據的存儲方式而言的。

共享表空間: 某一個數據庫的所有的表數據，索引文件全部放在一個文件中。

獨占表空間: 每一個表都將會生成以獨立的文件方式來進行存儲，每一個表都有一個.frm 表描述文件，還有 一個.ibd 文件。其中這個文件包括了 單獨一個表的數據 內容以及索引內容。

4.1 兩者對比

共享表空間：

優(yōu)點：可以放表空間分成多個文件存放到各個磁盤上。數據和文件放在一起方便管理。

缺點：所有的數據和索引存放到一個文件中，多個表及索引在表空間中混合存儲，這樣對于一個表做了大量刪除操作后表空間中將會有大量的空隙，特別是對于統(tǒng)計分析，日志系統(tǒng)這類應用最不適合用共享表空間。

獨立表空間：

優(yōu)點:

每個表都有自已獨立的表空間。

每個表的數據和索引都會存在自已的表空間中。

可以實現單表在不同的數據庫中移動。

空間可以回收

a) Drop table 操作自動回收表空間，如果對于統(tǒng)計分析或是日值表，刪除大量數據后可以通過: alter table TableName engine=innodb；回收不用的空間。

b) 對于使用獨立表空間的表，不管怎么刪除，表空間的碎片不會太嚴重的影響性能，而且還有機會處理。缺點：單表增加過大，如超過 100 G。相比較之下，使用獨占表空間的效率以及性能會更高一點 共享表空間和獨立表空間之間的轉換。

show variables like innodb_file_per_table ON 代表獨立表空間管理，OFF 代表共享表空間管理；修改數據庫的表空間管理方式 修改 innodb_file_per_table 的參數值即可，但是修改不能影響之前已經
使用過的共享表空間和獨立表空間；innodb_file_per_table=1 為使用獨占表空間
innodb_file_per_table=0 為使用共享表空間復制代碼 

2. 日志

日志文件：查詢日志、慢查詢日志、錯誤日志、事務日志、binlog 日志、錯誤日志、中繼日志

2.1 查詢日志

查詢日志在 mysql 中被稱之為 general log（通用日志），不要被 查詢日志 的名字誤導，錯誤的以為查詢日志只會記錄 select 語句，其實不然，查詢日志記錄了數據庫執(zhí)行的命令，不管這些語句是否正確，都會被記錄，我想這也是 general log 之所以 通用 的原因吧，由于數據庫操作命令有可能非常多而且執(zhí)行比較頻繁，所以當開啟了查詢日志以后，數據庫可能需要不停的寫入查詢日志，這樣會增大服務器的 IO 壓力，增加很多系統(tǒng)開銷，所以默認情況下，mysql 的查詢日志是沒有開啟的，但是開啟查詢日志也有助于我們分析哪些語句執(zhí)行密集，執(zhí)行密集的 select 語句對應的數據是否能夠被緩存，查詢日志也可以幫助我們分析問題，所以，我們可以根據實際情況決定是否開啟查詢日志，如果需要可以手動開啟。如果開啟了查詢日志，那么我們可以通過如下 3 種方式存儲查詢日志。

方式 1：將查詢日志存放于指定的日志文件中。

方式 2：將查詢日志存放于 mysql.general_log 表中。

方式 3：將查詢日志同時存放于指定的日志文件與 mysql 庫的 general_log 表中。

查看查詢日志是否開啟

show VARIABLES LIKE general_log 復制代碼 

show variables where variable_name like %general_log% or variable_name= log_output 復制代碼 

general_log: 表示查詢日志是否開啟，ON 表示開啟，OFF 表示未開啟，默認為 OFF

log_output: 表示當查詢日志開啟以后，以哪種方式存放，log_output 可以設置為 4 種值，FILE、TABLE、FILE,TABLE、NONE。

# 設置查詢日志的輸出方式
set global log_output=[none|file|table|file,table];
# 設置 general log 的日志文件路徑
set global general_log_file= /tmp/general.log 
# 開啟 general log
set global general_log=on;
# 關閉 general log
set global general_log=off; 復制代碼 

2.2 慢日志

所謂的慢查詢就是通過設置來記錄超過一定時間的 SQL 語句！

開啟 MySQL 的慢查詢日志功能

# 查看是否開啟 未使用索引的 SQL 記錄日志查詢
show variables like log_queries_not_using_indexes 
# 開啟 未使用索引的 SQL 記錄日志查詢
set global log_queries_not_using_indexs=on/off;
# 查看超過多長時間的查詢記入慢查詢日志中
show variables like long_query_time 
# 設置記錄時長，0 為全部記錄，設置之后需重新啟動
set global long_query_time=10
# 查看是否開啟 mysql 慢查詢日志功能
show variables like slow_qurey_log 
# 開啟、關閉慢日志
set global slow_qurey_log=on/off;
# 查看日志記錄位置
show variables like slow_query_log_file 
#日志存儲方式
show variables like log_output 復制代碼 

flie 方式

select sleep(10) 執(zhí)行完成查看日志

# Time: 2020-10-26T05:12:09.564006Z
# User@Host: root[root] @ localhost [] Id: 12
# Query_time: 10.000272 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1603689119;
select sleep(10); 復制代碼 

table 方式

日志分析工具 mysqldumpslow

查看 mysqldumpslow 的幫助信息：

-s ORDER ORDER 排序依據（al，at，ar，c，l，r，t），“at”是默認值
 al: 平均鎖定時間
 ar: 平均發(fā)送行數
 at: 平均查詢時間
 c: 計數
 l: 鎖定時間
 r: 已發(fā)送行
 t: 查詢時間 
 -r 反轉排序順序（最大的最后一個而不是第一個）-t NUM 只顯示前 n 個查詢
 -a 不要將所有數字抽象為 N，將字符串抽象為“S”-n NUM 名字中至少有 n 個數字的抽象數字
 -g PATTERN grep: 只考慮包含此字符串的記錄
 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
 default is * , i.e. match all
 -i NAME name of server instance (if using mysql.server startup script)
 -l don t subtract lock time from total time 復制代碼 

2.3 錯誤日志

錯誤日志（Error Log）是 MySQL 中最常用的一種日志，主要記錄 MySQL 服務器啟動和停止過程中的信 息、服務器在運行過程中發(fā)生的故障和異常情況等。

# 查看錯誤日志記錄位置
show variables like log_error 
# 在 MySQL 中，可以使用 mysqladmin 命令來開啟新的錯誤日志，以保證 MySQL 服務器上的硬盤空間。# mysqladmin 命令的語法如下：mysqladmin -uroot -p flush-logs
# 執(zhí)行該命令后，MySQL 服務器首先會自動創(chuàng)建一個新的錯誤日志，然后將舊的錯誤日志更名為 filename.err-old。可以手動直接刪除。#配置文件中配置
[mysqld]
log-error=dir/{filename} 復制代碼 

2.4 二進制日志

二進制日志（Binary Log）也可叫作變更日志（Update Log），是 MySQL 中非常重要的日志。主要用于記錄數據庫的變化情況，即 SQL 語句的 DDL 和 DML 語句，不包含數據記錄查詢操作。

# 查看 binary log 日志是否開啟，binary log 日志默認關閉
show variables like log_bin 
# 在 MySQL 中可以再配置文件中開啟二進制文件日志
[mysqld]
log-bin=dir/{filename} 復制代碼 

其中，dir 參數指定二進制文件的存儲路徑；filename 參數指定二進制文件的文件名，其形式為 filename.number，number 的形式為 000001、000002 等，每次重啟 MySQL 服務后，都會生成一個新的二進制日志文件，這些日志文件的文件名中 filename 部分不會改變，number 會不斷遞增。

二進制日志的格式有三種：STATEMENT，ROW，MIXED。

① STATEMENT 模式（SBR）

 每一條會修改數據的 sql 語句會記錄到 binlog 中。優(yōu)點是并不需要記錄每一條 sql 語句和每一行的數據變化，減少了 binlog 日志量，節(jié)約 IO，提高性能。缺點是在某些情況下會導致 master-slave 中的數據不一致 ( 如
 sleep() 函數，last_insert_id()，以及 user-defined functions(udf) 等會出現問題 ) 復制代碼 

② ROW 模式（RBR）

 不記錄每條 sql 語句的上下文信息，僅需記錄哪條數據被修改了，修改成什么樣了。而且不會出現某些特定情況下
的存儲過程、或 function、或 trigger 的調用和觸發(fā)無法被正確復制的問題。缺點是會產生大量的日志，尤其是
alter table 的時候會讓日志暴漲。復制代碼 

③ MIXED 模式（MBR）

 以上兩種模式的混合使用，一般的復制使用 STATEMENT 模式保存 binlog，對于 STATEMENT 模式無法復制的操作使用
 ROW 模式保存 binlog，MySQL 會根據執(zhí)行的 SQL 語句選擇日志保存方式。復制代碼 

binlog 復制配置

在 mysql 的配置文件 my.cnf 或中，可以通過一下選項配置 binary log

binlog_format = MIXED //binlog 日志格式，mysql 默認采用 statement，建議使用 mixed 
log-bin = mysql-bin //binlog 日志文件 
expire_logs_days = 7 //binlog 過期清理時間 
max_binlog_size = 100m //binlog 每個日志文件大小 
binlog_cache_size = 4m //binlog 緩存大小 
max_binlog_cache_size = 512m // 最大 binlog 緩存大小
server-id = 1 復制代碼 

2.5 二進制文件基本操作

可以使用如下命令查看 MySQL 中有哪些二進制日志文件：show binary logs

show master status 命令用來查看當前的二進制日志；

二進制日志使用二進制格式存儲，不能直接打開查看。如果需要查看二進制日志，使用 show binlog events in mysql-bin.000001 命令。

刪除二進制文件

使用 RESET MASTER 語句可以刪除的所有二進制日志每個二進制日志文件后面有一個 6 位數的編號，如 000001。使用 PURGE MASTER LOGS TO filename.number 語句，可以刪除指定二進制日志的編號之前的日志使用 PURGE MASTER LOGS TO yyyy-mm-dd hh:MM:ss 語句，可以刪除指定時間之前創(chuàng)建的二進制日志

使用二進制文件恢復數據

創(chuàng)建數據庫

CREATE TABLE `33hao_activity` (`activity_id` mediumint(9) NOT NULL AUTO_INCREMENT COMMENT id ,
 `activity_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 標題 ,
 `activity_type` enum(1 , 2) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT 活動類型 1: 商品 2: 團購 ,
 `activity_banner` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 活動橫幅大圖片 ,
 `activity_style` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 活動頁面模板樣式標識碼 ,
 `activity_desc` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 描述 ,
 `activity_start_date` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 開始時間 ,
 `activity_end_date` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 結束時間 ,
 `activity_sort` tinyint(1) UNSIGNED NOT NULL DEFAULT 255 COMMENT 排序 ,
 `activity_state` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT 活動狀態(tài) 0 為關閉 1 為開啟 ,
 PRIMARY KEY (`activity_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 活動表 ROW_FORMAT = Compact; 復制代碼 

新增 2 條數據

INSERT INTO `33hao_activity` VALUES (1, 2017 年跨年滿即送活動 , 1 , 05364373801675235.jpg , default_style , , 1483113600, 1483286400, 0, 0);
INSERT INTO `33hao_activity` VALUES (2, 轉盤抽獎彈窗 , 1 , 06480453986921327.jpg , , 轉盤抽獎彈窗 , 1594656000, 1594915200, 0, 0); 復制代碼 

刪除數據

drop table `33hao_activity`;

恢復數據

根據節(jié)點需要我們查看日志文件提供給我們的數據庫創(chuàng)建，表創(chuàng)建，數據新增等時創(chuàng)建的語句節(jié)點，從而恢復數據。

mysqlbinlog --start-position=154 --stop-position=2062 D:/phpstudy_pro/Extensions/MySQL5.7.26/data/mysql-bin.000001 | mysql -uroot - p 復制代碼 

根據時間恢復數據

mysqlbinlog --start-datetime= 2020-09-27 22:22:22 --stop-datetime= 2020-09-27 22:30:00 /www/server/data/mysql-bin.000036 | mysql -uroot - p 復制代碼 

直接執(zhí)行 binlog 日志

mysqlbinlog /www/server/data/mysql-bin.000036 | mysql -uroot -p

以上是 MySQL 物理文件有哪些的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注丸趣 TV 行業(yè)資訊頻道！

向 AI 問一下細節(jié)

丸趣 TV 網 – 提供最優(yōu)質的資源集合！