最近碰到一個(gè)線上問題，mysqldump 導(dǎo)出數(shù)據(jù)報(bào)錯：

mysqldump: Got error: 1146: 
  Table 'xxx.xxx' doesn't exist 
  when using LOCK TABLES

經(jīng)過分析發(fā)現(xiàn)，報(bào)錯信息中的數(shù)據(jù)庫，所有??表名??都混用了大小寫字母，因?yàn)閯?chuàng)建表之后，系統(tǒng)變量 ??lower_case_table_names?? 的值被從 0 修改為 1，導(dǎo)致刪除這個(gè)數(shù)據(jù)庫時(shí)，每個(gè)表的 ??ibd?? 文件刪除成功，??frm?? 文件刪除失敗。

本文我們就來聊聊這個(gè) mysqldump 問題產(chǎn)生的原因，以及在刪除數(shù)據(jù)庫的過程中，??lower_case_table_names?? 是怎么影響 frm、ibd 文件的刪除邏輯的。

本文內(nèi)容基于 MySQL 5.7.35 源碼，涉及存儲引擎為 InnoDB。

1、問題復(fù)現(xiàn)

我們先通過幾個(gè)步驟，來復(fù)現(xiàn) mysqldump 問題的產(chǎn)生過程。

第 1 步，確認(rèn)系統(tǒng)變量 lower_case_table_names 的值是 0：

MySQL root@localhost>
show variables like 'lower_case_table_names'
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+

第 2 步，創(chuàng)建測試數(shù)據(jù)庫、表：

-- 創(chuàng)建測試數(shù)據(jù)庫 test6
CREATE DATABASE `test6` DEFAULT CHARACTER SET utf8;

-- 創(chuàng)建測試表 Test，不需要插入數(shù)據(jù)，空表即可
CREATE TABLE Test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    i1 int
) ENGINE = InnoDB;

第 3 步，查看 test6 數(shù)據(jù)庫目錄下的文件：

## ls -l 的結(jié)果省略了一些信息，用 ... 表示
[root@VM-24-13-centos test6]$ ls -l
-rw-r----- 1 mysql mysql ... db.opt
-rw-r----- 1 mysql mysql ... Test.frm
-rw-r----- 1 mysql mysql ... Test.ibd

第 4 步，修改 MySQL 配置文件，把系統(tǒng)變量 lower_case_table_names 的值修改為 1，然后重啟 MySQL。

第 5 步，重新連接 MySQL，確認(rèn)系統(tǒng)變量 lower_case_table_names 的值是 1：

MySQL root@localhost>
show variables like 'lower_case_table_names'
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+

第 6 步，在 lower_case_table_names = 1 的場景下，刪除測試庫：

-- 刪除測試數(shù)據(jù)庫 test6
DROP DATABASE test6;

-- 會報(bào)以下錯誤
(1010, "Error dropping database 
    (can't rmdir './test6', errno: 39)")

報(bào)錯信息說明不能刪除 ./test6 目錄，這是因?yàn)?test6 目錄下還有 frm 文件：

## ls -l 的結(jié)果省略了一些信息，用 ... 表示
[root@VM-24-13-centos test6]$ ls -l
-rw-r----- 1 mysql mysql ... Test.frm

從上面的結(jié)果可以看到，db.opt、Test.ibd 都已經(jīng)刪除，只剩下 Test.frm。

InnoDB 刪除表時(shí)，會先把表的元數(shù)據(jù)從 information_schema 庫的 INNODB_SYS_TABLESPACES、INNODB_SYS_TABLES、INNODB_SYS_COLUMNS、INNODB_SYS_INDEXES 等數(shù)據(jù)字典表中刪除，最后才會刪除 ibd 文件。

刪除表的過程中，Test.ibd 文件被刪除了，就說明 Test 表被成功刪除了。Test.frm 文件雖然還在，但已經(jīng)沒有實(shí)際用處了。

此時(shí)，通過 show tables 還能列出測試庫 test6 中的 Test 表：

MySQL root@localhost>
SHOW TABLES FROM test6
+-----------------+
| Tables_in_test6 |
+-----------------+
| Test           |
+-----------------+

show tables 會掃描數(shù)據(jù)庫目錄，獲取其中的 frm 文件名（不含 .frm 后綴），并根據(jù) lower_case_table_names 的值，把 frm 文件名轉(zhuǎn)換為相應(yīng)的大小寫形式，作為該 frm 文件對應(yīng)的表名。

因?yàn)?test6 的數(shù)據(jù)庫目錄中還存在 Test.frm 文件，所以執(zhí)行結(jié)果中能看到 Test 表，但這并不表示 Test 表還存在，通過以下 SQL 可以驗(yàn)證：

