一、MySQL的內(nèi)部組件結(jié)構(gòu)

大體來(lái)說(shuō)，MySQL 可以分為 Server 層和存儲(chǔ)引擎層兩部分。

1、Server層

主要包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等，涵蓋 MySQL 的大多數(shù)核心服務(wù)功能，以及所有的內(nèi)置函數(shù)（如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)，比如存儲(chǔ)過(guò)程、觸發(fā)器、視圖等。

下面我們來(lái)看下Server層的連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器分別主要干了哪些事情。

1.1、連接器

我們知道由于MySQL是開源的，他有非常多種類的客戶端：navicat,mysql front,jdbc,SQLyog等非常豐富的客戶端，包括各種編程語(yǔ)言實(shí)現(xiàn)的客戶端連接程序，這些客戶端要向mysql發(fā)起通信都必須先跟Server端建立通信連接，而建立連接的工作就是有連接器完成的。

第一步，你會(huì)先連接到這個(gè)數(shù)據(jù)庫(kù)上，這時(shí)候接待你的就是連接器。連接器負(fù)責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。連接命令一般是這么寫的：

[root@192 ~]# mysql -h host[數(shù)據(jù)庫(kù)地址] -u root[用戶] -p root[密碼] -P 3306

連接命令中的 mysql 是客戶端工具，用來(lái)跟服務(wù)端建立連接。在完成經(jīng)典的 TCP 握手后，連接器就要開始認(rèn)證你的身份，這個(gè)時(shí)候用的就是你輸入的用戶名和密碼。

1、如果用戶名或密碼不對(duì)，你就會(huì)收到一個(gè)"Access denied for user"的錯(cuò)誤，然后客戶端程序結(jié)束執(zhí)行。

2、如果用戶名密碼認(rèn)證通過(guò)，連接器會(huì)到權(quán)限表里面查出你擁有的權(quán)限。之后，這個(gè)連接里面的權(quán)限判斷邏輯，都將依賴于此時(shí)讀到的權(quán)限。

這就意味著，一個(gè)用戶成功建立連接后，即使你用管理員賬號(hào)對(duì)這個(gè)用戶的權(quán)限做了修改，也不會(huì)影響已經(jīng)存在連接的權(quán)限。修改完成后，只有再新建的連接才會(huì)使用新的權(quán)限設(shè)置。

1.2、查詢緩存

連接建立完成后，你就可以執(zhí)行 select 語(yǔ)句了。執(zhí)行邏輯就會(huì)來(lái)到第二步：查詢緩存。

MySQL 拿到一個(gè)查詢請(qǐng)求后，會(huì)先到查詢緩存看看，之前是不是執(zhí)行過(guò)這條語(yǔ)句。之前執(zhí)行過(guò)的語(yǔ)句及其結(jié)果可能會(huì)以 key-value 對(duì)的形式，被直接緩存在內(nèi)存中。key 是查詢的語(yǔ)句，value 是查詢的結(jié)果。如果你的查詢能夠直接在這個(gè)緩存中找到 key，那么這個(gè) value 就會(huì)被直接返回給客戶端。

如果語(yǔ)句不在查詢緩存中，就會(huì)繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后，執(zhí)行結(jié)果會(huì)被存入查詢緩存中。你可以看到，如果查詢命中緩存，MySQL 不需要執(zhí)行后面的復(fù)雜操作，就可以直接返回結(jié)果，這個(gè)效率會(huì)很高。

大多數(shù)情況查詢緩存就是個(gè)雞肋，為什么呢？

因?yàn)椴樵兙彺嫱状笥诶?。查詢緩存的失效非常頻繁，只要有對(duì)一個(gè)表的更新，這個(gè)表上所有的查詢緩存都會(huì)被清空。因此很可能你費(fèi)勁地把結(jié)果存起來(lái)，還沒使用呢，就被一個(gè)更新全清空了。對(duì)于更新壓力大的數(shù)據(jù)庫(kù)來(lái)說(shuō)，查詢緩存的命中率會(huì)非常低。

