mysql教程欄目介紹MySQL物理文件。

創(chuàng)新互聯(lián)長期為上1000+客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為寧洱企業(yè)提供專業(yè)的成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作、外貿(mào)網(wǎng)站建設(shè)，寧洱網(wǎng)站改版等技術(shù)服務(wù)。擁有十多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。1.數(shù)據(jù)庫的數(shù)據(jù)存儲(chǔ)文件

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

1. ".FRM"文件

8.0之前無論是那種存儲(chǔ)引擎，創(chuàng)建表之后就一定會(huì)生成一個(gè)以表明命名的'.frm'文件。frm文件主要存放與表相關(guān)的數(shù)據(jù)信息，主要包括表結(jié)構(gòu)的定義信息。當(dāng)數(shù)據(jù)庫崩潰時(shí)，用戶可以通過frm文件來恢復(fù)數(shù)據(jù)表結(jié)構(gòu)。

2. ".MYD"文件

“.MYD”文件是MyISAM存儲(chǔ)引擎專用，存放MyISAM表的數(shù)據(jù)。每一個(gè)MyISAM表都會(huì)有一個(gè)“.MYD”文件與 之對(duì)應(yīng)，同樣存放于所屬數(shù)據(jù)庫的文件夾 下， 和“.frm”文件在一起。

3. ".MYI"文件

“.MYI”文件也是專屬于MyISAM存儲(chǔ)引擎的，主要存放MyISAM表的索引相關(guān)信息。對(duì)于MyISAM存儲(chǔ)來說， 可以被cache 的內(nèi)容主要就是來源 于“.MYI”文件中。 每一個(gè)MyISAM表對(duì)應(yīng)一個(gè)“.MYI”文件，存放于位置 和“.frm”以及“.MYD”一樣。

4. ".ibd"文件與".ibdata"文件

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

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

共享表空間: 某一個(gè)數(shù)據(jù)庫的所有的表數(shù)據(jù)，索引文件全部放在一個(gè)文件中。

獨(dú)占表空間: 每一個(gè)表都將會(huì)生成以獨(dú)立的文件方式來進(jìn)行存儲(chǔ)，每一個(gè)表都有一個(gè).frm表描述文件，還有 一個(gè).ibd文件。其中這個(gè)文件包括了 單獨(dú)一個(gè)表的數(shù)據(jù) 內(nèi)容以及索引內(nèi)容。

4.1 兩者對(duì)比

共享表空間：

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

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

獨(dú)立表空間：

優(yōu)點(diǎn):

每個(gè)表都有自已獨(dú)立的表空間。

每個(gè)表的數(shù)據(jù)和索引都會(huì)存在自已的表空間中。

可以實(shí)現(xiàn)單表在不同的數(shù)據(jù)庫中移動(dòng)。

空間可以回收

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

b) 對(duì)于使用獨(dú)立表空間的表，不管怎么刪除，表空間的碎片不會(huì)太嚴(yán)重的影響性能，而且還有機(jī)會(huì)處理。 缺點(diǎn)：?jiǎn)伪碓黾舆^大，如超過100 G。 相比較之下，使用獨(dú)占表空間的效率以及性能會(huì)更高一點(diǎn) 共享表空間和獨(dú)立表空間之間的轉(zhuǎn)換。

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

2. 日志

日志文件：查詢?nèi)罩尽⒙樵內(nèi)罩?、錯(cuò)誤日志、事務(wù)日志、binlog日志、錯(cuò)誤日志、中繼日志

2.1 查詢?nèi)罩?p>查詢?nèi)罩驹趍ysql中被稱之為 general log（通用日志），不要被"查詢?nèi)罩?quot;的名字誤導(dǎo)，錯(cuò)誤的以為查詢?nèi)罩局粫?huì)記錄select語句，其實(shí)不然，查詢?nèi)罩居涗浟藬?shù)據(jù)庫執(zhí)行的命令，不管這些語句是否正確，都會(huì)被記錄，我想這也是 general log之所以"通用"的原因吧，由于數(shù)據(jù)庫操作命令有可能非常多而且執(zhí)行比較頻繁，所以當(dāng)開啟了查詢?nèi)罩疽院?，?shù)據(jù)庫可能需要不停的寫入查詢?nèi)罩?，這樣會(huì)增大服務(wù)器的IO壓力，增加很多系統(tǒng)開銷，所以默認(rèn)情況下，mysql的查詢?nèi)罩臼菦]有開啟的，但是開啟查詢?nèi)罩疽灿兄谖覀兎治瞿男┱Z句執(zhí)行密集，執(zhí)行密集的select語句對(duì)應(yīng)的數(shù)據(jù)是否能夠被緩存，查詢?nèi)罩疽部梢詭椭覀兎治鰡栴}，所以，我們可以根據(jù)實(shí)際情況決定是否開啟查詢?nèi)罩荆绻枰梢允謩?dòng)開啟。如果開啟了查詢?nèi)罩?，那么我們可以通過如下3種方式存儲(chǔ)查詢?nèi)罩尽?/p>

方式1：將查詢?nèi)罩敬娣庞谥付ǖ娜罩疚募小?/p>

方式2：將查詢?nèi)罩敬娣庞?mysql.general_log表中。

方式3：將查詢?nèi)罩就瑫r(shí)存放于指定的日志文件與mysql庫的general_log表中。

查看查詢?nèi)罩臼欠耖_啟

show VARIABLES LIKE 'general_log';復(fù)制代碼

show variables where variable_name like "%general_log%" or variable_name="log_output";復(fù)制代碼

general_log:表示查詢?nèi)罩臼欠耖_啟，ON表示開啟，OFF表示未開啟，默認(rèn)為OFF

