MySQL文件分為MySQL數(shù)據(jù)庫文件和存儲(chǔ)引擎相關(guān)的各種類型文件。

1 參數(shù)文件

在MySQL實(shí)例啟動(dòng)，用來尋找數(shù)據(jù)庫的各種文件所在的位置，指定某些初始化參數(shù)。

定位文件命令

mysql --help|grep my.cnf

2 日志文件

日志文件記錄了影響MySQL數(shù)據(jù)庫的各種類型活動(dòng)。

2.1 錯(cuò)誤日志

對(duì)MySQL的啟動(dòng)，運(yùn)行，關(guān)閉過程進(jìn)行了記錄。不僅記錄所有的錯(cuò)誤信息，也記錄了一些警告信息或正確的信息。

定位文件命令

SHOW VARIABLES LIKE ‘log_error’

2.2 慢查詢?nèi)罩?/h3>

記錄執(zhí)行時(shí)間超過某一閥值的所有SQL，慢查日志可以幫助定位可能存在問題的額SQL語句。默認(rèn)情況下，MySQL并不啟動(dòng)慢查日志。

記錄慢SQL的開關(guān) （打開ON）

SHOW VARIABLES LIKE ‘log_slow_queries’

慢SQl的閥值默認(rèn)為10，單位秒（等于不會(huì)記）

SHOW VARIABLES LIKE ‘long_query_time’

是否記錄沒有使用索引的查詢

SHOW VARIABLES LIKE ‘long_queries_not_using_indexes’

每分鐘最多記錄沒使用索引的SQL的數(shù)量

SHOW VARIABLES LIKE ‘log_throttle_queries_not_using_indexs’

其他參數(shù)

slow-query-log-file:  指定目錄，默認(rèn)在data目錄
log_output：輸出格式，默認(rèn)為FILE，可以配置為TABLE（記錄到slow_log表）

通過表中slow_log查看

從5.1 開始，可以將慢查詢?nèi)罩据敵龅奖碇?/p>

表信息

SHOW CREATE TABLE mysql.slow_log

慢查詢的輸出格式，默認(rèn)為FILE，需要設(shè)置為TABLW

SET GLOBAL log_output=‘TABLE’

從table中查看慢SQL語句

select * from  mysql.slow_log

2.3 查詢?nèi)罩?/h3>
記錄所有對(duì)MySql數(shù)據(jù)庫的請(qǐng)求信息，無論請(qǐng)求是否執(zhí)行。
默認(rèn)文件名為主機(jī)名.log，也可以輸出到mysql架構(gòu)的general_log表中。

2.4 二進(jìn)制日志

記錄對(duì)MySql數(shù)據(jù)庫執(zhí)行更改的所有操作，不包括select，show這類操作。若更改操作并未修改，也會(huì)記錄。
可用于恢復(fù)、復(fù)制、審計(jì)。 MySql官方手冊(cè)測試表明，開啟二進(jìn)制日志會(huì)使性能下降1%，但考慮到復(fù)制，point-in-time的恢復(fù)等功，建議開啟。

查看是否開啟二進(jìn)制日志

MySQL8.0之前默認(rèn)是關(guān)閉的，之后默認(rèn)是開啟的

show variables like "log_bin"; //ON表示開啟

開啟二進(jìn)制日志

如果需要開啟二進(jìn)制日志，需要在配置文件中添加 log-bin 選項(xiàng)

[mysqld]
log-bin=dir/[filename]
// dir參數(shù)指定二進(jìn)制文件的存儲(chǔ)路徑
// filename參數(shù)指定二級(jí)制文件的文件名

如果沒有 dir 和 filename 參數(shù)，二進(jìn)制日志將默認(rèn)存儲(chǔ)在數(shù)據(jù)庫的數(shù)據(jù)目錄下，默認(rèn)的文件名為hostname-bin.number。

查看 MySQL 中有哪些二進(jìn)制日志文件

>show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000035 | 686110 | No |
| mysql-bin.000036 | 96148 | No |
+------------------+-----------+-----------+

查看當(dāng)前正在寫入的二進(jìn)制日志文件

>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000036 | 96148 | | | |
+------------------+----------+--------------+------------------+-------------------+

緩沖區(qū)同步到磁盤策略

當(dāng)使用事務(wù)的存儲(chǔ)引擎時(shí)（InnoDB存儲(chǔ)引擎）時(shí)，所有未提交的事務(wù)的二進(jìn)制日志會(huì)被記錄到一個(gè)緩沖區(qū)，緩沖區(qū)大小由binlog_cache_size決定。