一般建議大家在靜態(tài)表里使用查詢緩存，什么叫靜態(tài)表呢？就是一般我們極少更新的表。比如，一個(gè)系統(tǒng)配置表、字典表，那這張表上的查詢才適合使用查詢緩存。好在 MySQL 也提供了這種“按需使用”的方式。你可以將my.cnf參數(shù) query_cache_type 設(shè)置成 DEMAND。

my.cnf
#query_cache_type有3個(gè)值 0代表關(guān)閉查詢緩存OFF，1代表開啟ON，2（DEMAND）代表當(dāng)sql語(yǔ)句中有SQL_CACHE關(guān)鍵詞時(shí)才緩存
query_cache_type=2

這樣對(duì)于默認(rèn)的 SQL 語(yǔ)句都不使用查詢緩存。而對(duì)于你確定要使用查詢緩存的語(yǔ)句，可以用 SQL_CACHE 顯式指定，像下面這個(gè)語(yǔ)句一樣：

mysql> select SQL_CACHE * from test where ID=5；

查看當(dāng)前mysql實(shí)例是否開啟緩存機(jī)制

mysql> show global variables like "%query_cache_type%";

mysql 8.0已經(jīng)移除了查詢緩存功能

1.3、分析器

如果沒有命中查詢緩存，就要開始真正執(zhí)行語(yǔ)句了。首先，MySQL 需要知道你要做什么，因此需要對(duì) SQL 語(yǔ)句做解析。

分析器先會(huì)做“詞法分析”。你輸入的是由多個(gè)字符串和空格組成的一條 SQL 語(yǔ)句，MySQL 需要識(shí)別出里面的字符串分別是什么，代表什么。

MySQL 從你輸入的"select"這個(gè)關(guān)鍵字識(shí)別出來(lái)，這是一個(gè)查詢語(yǔ)句。它也要把字符串“T”識(shí)別成“表名 T”，把字符串“ID”識(shí)別成“列 ID”。

做完了這些識(shí)別以后，就要做“語(yǔ)法分析”。根據(jù)詞法分析的結(jié)果，語(yǔ)法分析器會(huì)根據(jù)語(yǔ)法規(guī)則，判斷你輸入的這個(gè) SQL 語(yǔ)句是否滿足 MySQL 語(yǔ)法。

如果你的語(yǔ)句不對(duì)，就會(huì)收到“You have an error in your SQL syntax”的錯(cuò)誤提醒，比如下面這個(gè)語(yǔ)句 from 寫成了 “rom”。

mysql> select * fro test where id=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fro test where id=1' at line 1

下圖是分析器對(duì)sql的分析過(guò)程步驟：

SQL語(yǔ)句經(jīng)過(guò)分析器分析之后，會(huì)生成一個(gè)這樣的語(yǔ)法樹

至此我們分析器的工作任務(wù)也基本圓滿了。接下來(lái)進(jìn)入到優(yōu)化器

1.4、優(yōu)化器

經(jīng)過(guò)了分析器，MySQL 就知道你要做什么了。在開始執(zhí)行之前，還要先經(jīng)過(guò)優(yōu)化器的處理。

優(yōu)化器是在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引；或者在一個(gè)語(yǔ)句有多表關(guān)聯(lián)（join）的時(shí)候，決定各個(gè)表的連接順序；以及一些mysql自己內(nèi)部的優(yōu)化機(jī)制。

1.5、執(zhí)行器

開始執(zhí)行的時(shí)候，要先判斷一下你對(duì)這個(gè)表 T 有沒有執(zhí)行查詢的權(quán)限，如果沒有，就會(huì)返回沒有權(quán)限的錯(cuò)誤，如下所示 (在工程實(shí)現(xiàn)上，如果命中查詢緩存，會(huì)在查詢緩存返回結(jié)果的時(shí)候，做權(quán)限驗(yàn)證)。

mysql> select * from test where id=10;

如果有權(quán)限，就打開表繼續(xù)執(zhí)行。打開表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的引擎定義，去使用這個(gè)引擎提供的接口。

2、存儲(chǔ)引擎層

存儲(chǔ)引擎層負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。其架構(gòu)模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎?，F(xiàn)在最常用的存儲(chǔ)引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認(rèn)存儲(chǔ)引擎。也就是說(shuō)如果我們?cè)赾reate table時(shí)不指定表的存儲(chǔ)引擎類型,默認(rèn)會(huì)給你設(shè)置存儲(chǔ)引擎為InnoDB。