MySQL root@localhost>
SELECT COUNT(*)
FROM information_schema.INNODB_SYS_TABLES
WHERE `name` LIKE 'test6/%'
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

從上面的執(zhí)行結(jié)果可以看到，InnoDB 的數(shù)據(jù)字典表中，已經(jīng)沒有測試庫 test6 的表了。

第 7 步，導(dǎo)出數(shù)據(jù)：

[root@VM-24-13-centos test6]#
mysqldump -uroot -p --all-databases > backup.sql
mysqldump: Got error: 1146: 
  Table 'test6.test' doesn't exist 
  when using LOCK TABLES

到這里，我們就已經(jīng)復(fù)現(xiàn)出來 mysqldump 導(dǎo)出數(shù)據(jù)報(bào)錯的問題了。

為什么報(bào)錯信息里的表名不是 Test，而是 test？

這是因?yàn)?lower_case_table_names = 1 時(shí)，MySQL 內(nèi)部會使用小寫形式的表名，具體請看后面關(guān)于 lower_case_table_names 的介紹。

2、解決方案

如果只想臨時(shí)解決 mysqldump 導(dǎo)出數(shù)據(jù)問題，可以通過 --databases 指定需要導(dǎo)出的數(shù)據(jù)庫：

mysqldump -uroot -p --databases db1 > db1.sql

如果想一勞永逸的解決問題，直接把已刪除數(shù)據(jù)庫的殘留目錄刪掉就可以了。

還是以前面的測試數(shù)據(jù)庫 test6 為例，因?yàn)橐呀?jīng)通過 DROP DATABASE 對 test6 進(jìn)行了刪除操作，該數(shù)據(jù)庫中的所有表都已經(jīng)被刪除了。

test6 目錄還在，是因?yàn)楸淼?frm 文件沒有被刪除，這些 frm 文件也沒有實(shí)際用處了，此時(shí)，test6 目錄屬于殘留目錄，可以刪除。

為了保險(xiǎn)起見，可以先把殲留目錄移動到其它目錄下暫存，確認(rèn) MySQL 一切正常之后，再刪除殘留目錄。

3、lower_case_table_names

系統(tǒng)變量 lower_case_table_names 會影響數(shù)據(jù)庫名、數(shù)據(jù)庫目錄名、表名、frm 文件名、ibd 文件名，它有 3 種取值（0、1、2)，接下來詳細(xì)介紹。

（1）lower_case_table_names = 0

lower_case_table_names = 0，Linux、Unix 的默認(rèn)值，表示數(shù)據(jù)庫名、表名區(qū)分大小寫：

• server 層的數(shù)據(jù)庫名 & 目錄名、InnoDB 數(shù)據(jù)字典表中存放的數(shù)據(jù)庫名是CREATE DATABASE 中指定的數(shù)據(jù)庫名。
• frm & ibd 文件名、InnoDB 數(shù)據(jù)字典表中存放的表名是CREATE TABLE 中指定的表名。

lower_case_table_names = 0 時(shí)，創(chuàng)建測試數(shù)據(jù)庫、表：

