據(jù)導(dǎo)出全攻略：從mysqldump到CSV的實(shí)戰(zhàn)避坑指南)
1. 先把“導(dǎo)出”這件事想清楚你要的是數(shù)據(jù)還是數(shù)據(jù)庫(kù)很多人一提“MySQL 導(dǎo)出數(shù)據(jù)”第一反應(yīng)就是打開(kāi)命令行敲一句mysqldump或者右鍵點(diǎn)一下“導(dǎo)出”然后拿著生成的 SQL 文件到處跑。這個(gè)動(dòng)作本身沒(méi)錯(cuò)但作為實(shí)際處理過(guò)大量數(shù)據(jù)遷移、備份恢復(fù)、跨環(huán)境數(shù)據(jù)同步的人我得先潑一盆冷水導(dǎo)出從來(lái)不是一個(gè)單一動(dòng)作它背后對(duì)應(yīng)的是完全不同的訴求。訴求沒(méi)想清楚選錯(cuò)工具和參數(shù)后面全是在給自己埋雷。我習(xí)慣把導(dǎo)出場(chǎng)景拆成三類第一類是結(jié)構(gòu)數(shù)據(jù)整體備份目標(biāo)是災(zāi)難恢復(fù)或環(huán)境復(fù)制這種情況要求“導(dǎo)出的東西再導(dǎo)回去能原樣跑起來(lái)”索引、觸發(fā)器、存儲(chǔ)過(guò)程、外鍵一個(gè)都不能少第二類是純數(shù)據(jù)搬運(yùn)比如從正式區(qū)抽一部分?jǐn)?shù)據(jù)到測(cè)試區(qū)讓開(kāi)發(fā)同學(xué)有真實(shí)的樣本數(shù)據(jù)可用這時(shí)往往只需要表結(jié)構(gòu)和數(shù)據(jù)甚至只要部分字段第三類是對(duì)外交付比如把某些表導(dǎo)成 CSV 給業(yè)務(wù)運(yùn)營(yíng)做分析或者導(dǎo)成 Excel 給財(cái)務(wù)核對(duì)這類場(chǎng)景關(guān)心的是“人能不能方便地打開(kāi)”而不是“數(shù)據(jù)庫(kù)能不能直接恢復(fù)”。這三種訴求對(duì)應(yīng)的技術(shù)選型完全不同。第一種我會(huì)優(yōu)先考慮mysqldump它最穩(wěn)生態(tài)最成熟幾乎所有 MySQL 版本都自帶第二種可以考慮mysqldump加參數(shù)過(guò)濾也可以考慮圖形化工具按查詢結(jié)果導(dǎo)出第三種基本上就是SELECT INTO OUTFILE或者圖形化工具的 CSV/Excel 導(dǎo)出功能壓根不需要 SQL 文件。不少剛?cè)胄械耐瑢W(xué)會(huì)覺(jué)得“導(dǎo)出數(shù)據(jù)”就是把表里的記錄寫到一個(gè)文件里但實(shí)際操作中翻車最多的恰恰就是這個(gè)認(rèn)知偏差。舉個(gè)我親眼見(jiàn)過(guò)的例子同事把生產(chǎn)庫(kù)用mysqldump導(dǎo)了一份 SQL拿到測(cè)試庫(kù)執(zhí)行結(jié)果發(fā)現(xiàn)測(cè)試庫(kù)的存儲(chǔ)過(guò)程、自定義函數(shù)全部丟失。為什么會(huì)丟因?yàn)槟J(rèn)的mysqldump實(shí)際上不會(huì)導(dǎo)出存儲(chǔ)過(guò)程和函數(shù)需要顯式加上--routines參數(shù)。類似這種細(xì)節(jié)還有很多后面我會(huì)把參數(shù)和行為之間的對(duì)應(yīng)關(guān)系逐個(gè)拆開(kāi)講。一句話先總結(jié)我這幾年的經(jīng)驗(yàn)導(dǎo)出的本質(zhì)是“根據(jù)消費(fèi)方的需求把數(shù)據(jù)庫(kù)轉(zhuǎn)成另一種形態(tài)”。消費(fèi)方是數(shù)據(jù)庫(kù)實(shí)例你要導(dǎo) SQL消費(fèi)方是分析師你要導(dǎo) CSV消費(fèi)方是另一個(gè)團(tuán)隊(duì)你要導(dǎo)他們能直接索引的結(jié)構(gòu)化文件。搞清楚消費(fèi)方再?zèng)Q定工具和參數(shù)這篇博文后面的所有內(nèi)容才有意義。2. 命令行是第一選擇mysqldump 的參數(shù)組合與取舍邏輯2.1 結(jié)構(gòu)、數(shù)據(jù)、例程一個(gè)都不能少說(shuō)到命令行導(dǎo)出mysqldump是當(dāng)之無(wú)愧的主力。它是 MySQL 官方自帶的邏輯備份工具生成的產(chǎn)物是一堆 SQL 語(yǔ)句在目標(biāo)庫(kù)執(zhí)行一遍就能重建所有對(duì)象和數(shù)據(jù)。它最大的優(yōu)勢(shì)是與存儲(chǔ)引擎無(wú)關(guān)、與平臺(tái)無(wú)關(guān)導(dǎo)出的文件到哪都能用所以跨版本、跨環(huán)境、跨操作系統(tǒng)的遷移場(chǎng)景里它幾乎是唯一解。先說(shuō)我最常用的一套完整備份命令mysqldump -h 127.0.0.1 -P 3306 -u root -p \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --databases db_name db_name.sql參數(shù)一個(gè)個(gè)說(shuō)。--single-transaction是 InnoDB 下最重要的參數(shù)它利用事務(wù)的快照讀特性在不鎖表的情況下拿到一致性的數(shù)據(jù)快照。注意這個(gè)參數(shù)對(duì) MyISAM 表不生效所以如果庫(kù)里還有 MyISAM 表--single-transaction并不能保證一致性這種情況下就需要乖乖停機(jī)或者接受數(shù)據(jù)不完全一致的風(fēng)險(xiǎn)。這也是為什么我接手過(guò)的項(xiàng)目我都會(huì)強(qiáng)烈建議把核心業(yè)務(wù)表全部轉(zhuǎn)成 InnoDB不只是為了事務(wù)更是為了能在線備份。--routines導(dǎo)出存儲(chǔ)過(guò)程和函數(shù)--triggers導(dǎo)出觸發(fā)器--events導(dǎo)出定時(shí)任務(wù)。這三個(gè)參數(shù)默認(rèn)都是不開(kāi)啟的如果你做的是整體遷移忘了加--routines導(dǎo)出的文件在新環(huán)境里就會(huì)靜默缺少所有存儲(chǔ)過(guò)程這種問(wèn)題排查起來(lái)非常惡心因?yàn)闆](méi)有報(bào)錯(cuò)只有等程序跑起來(lái)才發(fā)現(xiàn)函數(shù)不存在。我的習(xí)慣是把這三個(gè)參數(shù)固化成一組完整的備份命令而不是每一次都臨時(shí)拼參數(shù)。把下面這一段存成mysql_full_backup.sh里的核心調(diào)用平時(shí)基本不會(huì)忘MYSQL_CMDmysqldump -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST --single-transaction --routines --triggers --events --set-gtid-purgedOFF為什么要加--set-gtid-purgedOFF如果你用的是 MySQL 5.6 以上版本且開(kāi)啟了 GTID 模式dump 出來(lái)的文件里默認(rèn)會(huì)帶上SET GLOBAL.GTID_PURGED...這一句。這句在導(dǎo)入到另一臺(tái)實(shí)例時(shí)經(jīng)常會(huì)因?yàn)槟繕?biāo)庫(kù)的 GTID 狀態(tài)不一致而報(bào)錯(cuò)導(dǎo)致導(dǎo)入失敗。很多新手在自己本機(jī)導(dǎo)入生產(chǎn)庫(kù)的備份時(shí)報(bào)錯(cuò)十有八九就是這個(gè)問(wèn)題。加上這個(gè)參數(shù)讓 dump 文件不包含 GTID 信息導(dǎo)入時(shí)反而少很多麻煩。2.2 只導(dǎo)部分?jǐn)?shù)據(jù)的正確姿勢(shì)整體備份只需要一條命令但現(xiàn)實(shí)里更常見(jiàn)的需求是“只導(dǎo)一部分”。比如我從正式區(qū)導(dǎo)出某些業(yè)務(wù)表給測(cè)試區(qū)通常只要最近三個(gè)月的數(shù)據(jù)或者只要某個(gè)用戶維度的數(shù)據(jù)。mysqldump本身就支持這個(gè)用--where參數(shù)mysqldump -u root -p dba_test order_info \ --wherecreated_at 2024-01-01 AND created_at 2024-04-01 \ order_info_2024_q1.sql這里有個(gè)細(xì)節(jié)要注意--where參數(shù)應(yīng)該放在庫(kù)名表名之后否則某些版本會(huì)報(bào)參數(shù)解析錯(cuò)誤。還有--where里的條件值如果包含空格或特殊字符需要用引號(hào)把整個(gè)條件包裹起來(lái)。我見(jiàn)過(guò)有人圖省事直接寫--whereid1000沒(méi)加引號(hào)結(jié)果 shell 解釋的時(shí)候把當(dāng)成輸入重定向符生成的文件是空的排查了半天才發(fā)現(xiàn)是符號(hào)被吞了。所以養(yǎng)成習(xí)慣where 條件一定加單引號(hào)。--where只導(dǎo)部分行那如果我只想導(dǎo)表結(jié)構(gòu)不要數(shù)據(jù)呢用--no-data。反過(guò)來(lái)--no-create-info是只導(dǎo)數(shù)據(jù)不導(dǎo)建表語(yǔ)句。這兩個(gè)參數(shù)組合起來(lái)非常靈活比如我要把一個(gè)表的數(shù)據(jù)從正式區(qū)搬到測(cè)試區(qū)目標(biāo)表已經(jīng)提前建好了結(jié)構(gòu)那么只導(dǎo)數(shù)據(jù)即可mysqldump -u root -p dba_test order_info \ --no-create-info \ --wherestatus 1 \ order_info_data.sql2.3 大表導(dǎo)出時(shí)的 IO 與鎖問(wèn)題大表導(dǎo)出重點(diǎn)考慮兩件事會(huì)不會(huì)長(zhǎng)時(shí)間占用資源、會(huì)不會(huì)長(zhǎng)時(shí)間持有鎖。先說(shuō)鎖。剛才提到的--single-transaction是通過(guò) InnoDB 的 MVCC 機(jī)制拿快照理論上導(dǎo)出一致性數(shù)據(jù)不需要鎖表。但有一個(gè)前提導(dǎo)出的過(guò)程中不能有 DDL 操作。因?yàn)?MySQL 的 DDL 會(huì)觸發(fā)隱式提交極有可能打斷事務(wù)快照的一致性造成 dump 中途報(bào)ERROR 1412: Table definition has changed, please retry transaction之類的錯(cuò)誤。所以即使是--single-transaction保護(hù)下的在線導(dǎo)出我也建議在業(yè)務(wù)低峰期執(zhí)行并且盡量從只讀從庫(kù)導(dǎo)出這是最穩(wěn)妥的做法。再說(shuō) IO。導(dǎo)出超大表上億行時(shí)mysqldump客戶端與服務(wù)端之間的數(shù)據(jù)傳輸會(huì)占用不少帶寬和 CPU如果應(yīng)用和數(shù)據(jù)庫(kù)在同一臺(tái)機(jī)器上還會(huì)互相搶占資源。我的做法是盡量把導(dǎo)出操作放到單獨(dú)的執(zhí)行機(jī)上去跑避免直接在數(shù)據(jù)庫(kù)宿主機(jī)上敲命令同時(shí)用--compress參數(shù)在傳輸過(guò)程中壓縮數(shù)據(jù)減少網(wǎng)絡(luò)開(kāi)銷。--compress是在 client 和 server 之間壓縮不是把生成的文件壓縮這一點(diǎn)要分清。如果需要最終產(chǎn)物也是壓縮包可以用管道把輸出直接交給 gzipmysqldump -u root -p dba_test order_info --single-transaction | gzip order_info.sql.gz這一招在生產(chǎn)環(huán)境非常實(shí)用SQL 文本文件的壓縮率通常在 10:1 以上一個(gè) 10GB 的庫(kù)導(dǎo)出來(lái)可能只有 1GB 左右傳輸和存儲(chǔ)壓力都小很多。還有一個(gè)參數(shù)容易被忽略--max-allowed-packet。默認(rèn)值通常是 64MB如果在表里存了大字段比如 BLOB、TEXT單個(gè) SQL 語(yǔ)句可能超過(guò)這個(gè)上限導(dǎo)出時(shí)一切正常導(dǎo)入時(shí)報(bào)packet too large。這種情況在導(dǎo)出的命令里加--max-allowed-packet1G注意是在 mysqldump 命令里不是 mysql 客戶端命令里導(dǎo)出的文件頭部會(huì)生成對(duì)應(yīng)的SET GLOBAL max_allowed_packet...語(yǔ)句導(dǎo)入時(shí)才能順利吞下大包。3. 只導(dǎo)數(shù)據(jù)不導(dǎo)結(jié)構(gòu)SELECT INTO OUTFILE 與 CSV 的邊界3.1 什么時(shí)候該用 SELECT INTO OUTFILEmysqldump生成的是 SQL 文件給數(shù)據(jù)庫(kù)用很合適但給人和常見(jiàn)辦公軟件用就很別扭。比如業(yè)務(wù)方要一份用戶訂單明細(xì)他們不會(huì)去導(dǎo)入 SQL他們只想拿到一個(gè) CSV 或 Excel雙擊就能打開(kāi)。這種場(chǎng)景SELECT INTO OUTFILE就是最直接的手段。基本語(yǔ)法SELECT id, user_id, order_amount, created_at INTO OUTFILE /var/lib/mysql-files/orders_2024.csv FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n FROM order_info WHERE created_at 2024-01-01 AND created_at 2024-04-01;這個(gè)寫法的思路是MySQL 服務(wù)端把查詢結(jié)果直接寫到服務(wù)器本地文件不需要經(jīng)過(guò)客戶端網(wǎng)絡(luò)傳輸。所以它有幾個(gè)特性決定了適用場(chǎng)景第一文件寫在數(shù)據(jù)庫(kù)服務(wù)器本地不是你的電腦上。很多人第一次用這個(gè)功能明明執(zhí)行成功了在自己電腦上找文件找半天找不到然后才反應(yīng)過(guò)來(lái)文件在服務(wù)器上。如果是云數(shù)據(jù)庫(kù)RDS 之類很多情況下這個(gè)功能根本沒(méi)有開(kāi)放因?yàn)镮NTO OUTFILE會(huì)往數(shù)據(jù)庫(kù)主機(jī)磁盤上寫文件云廠商出于安全考慮默認(rèn)禁用。第二它是純數(shù)據(jù)導(dǎo)出不帶任何建表語(yǔ)句只會(huì)把查詢結(jié)果的行按你指定的格式輸出。字段分隔符、行分隔符、字段包裹符都靠自己定義這也是 CSV 標(biāo)準(zhǔn)格式的做法。3.2 secure-file-priv 是繞不開(kāi)的坎第一次用SELECT INTO OUTFILE的人大概率會(huì)遇到一個(gè)報(bào)錯(cuò)ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement這是 MySQL 5.7 之后引入的安全機(jī)制限制了INTO OUTFILE和LOAD DATA INFILE的可寫目錄。默認(rèn)情況下它只允許寫入一個(gè)由secure_file_priv指定的目錄查看當(dāng)前配置SHOW VARIABLES LIKE secure_file_priv;如果結(jié)果是/var/lib/mysql-files/那就表示只能寫在這個(gè)目錄下。如果你用的是自己安裝的 MySQL想放開(kāi)或者改目錄可以在配置文件my.cnf的[mysqld]段里設(shè)置[mysqld] secure_file_priv/tmp/mysql_exports設(shè)置完重啟 MySQL 服務(wù)再把導(dǎo)出路徑改成你自己的目錄即可。但如果你用的是云數(shù)據(jù)庫(kù)這個(gè)參數(shù)通常是改不了的所以我一般會(huì)先在本地建一個(gè)“中轉(zhuǎn)庫(kù)”把云上數(shù)據(jù)用mysqldump導(dǎo)到本地再在本地 MySQL 里執(zhí)行SELECT INTO OUTFILE。繞是繞一點(diǎn)但這是云環(huán)境下最穩(wěn)妥的路徑。3.3 分隔符選擇里的隱形坑CSV 的分隔符不是隨便選的。字段值里如果包含逗號(hào)、換行、雙引號(hào)直接按最簡(jiǎn)單的方式拼出來(lái)的 CSV 在 Excel 里一定會(huì)錯(cuò)位。所以要用ENCLOSED BY 把每個(gè)字段用雙引號(hào)包起來(lái)Excel 才能正確識(shí)別包含逗號(hào)的字段。如果你自己寫腳本去解析這些 CSV也務(wù)必要做引號(hào)配對(duì)處理不能簡(jiǎn)單按逗號(hào) split。還有換行符。Linux 下 LINES TERMINATED BY \n 生成的 CSV在 Windows 的 Excel 里打開(kāi)時(shí)能夠識(shí)別但有些老版本的 Excel 會(huì)把換行符解析成兩點(diǎn)之間的分割錯(cuò)亂。更兼容的做法是用\r\n也就是 Windows 風(fēng)格的換行符。我一般統(tǒng)一用\r\n這樣在 Windows 和 macOS 下打開(kāi)都不會(huì)出問(wèn)題。SELECT * INTO OUTFILE /var/lib/mysql-files/orders_2024_win.csv FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \r\n FROM order_info;另外一個(gè)容易被忽略的是字符集。默認(rèn)導(dǎo)出的 CSV 是 UTF-8 編碼Excel 直接雙擊打開(kāi) UTF-8 無(wú) BOM 的文件時(shí)中文會(huì)亂碼。解決辦法有兩個(gè)一是用 WPS 或者 Excel 的文本導(dǎo)入向?qū)нx擇 UTF-8 編碼再打開(kāi)二是導(dǎo)出的文件里在最前面塞一個(gè) BOM 頭我通常的做法是導(dǎo)出后在服務(wù)器上用sed給文件開(kāi)頭加一個(gè) BOMsed -i 1s/^/\xef\xbb\xbf/ /var/lib/mysql-files/orders_2024_win.csv這樣 Excel 雙擊打開(kāi)就是正常中文。這個(gè)小細(xì)節(jié)很多教程不會(huì)講但實(shí)際交付 CSV 給不懂技術(shù)的同事時(shí)這一步能省掉大量“誒怎么亂碼了”的溝通成本。4. 圖形化工具權(quán)限與控制力從“右鍵導(dǎo)出”到“確定性導(dǎo)出”4.1 Navicat、DBeaver、MySQL Workbench 的導(dǎo)出邏輯差異很多人習(xí)慣用圖形化工具做導(dǎo)出確實(shí)方便點(diǎn)幾下就完事。但不同工具導(dǎo)出的產(chǎn)物在細(xì)節(jié)上有很大差別我在這上面翻過(guò)車。先說(shuō)Navicat。它提供兩種導(dǎo)出形式一種是導(dǎo)出 SQL 文件里面包含建表語(yǔ)句和 INSERT 語(yǔ)句另一種是導(dǎo)出為其他格式CSV、Excel、JSON 等。導(dǎo)出 SQL 時(shí)它有“創(chuàng)建表結(jié)構(gòu)”和“包含數(shù)據(jù)”兩個(gè)勾選項(xiàng)默認(rèn)全選。有一個(gè)容易忽略的選項(xiàng)是“每次插入的行數(shù)”默認(rèn)可能是 100 行甚至更少這意味著一個(gè)十萬(wàn)行的表會(huì)拆成一千條 INSERT 語(yǔ)句。這種文件在導(dǎo)入時(shí)執(zhí)行效率非常低如果把 batch size 調(diào)大比如 1000 或 2000導(dǎo)入性能能提升一個(gè)數(shù)量級(jí)。我一般會(huì)調(diào)成 1000 左右太大容易觸發(fā)max_allowed_packet的限制太小導(dǎo)入太慢1000 是一個(gè)平衡的數(shù)值。再說(shuō)DBeaver。它的“導(dǎo)出數(shù)據(jù)”功能非常靈活可以基于當(dāng)前查詢結(jié)果直接導(dǎo)出支持 SQL 文件、CSV、Excel、JSON 等多種格式。但因?yàn)樘`活導(dǎo)致新手容易踩一個(gè)坑DBeaver 導(dǎo)出 Excel 時(shí)默認(rèn)是一個(gè)表一個(gè) Sheet如果查詢結(jié)果里有大量中文或者特殊字符導(dǎo)出過(guò)程中偶爾會(huì)出現(xiàn)編碼問(wèn)題。我的經(jīng)驗(yàn)是DBeaver 導(dǎo)出前先確認(rèn)“編碼”下拉框選的是 UTF-8。MySQL Workbench的導(dǎo)出能力其實(shí)被很多人低估了。它的 Data Export 功能支持選擇多個(gè) Schema、多個(gè)表也能選擇“僅結(jié)構(gòu)”“結(jié)構(gòu)和數(shù)據(jù)”“僅數(shù)據(jù)”三種模式。它的導(dǎo)出的 SQL 文件里會(huì)自動(dòng)加入DROP TABLE IF EXISTS這樣的語(yǔ)句所以導(dǎo)入到已有同名的目標(biāo)庫(kù)時(shí)會(huì)先刪掉舊表再建新表。這本來(lái)是方便但如果目標(biāo)庫(kù)里有你不希望被覆蓋的表而你又只勾選了某幾張表導(dǎo)出一旦不注意選錯(cuò)范圍后果很嚴(yán)重。所以在 Workbench 里點(diǎn)擊 Start Export 之前我一定會(huì)把“Selected Tables”和“Export to Self-Contained File”這兩處逐字檢查一遍。4.2 圖形化導(dǎo)出最大的問(wèn)題結(jié)果不可重現(xiàn)圖形化工具方便是方便但最大的問(wèn)題在于導(dǎo)出過(guò)程的參數(shù)不透明。你這次點(diǎn)了 A、B、C 三個(gè)選項(xiàng)導(dǎo)出了一個(gè)結(jié)果下次換了同事來(lái)操作他點(diǎn)了默認(rèn)選項(xiàng)導(dǎo)出結(jié)果可能跟上次完全不同。尤其是在團(tuán)隊(duì)協(xié)作中如果依賴圖形化工具做數(shù)據(jù)導(dǎo)出流程很難標(biāo)準(zhǔn)化。所以我的建議是圖形化工具適合臨時(shí)性、探索性的導(dǎo)出比如快速看看某張表的數(shù)據(jù)長(zhǎng)什么樣或者臨時(shí)給業(yè)務(wù)方拉一個(gè)幾萬(wàn)行的數(shù)據(jù)。一旦導(dǎo)出動(dòng)作需要定期執(zhí)行、需要多個(gè)表組合、需要指定條件就必須把它固化成命令行腳本走自動(dòng)化。后面我會(huì)專門講怎么把導(dǎo)出做成自動(dòng)化。5. 跨環(huán)境導(dǎo)出的坑從正式區(qū)到測(cè)試區(qū)亂碼與不一致從哪來(lái)5.1 導(dǎo)出的 SQL 在目標(biāo)庫(kù)執(zhí)行時(shí)怎么保證不踩坑從正式區(qū)導(dǎo)出數(shù)據(jù)到測(cè)試區(qū)是日常開(kāi)發(fā)里最頻繁的跨環(huán)境操作。操作的路徑通常是在正式庫(kù)上執(zhí)行mysqldump然后在測(cè)試庫(kù)上執(zhí)行 SQL 文件。這個(gè)流程看似簡(jiǎn)單但有幾個(gè)環(huán)節(jié)容易出問(wèn)題。第一個(gè)坑字符集不匹配。正式庫(kù)如果建表時(shí)用的字符集是utf8mb4而測(cè)試庫(kù)建表時(shí)用的是utf8導(dǎo)入時(shí)中文會(huì)變成問(wèn)號(hào)。解決辦法是導(dǎo)出時(shí)顯式指定字符集mysqldump --default-character-setutf8mb4 -u root -p dba_test dba_test.sql同時(shí)在導(dǎo)入時(shí)也指定同樣的字符集mysql --default-character-setutf8mb4 -u root -p dba_test dba_test.sql前后端都統(tǒng)一指定不要在兩端留默認(rèn)值。默認(rèn)值的問(wèn)題在于它會(huì)依賴服務(wù)端配置和客戶端配置不同環(huán)境很可能不一樣。第二個(gè)坑目標(biāo)庫(kù)已經(jīng)存在同名表。如果直接執(zhí)行mysqldump導(dǎo)出的文件文件里默認(rèn)不帶DROP TABLE語(yǔ)句所以如果目標(biāo)庫(kù)已經(jīng)有同名的表導(dǎo)入時(shí)會(huì)變成“追加 INSERT 數(shù)據(jù)”。如果目標(biāo)表結(jié)構(gòu)跟源表不完全一致輕則導(dǎo)入失敗重則數(shù)據(jù)錯(cuò)亂。所以我在往測(cè)試區(qū)導(dǎo)入之前會(huì)先評(píng)估基礎(chǔ)數(shù)據(jù)要不要清空一般用以下兩種方式之一要么在mysqldump導(dǎo)出時(shí)加--add-drop-table這個(gè)參數(shù)生成的 SQL 里會(huì)帶DROP TABLE IF EXISTS要么在導(dǎo)入前手動(dòng)執(zhí)行清空語(yǔ)句。第三個(gè)坑外鍵約束導(dǎo)致導(dǎo)入順序錯(cuò)誤。如果一個(gè)庫(kù)里有多個(gè)表互相有外鍵關(guān)系mysqldump導(dǎo)出的文件默認(rèn)在開(kāi)頭包含SET FOREIGN_KEY_CHECKS 0在結(jié)尾包含SET FOREIGN_KEY_CHECKS 1。這個(gè)機(jī)制保證了導(dǎo)入過(guò)程中不會(huì)因?yàn)橥怄I順序報(bào)錯(cuò)。但如果你用圖形化工具導(dǎo)出的 SQL 文件沒(méi)有這兩行導(dǎo)入時(shí)就很可能會(huì)出現(xiàn)Cannot add or update a child row: a foreign key constraint fails之類的錯(cuò)誤。解決方法是在導(dǎo)入前先手動(dòng)執(zhí)行SET FOREIGN_KEY_CHECKS 0;導(dǎo)入完成后SET FOREIGN_KEY_CHECKS 1;5.2 敏感數(shù)據(jù)脫敏導(dǎo)出之前先想清楚合規(guī)這是跨環(huán)境導(dǎo)出里最容易被忽略的問(wèn)題。正式區(qū)的數(shù)據(jù)通常是真實(shí)用戶數(shù)據(jù)直接一股腦導(dǎo)進(jìn)測(cè)試區(qū)等于在測(cè)試環(huán)境擴(kuò)散了敏感信息。比較好的做法是在導(dǎo)出時(shí)直接用 SQL 做脫敏處理導(dǎo)出后再?gòu)?fù)制到測(cè)試區(qū)。mysqldump本身不支持列級(jí)脫敏所以我在做這類需求時(shí)會(huì)先用SELECT生成脫敏后的數(shù)據(jù)再用mysqldump --no-data導(dǎo)出表結(jié)構(gòu)最后把脫敏數(shù)據(jù)導(dǎo)入目標(biāo)表。這個(gè)流程稍微復(fù)雜但它能保證測(cè)試區(qū)拿到的數(shù)據(jù)既接近真實(shí)的“數(shù)據(jù)分布”又不包含真實(shí)手機(jī)號(hào)、身份證、地址等敏感字段。如果只是臨時(shí)同步幾張表可以寫一條INSERT INTO ... SELECT配合脫敏函數(shù)。比如手機(jī)號(hào)只保留前三位和后四位INSERT INTO test_db.user_info (id, name, phone) SELECT id, name, CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) FROM prod_db.user_info WHERE created_at 2024-01-01;這種做法的好處是整個(gè)過(guò)程都在數(shù)據(jù)庫(kù)內(nèi)部完成沒(méi)有中間文件敏感數(shù)據(jù)不會(huì)落地。缺點(diǎn)是跨庫(kù)訪問(wèn)需要兩個(gè)庫(kù)在同一實(shí)例或具備遠(yuǎn)程訪問(wèn)權(quán)限實(shí)際操作時(shí)需要根據(jù)網(wǎng)絡(luò)環(huán)境調(diào)整。6. 導(dǎo)出實(shí)戰(zhàn)中的報(bào)錯(cuò)排查從現(xiàn)象到根因的完整鏈路6.1 導(dǎo)出速度慢到想放棄先查這幾個(gè)點(diǎn)一個(gè)大表導(dǎo)出耗時(shí)特別長(zhǎng)先別急著怪 MySQL大概率是下面幾種情況之一。第一種慢查詢被調(diào)用了。mysqldump導(dǎo)出數(shù)據(jù)時(shí)本質(zhì)上是在執(zhí)行SELECT * FROM table如果表上沒(méi)有合適的索引或者表數(shù)據(jù)量巨大全表掃描就會(huì)很慢。這種場(chǎng)景下導(dǎo)出前先看看表的行數(shù)和大小SELECT table_name, table_rows, ROUND(data_length / 1024 / 1024, 2) AS data_mb FROM information_schema.tables WHERE table_schema dba_test ORDER BY data_length DESC;如果數(shù)據(jù)量確實(shí)大那慢是正常的可以考慮用并行導(dǎo)出工具比如 mydumper或者分批導(dǎo)出再合并。第二種網(wǎng)絡(luò)是瓶頸。如果mysqldump是在一套獨(dú)立的機(jī)器上執(zhí)行連接的是遠(yuǎn)程數(shù)據(jù)庫(kù)那么導(dǎo)出的速度受限于客戶端與服務(wù)器之間的網(wǎng)絡(luò)帶寬。用--compress參數(shù)壓縮傳輸數(shù)據(jù)是最直接的優(yōu)化手段。第三種磁盤 IO 被拖滿。如果數(shù)據(jù)庫(kù)服務(wù)器本身的磁盤已經(jīng)接近滿載大量頁(yè)在內(nèi)存和磁盤之間來(lái)回切換任何 SQL 都會(huì)變慢。這時(shí)導(dǎo)出操作帶來(lái)的額外 IO 會(huì)讓情況雪上加霜。查看系統(tǒng) IO 負(fù)載iostat -x 1如果%util長(zhǎng)期接近 100%說(shuō)明磁盤已經(jīng)到了瓶頸。這種環(huán)境下強(qiáng)行導(dǎo)出不是一個(gè)好主意建議選擇業(yè)務(wù)低峰期或者先擴(kuò)容再操作。6.2 導(dǎo)出的 SQL 在目標(biāo)庫(kù)執(zhí)行時(shí)報(bào)錯(cuò)逐個(gè)定位根因假設(shè)你已經(jīng)導(dǎo)出了一個(gè) SQL 文件在目標(biāo)庫(kù)執(zhí)行時(shí)報(bào)錯(cuò)很多人第一反應(yīng)是重新導(dǎo)出。但如果每次都只是重新導(dǎo)出、重新導(dǎo)入問(wèn)題往往反復(fù)出現(xiàn)。正確的排錯(cuò)方式是把報(bào)錯(cuò)當(dāng)作線索一層層往前排查。最常見(jiàn)的報(bào)錯(cuò)是ERROR 1064 (42000): You have an error in your SQL syntax。這個(gè)報(bào)錯(cuò)通常指向字符集或版本差異。比如源庫(kù)是 MySQL 8.0導(dǎo)出的 SQL 里可能包含新的語(yǔ)法特性比如DEFAULT CURRENT_TIMESTAMP(6)導(dǎo)入到 MySQL 5.7 時(shí)就會(huì)報(bào)語(yǔ)法錯(cuò)誤。這種跨大版本導(dǎo)入光靠mysqldump默認(rèn)參數(shù)是不夠的建議先用mysqldump --compatiblemysql56這類兼容模式導(dǎo)出或者對(duì)比兩邊的版本差異手動(dòng)修正 SQL 文件。另一種常見(jiàn)的報(bào)錯(cuò)是ERROR 1366 (HY000): Incorrect string value。這種情況通常是導(dǎo)入時(shí)客戶端字符集與目標(biāo)表字符集不一致中文字符被轉(zhuǎn)成非法字節(jié)序列。解決方法和前面提到的字符集統(tǒng)一一樣導(dǎo)入前先執(zhí)行SET NAMES utf8mb4;再繼續(xù)導(dǎo)入。執(zhí)行 SQL 文件時(shí)也可以在命令中指定mysql -u root -p --default-character-setutf8mb4 dba_test dba_test.sql還有一種報(bào)錯(cuò)是ERROR 1146 (42S02): Table xxx doesnt exist。如果導(dǎo)出的 SQL 文件里沒(méi)有包含建表語(yǔ)句而目標(biāo)庫(kù)又沒(méi)有這張表就會(huì)報(bào)這個(gè)錯(cuò)。有的同學(xué)會(huì)困惑“我明明導(dǎo)出了這張表的數(shù)據(jù)”那是因?yàn)樗贿x了數(shù)據(jù)導(dǎo)出沒(méi)有選結(jié)構(gòu)導(dǎo)出。檢查一下導(dǎo)出的 SQL 文件頭部有沒(méi)有CREATE TABLE語(yǔ)句就知道了。6.3 導(dǎo)入速度慢利用事務(wù)大小和索引策略優(yōu)化導(dǎo)完以后最痛苦的事就是導(dǎo)入。一個(gè) 5GB 的 SQL 文件在目標(biāo)庫(kù)上可能要跑半個(gè)小時(shí)甚至更久。如果導(dǎo)入的是一個(gè)全新的空庫(kù)最有效的優(yōu)化手段是延遲創(chuàng)建次要索引。默認(rèn)情況下建表語(yǔ)句里會(huì)帶上所有索引定義。當(dāng) SQL 文件像一條大河一樣流入時(shí)每插入一行數(shù)據(jù)MySQL 都要同時(shí)維護(hù)主鍵索引和所有二級(jí)索引代價(jià)非常大。我的做法是導(dǎo)出的 SQL 文件先不要直接導(dǎo)入而是用文本工具編輯一下把建表語(yǔ)句里的二級(jí)索引去掉只保留主鍵數(shù)據(jù)全部導(dǎo)入之后再手動(dòng)執(zhí)行ALTER TABLE語(yǔ)句重新創(chuàng)建索引。這樣做的經(jīng)驗(yàn)數(shù)據(jù)是大數(shù)據(jù)量導(dǎo)入時(shí)長(zhǎng)能縮短一半以上。另外如果導(dǎo)出的 SQL 文件里每條 INSERT 語(yǔ)句只插入幾行數(shù)據(jù)Navicat 默認(rèn)的 batch size 如果設(shè)得小就會(huì)出現(xiàn)這種情況導(dǎo)入效率非常低。我一般會(huì)在導(dǎo)出時(shí)就盡量讓每一條 INSERT 包含盡可能多的行比如 1000 行或者在拿到 SQL 文件后用腳本做一次粗加工把多條 INSERT 合并成一條。這種文件級(jí)別的優(yōu)化比調(diào)數(shù)據(jù)庫(kù)參數(shù)來(lái)得更直接、更可控。7. 自動(dòng)化導(dǎo)出把“手動(dòng)操作”變成“定時(shí)任務(wù)”導(dǎo)出這個(gè)動(dòng)作一旦變成例行需求靠人工敲命令遲早會(huì)出錯(cuò)。我見(jiàn)過(guò)最典型的場(chǎng)景是每月末需要給財(cái)務(wù)提供一份對(duì)賬單數(shù)據(jù)有人就每月末手動(dòng)執(zhí)行一次 SQL 導(dǎo)出然后發(fā)郵件。終于有一次手滑少加了一個(gè)WHERE條件整張表的數(shù)據(jù)被導(dǎo)出去發(fā)給了財(cái)務(wù)造成嚴(yán)重的數(shù)據(jù)泄露事故。所以我一直強(qiáng)調(diào)凡是每月、每周、每天都要做的導(dǎo)出必須腳本化、自動(dòng)化讓人工的參與降到最低。在 Linux 環(huán)境下最簡(jiǎn)單的方案是寫一個(gè) shell 腳本配合crontab做定時(shí)任務(wù)。下面是我常用的一個(gè)自動(dòng)化導(dǎo)出腳本的骨架做了幾件事導(dǎo)出數(shù)據(jù)、壓縮文件、按日期歸檔、清理 30 天前的舊文件#!/bin/bash # mysql_daily_export.sh BACKUP_DIR/data/mysql_exports/$(date %Y%m%d) mkdir -p $BACKUP_DIR DB_USERbackup_user DB_PASSbackup_pass DB_NAMEdba_test mysqldump -u$DB_USER -p$DB_PASS $DB_NAME \ --single-transaction \ --routines \ --triggers \ --events \ | gzip $BACKUP_DIR/dba_test_$(date %H%M%S).sql.gz # 清理30天前的舊文件 find /data/mysql_exports/ -type f -name *.sql.gz -mtime 30 -delete腳本里的backup_user我建議單獨(dú)創(chuàng)建一個(gè)賬號(hào)只授SELECT、LOCK TABLES、SHOW VIEW、TRIGGER等最小權(quán)限避免備份賬號(hào)權(quán)限過(guò)大成為安全隱患CREATE USER backup_userlocalhost IDENTIFIED BY backup_pass; GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON dba_test.* TO backup_userlocalhost; FLUSH PRIVILEGES;配合 crontab0 2 * * * /usr/local/bin/mysql_daily_export.sh /var/log/mysql_export.log 21這樣每天凌晨?jī)牲c(diǎn)自動(dòng)導(dǎo)出日志也留了痕跡如果哪天沒(méi)跑成功查日志就能定位。對(duì)于 CSV 這類需要給外部系統(tǒng)用的導(dǎo)出我一般不建議直接定時(shí)導(dǎo)出整個(gè)文件而是用SELECT INTO OUTFILE配合定時(shí) SQL 腳本可以精確控制導(dǎo)出的字段、條件和文件格式再通過(guò)事件調(diào)度器或外部計(jì)劃任務(wù)定期執(zhí)行。因?yàn)?CSV 文件的消費(fèi)方經(jīng)常是異構(gòu)系統(tǒng)格式穩(wěn)定性比“跑通一次”重要得多。8. 導(dǎo)出之后的那幾步驗(yàn)證導(dǎo)入才是導(dǎo)出的終點(diǎn)導(dǎo)出這個(gè)環(huán)節(jié)很多教程都寫得很詳細(xì)但真正讓數(shù)據(jù)“可用”的往往是導(dǎo)出之后的驗(yàn)證環(huán)節(jié)。這里分享一個(gè)我個(gè)人的習(xí)慣任何導(dǎo)出的文件在交付之前必須做一次驗(yàn)證驗(yàn)證的標(biāo)準(zhǔn)是以目標(biāo)角色去消費(fèi)這份數(shù)據(jù)而不是只看文件大小。如果導(dǎo)出的目標(biāo)是 SQL 文件我會(huì)在測(cè)試環(huán)境執(zhí)行一遍完整的導(dǎo)入流程確認(rèn)沒(méi)有報(bào)錯(cuò)然后執(zhí)行幾個(gè)關(guān)鍵查詢例如SELECT COUNT(*)對(duì)比源庫(kù)和目標(biāo)庫(kù)的行數(shù)檢查最大 ID 是否一致。這種基礎(chǔ)的行數(shù)校驗(yàn)?zāi)馨l(fā)現(xiàn) 99% 的明顯問(wèn)題。如果導(dǎo)出的目標(biāo)是 CSV 文件我會(huì)用 Python 或其他工具快速讀一遍文件頭、統(tǒng)計(jì)總行數(shù)、檢查字段列數(shù)是否一致再確認(rèn)中文沒(méi)有亂碼。有時(shí)候看似簡(jiǎn)單的 CSV 文件因?yàn)槟硞€(gè)字段值里夾帶了換行符導(dǎo)致文件整體行數(shù)比預(yù)期多出幾百行這種問(wèn)題不校驗(yàn)很難發(fā)現(xiàn)。甚至有一種更極端的情況導(dǎo)出的文件很大表面看起來(lái)一切正常但在導(dǎo)入時(shí)發(fā)現(xiàn)文件里有個(gè)別特殊字符比如\0導(dǎo)致目標(biāo)庫(kù)無(wú)法正常寫入。這種問(wèn)題在源庫(kù)中就能通過(guò) SQL 查詢提前排查出來(lái)SELECT COUNT(*) FROM order_info WHERE field1 LIKE CONCAT(%, CHAR(0), %);從我在生產(chǎn)環(huán)境踩過(guò)的坑來(lái)看導(dǎo)出的工作從來(lái)不是“敲一條命令”那么輕巧它需要你對(duì)數(shù)據(jù)的流向有完整的認(rèn)知數(shù)據(jù)從哪里來(lái)、經(jīng)過(guò)什么加工、到哪里去、誰(shuí)在消費(fèi)它、消費(fèi)時(shí)對(duì)格式有什么要求。把這五個(gè)問(wèn)題想清楚你選用的工具和參數(shù)自然就對(duì)了。希望這篇從真實(shí)場(chǎng)景和踩坑經(jīng)歷出發(fā)的梳理能讓你以后在 MySQL 導(dǎo)數(shù)據(jù)這件事上少走一些彎路。 MySQL數(shù)據(jù)導(dǎo)出全攻略：從mysqldump到CSV的實(shí)戰(zhàn)避坑指南
1. 先把“導(dǎo)出”這件事想清楚你要的是數(shù)據(jù)還是數(shù)據(jù)庫(kù)很多人一提“MySQL 導(dǎo)出數(shù)據(jù)”第一反應(yīng)就是打開(kāi)命令行敲一句mysqldump或者右鍵點(diǎn)一下“導(dǎo)出”然后拿著生成的 SQL 文件到處跑。這個(gè)動(dòng)作本身沒(méi)錯(cuò)但作為實(shí)際處理過(guò)大量數(shù)據(jù)遷移、備份恢復(fù)、跨環(huán)境數(shù)據(jù)同步的人我得先潑一盆冷水導(dǎo)出從來(lái)不是一個(gè)單一動(dòng)作它背后對(duì)應(yīng)的是完全不同的訴求。訴求沒(méi)想清楚選錯(cuò)工具和參數(shù)后面全是在給自己埋雷。我習(xí)慣把導(dǎo)出場(chǎng)景拆成三類第一類是結(jié)構(gòu)數(shù)據(jù)整體備份目標(biāo)是災(zāi)難恢復(fù)或環(huán)境復(fù)制這種情況要求“導(dǎo)出的東西再導(dǎo)回去能原樣跑起來(lái)”索引、觸發(fā)器、存儲(chǔ)過(guò)程、外鍵一個(gè)都不能少第二類是純數(shù)據(jù)搬運(yùn)比如從正式區(qū)抽一部分?jǐn)?shù)據(jù)到測(cè)試區(qū)讓開(kāi)發(fā)同學(xué)有真實(shí)的樣本數(shù)據(jù)可用這時(shí)往往只需要表結(jié)構(gòu)和數(shù)據(jù)甚至只要部分字段第三類是對(duì)外交付比如把某些表導(dǎo)成 CSV 給業(yè)務(wù)運(yùn)營(yíng)做分析或者導(dǎo)成 Excel 給財(cái)務(wù)核對(duì)這類場(chǎng)景關(guān)心的是“人能不能方便地打開(kāi)”而不是“數(shù)據(jù)庫(kù)能不能直接恢復(fù)”。這三種訴求對(duì)應(yīng)的技術(shù)選型完全不同。第一種我會(huì)優(yōu)先考慮mysqldump它最穩(wěn)生態(tài)最成熟幾乎所有 MySQL 版本都自帶第二種可以考慮mysqldump加參數(shù)過(guò)濾也可以考慮圖形化工具按查詢結(jié)果導(dǎo)出第三種基本上就是SELECT INTO OUTFILE或者圖形化工具的 CSV/Excel 導(dǎo)出功能壓根不需要 SQL 文件。不少剛?cè)胄械耐瑢W(xué)會(huì)覺(jué)得“導(dǎo)出數(shù)據(jù)”就是把表里的記錄寫到一個(gè)文件里但實(shí)際操作中翻車最多的恰恰就是這個(gè)認(rèn)知偏差。舉個(gè)我親眼見(jiàn)過(guò)的例子同事把生產(chǎn)庫(kù)用mysqldump導(dǎo)了一份 SQL拿到測(cè)試庫(kù)執(zhí)行結(jié)果發(fā)現(xiàn)測(cè)試庫(kù)的存儲(chǔ)過(guò)程、自定義函數(shù)全部丟失。為什么會(huì)丟因?yàn)槟J(rèn)的mysqldump實(shí)際上不會(huì)導(dǎo)出存儲(chǔ)過(guò)程和函數(shù)需要顯式加上--routines參數(shù)。類似這種細(xì)節(jié)還有很多后面我會(huì)把參數(shù)和行為之間的對(duì)應(yīng)關(guān)系逐個(gè)拆開(kāi)講。一句話先總結(jié)我這幾年的經(jīng)驗(yàn)導(dǎo)出的本質(zhì)是“根據(jù)消費(fèi)方的需求把數(shù)據(jù)庫(kù)轉(zhuǎn)成另一種形態(tài)”。消費(fèi)方是數(shù)據(jù)庫(kù)實(shí)例你要導(dǎo) SQL消費(fèi)方是分析師你要導(dǎo) CSV消費(fèi)方是另一個(gè)團(tuán)隊(duì)你要導(dǎo)他們能直接索引的結(jié)構(gòu)化文件。搞清楚消費(fèi)方再?zèng)Q定工具和參數(shù)這篇博文后面的所有內(nèi)容才有意義。2. 命令行是第一選擇mysqldump 的參數(shù)組合與取舍邏輯2.1 結(jié)構(gòu)、數(shù)據(jù)、例程一個(gè)都不能少說(shuō)到命令行導(dǎo)出mysqldump是當(dāng)之無(wú)愧的主力。它是 MySQL 官方自帶的邏輯備份工具生成的產(chǎn)物是一堆 SQL 語(yǔ)句在目標(biāo)庫(kù)執(zhí)行一遍就能重建所有對(duì)象和數(shù)據(jù)。它最大的優(yōu)勢(shì)是與存儲(chǔ)引擎無(wú)關(guān)、與平臺(tái)無(wú)關(guān)導(dǎo)出的文件到哪都能用所以跨版本、跨環(huán)境、跨操作系統(tǒng)的遷移場(chǎng)景里它幾乎是唯一解。先說(shuō)我最常用的一套完整備份命令mysqldump -h 127.0.0.1 -P 3306 -u root -p \ --single-transaction \ --routines \ --triggers \ --events \ --set-gtid-purgedOFF \ --databases db_name db_name.sql參數(shù)一個(gè)個(gè)說(shuō)。--single-transaction是 InnoDB 下最重要的參數(shù)它利用事務(wù)的快照讀特性在不鎖表的情況下拿到一致性的數(shù)據(jù)快照。注意這個(gè)參數(shù)對(duì) MyISAM 表不生效所以如果庫(kù)里還有 MyISAM 表--single-transaction并不能保證一致性這種情況下就需要乖乖停機(jī)或者接受數(shù)據(jù)不完全一致的風(fēng)險(xiǎn)。這也是為什么我接手過(guò)的項(xiàng)目我都會(huì)強(qiáng)烈建議把核心業(yè)務(wù)表全部轉(zhuǎn)成 InnoDB不只是為了事務(wù)更是為了能在線備份。--routines導(dǎo)出存儲(chǔ)過(guò)程和函數(shù)--triggers導(dǎo)出觸發(fā)器--events導(dǎo)出定時(shí)任務(wù)。這三個(gè)參數(shù)默認(rèn)都是不開(kāi)啟的如果你做的是整體遷移忘了加--routines導(dǎo)出的文件在新環(huán)境里就會(huì)靜默缺少所有存儲(chǔ)過(guò)程這種問(wèn)題排查起來(lái)非常惡心因?yàn)闆](méi)有報(bào)錯(cuò)只有等程序跑起來(lái)才發(fā)現(xiàn)函數(shù)不存在。我的習(xí)慣是把這三個(gè)參數(shù)固化成一組完整的備份命令而不是每一次都臨時(shí)拼參數(shù)。把下面這一段存成mysql_full_backup.sh里的核心調(diào)用平時(shí)基本不會(huì)忘MYSQL_CMDmysqldump -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST --single-transaction --routines --triggers --events --set-gtid-purgedOFF為什么要加--set-gtid-purgedOFF如果你用的是 MySQL 5.6 以上版本且開(kāi)啟了 GTID 模式dump 出來(lái)的文件里默認(rèn)會(huì)帶上SET GLOBAL.GTID_PURGED...這一句。這句在導(dǎo)入到另一臺(tái)實(shí)例時(shí)經(jīng)常會(huì)因?yàn)槟繕?biāo)庫(kù)的 GTID 狀態(tài)不一致而報(bào)錯(cuò)導(dǎo)致導(dǎo)入失敗。很多新手在自己本機(jī)導(dǎo)入生產(chǎn)庫(kù)的備份時(shí)報(bào)錯(cuò)十有八九就是這個(gè)問(wèn)題。加上這個(gè)參數(shù)讓 dump 文件不包含 GTID 信息導(dǎo)入時(shí)反而少很多麻煩。2.2 只導(dǎo)部分?jǐn)?shù)據(jù)的正確姿勢(shì)整體備份只需要一條命令但現(xiàn)實(shí)里更常見(jiàn)的需求是“只導(dǎo)一部分”。比如我從正式區(qū)導(dǎo)出某些業(yè)務(wù)表給測(cè)試區(qū)通常只要最近三個(gè)月的數(shù)據(jù)或者只要某個(gè)用戶維度的數(shù)據(jù)。mysqldump本身就支持這個(gè)用--where參數(shù)mysqldump -u root -p dba_test order_info \ --wherecreated_at 2024-01-01 AND created_at 2024-04-01 \ order_info_2024_q1.sql這里有個(gè)細(xì)節(jié)要注意--where參數(shù)應(yīng)該放在庫(kù)名表名之后否則某些版本會(huì)報(bào)參數(shù)解析錯(cuò)誤。還有--where里的條件值如果包含空格或特殊字符需要用引號(hào)把整個(gè)條件包裹起來(lái)。我見(jiàn)過(guò)有人圖省事直接寫--whereid1000沒(méi)加引號(hào)結(jié)果 shell 解釋的時(shí)候把當(dāng)成輸入重定向符生成的文件是空的排查了半天才發(fā)現(xiàn)是符號(hào)被吞了。所以養(yǎng)成習(xí)慣where 條件一定加單引號(hào)。--where只導(dǎo)部分行那如果我只想導(dǎo)表結(jié)構(gòu)不要數(shù)據(jù)呢用--no-data。反過(guò)來(lái)--no-create-info是只導(dǎo)數(shù)據(jù)不導(dǎo)建表語(yǔ)句。這兩個(gè)參數(shù)組合起來(lái)非常靈活比如我要把一個(gè)表的數(shù)據(jù)從正式區(qū)搬到測(cè)試區(qū)目標(biāo)表已經(jīng)提前建好了結(jié)構(gòu)那么只導(dǎo)數(shù)據(jù)即可mysqldump -u root -p dba_test order_info \ --no-create-info \ --wherestatus 1 \ order_info_data.sql2.3 大表導(dǎo)出時(shí)的 IO 與鎖問(wèn)題大表導(dǎo)出重點(diǎn)考慮兩件事會(huì)不會(huì)長(zhǎng)時(shí)間占用資源、會(huì)不會(huì)長(zhǎng)時(shí)間持有鎖。先說(shuō)鎖。剛才提到的--single-transaction是通過(guò) InnoDB 的 MVCC 機(jī)制拿快照理論上導(dǎo)出一致性數(shù)據(jù)不需要鎖表。但有一個(gè)前提導(dǎo)出的過(guò)程中不能有 DDL 操作。因?yàn)?MySQL 的 DDL 會(huì)觸發(fā)隱式提交極有可能打斷事務(wù)快照的一致性造成 dump 中途報(bào)ERROR 1412: Table definition has changed, please retry transaction之類的錯(cuò)誤。所以即使是--single-transaction保護(hù)下的在線導(dǎo)出我也建議在業(yè)務(wù)低峰期執(zhí)行并且盡量從只讀從庫(kù)導(dǎo)出這是最穩(wěn)妥的做法。再說(shuō) IO。導(dǎo)出超大表上億行時(shí)mysqldump客戶端與服務(wù)端之間的數(shù)據(jù)傳輸會(huì)占用不少帶寬和 CPU如果應(yīng)用和數(shù)據(jù)庫(kù)在同一臺(tái)機(jī)器上還會(huì)互相搶占資源。我的做法是盡量把導(dǎo)出操作放到單獨(dú)的執(zhí)行機(jī)上去跑避免直接在數(shù)據(jù)庫(kù)宿主機(jī)上敲命令同時(shí)用--compress參數(shù)在傳輸過(guò)程中壓縮數(shù)據(jù)減少網(wǎng)絡(luò)開(kāi)銷。--compress是在 client 和 server 之間壓縮不是把生成的文件壓縮這一點(diǎn)要分清。如果需要最終產(chǎn)物也是壓縮包可以用管道把輸出直接交給 gzipmysqldump -u root -p dba_test order_info --single-transaction | gzip order_info.sql.gz這一招在生產(chǎn)環(huán)境非常實(shí)用SQL 文本文件的壓縮率通常在 10:1 以上一個(gè) 10GB 的庫(kù)導(dǎo)出來(lái)可能只有 1GB 左右傳輸和存儲(chǔ)壓力都小很多。還有一個(gè)參數(shù)容易被忽略--max-allowed-packet。默認(rèn)值通常是 64MB如果在表里存了大字段比如 BLOB、TEXT單個(gè) SQL 語(yǔ)句可能超過(guò)這個(gè)上限導(dǎo)出時(shí)一切正常導(dǎo)入時(shí)報(bào)packet too large。這種情況在導(dǎo)出的命令里加--max-allowed-packet1G注意是在 mysqldump 命令里不是 mysql 客戶端命令里導(dǎo)出的文件頭部會(huì)生成對(duì)應(yīng)的SET GLOBAL max_allowed_packet...語(yǔ)句導(dǎo)入時(shí)才能順利吞下大包。3. 只導(dǎo)數(shù)據(jù)不導(dǎo)結(jié)構(gòu)SELECT INTO OUTFILE 與 CSV 的邊界3.1 什么時(shí)候該用 SELECT INTO OUTFILEmysqldump生成的是 SQL 文件給數(shù)據(jù)庫(kù)用很合適但給人和常見(jiàn)辦公軟件用就很別扭。比如業(yè)務(wù)方要一份用戶訂單明細(xì)他們不會(huì)去導(dǎo)入 SQL他們只想拿到一個(gè) CSV 或 Excel雙擊就能打開(kāi)。這種場(chǎng)景SELECT INTO OUTFILE就是最直接的手段。基本語(yǔ)法SELECT id, user_id, order_amount, created_at INTO OUTFILE /var/lib/mysql-files/orders_2024.csv FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n FROM order_info WHERE created_at 2024-01-01 AND created_at 2024-04-01;這個(gè)寫法的思路是MySQL 服務(wù)端把查詢結(jié)果直接寫到服務(wù)器本地文件不需要經(jīng)過(guò)客戶端網(wǎng)絡(luò)傳輸。所以它有幾個(gè)特性決定了適用場(chǎng)景第一文件寫在數(shù)據(jù)庫(kù)服務(wù)器本地不是你的電腦上。很多人第一次用這個(gè)功能明明執(zhí)行成功了在自己電腦上找文件找半天找不到然后才反應(yīng)過(guò)來(lái)文件在服務(wù)器上。如果是云數(shù)據(jù)庫(kù)RDS 之類很多情況下這個(gè)功能根本沒(méi)有開(kāi)放因?yàn)镮NTO OUTFILE會(huì)往數(shù)據(jù)庫(kù)主機(jī)磁盤上寫文件云廠商出于安全考慮默認(rèn)禁用。第二它是純數(shù)據(jù)導(dǎo)出不帶任何建表語(yǔ)句只會(huì)把查詢結(jié)果的行按你指定的格式輸出。字段分隔符、行分隔符、字段包裹符都靠自己定義這也是 CSV 標(biāo)準(zhǔn)格式的做法。3.2 secure-file-priv 是繞不開(kāi)的坎第一次用SELECT INTO OUTFILE的人大概率會(huì)遇到一個(gè)報(bào)錯(cuò)ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement這是 MySQL 5.7 之后引入的安全機(jī)制限制了INTO OUTFILE和LOAD DATA INFILE的可寫目錄。默認(rèn)情況下它只允許寫入一個(gè)由secure_file_priv指定的目錄查看當(dāng)前配置SHOW VARIABLES LIKE secure_file_priv;如果結(jié)果是/var/lib/mysql-files/那就表示只能寫在這個(gè)目錄下。如果你用的是自己安裝的 MySQL想放開(kāi)或者改目錄可以在配置文件my.cnf的[mysqld]段里設(shè)置[mysqld] secure_file_priv/tmp/mysql_exports設(shè)置完重啟 MySQL 服務(wù)再把導(dǎo)出路徑改成你自己的目錄即可。但如果你用的是云數(shù)據(jù)庫(kù)這個(gè)參數(shù)通常是改不了的所以我一般會(huì)先在本地建一個(gè)“中轉(zhuǎn)庫(kù)”把云上數(shù)據(jù)用mysqldump導(dǎo)到本地再在本地 MySQL 里執(zhí)行SELECT INTO OUTFILE。繞是繞一點(diǎn)但這是云環(huán)境下最穩(wěn)妥的路徑。3.3 分隔符選擇里的隱形坑CSV 的分隔符不是隨便選的。字段值里如果包含逗號(hào)、換行、雙引號(hào)直接按最簡(jiǎn)單的方式拼出來(lái)的 CSV 在 Excel 里一定會(huì)錯(cuò)位。所以要用ENCLOSED BY 把每個(gè)字段用雙引號(hào)包起來(lái)Excel 才能正確識(shí)別包含逗號(hào)的字段。如果你自己寫腳本去解析這些 CSV也務(wù)必要做引號(hào)配對(duì)處理不能簡(jiǎn)單按逗號(hào) split。還有換行符。Linux 下 LINES TERMINATED BY \n 生成的 CSV在 Windows 的 Excel 里打開(kāi)時(shí)能夠識(shí)別但有些老版本的 Excel 會(huì)把換行符解析成兩點(diǎn)之間的分割錯(cuò)亂。更兼容的做法是用\r\n也就是 Windows 風(fēng)格的換行符。我一般統(tǒng)一用\r\n這樣在 Windows 和 macOS 下打開(kāi)都不會(huì)出問(wèn)題。SELECT * INTO OUTFILE /var/lib/mysql-files/orders_2024_win.csv FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \r\n FROM order_info;另外一個(gè)容易被忽略的是字符集。默認(rèn)導(dǎo)出的 CSV 是 UTF-8 編碼Excel 直接雙擊打開(kāi) UTF-8 無(wú) BOM 的文件時(shí)中文會(huì)亂碼。解決辦法有兩個(gè)一是用 WPS 或者 Excel 的文本導(dǎo)入向?qū)нx擇 UTF-8 編碼再打開(kāi)二是導(dǎo)出的文件里在最前面塞一個(gè) BOM 頭我通常的做法是導(dǎo)出后在服務(wù)器上用sed給文件開(kāi)頭加一個(gè) BOMsed -i 1s/^/\xef\xbb\xbf/ /var/lib/mysql-files/orders_2024_win.csv這樣 Excel 雙擊打開(kāi)就是正常中文。這個(gè)小細(xì)節(jié)很多教程不會(huì)講但實(shí)際交付 CSV 給不懂技術(shù)的同事時(shí)這一步能省掉大量“誒怎么亂碼了”的溝通成本。4. 圖形化工具權(quán)限與控制力從“右鍵導(dǎo)出”到“確定性導(dǎo)出”4.1 Navicat、DBeaver、MySQL Workbench 的導(dǎo)出邏輯差異很多人習(xí)慣用圖形化工具做導(dǎo)出確實(shí)方便點(diǎn)幾下就完事。但不同工具導(dǎo)出的產(chǎn)物在細(xì)節(jié)上有很大差別我在這上面翻過(guò)車。先說(shuō)Navicat。它提供兩種導(dǎo)出形式一種是導(dǎo)出 SQL 文件里面包含建表語(yǔ)句和 INSERT 語(yǔ)句另一種是導(dǎo)出為其他格式CSV、Excel、JSON 等。導(dǎo)出 SQL 時(shí)它有“創(chuàng)建表結(jié)構(gòu)”和“包含數(shù)據(jù)”兩個(gè)勾選項(xiàng)默認(rèn)全選。有一個(gè)容易忽略的選項(xiàng)是“每次插入的行數(shù)”默認(rèn)可能是 100 行甚至更少這意味著一個(gè)十萬(wàn)行的表會(huì)拆成一千條 INSERT 語(yǔ)句。這種文件在導(dǎo)入時(shí)執(zhí)行效率非常低如果把 batch size 調(diào)大比如 1000 或 2000導(dǎo)入性能能提升一個(gè)數(shù)量級(jí)。我一般會(huì)調(diào)成 1000 左右太大容易觸發(fā)max_allowed_packet的限制太小導(dǎo)入太慢1000 是一個(gè)平衡的數(shù)值。再說(shuō)DBeaver。它的“導(dǎo)出數(shù)據(jù)”功能非常靈活可以基于當(dāng)前查詢結(jié)果直接導(dǎo)出支持 SQL 文件、CSV、Excel、JSON 等多種格式。但因?yàn)樘`活導(dǎo)致新手容易踩一個(gè)坑DBeaver 導(dǎo)出 Excel 時(shí)默認(rèn)是一個(gè)表一個(gè) Sheet如果查詢結(jié)果里有大量中文或者特殊字符導(dǎo)出過(guò)程中偶爾會(huì)出現(xiàn)編碼問(wèn)題。我的經(jīng)驗(yàn)是DBeaver 導(dǎo)出前先確認(rèn)“編碼”下拉框選的是 UTF-8。MySQL Workbench的導(dǎo)出能力其實(shí)被很多人低估了。它的 Data Export 功能支持選擇多個(gè) Schema、多個(gè)表也能選擇“僅結(jié)構(gòu)”“結(jié)構(gòu)和數(shù)據(jù)”“僅數(shù)據(jù)”三種模式。它的導(dǎo)出的 SQL 文件里會(huì)自動(dòng)加入DROP TABLE IF EXISTS這樣的語(yǔ)句所以導(dǎo)入到已有同名的目標(biāo)庫(kù)時(shí)會(huì)先刪掉舊表再建新表。這本來(lái)是方便但如果目標(biāo)庫(kù)里有你不希望被覆蓋的表而你又只勾選了某幾張表導(dǎo)出一旦不注意選錯(cuò)范圍后果很嚴(yán)重。所以在 Workbench 里點(diǎn)擊 Start Export 之前我一定會(huì)把“Selected Tables”和“Export to Self-Contained File”這兩處逐字檢查一遍。4.2 圖形化導(dǎo)出最大的問(wèn)題結(jié)果不可重現(xiàn)圖形化工具方便是方便但最大的問(wèn)題在于導(dǎo)出過(guò)程的參數(shù)不透明。你這次點(diǎn)了 A、B、C 三個(gè)選項(xiàng)導(dǎo)出了一個(gè)結(jié)果下次換了同事來(lái)操作他點(diǎn)了默認(rèn)選項(xiàng)導(dǎo)出結(jié)果可能跟上次完全不同。尤其是在團(tuán)隊(duì)協(xié)作中如果依賴圖形化工具做數(shù)據(jù)導(dǎo)出流程很難標(biāo)準(zhǔn)化。所以我的建議是圖形化工具適合臨時(shí)性、探索性的導(dǎo)出比如快速看看某張表的數(shù)據(jù)長(zhǎng)什么樣或者臨時(shí)給業(yè)務(wù)方拉一個(gè)幾萬(wàn)行的數(shù)據(jù)。一旦導(dǎo)出動(dòng)作需要定期執(zhí)行、需要多個(gè)表組合、需要指定條件就必須把它固化成命令行腳本走自動(dòng)化。后面我會(huì)專門講怎么把導(dǎo)出做成自動(dòng)化。5. 跨環(huán)境導(dǎo)出的坑從正式區(qū)到測(cè)試區(qū)亂碼與不一致從哪來(lái)5.1 導(dǎo)出的 SQL 在目標(biāo)庫(kù)執(zhí)行時(shí)怎么保證不踩坑從正式區(qū)導(dǎo)出數(shù)據(jù)到測(cè)試區(qū)是日常開(kāi)發(fā)里最頻繁的跨環(huán)境操作。操作的路徑通常是在正式庫(kù)上執(zhí)行mysqldump然后在測(cè)試庫(kù)上執(zhí)行 SQL 文件。這個(gè)流程看似簡(jiǎn)單但有幾個(gè)環(huán)節(jié)容易出問(wèn)題。第一個(gè)坑字符集不匹配。正式庫(kù)如果建表時(shí)用的字符集是utf8mb4而測(cè)試庫(kù)建表時(shí)用的是utf8導(dǎo)入時(shí)中文會(huì)變成問(wèn)號(hào)。解決辦法是導(dǎo)出時(shí)顯式指定字符集mysqldump --default-character-setutf8mb4 -u root -p dba_test dba_test.sql同時(shí)在導(dǎo)入時(shí)也指定同樣的字符集mysql --default-character-setutf8mb4 -u root -p dba_test dba_test.sql前后端都統(tǒng)一指定不要在兩端留默認(rèn)值。默認(rèn)值的問(wèn)題在于它會(huì)依賴服務(wù)端配置和客戶端配置不同環(huán)境很可能不一樣。第二個(gè)坑目標(biāo)庫(kù)已經(jīng)存在同名表。如果直接執(zhí)行mysqldump導(dǎo)出的文件文件里默認(rèn)不帶DROP TABLE語(yǔ)句所以如果目標(biāo)庫(kù)已經(jīng)有同名的表導(dǎo)入時(shí)會(huì)變成“追加 INSERT 數(shù)據(jù)”。如果目標(biāo)表結(jié)構(gòu)跟源表不完全一致輕則導(dǎo)入失敗重則數(shù)據(jù)錯(cuò)亂。所以我在往測(cè)試區(qū)導(dǎo)入之前會(huì)先評(píng)估基礎(chǔ)數(shù)據(jù)要不要清空一般用以下兩種方式之一要么在mysqldump導(dǎo)出時(shí)加--add-drop-table這個(gè)參數(shù)生成的 SQL 里會(huì)帶DROP TABLE IF EXISTS要么在導(dǎo)入前手動(dòng)執(zhí)行清空語(yǔ)句。第三個(gè)坑外鍵約束導(dǎo)致導(dǎo)入順序錯(cuò)誤。如果一個(gè)庫(kù)里有多個(gè)表互相有外鍵關(guān)系mysqldump導(dǎo)出的文件默認(rèn)在開(kāi)頭包含SET FOREIGN_KEY_CHECKS 0在結(jié)尾包含SET FOREIGN_KEY_CHECKS 1。這個(gè)機(jī)制保證了導(dǎo)入過(guò)程中不會(huì)因?yàn)橥怄I順序報(bào)錯(cuò)。但如果你用圖形化工具導(dǎo)出的 SQL 文件沒(méi)有這兩行導(dǎo)入時(shí)就很可能會(huì)出現(xiàn)Cannot add or update a child row: a foreign key constraint fails之類的錯(cuò)誤。解決方法是在導(dǎo)入前先手動(dòng)執(zhí)行SET FOREIGN_KEY_CHECKS 0;導(dǎo)入完成后SET FOREIGN_KEY_CHECKS 1;5.2 敏感數(shù)據(jù)脫敏導(dǎo)出之前先想清楚合規(guī)這是跨環(huán)境導(dǎo)出里最容易被忽略的問(wèn)題。正式區(qū)的數(shù)據(jù)通常是真實(shí)用戶數(shù)據(jù)直接一股腦導(dǎo)進(jìn)測(cè)試區(qū)等于在測(cè)試環(huán)境擴(kuò)散了敏感信息。比較好的做法是在導(dǎo)出時(shí)直接用 SQL 做脫敏處理導(dǎo)出后再?gòu)?fù)制到測(cè)試區(qū)。mysqldump本身不支持列級(jí)脫敏所以我在做這類需求時(shí)會(huì)先用SELECT生成脫敏后的數(shù)據(jù)再用mysqldump --no-data導(dǎo)出表結(jié)構(gòu)最后把脫敏數(shù)據(jù)導(dǎo)入目標(biāo)表。這個(gè)流程稍微復(fù)雜但它能保證測(cè)試區(qū)拿到的數(shù)據(jù)既接近真實(shí)的“數(shù)據(jù)分布”又不包含真實(shí)手機(jī)號(hào)、身份證、地址等敏感字段。如果只是臨時(shí)同步幾張表可以寫一條INSERT INTO ... SELECT配合脫敏函數(shù)。比如手機(jī)號(hào)只保留前三位和后四位INSERT INTO test_db.user_info (id, name, phone) SELECT id, name, CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) FROM prod_db.user_info WHERE created_at 2024-01-01;這種做法的好處是整個(gè)過(guò)程都在數(shù)據(jù)庫(kù)內(nèi)部完成沒(méi)有中間文件敏感數(shù)據(jù)不會(huì)落地。缺點(diǎn)是跨庫(kù)訪問(wèn)需要兩個(gè)庫(kù)在同一實(shí)例或具備遠(yuǎn)程訪問(wèn)權(quán)限實(shí)際操作時(shí)需要根據(jù)網(wǎng)絡(luò)環(huán)境調(diào)整。6. 導(dǎo)出實(shí)戰(zhàn)中的報(bào)錯(cuò)排查從現(xiàn)象到根因的完整鏈路6.1 導(dǎo)出速度慢到想放棄先查這幾個(gè)點(diǎn)一個(gè)大表導(dǎo)出耗時(shí)特別長(zhǎng)先別急著怪 MySQL大概率是下面幾種情況之一。第一種慢查詢被調(diào)用了。mysqldump導(dǎo)出數(shù)據(jù)時(shí)本質(zhì)上是在執(zhí)行SELECT * FROM table如果表上沒(méi)有合適的索引或者表數(shù)據(jù)量巨大全表掃描就會(huì)很慢。這種場(chǎng)景下導(dǎo)出前先看看表的行數(shù)和大小SELECT table_name, table_rows, ROUND(data_length / 1024 / 1024, 2) AS data_mb FROM information_schema.tables WHERE table_schema dba_test ORDER BY data_length DESC;如果數(shù)據(jù)量確實(shí)大那慢是正常的可以考慮用并行導(dǎo)出工具比如 mydumper或者分批導(dǎo)出再合并。第二種網(wǎng)絡(luò)是瓶頸。如果mysqldump是在一套獨(dú)立的機(jī)器上執(zhí)行連接的是遠(yuǎn)程數(shù)據(jù)庫(kù)那么導(dǎo)出的速度受限于客戶端與服務(wù)器之間的網(wǎng)絡(luò)帶寬。用--compress參數(shù)壓縮傳輸數(shù)據(jù)是最直接的優(yōu)化手段。第三種磁盤 IO 被拖滿。如果數(shù)據(jù)庫(kù)服務(wù)器本身的磁盤已經(jīng)接近滿載大量頁(yè)在內(nèi)存和磁盤之間來(lái)回切換任何 SQL 都會(huì)變慢。這時(shí)導(dǎo)出操作帶來(lái)的額外 IO 會(huì)讓情況雪上加霜。查看系統(tǒng) IO 負(fù)載iostat -x 1如果%util長(zhǎng)期接近 100%說(shuō)明磁盤已經(jīng)到了瓶頸。這種環(huán)境下強(qiáng)行導(dǎo)出不是一個(gè)好主意建議選擇業(yè)務(wù)低峰期或者先擴(kuò)容再操作。6.2 導(dǎo)出的 SQL 在目標(biāo)庫(kù)執(zhí)行時(shí)報(bào)錯(cuò)逐個(gè)定位根因假設(shè)你已經(jīng)導(dǎo)出了一個(gè) SQL 文件在目標(biāo)庫(kù)執(zhí)行時(shí)報(bào)錯(cuò)很多人第一反應(yīng)是重新導(dǎo)出。但如果每次都只是重新導(dǎo)出、重新導(dǎo)入問(wèn)題往往反復(fù)出現(xiàn)。正確的排錯(cuò)方式是把報(bào)錯(cuò)當(dāng)作線索一層層往前排查。最常見(jiàn)的報(bào)錯(cuò)是ERROR 1064 (42000): You have an error in your SQL syntax。這個(gè)報(bào)錯(cuò)通常指向字符集或版本差異。比如源庫(kù)是 MySQL 8.0導(dǎo)出的 SQL 里可能包含新的語(yǔ)法特性比如DEFAULT CURRENT_TIMESTAMP(6)導(dǎo)入到 MySQL 5.7 時(shí)就會(huì)報(bào)語(yǔ)法錯(cuò)誤。這種跨大版本導(dǎo)入光靠mysqldump默認(rèn)參數(shù)是不夠的建議先用mysqldump --compatiblemysql56這類兼容模式導(dǎo)出或者對(duì)比兩邊的版本差異手動(dòng)修正 SQL 文件。另一種常見(jiàn)的報(bào)錯(cuò)是ERROR 1366 (HY000): Incorrect string value。這種情況通常是導(dǎo)入時(shí)客戶端字符集與目標(biāo)表字符集不一致中文字符被轉(zhuǎn)成非法字節(jié)序列。解決方法和前面提到的字符集統(tǒng)一一樣導(dǎo)入前先執(zhí)行SET NAMES utf8mb4;再繼續(xù)導(dǎo)入。執(zhí)行 SQL 文件時(shí)也可以在命令中指定mysql -u root -p --default-character-setutf8mb4 dba_test dba_test.sql還有一種報(bào)錯(cuò)是ERROR 1146 (42S02): Table xxx doesnt exist。如果導(dǎo)出的 SQL 文件里沒(méi)有包含建表語(yǔ)句而目標(biāo)庫(kù)又沒(méi)有這張表就會(huì)報(bào)這個(gè)錯(cuò)。有的同學(xué)會(huì)困惑“我明明導(dǎo)出了這張表的數(shù)據(jù)”那是因?yàn)樗贿x了數(shù)據(jù)導(dǎo)出沒(méi)有選結(jié)構(gòu)導(dǎo)出。檢查一下導(dǎo)出的 SQL 文件頭部有沒(méi)有CREATE TABLE語(yǔ)句就知道了。6.3 導(dǎo)入速度慢利用事務(wù)大小和索引策略優(yōu)化導(dǎo)完以后最痛苦的事就是導(dǎo)入。一個(gè) 5GB 的 SQL 文件在目標(biāo)庫(kù)上可能要跑半個(gè)小時(shí)甚至更久。如果導(dǎo)入的是一個(gè)全新的空庫(kù)最有效的優(yōu)化手段是延遲創(chuàng)建次要索引。默認(rèn)情況下建表語(yǔ)句里會(huì)帶上所有索引定義。當(dāng) SQL 文件像一條大河一樣流入時(shí)每插入一行數(shù)據(jù)MySQL 都要同時(shí)維護(hù)主鍵索引和所有二級(jí)索引代價(jià)非常大。我的做法是導(dǎo)出的 SQL 文件先不要直接導(dǎo)入而是用文本工具編輯一下把建表語(yǔ)句里的二級(jí)索引去掉只保留主鍵數(shù)據(jù)全部導(dǎo)入之后再手動(dòng)執(zhí)行ALTER TABLE語(yǔ)句重新創(chuàng)建索引。這樣做的經(jīng)驗(yàn)數(shù)據(jù)是大數(shù)據(jù)量導(dǎo)入時(shí)長(zhǎng)能縮短一半以上。另外如果導(dǎo)出的 SQL 文件里每條 INSERT 語(yǔ)句只插入幾行數(shù)據(jù)Navicat 默認(rèn)的 batch size 如果設(shè)得小就會(huì)出現(xiàn)這種情況導(dǎo)入效率非常低。我一般會(huì)在導(dǎo)出時(shí)就盡量讓每一條 INSERT 包含盡可能多的行比如 1000 行或者在拿到 SQL 文件后用腳本做一次粗加工把多條 INSERT 合并成一條。這種文件級(jí)別的優(yōu)化比調(diào)數(shù)據(jù)庫(kù)參數(shù)來(lái)得更直接、更可控。7. 自動(dòng)化導(dǎo)出把“手動(dòng)操作”變成“定時(shí)任務(wù)”導(dǎo)出這個(gè)動(dòng)作一旦變成例行需求靠人工敲命令遲早會(huì)出錯(cuò)。我見(jiàn)過(guò)最典型的場(chǎng)景是每月末需要給財(cái)務(wù)提供一份對(duì)賬單數(shù)據(jù)有人就每月末手動(dòng)執(zhí)行一次 SQL 導(dǎo)出然后發(fā)郵件。終于有一次手滑少加了一個(gè)WHERE條件整張表的數(shù)據(jù)被導(dǎo)出去發(fā)給了財(cái)務(wù)造成嚴(yán)重的數(shù)據(jù)泄露事故。所以我一直強(qiáng)調(diào)凡是每月、每周、每天都要做的導(dǎo)出必須腳本化、自動(dòng)化讓人工的參與降到最低。在 Linux 環(huán)境下最簡(jiǎn)單的方案是寫一個(gè) shell 腳本配合crontab做定時(shí)任務(wù)。下面是我常用的一個(gè)自動(dòng)化導(dǎo)出腳本的骨架做了幾件事導(dǎo)出數(shù)據(jù)、壓縮文件、按日期歸檔、清理 30 天前的舊文件#!/bin/bash # mysql_daily_export.sh BACKUP_DIR/data/mysql_exports/$(date %Y%m%d) mkdir -p $BACKUP_DIR DB_USERbackup_user DB_PASSbackup_pass DB_NAMEdba_test mysqldump -u$DB_USER -p$DB_PASS $DB_NAME \ --single-transaction \ --routines \ --triggers \ --events \ | gzip $BACKUP_DIR/dba_test_$(date %H%M%S).sql.gz # 清理30天前的舊文件 find /data/mysql_exports/ -type f -name *.sql.gz -mtime 30 -delete腳本里的backup_user我建議單獨(dú)創(chuàng)建一個(gè)賬號(hào)只授SELECT、LOCK TABLES、SHOW VIEW、TRIGGER等最小權(quán)限避免備份賬號(hào)權(quán)限過(guò)大成為安全隱患CREATE USER backup_userlocalhost IDENTIFIED BY backup_pass; GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER ON dba_test.* TO backup_userlocalhost; FLUSH PRIVILEGES;配合 crontab0 2 * * * /usr/local/bin/mysql_daily_export.sh /var/log/mysql_export.log 21這樣每天凌晨?jī)牲c(diǎn)自動(dòng)導(dǎo)出日志也留了痕跡如果哪天沒(méi)跑成功查日志就能定位。對(duì)于 CSV 這類需要給外部系統(tǒng)用的導(dǎo)出我一般不建議直接定時(shí)導(dǎo)出整個(gè)文件而是用SELECT INTO OUTFILE配合定時(shí) SQL 腳本可以精確控制導(dǎo)出的字段、條件和文件格式再通過(guò)事件調(diào)度器或外部計(jì)劃任務(wù)定期執(zhí)行。因?yàn)?CSV 文件的消費(fèi)方經(jīng)常是異構(gòu)系統(tǒng)格式穩(wěn)定性比“跑通一次”重要得多。8. 導(dǎo)出之后的那幾步驗(yàn)證導(dǎo)入才是導(dǎo)出的終點(diǎn)導(dǎo)出這個(gè)環(huán)節(jié)很多教程都寫得很詳細(xì)但真正讓數(shù)據(jù)“可用”的往往是導(dǎo)出之后的驗(yàn)證環(huán)節(jié)。這里分享一個(gè)我個(gè)人的習(xí)慣任何導(dǎo)出的文件在交付之前必須做一次驗(yàn)證驗(yàn)證的標(biāo)準(zhǔn)是以目標(biāo)角色去消費(fèi)這份數(shù)據(jù)而不是只看文件大小。如果導(dǎo)出的目標(biāo)是 SQL 文件我會(huì)在測(cè)試環(huán)境執(zhí)行一遍完整的導(dǎo)入流程確認(rèn)沒(méi)有報(bào)錯(cuò)然后執(zhí)行幾個(gè)關(guān)鍵查詢例如SELECT COUNT(*)對(duì)比源庫(kù)和目標(biāo)庫(kù)的行數(shù)檢查最大 ID 是否一致。這種基礎(chǔ)的行數(shù)校驗(yàn)?zāi)馨l(fā)現(xiàn) 99% 的明顯問(wèn)題。如果導(dǎo)出的目標(biāo)是 CSV 文件我會(huì)用 Python 或其他工具快速讀一遍文件頭、統(tǒng)計(jì)總行數(shù)、檢查字段列數(shù)是否一致再確認(rèn)中文沒(méi)有亂碼。有時(shí)候看似簡(jiǎn)單的 CSV 文件因?yàn)槟硞€(gè)字段值里夾帶了換行符導(dǎo)致文件整體行數(shù)比預(yù)期多出幾百行這種問(wèn)題不校驗(yàn)很難發(fā)現(xiàn)。甚至有一種更極端的情況導(dǎo)出的文件很大表面看起來(lái)一切正常但在導(dǎo)入時(shí)發(fā)現(xiàn)文件里有個(gè)別特殊字符比如\0導(dǎo)致目標(biāo)庫(kù)無(wú)法正常寫入。這種問(wèn)題在源庫(kù)中就能通過(guò) SQL 查詢提前排查出來(lái)SELECT COUNT(*) FROM order_info WHERE field1 LIKE CONCAT(%, CHAR(0), %);從我在生產(chǎn)環(huán)境踩過(guò)的坑來(lái)看導(dǎo)出的工作從來(lái)不是“敲一條命令”那么輕巧它需要你對(duì)數(shù)據(jù)的流向有完整的認(rèn)知數(shù)據(jù)從哪里來(lái)、經(jīng)過(guò)什么加工、到哪里去、誰(shuí)在消費(fèi)它、消費(fèi)時(shí)對(duì)格式有什么要求。把這五個(gè)問(wèn)題想清楚你選用的工具和參數(shù)自然就對(duì)了。希望這篇從真實(shí)場(chǎng)景和踩坑經(jīng)歷出發(fā)的梳理能讓你以后在 MySQL 導(dǎo)數(shù)據(jù)這件事上少走一些彎路。