log_output:表示當(dāng)查詢?nèi)罩鹃_啟以后，以哪種方式存放，log_output可以設(shè)置為4種值，"FILE"、"TABLE"、"FILE,TABLE"、"NONE"。

# 設(shè)置查詢?nèi)罩镜妮敵龇绞?set global log_output=[none|file|table|file,table];
# 設(shè)置general log的日志文件路徑
set global general_log_file='/tmp/general.log';
# 開啟general log
set global general_log=on;
# 關(guān)閉general log
set global general_log=off;復(fù)制代碼

2.2 慢日志

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

開啟MySQL的慢查詢?nèi)罩竟δ?pre># 查看是否開啟 未使用索引的SQL記錄日志查詢 show variables like 'log_queries_not_using_indexes'; # 開啟 未使用索引的SQL記錄日志查詢 set global log_queries_not_using_indexs=on/off; # 查看超過多長時(shí)間的查詢記入慢查詢?nèi)罩局?show variables like 'long_query_time'; # 設(shè)置記錄時(shí)長，0為全部記錄，設(shè)置之后需重新啟動(dòng) set global long_query_time=10 # 查看是否開啟 mysql慢查詢?nèi)罩竟δ?show variables like 'slow_qurey_log' # 開啟、關(guān)閉慢日志 set global slow_qurey_log=on/off; # 查看日志記錄位置 show variables like 'slow_query_log_file'; #日志存儲(chǔ)方式 show variables like "log_output";復(fù)制代碼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);復(fù)制代碼

table方式

日志分析工具 mysqldumpslow

查看 mysqldumpslow 的幫助信息：

-s ORDER     ORDER排序依據(jù)（al，at，ar，c，l，r，t），“at”是默認(rèn)值
                al: 平均鎖定時(shí)間
                ar: 平均發(fā)送行數(shù)
                at: 平均查詢時(shí)間
                 c: 計(jì)數(shù)
                 l: 鎖定時(shí)間
                 r: 已發(fā)送行
                 t: 查詢時(shí)間  
  -r           反轉(zhuǎn)排序順序（的最后一個(gè)而不是第一個(gè)）
  -t NUM       只顯示前n個(gè)查詢
  -a           不要將所有數(shù)字抽象為N，將字符串抽象為“S”
  -n NUM       名字中至少有n個(gè)數(shù)字的抽象數(shù)字
  -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復(fù)制代碼

2.3 錯(cuò)誤日志

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

# 查看錯(cuò)誤日志記錄位置
show variables like "log_error";
# 在 MySQL 中，可以使用 mysqladmin 命令來開啟新的錯(cuò)誤日志，以保證 MySQL 服務(wù)器上的硬盤空間。
# mysqladmin 命令的語法如下：
mysqladmin -uroot -p flush-logs
# 執(zhí)行該命令后，MySQL 服務(wù)器首先會(huì)自動(dòng)創(chuàng)建一個(gè)新的錯(cuò)誤日志，然后將舊的錯(cuò)誤日志更名為 filename.err-old ?？梢允謩?dòng)直接刪除。

#配置文件中配置
[mysqld]
log-error=dir/{filename}復(fù)制代碼

2.4 二進(jìn)制日志

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

# 查看 binary log 日志是否開啟，binary log日志默認(rèn)關(guān)閉
show variables like "log_bin";
# 在MySQL中可以再配置文件中開啟二進(jìn)制文件日志
[mysqld]
log-bin=dir/{filename}復(fù)制代碼

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

二進(jìn)制日志的格式有三種：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

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

② ROW模式（RBR）

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

③ MIXED模式（MBR）

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

binlog復(fù)制配置

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

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

2.5二進(jìn)制文件基本操作

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

show master status命令用來查看當(dāng)前的二進(jìn)制日志；

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

刪除二進(jìn)制文件

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

使用二進(jìn)制文件恢復(fù)數(shù)據(jù)

創(chuàng)建數(shù)據(jù)庫

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 '標(biāo)題',
  `activity_type` enum('1','2') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '活動(dòng)類型 1:商品 2:團(tuán)購',
  `activity_banner` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '活動(dòng)橫幅大圖片',
  `activity_style` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '活動(dòng)頁面模板樣式標(biāo)識(shí)碼',
  `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 '開始時(shí)間',
  `activity_end_date` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '結(jié)束時(shí)間',
  `activity_sort` tinyint(1) UNSIGNED NOT NULL DEFAULT 255 COMMENT '排序',
  `activity_state` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '活動(dòng)狀態(tài) 0為關(guān)閉 1為開啟',
  PRIMARY KEY (`activity_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '活動(dòng)表' ROW_FORMAT = Compact;復(fù)制代碼

新增 2 條數(shù)據(jù)

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

刪除數(shù)據(jù)

drop table `33hao_activity`;復(fù)制代碼

恢復(fù)數(shù)據(jù)

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

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

根據(jù)時(shí)間恢復(fù)數(shù)據(jù)

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復(fù)制代碼

直接執(zhí)行binlog日志

mysqlbinlog /www/server/data/mysql-bin.000036 | mysql -uroot -p復(fù)制代碼

更多相關(guān)免費(fèi)學(xué)習(xí)推薦：mysql教程(視頻)

當(dāng)前題目：認(rèn)識(shí)MySQL物理文件
文章位置：http://www.ekvhdxd.cn/article4/cghooe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供商城網(wǎng)站、企業(yè)網(wǎng)站制作、標(biāo)簽優(yōu)化、App開發(fā)、品牌網(wǎng)站設(shè)計(jì)、品牌網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)