二、Innodb底層原理與Mysql日志機(jī)制

1、redo log重做日志關(guān)鍵參數(shù)

innodb_log_buffer_size：設(shè)置redo log buffer大小參數(shù)，默認(rèn)16M ，最大值是4096M，最小值為1M。

show variables like '%innodb_log_buffer_size%';

innodb_log_group_home_dir：設(shè)置redo log文件存儲(chǔ)位置參數(shù)，默認(rèn)值為"./"，即innodb數(shù)據(jù)文件存儲(chǔ)位置，其中的 ib_logfile0 和 ib_logfile1 即為redo log文件。

show variables like '%innodb_log_group_home_dir%';

innodb_log_files_in_group：設(shè)置redo log文件的個(gè)數(shù)，命名方式如: ib_logfile0, iblogfile1… iblogfileN。默認(rèn)2個(gè)，最大100個(gè)。

show variables like '%innodb_log_files_in_group%';

innodb_log_file_size：設(shè)置單個(gè)redo log文件大小，默認(rèn)值為48M。最大值為512G，注意最大值指的是整個(gè) redo log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。

show variables like '%innodb_log_file_size%';

redo log 寫入磁盤過(guò)程分析：

redo log 從頭開始寫，寫完一個(gè)文件繼續(xù)寫另一個(gè)文件，寫到最后一個(gè)文件末尾就又回到第一個(gè)文件開頭循環(huán)寫，如下面這個(gè)圖所示。

write pos 是當(dāng)前記錄的位置，一邊寫一邊后移，寫到第 3 號(hào)文件末尾后就回到 0 號(hào)文件開頭。

checkpoint 是當(dāng)前要擦除的位置，也是往后推移并且循環(huán)的，擦除記錄前要把記錄更新到數(shù)據(jù)文件里。

write pos 和 checkpoint 之間的部分就是空著的可寫部分，可以用來(lái)記錄新的操作。如果 write pos 追上checkpoint，表示redo log寫滿了，這時(shí)候不能再執(zhí)行新的更新，得停下來(lái)先擦掉一些記錄，把 checkpoint 推進(jìn)一下。

innodb_flush_log_at_trx_commit：這個(gè)參數(shù)控制 redo log 的寫入策略，它有三種可能取值：

• 設(shè)置為0：表示每次事務(wù)提交時(shí)都只是把 redo log 留在 redo log buffer 中，數(shù)據(jù)庫(kù)宕機(jī)可能會(huì)丟失數(shù)據(jù)。

• 設(shè)置為1(默認(rèn)值)：表示每次事務(wù)提交時(shí)都將 redo log 直接持久化到磁盤，數(shù)據(jù)最安全，不會(huì)因?yàn)閿?shù)據(jù)庫(kù)宕機(jī)丟失數(shù)據(jù)，但是效率稍微差一點(diǎn)，線上系統(tǒng)推薦這個(gè)設(shè)置。

• 設(shè)置為2：表示每次事務(wù)提交時(shí)都只是把 redo log 寫到操作系統(tǒng)的緩存page cache里，這種情況如果數(shù)據(jù)庫(kù)宕機(jī)是不會(huì)丟失數(shù)據(jù)的，但是操作系統(tǒng)如果宕機(jī)了，page cache里的數(shù)據(jù)還沒來(lái)得及寫入磁盤文件的話就會(huì)丟失數(shù)據(jù)。

InnoDB 有一個(gè)后臺(tái)線程，每隔 1 秒，就會(huì)把 redo log buffer 中的日志，調(diào)用 操作系統(tǒng)函數(shù) write 寫到文件系統(tǒng)的 page cache，然后調(diào)用操作系統(tǒng)函數(shù) fsync 持久化到磁盤文件。

redo log寫入策略參看下圖：

# 查看innodb_flush_log_at_trx_commit參數(shù)值：
show variables like 'innodb_flush_log_at_trx_commit';
# 設(shè)置innodb_flush_log_at_trx_commit參數(shù)值(也可以在my.ini或my.cnf文件里配置)：
set global innodb_flush_log_at_trx_commit=1;  

2、binlog二進(jìn)制歸檔日志