binlog_cache_size 二進(jìn)制日志事務(wù)緩沖區(qū)大小
事務(wù)開啟時(shí)，uncommited的日志記錄到緩沖，事務(wù)提交時(shí)，緩存寫入二進(jìn)制日志文件。事務(wù)開始時(shí)自動(dòng)分配，因此不能設(shè)置太大。當(dāng)大于時(shí)，會(huì)把緩沖中的日志寫入一個(gè)臨時(shí)文件，因此也不能太小。默認(rèn)32k。

二進(jìn)制日志不是每次寫入的時(shí)候同步到磁盤，MySQL提供sync_binlog參數(shù)來控制數(shù)據(jù)庫緩沖區(qū)的binlog刷到磁盤上的策略。

sync_binlog = N 表示每次緩沖寫N次將binlog_cache中的數(shù)據(jù)強(qiáng)制寫入磁盤, 默認(rèn)為0
sync_binlog = 0，當(dāng)事務(wù)提交之后，不做fsync同步，依賴操作系統(tǒng)本身不定期把文件內(nèi)容刷新到磁盤
sync_binlog = 1，每進(jìn)行1次事務(wù)提交之后，fsync同步磁盤
sync_binlog = n，每進(jìn)行n次事務(wù)提交之后，fsync同步磁盤

其他參數(shù)

max_binlog_size 單個(gè)二進(jìn)制日志文件的最大值
該設(shè)置并不能嚴(yán)格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個(gè)比較大事務(wù)時(shí)，為了保證事務(wù)的完整性，不可能做切換日志的動(dòng)作，只能將該事務(wù)的所有SQL都記錄進(jìn)當(dāng)前日志，直到事務(wù)結(jié)束。超過后產(chǎn)生新的二進(jìn)制文件，后綴+1，并記錄到.index。默認(rèn)1G。

二進(jìn)制日志格式

STATEMENT：邏輯SQL語句。
對(duì)復(fù)制有要求，如主服務(wù)器運(yùn)行rand，uuid，觸發(fā)器等操作，可能導(dǎo)致主從表中數(shù)據(jù)不一致；
默認(rèn)事務(wù)級(jí)別 repeatable read，也是因?yàn)槎M(jìn)制日志文件的關(guān)系，若使用read commited, 會(huì)出現(xiàn)類似丟失，主從不一致。
ROW：表的行更改情況
可以為數(shù)據(jù)庫恢復(fù)和復(fù)制帶來更好的可靠性，但是二進(jìn)制文件大小增加。
MIXED
因?yàn)?STATEMENT 和 ROW 各有優(yōu)缺點(diǎn)，因此 MySQL 新增了一種格式 MIXED，采用折中方案，MySQL 會(huì)判斷采用 STATEMENT 還是 ROW 來記錄 binlog。

binlog_format 是動(dòng)態(tài)參數(shù)，可以更改:

SET @@session.binlog.format='ROW';
SET GLOBAL binlog.format='ROW';

2.5 套接字文件

本地連接mysql可以使用UNIX域套接字，需要套接字文件。

SHOW VARIABLES LIKE ‘socket’

一般在/tmp/mysql.sock。

2.6 pid文件

MySQL實(shí)例的進(jìn)程ID文件。

SHOW VARIABLES LIKE ‘pid_file’

默認(rèn)數(shù)據(jù)庫目錄下/user/local/mysql/data/，主機(jī)名.pid。

2.6 表結(jié)構(gòu)定義文件

存放MySql表結(jié)構(gòu)定義。
在數(shù)據(jù)目錄下，每一個(gè)表都有一個(gè)子目錄，存放對(duì)應(yīng)的表結(jié)構(gòu)定義文件，后綴為frm
MySql8.0后，移除了.frm文件，表結(jié)構(gòu)定義存放到數(shù)據(jù)庫系統(tǒng)表中。

3 InnoDB存儲(chǔ)引擎文件

3.1 表空間文件

image.png

共享表空間文件

InnoDB將所有數(shù)據(jù)（表數(shù)據(jù)，索引，插入緩沖索引頁，回滾信息，插入緩沖索引頁，系統(tǒng)事務(wù)信息，二次寫緩沖等等）邏輯地放在一個(gè)空間中。

innodb_data_file_path = datafile_spec1[;datafile_spec2]...
可通過多個(gè)文件組成一個(gè)表空間，文件位于不同的磁盤上，磁盤負(fù)載可能被平均，可以提高數(shù)據(jù)庫整體性能。

獨(dú)立表空間文件

