重慶分公司
重慶分公司，新征程啟航
為企業提供網站建設、域名注冊、服務器等服務
認識MySQL物理文件
mysql教程欄目介紹MySQL物理文件。
MySQL 數據庫會在data目錄下面建立一個以數據庫為名的文件夾，用來存儲數據庫中的表文件數據。不同 的數據庫引擎，每個表的擴展名也不一樣 ，例如： MyISAM 用“ .MYD ”作為擴展名， Innodb 用 “.ibd” ， Archive 用 “.arc” ，CSV 用 “.csv“。
1. ".FRM"文件8.0之前無論是那種存儲引擎，創建表之后就一定會生成一個以表明命名的'.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 兩者對比共享表空間：
優點： 可以放表空間分成多個文件存放到各個磁盤上。數據和文件放在一起方便管理。
缺點： 所有的數據和索引存放到一個文件中，多個表及索引在表空間中混合存儲，這樣對于一個表做了大量刪除操作后表空間中將會有大量的空隙，特別是對于統計分析，日志系統這類應用最不適合用共享表空間。
獨立表空間：
優點:
每個表都有自已獨立的表空間。
每個表的數據和索引都會存在自已的表空間中。
可以實現單表在不同的數據庫中移動。
空間可以回收
a) Drop table操作自動回收表空間，如果對于統計分析或是日值表，刪除大量數據后可以通過: 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. 日志
2.1 查詢日志日志文件：查詢日志、慢查詢日志、錯誤日志、事務日志、binlog日志、錯誤日志、中繼日志
查詢日志在mysql中被稱之為 general log（通用日志），不要被"查詢日志"的名字誤導，錯誤的以為查詢日志只會記錄select語句，其實不然，查詢日志記錄了數據庫執行的命令，不管這些語句是否正確，都會被記錄，我想這也是 general log之所以"通用"的原因吧，由于數據庫操作命令有可能非常多而且執行比較頻繁，所以當開啟了查詢日志以后，數據庫可能需要不停的寫入查詢日志，這樣會增大服務器的IO壓力，增加很多系統開銷，所以默認情況下，mysql的查詢日志是沒有開啟的，但是開啟查詢日志也有助于我們分析哪些語句執行密集，執行密集的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)執行完成查看日志
# 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: 平均發送行數
at: 平均查詢時間
c: 計數
l: 鎖定時間
r: 已發送行
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 服務器啟動和停止過程中的信 息、服務器在運行過程中發生的故障和異常情況等。
# 查看錯誤日志記錄位置
show variables like "log_error";
# 在 MySQL 中，可以使用 mysqladmin 命令來開啟新的錯誤日志，以保證 MySQL 服務器上的硬盤空間。
# mysqladmin 命令的語法如下：
mysqladmin -uroot -p flush-logs
# 執行該命令后，MySQL 服務器首先會自動創建一個新的錯誤日志，然后將舊的錯誤日志更名為 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 中。優點是并不需要記錄每一條 sql 語句和每一行的數據變化， 減少了 binlog 日志量，節約 IO ，提高性能。缺點是在某些情況下會導致 master-slave 中的數據不一致(如 sleep() 函數， last_insert_id() ，以及 user-defined functions(udf) 等會出現問題)復制代碼
② ROW模式（RBR）
不記錄每條 sql 語句的上下文信息，僅需記錄哪條數據被修改了，修改成什么樣了。而且不會出現某些特定情況下 的存儲過程、或 function 、或 trigger 的調用和觸發無法被正確復制的問題。缺點是會產生大量的日志，尤其是 alter table的時候會讓日志暴漲。復制代碼
③ MIXED模式（MBR）
以上兩種模式的混合使用，一般的復制使用 STATEMENT 模式保存 binlog ，對于 STATEMENT 模式無法復制的操作使用 ROW 模式保存 binlog ， MySQL 會根據執行的 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'語句，可以刪除指定時間之前創建的二進制日志使用二進制文件恢復數據
創建數據庫
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 '活動狀態 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`;復制代碼
恢復數據
根據節點需要我們查看日志文件提供給我們的數據庫創建，表創建，數據新增等時創建的語句節點，從而恢復數據。
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復制代碼
直接執行binlog日志
mysqlbinlog /www/server/data/mysql-bin.000036 | mysql -uroot -p復制代碼
更多相關免費學習推薦：mysql教程(視頻)
文章名稱：認識MySQL物理文件
網頁網址：http://www.xueling.net.cn/article/cghooe.html