binlog二進(jìn)制日志記錄保存了所有執(zhí)行過(guò)的修改操作語(yǔ)句，不保存查詢操作。如果 MySQL 服務(wù)意外停止，可通過(guò)二進(jìn)制日志文件排查，用戶操作或表結(jié)構(gòu)操作，從而來(lái)恢復(fù)數(shù)據(jù)庫(kù)數(shù)據(jù)。

啟動(dòng)binlog記錄功能，會(huì)影響服務(wù)器性能，但如果需要恢復(fù)數(shù)據(jù)或主從復(fù)制功能，則好處則大于對(duì)服務(wù)器的影響。

# 查看binlog相關(guān)參數(shù)
show variables like '%log_bin%';

MySQL5.7 版本中，binlog默認(rèn)是關(guān)閉的，8.0版本默認(rèn)是打開的。上圖中l(wèi)og_bin的值是OFF就代表binlog是關(guān)閉狀態(tài)，打開binlog功能，需要修改配置文件my.ini(windows)或my.cnf(linux)，然后重啟數(shù)據(jù)庫(kù)。

在配置文件中的[mysqld]部分增加如下配置:

# log-bin設(shè)置binlog的存放位置，可以是絕對(duì)路徑，也可以是相對(duì)路徑，這里寫的相對(duì)路徑，則binlog文件默認(rèn)會(huì)放在data數(shù)據(jù)目錄下
log-bin=mysql-binlog
# Server Id是數(shù)據(jù)庫(kù)服務(wù)器id，隨便寫一個(gè)數(shù)都可以，這個(gè)id用來(lái)在mysql集群環(huán)境中標(biāo)記唯一mysql服務(wù)器，集群環(huán)境中每臺(tái)mysql服務(wù)器的id不能一樣，不加啟動(dòng)會(huì)報(bào)錯(cuò)
server-id=1
# 其他配置
binlog_format = row # 日志文件格式，下面會(huì)詳細(xì)解釋
expire_logs_days = 15 # 執(zhí)行自動(dòng)刪除距離當(dāng)前15天以前的binlog日志文件的天數(shù)， 默認(rèn)為0， 表示不自動(dòng)刪除
max_binlog_size = 200M # 單個(gè)binlog日志文件的大小限制，默認(rèn)為 1GB

重啟數(shù)據(jù)庫(kù)后我們?cè)偃タ磀ata數(shù)據(jù)目錄會(huì)多出兩個(gè)文件，第一個(gè)就是binlog日志文件，第二個(gè)是binlog文件的索引文件，這個(gè)文件管理了所有的binlog文件的目錄。

當(dāng)然也可以執(zhí)行命令查看有多少binlog文件

show binary logs;

show variables like '%log_bin%';

log_bin：binlog日志是否打開狀態(tài)
log_bin_basename：是binlog日志的基本文件名，后面會(huì)追加標(biāo)識(shí)來(lái)表示每一個(gè)文件，binlog日志文件會(huì)滾動(dòng)增加
log_bin_index：指定的是binlog文件的索引文件，這個(gè)文件管理了所有的binlog文件的目錄。
sql_log_bin：sql語(yǔ)句是否寫入binlog文件，ON代表需要寫入，OFF代表不需要寫入。如果想在主庫(kù)上執(zhí)行一些操作，但不復(fù)制到slave庫(kù)上，可以通過(guò)修改參數(shù)sql_log_bin來(lái)實(shí)現(xiàn)。比如說(shuō)，模擬主從同步復(fù)制異常。

binlog 的日志格式

用參數(shù) binlog_format 可以設(shè)置binlog日志的記錄格式，mysql支持三種格式類型：

• STATEMENT：基于SQL語(yǔ)句的復(fù)制，每一條會(huì)修改數(shù)據(jù)的sql都會(huì)記錄到master機(jī)器的bin-log中，這種方式日志量小，節(jié)約IO開銷，提高性能，但是對(duì)于一些執(zhí)行過(guò)程中才能確定結(jié)果的函數(shù)，比如UUID()、SYSDATE()等函數(shù)如果隨sql同步到slave機(jī)器去執(zhí)行，則結(jié)果跟master機(jī)器執(zhí)行的不一樣。