從mysql 5.6.6版本開始，獨(dú)立表空間（file-per-table tablespaces）默認(rèn)是開啟的。在開啟的情況下，你創(chuàng)建一個(gè)innodb引擎的表，那么表有自己獨(dú)立的一些數(shù)據(jù)文件。

表名.frm # 表的表結(jié)構(gòu)文件(里面存放的是表的創(chuàng)建語句)
表名.ibd # 表的數(shù)據(jù)文件(當(dāng)有數(shù)據(jù)往表中插入時(shí),數(shù)據(jù)就保存之個(gè)文件中的)

3.2 重做日志文件

重做日志是為了保證事務(wù)的原子性，持久性。InnoDB采用Write Ahread Log策略，事務(wù)提交時(shí)，先寫重做日志，再修改頁。
重做日志（redo log）由內(nèi)存中的重做日志緩沖（redo log buffer），和重做日志文件（redo log files）組成。如下圖所示：

image.png

事務(wù)提交時(shí)，先寫重做日志。redo log都是以512字節(jié)存儲(chǔ)，重做日志緩沖，和重做日志文件都是以塊（block）進(jìn)行保存，重做日志塊（redo log block）和磁盤扇區(qū)大小一樣，都是512字節(jié)。因此重做日志的寫入可以保證原子性，不需要doublewrite技術(shù)。

重做日志文件參數(shù)

每個(gè)innoDB存儲(chǔ)引擎至少有一個(gè)重做日志文件組，每個(gè)文件組至少2個(gè)重做日志文件。默認(rèn)在數(shù)據(jù)目錄下，ib_logfile0和ib_logfile1。

innodb_log_file_size 日志組中每個(gè)日志文件的大小
如果該參數(shù)設(shè)置太大，由于意外(斷電，OOM-Kill等)宕機(jī)時(shí)，二進(jìn)制日志很大恢復(fù)需要時(shí)間長；參數(shù)設(shè)置太小，一個(gè)事物日志多次切換重做日志文件，頻發(fā)發(fā)生asyc checkpoint，從而影響IO性能。最大512G。
innodb_log_files_in_group
指定日志組個(gè)數(shù)，默認(rèn)為2個(gè)日志組
innodb_log_group_home_dir
指定日志組所在的路徑，默認(rèn)為./ ，表示在MySQL數(shù)據(jù)庫的數(shù)據(jù)目錄下。

重做日志緩沖（redo log buffer）什么時(shí)候?qū)懭胫刈鋈罩?/h5>

master thread每秒（無論事務(wù)是否提交）

innodb_flush_log_at_trx_commit 參數(shù)控制

關(guān)于innodb_flush_log_at_trx_commit參數(shù)

0 : 表示由后臺(tái)Master線程每隔 1秒把 log buffer 刷到文件系統(tǒng)中(os buffer)去，并且調(diào)用文件系統(tǒng)的“flush”操作將緩存刷新到磁盤上去。也就是說一秒之前的日志都保存在日志緩沖區(qū)，如果機(jī)器宕掉，可能丟失1秒的事務(wù)數(shù)據(jù)；
1: 表示在每次事務(wù)提交的時(shí)候，都把 log buffer 刷到文件系統(tǒng)中(os buffer)去，并且調(diào)用文件系統(tǒng)的“flush”操作將緩存刷新到磁盤上去；
2：表示在每次事務(wù)提交的時(shí)候會(huì)把 log buffer 刷到文件系統(tǒng)中去，但并不會(huì)立即刷寫到磁盤。

注意
為0或者2時(shí)，都有可能發(fā)生恢復(fù)時(shí)部分事務(wù)丟失。
不同的是，設(shè)置為2時(shí)，當(dāng)數(shù)據(jù)庫發(fā)生宕機(jī)，但是操作系統(tǒng)服務(wù)器并沒有宕時(shí)，由于此時(shí)未寫入磁盤的事務(wù)日志保存在文件系統(tǒng)緩存中，當(dāng)恢復(fù)時(shí)同樣能保證數(shù)據(jù)恢復(fù)不丟失。
為了ACID的持久性，建議 innodb_flush_log_at_trx_commit 設(shè)置為1，默認(rèn)也是1。

重做日志與二進(jìn)制日志區(qū)別