-- 創(chuàng)建測試數(shù)據(jù)庫
CREATE DATABASE Db_Lower_Case_0 DEFAULT CHARACTER SET utf8;

-- 創(chuàng)建測試表
CREATE TABLE Test_Table_0 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    i1 int
) ENGINE = InnoDB;

查看數(shù)據(jù)庫目錄名、表的 frm、ibd 文件名：

## 查看數(shù)據(jù)庫目錄名
[root@Centos mysql]# ls -l | grep Db_Lower_Case_0
drwxr-x--- 2 mysql mysql ... Db_Lower_Case_0
## 查看表名
[root@Centos mysql]# ls -l Db_Lower_Case_0
-rw-r----- 1 mysql mysql ... db.opt
-rw-r----- 1 mysql mysql ... Test_Table_0.frm
-rw-r----- 1 mysql mysql ... Test_Table_0.ibd

server 層通過表名去 InnoDB 中查找對應(yīng)的表時(shí)，也會區(qū)分大小寫：

MySQL root@localhost>
SELECT COUNT(*) FROM Test_Table_0
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

MySQL root@localhost>
SELECT COUNT(*) FROM test_table_0
(1146, "Table 'Db_Lower_Case_0.test_table_0' doesn't exist")

MySQL root@localhost>
SELECT COUNT(*) FROM Test_table_0
(1146, "Table 'Db_Lower_Case_0.Test_table_0' doesn't exist")

從示例 SQL 可以看到，只有指定正確的大小寫，SQL 才能執(zhí)行成功，否則都會報(bào)錯說表不存在。

通過以下 SQL 也可以驗(yàn)證，存放在 InnoDB 數(shù)據(jù)字典中的數(shù)據(jù)庫名、表名是 CREATE DATABASE、CREATE TABLE 中指定的數(shù)據(jù)庫名、表名：

MySQL root@localhost>
SELECT * FROM information_schema.INNODB_SYS_TABLES
WHERE name LIKE 'Db_Lower_Case_0%'\G
***************************[ 1. row ]***************************
TABLE_ID      | 151
NAME          | Db_Lower_Case_0/Test_Table_0
FLAG          | 33
N_COLS        | 5
SPACE         | 161
FILE_FORMAT   | Barracuda
ROW_FORMAT    | Dynamic
ZIP_PAGE_SIZE | 0
SPACE_TYPE    | Single

（2）lower_case_table_names = 1

lower_case_table_names = 1，Windows 的默認(rèn)值，表示數(shù)據(jù)庫名、表名都不區(qū)分大小寫：

• server 層的數(shù)據(jù)庫名 & 目錄名、InnoDB 數(shù)據(jù)字典表中存放的數(shù)據(jù)庫名是CREATE DATABASE 中指定數(shù)據(jù)庫名的小寫形式。
• frm & ibd 文件名、 InnoDB 數(shù)據(jù)字典表中存放的表名是CREATE TABLE 中指定表名的小寫形式。

lower_case_table_names = 1 時(shí)，創(chuàng)建測試數(shù)據(jù)庫、表：

-- 創(chuàng)建測試數(shù)據(jù)庫
CREATE DATABASE Db_Lower_Case_1 DEFAULT CHARACTER SET utf8;

-- 創(chuàng)建測試表
CREATE TABLE Test_Table_1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    i1 int
) ENGINE = InnoDB;

查看數(shù)據(jù)庫目錄名、表的 frm、ibd 文件名，全部被轉(zhuǎn)換為小寫了：

# 查看數(shù)據(jù)庫目錄名
[root@Centos mysql]$ ls -l | grep db_lower_case_1
drwxr-x--- 2 mysql mysql ... db_lower_case_1
# 查看表名
[root@Centos mysql]# ls -l db_lower_case_1
-rw-r----- 1 mysql mysql ... db.opt
-rw-r----- 1 mysql mysql ... test_table_1.frm
-rw-r----- 1 mysql mysql ... test_table_1.ibd