• ROW：基于行的復(fù)制，日志中會(huì)記錄成每一行數(shù)據(jù)被修改的形式，然后在slave端再對(duì)相同的數(shù)據(jù)進(jìn)行修改記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)，可以解決函數(shù)、存儲(chǔ)過(guò)程等在slave機(jī)器的復(fù)制問(wèn)題，但這種方式日志量較大，性能不如Statement。舉個(gè)例子，假設(shè)update語(yǔ)句更新10行數(shù)據(jù)，Statement方式就記錄這條update語(yǔ)句，Row方式會(huì)記錄被修改的10行數(shù)據(jù)。

• MIXED：混合模式復(fù)制，實(shí)際就是前兩種模式的結(jié)合，在Mixed模式下，MySQL會(huì)根據(jù)執(zhí)行的每一條具體的sql語(yǔ)句來(lái)區(qū)分對(duì)待記錄的日志形式，也就是在Statement和Row之間選擇一種，如果sql里有函數(shù)或一些在執(zhí)行時(shí)才知道結(jié)果的情況，會(huì)選擇Row，其它情況選擇Statement，推薦使用這一種。

binlog寫入磁盤機(jī)制

binlog寫入磁盤機(jī)制主要通過(guò) sync_binlog 參數(shù)控制，默認(rèn)值是 0。

• 為0的時(shí)候，表示每次提交事務(wù)都只 write 到page cache，由系統(tǒng)自行判斷什么時(shí)候執(zhí)行 fsync 寫入磁盤。雖然性能得到提升，但是機(jī)器宕機(jī)，page cache里面的 binlog 會(huì)丟失。

• 也可以設(shè)置為1，表示每次提交事務(wù)都會(huì)執(zhí)行 fsync 寫入磁盤，這種方式最安全。

• 還有一種折中方式，可以設(shè)置為N(N>1)，表示每次提交事務(wù)都write 到page cache，但累積N個(gè)事務(wù)后才 fsync 寫入磁盤，這種如果機(jī)器宕機(jī)會(huì)丟失N個(gè)事務(wù)的binlog。

發(fā)生以下任何事件時(shí), binlog日志文件會(huì)重新生成：

• 服務(wù)器啟動(dòng)或重新啟動(dòng)

• 服務(wù)器刷新日志，執(zhí)行命令flush logs

• 日志文件大小達(dá)到 max_binlog_size 值，默認(rèn)值為 1GB

刪除 binlog 日志文件

刪除當(dāng)前的binlog文件
reset master;
# 刪除指定日志文件之前的所有日志文件，下面這個(gè)是刪除6之前的所有日志文件，當(dāng)前這個(gè)文件不刪除
purge master logs to 'mysql-binlog.000006';
# 刪除指定日期前的日志索引中binlog日志文件
purge master logs before '2023-01-21 14:00:00';

查看 binlog 日志文件

可以用mysql自帶的命令工具 mysqlbinlog 查看binlog日志內(nèi)容

# 查看bin-log二進(jìn)制文件（命令行方式，不用登錄mysql）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 # 查看bin-log二進(jìn)制文件（帶查詢條件）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 start-datetime="2023-01-21 00:00:00" stop-datetime="2023-02-01 00:00:00" start-position="5000" stop-position="20000"

執(zhí)行mysqlbinlog命令

mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007

查出來(lái)的binlog日志文件內(nèi)容如下：

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230127 21:13:51 server id 1  end_log_pos 123 CRC32 0x084f390f  Start: binlog v 4, server v 5.7.25-log created 230127 21:13:51 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#230127 21:13:51 server id 1  end_log_pos 154 CRC32 0x672ba207  Previous-GTIDs
# [empty]
# at 154
#230127 21:22:48 server id 1  end_log_pos 219 CRC32 0x8349d010  Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#230127 21:22:48 server id 1  end_log_pos 291 CRC32 0xbf49de02  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1674825768/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1342177280/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#230127 21:22:48 server id 1  end_log_pos 345 CRC32 0xc4ab653e  Table_map: `test`.`account` mapped to number 99
# at 345
#230127 21:22:48 server id 1  end_log_pos 413 CRC32 0x54a124bd  Update_rows: table id 99 flags: STMT_END_F
### UPDATE `test`.`account`
### WHERE
###   @1=1
###   @2='lilei'
###   @3=1000
### SET
###   @1=1
###   @2='lilei'
###   @3=2000
# at 413
#230127 21:22:48 server id 1  end_log_pos 444 CRC32 0x23355595  Xid = 10
COMMIT/*!*/;
# at 444
。。。