二進(jìn)制日志記錄所有包括Innodb，MYISAM，Heap等其他存儲(chǔ)引擎的日志，
重做日志是innodb產(chǎn)生的，記錄存儲(chǔ)引擎本身的事務(wù)日志。
二進(jìn)制日志是是邏輯日志，記錄關(guān)于事務(wù)的具體操作內(nèi)容；
重做日志是物理格式日志，記錄關(guān)于每個(gè)頁的更改的物理情況。
二進(jìn)制僅日志在事務(wù)提交完成時(shí)一次寫入，即只寫磁盤一次；
重做日志在事務(wù)進(jìn)行中不斷寫入。

重做日志與Checkpoint機(jī)制的聯(lián)系

如下圖所示，一組redo log文件是一個(gè)類似環(huán)形的狀態(tài)，循環(huán)利用。
write pos指的是當(dāng)前寫入redo log的位置，check point是要擦除并更新到數(shù)據(jù)文件的位置，所以write pos 到check point 位置就是還未使用的空閑空間。

image.png

InnoDB中重做日志文件是循環(huán)使用的。當(dāng)頁被Checkpoint刷新到磁盤后，對(duì)應(yīng)的重做日志就不需要使用，其空間可以被覆蓋重用。
如果待寫入的重做日志文件空間不可用（臟頁還沒有刷新到磁盤），就需要強(qiáng)制產(chǎn)生Checkpoint，將緩沖池中的頁至少刷新到當(dāng)前重做日志的位置。
數(shù)據(jù)庫宕機(jī)重啟時(shí)通過執(zhí)行重做日志恢復(fù)數(shù)據(jù)。但由于Checkpoint機(jī)制，數(shù)據(jù)庫宕機(jī)重啟并不需要重做所有的日志，因?yàn)镃heckpoint之前的頁都刷新到磁盤了，只需執(zhí)行最新一次Checkpoint后的重做日志進(jìn)行恢復(fù)，這樣可以縮短數(shù)據(jù)庫的恢復(fù)時(shí)間。

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

MySQL系列2｜文件

MySQL系列2｜文件

1 參數(shù)文件

2 日志文件

2.1 錯(cuò)誤日志

通過表中slow_log查看

2.3 查詢?nèi)罩?/h3>
記錄所有對(duì)MySql數(shù)據(jù)庫的請(qǐng)求信息，無論請(qǐng)求是否執(zhí)行。
默認(rèn)文件名為主機(jī)名.log，也可以輸出到mysql架構(gòu)的general_log表中。

2.4 二進(jìn)制日志

查看是否開啟二進(jìn)制日志

開啟二進(jìn)制日志

查看 MySQL 中有哪些二進(jìn)制日志文件

查看當(dāng)前正在寫入的二進(jìn)制日志文件

緩沖區(qū)同步到磁盤策略

其他參數(shù)

二進(jìn)制日志格式

2.5 套接字文件

2.6 pid文件

2.6 表結(jié)構(gòu)定義文件

3 InnoDB存儲(chǔ)引擎文件

3.1 表空間文件

共享表空間文件

獨(dú)立表空間文件

3.2 重做日志文件

重做日志文件參數(shù)

重做日志與二進(jìn)制日志區(qū)別

重做日志與Checkpoint機(jī)制的聯(lián)系

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

MySQL系列2｜文件

1 參數(shù)文件

2 日志文件

2.1 錯(cuò)誤日志

通過表中slow_log查看

2.3 查詢?nèi)罩?/h3> 記錄所有對(duì)MySql數(shù)據(jù)庫的請(qǐng)求信息，無論請(qǐng)求是否執(zhí)行。 默認(rèn)文件名為主機(jī)名.log，也可以輸出到mysql架構(gòu)的general_log表中。

2.4 二進(jìn)制日志

查看是否開啟二進(jìn)制日志

開啟二進(jìn)制日志

查看 MySQL 中有哪些二進(jìn)制日志文件

查看當(dāng)前正在寫入的二進(jìn)制日志文件

緩沖區(qū)同步到磁盤策略

其他參數(shù)

二進(jìn)制日志格式

2.5 套接字文件

2.6 pid文件

2.6 表結(jié)構(gòu)定義文件

3 InnoDB存儲(chǔ)引擎文件

3.1 表空間文件

共享表空間文件

獨(dú)立表空間文件

3.2 重做日志文件

重做日志文件參數(shù)

重做日志與二進(jìn)制日志區(qū)別

重做日志與Checkpoint機(jī)制的聯(lián)系

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

2.3 查詢?nèi)罩?/h3>
記錄所有對(duì)MySql數(shù)據(jù)庫的請(qǐng)求信息，無論請(qǐng)求是否執(zhí)行。
默認(rèn)文件名為主機(jī)名.log，也可以輸出到mysql架構(gòu)的general_log表中。