server 層通過表名去 InnoDB 查找對應(yīng)的表之前，也會把表名轉(zhuǎn)換為小寫形式：

MySQL root@localhost:Db_Lower_Case_1>
SELECT COUNT(*) FROM Test_Table_1
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

MySQL root@localhost:Db_Lower_Case_1>
SELECT COUNT(*) FROM test_table_1
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

從示例 SQL 可以看到，表名包含大小寫字母、全部是小寫字母，SQL 都能執(zhí)行成功。

通過以下 SQL 也可以驗(yàn)證，存放在 InnoDB 數(shù)據(jù)字典中的數(shù)據(jù)庫名、表名都轉(zhuǎn)換為小寫形式了：

MySQL root@localhost>
SELECT * FROM information_schema.INNODB_SYS_TABLES
WHERE name LIKE 'Db_Lower_Case_1%'\G
***************************[ 1. row ]***************************
TABLE_ID      | 152
NAME          | db_lower_case_1/test_table_1
FLAG          | 33
N_COLS        | 5
SPACE         | 163
FILE_FORMAT   | Barracuda
ROW_FORMAT    | Dynamic
ZIP_PAGE_SIZE | 0
SPACE_TYPE    | Single

（3）lower_case_table_names = 2

lower_case_table_names = 2，這是 MacOS 的默認(rèn)值，這個(gè)選項(xiàng)值的情況比前面兩種復(fù)雜一些：

• 數(shù)據(jù)庫名、數(shù)據(jù)庫目錄名是CREATE DATABASE 中指定的數(shù)據(jù)庫名。
• 表的 frm 文件名是CREATE TABLE 中指定的表名。
• 表的 ibd 文件名是CREATE TABLE 中指定表名的小寫形式。
• InnoDB 數(shù)據(jù)字典表中存放的數(shù)據(jù)庫名、表名小寫形式。

?上面 4 條可以歸納為 2 條：

• server 層使用 CREATE DATABASE、CREATE TABLE 中指定的數(shù)據(jù)庫名、表名。
• InnoDB 使用 CREATE DATABASE、CREATE TABLE 中指定數(shù)據(jù)庫名、表名的小寫形式。

lower_case_table_names = 2 時(shí)，創(chuàng)建測試數(shù)據(jù)庫、表：

-- 創(chuàng)建測試數(shù)據(jù)庫
CREATE DATABASE Db_Lower_Case_2 DEFAULT CHARACTER SET utf8;

-- 創(chuàng)建測試表
CREATE TABLE Test_Table_2 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    i1 int
) ENGINE = InnoDB;

查看數(shù)據(jù)庫目錄名、表的 frm、ibd 文件名：

# 查看數(shù)據(jù)庫目錄名
[test@MacOS data]$ ls -l | grep Db_Lower_Case_2
drwxr-x---  5 test  staff ... Db_Lower_Case_2
# 查看表名
[test@MacOS data]$ ls -l Db_Lower_Case_2
-rw-r-----  1 test  staff ... db.opt
-rw-r-----  1 test  staff ... Test_Table_2.frm
-rw-r-----  1 test  staff ... test_table_2.ibd

數(shù)據(jù)庫目錄由 server 層創(chuàng)建，目錄名是 CREATE DATABASE 中指定的數(shù)據(jù)庫名。

frm 文件由 server 層創(chuàng)建，文件名是 CREATE TABLE 中指定的表名。

ibd 文件由 InnoDB 創(chuàng)建，文件名是 CREATE TABLE 中指定表名的小寫形式。

server 層通過表名去 InnoDB 查找對應(yīng)的表之前，也會把表名轉(zhuǎn)換為小寫形式：

MySQL root@localhost:Db_Lower_Case_2>
SELECT COUNT(*) FROM Test_Table_2
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