能看到里面有具體執(zhí)行的修改偽sql語(yǔ)句以及執(zhí)行時(shí)的相關(guān)情況。

2.1、binlog日志文件恢復(fù)數(shù)據(jù)

用binlog日志文件恢復(fù)數(shù)據(jù)其實(shí)就是回放執(zhí)行之前記錄在binlog文件里的sql，舉一個(gè)數(shù)據(jù)恢復(fù)的例子

# 先執(zhí)行刷新日志的命令生成一個(gè)新的binlog文件mysql-binlog.000008，后面我們的修改操作日志都會(huì)記錄在最新的這個(gè)文件里
flush logs;
# 執(zhí)行兩條插入語(yǔ)句
INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('4', 'zhuge', '666');
INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('5', 'zhuge1', '888');
# 假設(shè)現(xiàn)在誤操作執(zhí)行了一條刪除語(yǔ)句把剛新增的兩條數(shù)據(jù)刪掉了
delete from account where id > 3;

現(xiàn)在需要恢復(fù)被刪除的兩條數(shù)據(jù)，我們先查看binlog日志文件

mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000008

文件內(nèi)容如下：

。。。。。。
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#230127 23:32:24 server id 1  end_log_pos 291 CRC32 0x4528234f  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1674833544/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1342177280/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#230127 23:32:24 server id 1  end_log_pos 345 CRC32 0x7482741d  Table_map: `test`.`account` mapped to number 99
# at 345
#230127 23:32:24 server id 1  end_log_pos 396 CRC32 0x5e443cf0  Write_rows: table id 99 flags: STMT_END_F
### INSERT INTO `test`.`account`
### SET
###   @1=4
###   @2='zhuge'
###   @3=666
# at 396
#230127 23:32:24 server id 1  end_log_pos 427 CRC32 0x8a0d8a3c  Xid = 56
COMMIT/*!*/;
# at 427
#230127 23:32:40 server id 1  end_log_pos 492 CRC32 0x5261a37e  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 492
#230127 23:32:40 server id 1  end_log_pos 564 CRC32 0x01086643  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1674833560/*!*/;
BEGIN
/*!*/;
# at 564
#230127 23:32:40 server id 1  end_log_pos 618 CRC32 0xc26b6719  Table_map: `test`.`account` mapped to number 99
# at 618
#230127 23:32:40 server id 1  end_log_pos 670 CRC32 0x8e272176  Write_rows: table id 99 flags: STMT_END_F
### INSERT INTO `test`.`account`
### SET
###   @1=5
###   @2='zhuge1'
###   @3=888
# at 670
#230127 23:32:40 server id 1  end_log_pos 701 CRC32 0xb5e63d00  Xid = 58
COMMIT/*!*/;
# at 701
#230127 23:34:23 server id 1  end_log_pos 766 CRC32 0xa0844501  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 766
#230127 23:34:23 server id 1  end_log_pos 838 CRC32 0x687bdf88  Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1674833663/*!*/;
BEGIN
/*!*/;
# at 838
#230127 23:34:23 server id 1  end_log_pos 892 CRC32 0x4f7b7d6a  Table_map: `test`.`account` mapped to number 99
# at 892
#230127 23:34:23 server id 1  end_log_pos 960 CRC32 0xc47ac777  Delete_rows: table id 99 flags: STMT_END_F
### DELETE FROM `test`.`account`
### WHERE
###   @1=4
###   @2='zhuge'
###   @3=666
### DELETE FROM `test`.`account`
### WHERE
###   @1=5
###   @2='zhuge1'
###   @3=888
# at 960
#230127 23:34:23 server id 1  end_log_pos 991 CRC32 0x386699fe  Xid = 65
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
。。。。。。

找到兩條插入數(shù)據(jù)的sql，每條sql的上下都有BEGIN和COMMIT，我們找到第一條sql BEGIN前面的文件位置標(biāo)識(shí) at 219(這是文件的位置標(biāo)識(shí))，再找到第二條sql COMMIT后面的文件位置標(biāo)識(shí) at 701

我們可以根據(jù)文件位置標(biāo)識(shí)來(lái)恢復(fù)數(shù)據(jù)，執(zhí)行如下sql：

mysqlbinlog  --no-defaults --start-position=219 --stop-position=701 --database=test D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009 | mysql -uroot -p123456 -v test# 補(bǔ)充一個(gè)根據(jù)時(shí)間來(lái)恢復(fù)數(shù)據(jù)的命令，我們找到第一條sql BEGIN前面的時(shí)間戳標(biāo)記 SET TIMESTAMP=1674833544，再找到第二條sql COMMIT后面的時(shí)間戳標(biāo)記 SET TIMESTAMP=1674833663，轉(zhuǎn)成datetime格式
mysqlbinlog  --no-defaults --start-datetime="2023-1-27 23:32:24" --stop-datetime="2023-1-27 23:34:23" --database=test D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009 | mysql -uroot -p123456 -v test

被刪除數(shù)據(jù)被恢復(fù)！

注意：如果要恢復(fù)大量數(shù)據(jù)，比如程序員經(jīng)常說(shuō)的刪庫(kù)跑路的話題，假設(shè)我們把數(shù)據(jù)庫(kù)所有數(shù)據(jù)都刪除了要怎么恢復(fù)了，如果數(shù)據(jù)庫(kù)之前沒有備份，所有的binlog日志都在的話，就從binlog第一個(gè)文件開始逐個(gè)恢復(fù)每個(gè)binlog文件里的數(shù)據(jù)，這種一般不太可能，因?yàn)閎inlog日志比較大，早期的binlog文件會(huì)定期刪除的，所以一般不可能用binlog文件恢復(fù)整個(gè)數(shù)據(jù)庫(kù)的。

一般我們推薦的是每天(在凌晨后)需要做一次全量數(shù)據(jù)庫(kù)備份，那么恢復(fù)數(shù)據(jù)庫(kù)可以用最近的一次全量備份再加上備份時(shí)間點(diǎn)之后的binlog來(lái)恢復(fù)數(shù)據(jù)。

備份數(shù)據(jù)庫(kù)一般可以用mysqldump 命令工具

mysqldump -u root 數(shù)據(jù)庫(kù)名>備份文件名;   #備份整個(gè)數(shù)據(jù)庫(kù)
mysqldump -u root 數(shù)據(jù)庫(kù)名 表名字>備份文件名;  #備份整個(gè)表mysql -u root test < 備份文件名 #恢復(fù)整個(gè)數(shù)據(jù)庫(kù)，test為數(shù)據(jù)庫(kù)名稱，需要自己先建一個(gè)數(shù)據(jù)庫(kù)test

為什么會(huì)有redo log和binlog兩份日志呢？

因?yàn)樽铋_始 MySQL 里并沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是MyISAM 沒有 crash-safe 的能力，binlog 日志只能用于歸檔。而 InnoDB 是另一個(gè)公司以插件形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以InnoDB 使用另外一套日志系統(tǒng)——也就是 redo log 來(lái)實(shí)現(xiàn) crash-safe 能力。

有了 redo log，InnoDB 就可以保證即使數(shù)據(jù)庫(kù)發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為crash-safe。

3、undo log回滾日志

InnoDB對(duì)undo log文件的管理采用段的方式，也就是回滾段（rollback segment） 。每個(gè)回滾段記錄了 1024 個(gè) undo log segment ，每個(gè)事務(wù)只會(huì)使用一個(gè)undo log segment。

在MySQL5.5的時(shí)候，只有一個(gè)回滾段，那么最大同時(shí)支持的事務(wù)數(shù)量為1024個(gè)。在MySQL 5.6開始，InnoDB支持最大128個(gè)回滾段，故其支持同時(shí)在線的事務(wù)限制提高到了 128*1024 。