MySQL root@localhost:Db_Lower_Case_2>
SELECT COUNT(*) FROM test_table_2
+----------+
| COUNT(*) |
+----------+
| 0        |
+----------+

從示例 SQL 可以看到，表名包含大小寫字母、全部是小寫字母，SQL 都能執(zhí)行成功。

通過以下 SQL 也可以驗(yàn)證，存放在 InnoDB 數(shù)據(jù)字典中的數(shù)據(jù)庫名、表名都轉(zhuǎn)換為小寫形式了：

MySQL root@localhost>
SELECT * FROM information_schema.INNODB_SYS_TABLES
WHERE name LIKE 'Db_Lower_Case_2%'\G
***************************[ 1. row ]***************************
TABLE_ID      | 236
NAME          | db_lower_case_2/test_table_2
FLAG          | 33
N_COLS        | 5
SPACE         | 458
FILE_FORMAT   | Barracuda
ROW_FORMAT    | Dynamic
ZIP_PAGE_SIZE | 0
SPACE_TYPE    | Single

4、為什么 frm 文件會刪除失??？

我們先來回顧一下 frm 文件刪除失敗的場景：

• lower_case_table_names = 0 時(shí)，創(chuàng)建了數(shù)據(jù)庫和表（表名包含大小寫字母）。
• lower_case_table_names = 1 時(shí)，刪除數(shù)據(jù)庫，ibd 文件刪除成功，frm 文件刪除失敗。

我們還是以 1. 問題復(fù)現(xiàn)中的測試數(shù)據(jù)庫、表為例，lower_case_table_names = 0 時(shí)，創(chuàng)建測試數(shù)據(jù)庫、表之后，frm、ibd 文件如下：

[root@VM-24-13-centos test6]$ ls -l
-rw-r----- 1 mysql mysql ... db.opt
-rw-r----- 1 mysql mysql ... Test.frm
-rw-r----- 1 mysql mysql ... Test.ibd

3.1 lower_case_table_names = 1 小節(jié)介紹過，lower_case_table_names 修改為 1 之后，server 層通過表名去 InnoDB 查找對應(yīng)的表之前，會把表名轉(zhuǎn)換為小寫形式。

接下來，我們先來看看刪除數(shù)據(jù)庫的主要邏輯：

第 1 步，遍歷待刪除數(shù)據(jù)庫的目錄，找到該目錄下所有的 frm 文件，把 frm 文件名（不含 .frm? 后綴）轉(zhuǎn)換為小寫?，作為表名。

以 test6 數(shù)據(jù)庫為例：

遍歷 test6 目錄，找到該目錄下的 frm 文件，該目錄下只有一個(gè) frm 文件：Test.frm。

把 frm 文件名轉(zhuǎn)換為小寫，得到表名 test。

第 2 步，執(zhí)行第一種刪表邏輯：以第 1 步中根據(jù) frm 文件名得到的表名執(zhí)行刪表操作，由 InnoDB 和 server 層共同完成，InnoDB 負(fù)責(zé)刪除表的元數(shù)據(jù)和 ibd 文件，server 層負(fù)責(zé)刪除 frm 文件。

遍歷第 1 步得到的表名?，加上 .frm 后綴，得到 frm 文件名，然后根據(jù) frm 文件是否存在執(zhí)行不同的邏輯。

如果 frm 文件存在?，則調(diào)用 InnoDB 的刪表方法，從 InnoDB 數(shù)據(jù)字典表中刪除該表的元數(shù)據(jù)，以及刪除 ibd 文件。

InnoDB 刪表成功之后，server 層會刪除該表的 frm 文件；InnoDB 刪表失敗，server 層會記錄第一種刪表邏輯中存在刪除失敗的表。

如果 frm 文件不存在，不會調(diào)用 InnoDB 的刪表方法，server 層也會記錄第一種刪表邏輯中存在刪除失敗的表。

以測試數(shù)據(jù)庫 test6 為例：