innodb_undo_directory：設(shè)置undo log文件所在的路徑。該參數(shù)的默認(rèn)值為"./"，即innodb數(shù)據(jù)文件存儲(chǔ)位置，目錄下ibdata1文件就是undo log存儲(chǔ)的位置。
innodb_undo_logs: 設(shè)置undo log文件內(nèi)部回滾段的個(gè)數(shù)，默認(rèn)值為128。
innodb_undo_tablespaces: 設(shè)置undo log文件的數(shù)量，這樣回滾段可以較為平均地分布在多個(gè)文件中。設(shè)置該參數(shù)后，會(huì)在路徑innodb_undo_directory看到undo為前綴的文件。

undo log日志什么時(shí)候刪除

新增類型的，在事務(wù)提交之后就可以清除掉了。

修改類型的，事務(wù)提交之后不能立即清除掉，這些日志會(huì)用于mvcc。只有當(dāng)沒有事務(wù)用到該版本信息時(shí)才可以清除。

為什么Mysql不能直接更新磁盤上的數(shù)據(jù)而設(shè)置這么一套復(fù)雜的機(jī)制來(lái)執(zhí)行SQL了？

因?yàn)閬?lái)一個(gè)請(qǐng)求就直接對(duì)磁盤文件進(jìn)行隨機(jī)讀寫，然后更新磁盤文件里的數(shù)據(jù)性能可能相當(dāng)差。

因?yàn)榇疟P隨機(jī)讀寫的性能是非常差的，所以直接更新磁盤文件是不能讓數(shù)據(jù)庫(kù)抗住很高并發(fā)的。

Mysql這套機(jī)制看起來(lái)復(fù)雜，但它可以保證每個(gè)更新請(qǐng)求都是更新內(nèi)存BufferPool，然后順序?qū)懭罩疚募?#xff0c;同時(shí)還能保證各種異常情況下的數(shù)據(jù)一致性。

更新內(nèi)存的性能是極高的，然后順序?qū)懘疟P上的日志文件的性能也是非常高的，要遠(yuǎn)高于隨機(jī)讀寫磁盤文件。

正是通過(guò)這套機(jī)制，才能讓我們的MySQL數(shù)據(jù)庫(kù)在較高配置的機(jī)器上每秒可以抗下幾干甚至上萬(wàn)的讀寫請(qǐng)求。

4、錯(cuò)誤日志

Mysql還有一個(gè)比較重要的日志是錯(cuò)誤日志，它記錄了數(shù)據(jù)庫(kù)啟動(dòng)和停止，以及運(yùn)行過(guò)程中發(fā)生任何嚴(yán)重錯(cuò)誤時(shí)的相關(guān)信息。當(dāng)數(shù)據(jù)庫(kù)出現(xiàn)任何故障導(dǎo)致無(wú)法正常使用時(shí)，建議首先查看此日志。

在MySQL數(shù)據(jù)庫(kù)中，錯(cuò)誤日志功能是默認(rèn)開啟的，而且無(wú)法被關(guān)閉。

# 查看錯(cuò)誤日志存放位置
show variables like '%log_error%';

5、通用查詢?nèi)罩?/h3>

通用查詢?nèi)罩居涗浻脩舻乃胁僮?#xff0c;包括啟動(dòng)和關(guān)閉MySQL服務(wù)、所有用戶的連接開始時(shí)間和截止時(shí)間、發(fā)給 MySQL 數(shù)據(jù)庫(kù)服務(wù)器的所有 SQL 指令等，如select、show等，無(wú)論SQL的語(yǔ)法正確還是錯(cuò)誤、也無(wú)論SQL執(zhí)行成功還是失敗，MySQL都會(huì)將其記錄下來(lái)。

通用查詢?nèi)罩居脕?lái)還原操作時(shí)的具體場(chǎng)景，可以幫助我們準(zhǔn)確定位一些疑難問(wèn)題，比如重復(fù)支付等問(wèn)題。

general_log：是否開啟日志參數(shù)，默認(rèn)為OFF，處于關(guān)閉狀態(tài)，因?yàn)殚_啟會(huì)消耗系統(tǒng)資源并且占用磁盤空間。一般不建議開啟，只在需要調(diào)試查詢問(wèn)題時(shí)開啟。

general_log_file：通用查詢?nèi)罩居涗浀奈恢脜?shù)。

show variables like '%general_log%';
# 打開通用查詢?nèi)罩?/span>
SET GLOBAL general_log=on;