第 1 步得到的表名為 test?，加上 .frm? 后綴，得到文件名：test.frm。

Linux 系統(tǒng)的文件名是區(qū)分大小寫的，test6 目錄下只存在 Test.frm，用 test.frm 無法匹配 Test.frm 文件，也就是說，test.frm 文件不存在。

因?yàn)?test6 目錄下不存在 test.frm 文件，server 層會記錄第一種刪表邏輯中存在刪除失敗的表。

第 3 步，判斷第 2 步是否存在刪除失敗的表。

如果存在刪除失敗的表，會執(zhí)行第二種?刪表邏輯，由 InnoDB 獨(dú)自完成：從 ?information_schema.INNODB_SYS_TABLES? 中獲取要刪除的數(shù)據(jù)庫中的表名，逐個(gè)執(zhí)行刪表操作。

從 INNODB_SYS_TABLES 中獲取表名，以及刪表操作都在 InnoDB 中進(jìn)行，不會受到 lower_case_table_names 的影響。

以 test6 數(shù)據(jù)庫為例，第二種刪表邏輯如下：

① 從 INNODB_SYS_TABLES 表獲取 test6 數(shù)據(jù)庫中未被刪除的第一個(gè)表名。

② 把該表的元數(shù)據(jù)信息從對應(yīng)的數(shù)據(jù)字典表中刪除。

③ 刪除該表的 ibd 文件。

循環(huán) ① ~ ③，直到 test6 中的所有表都被刪除之后，第二種刪表邏輯結(jié)束。

介紹完刪除數(shù)據(jù)庫的邏輯，我們來總結(jié)一下：為什么 frm 文件會刪除失??？

lower_case_table_names 的值從 0 修改為 1 之后，第一種刪表邏輯，因?yàn)楸砻拇笮憜栴}，導(dǎo)致找不到 frm 文件，執(zhí)行失敗，轉(zhuǎn)而執(zhí)行第二種刪表邏輯。

第二種刪表邏輯，只會從 InnoDB 數(shù)據(jù)字典表中刪除表的元數(shù)據(jù)，然后刪除表的 ibd 文件，不包含刪除 frm 文件的操作，frm 文件也就不會被刪了。

5、為什么 ibd 文件能刪除成功？

通過 4. 為什么 frm 文件會刪除失?。啃」?jié)的介紹，我們可以看到，第一種刪表邏輯，由于找不到表的 frm 文件，不會觸發(fā) InnoDB 的刪表操作，也就不會刪除 ibd 文件了。

第二種刪表邏輯，先從 INNODB_SYS_TABLES 表中獲取表名，然后通過表名找表對應(yīng)的表空間，表空間信息中包含從 INNODB_SYS_DATAFILES 表中讀取到的 ibd 文件路徑。

刪除 ibd 文件時(shí)，會從表空間信息中獲取 ibd 文件路徑。

ibd  文件能刪除成功，取決于以下 2 個(gè)因素：

• 第二種刪表邏輯，從INNODB_SYS_TABLES 中獲取表名之后，不會進(jìn)行大小寫轉(zhuǎn)換（也就是不會受到 lower_case_table_names 的影響），而是直接以獲取到的表名，加載表的元數(shù)據(jù)信息。
• 創(chuàng)建表時(shí)寫入INNODB_SYS_DATAFILES 表中的 ibd 文件路徑，不管系統(tǒng)變量 lower_case_table_names 的值修改成什么，該表中存放的 ibd 文件路徑都不會變。

6、總結(jié)

如果程序代碼中已經(jīng)使用了某個(gè)數(shù)據(jù)庫的表，或者 MySQL 實(shí)例已經(jīng)在線上正式使用，最好不要修改 lower_case_table_names 的值，否則，可能會造成意想不到的問題。

本文轉(zhuǎn)載自微信公眾號「一樹一溪」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請聯(lián)系一樹一溪公眾號。