我有一個 mysql 的 sql 檔，大約 1.9G，當我做匯入該檔時，整個資料庫主機幾乎 hold 住了，其他人的操作會卡住，有沒有什麼辦法或參數設定可以解決大量匯入時的卡機問題？或者，只能利用三更半夜來做？
同樣的情況，匯出大資料庫時（mysqldump），也一樣有卡機的情形。
請教諸位先進，對於大資料庫的匯出匯入，有沒有什麼建議？
已邀請的邦友 {{ invite_list.length }}/5
匯入和匯出卡住的原因不同..
匯入卡住, 是因為他連續執行 sql 檔案裡面的 insert 或 update 指令, 每一個指令都需要消耗好幾筆(或者幾十筆) I/O Write 指令, 即便資料量很少, 但是 I/O Write 次數很多, 代表你的 Disk 需要承受非常大量的 IOPS (?? Disk Write/sec) 而不是 Throughput (?? MB/Sec).
一般消費級 SATA 的單顆 HDD 的 IOPS 大約只有 150~200/Sec 之間, 企業級 SAS 大約在 200~250 左右, 多顆組成 RAID 可以向上增加 IOPS; 但是 MySQL import 產生的 IOPS, 可能瞬間高達 6,000~15,000 Write I/O, 這時候如果你沒有做 log 寫入分離, 同時磁碟子系統無法上衝到這個數字的話, 結果就是大家停下來等, 等你的 Disk 把剩餘的 Write 全部寫入完畢之後, 才能繼續作業....
匯出的狀況可能不太相同, 因為匯出的 select 指令, 他的 IOPS 可以被安排成循序大量執行, 只要你 Disk 的 Throughput 夠高, 不至於卡住 (當然, 不夠的話還是會卡). 但另外一個卡住你的真正原因是: mysqldump 匯出的時候, 他會先發動 Table lock, 也就是在匯出作業完成之前, 這個 Table 不能有人去操作他. 如此一來, 其他人想要存取的時候, 如果正好遇上你在 export 他要寫入的 Table, 就會卡住直到 timeout 或者你的匯出結束.
MySQL Pump 這個比 mysqldump 快,功能更多.
https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html
5.7版就有了
https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html
https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html#mysqlpump-parallelism 這裡有講到 Parallel Processing
https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html#option_mysqlpump_single-transaction
可以試試看使用 --single-transaction 這個與 --add-locks 是互斥的.
這裡有講到 compress output
https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html#option_mysqlpump_compress-output
https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html#option_mysqlpump_zstd-compression-level
Decompress
https://dev.mysql.com/doc/refman/8.0/en/lz4-decompress.html
https://dev.mysql.com/doc/refman/8.0/en/zlib-decompress.html
可以測試評估看看.
感恩殺豬大的指點名燈，我再一一測試看看，測完再報告。
以前的檔案都算小，不曾遇過問題，這回檔太大了，光 sql 檔就近 2G 了，再過幾年，又不知要膨脹到什麼地步，這不得不好好學習解決之道。
咦？ mariadb 沒有 MySQL Pump ?
你可以用mysql 的 mysqlpump 去備份 Mariadb 看看.
--single-transaction 這個參數,你可以參考看看.mysqldump
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_single-transaction
感恩殺豬大，【--single-transaction】這個參數，確實有助於 mysqldump 不會卡住別人，很有助益。
但，匯入部份呢？匯入大資料庫是卡機問題，有什麼參數可用嗎？
你可以用mysql 的 mysqlpump 去備份 Mariadb 看看.
這個行不通，我有測試，我若要單獨安裝 mysqlpump 它會移除我的 mariadb-server , 不能並存，左右為難。
我猜你是用Ubuntu,建議可以先在別台安裝MySQL 把這些工具都安裝好.然後複製留起來,放到USB Disk或雲端.然後就可以另外copy到你要工作的機器上的不同目錄.使用時,搭配絕對路徑方式呼叫.
這裡有另外一個
https://github.com/mydumper/mydumper
這裡有一篇比較,percona 是在MySQL方面提供第三方工具與服務等的公司.
https://www.percona.com/blog/dump-performance-comparison-mysqldump-vs-mysql-shell-utilities-vs-mydumper/
裡面你可以看到還有一個 MySQL Shell, 這是Oracle在比較新的版本中提供的.這些因為是後續開發的,所以速度都比mysqldump 快很多.
你提到 匯入大資料庫是卡機問題，有什麼參數可用嗎？
這裡有個工具程式,MariaDB 也有的.
https://mariadb.com/kb/en/mysqlimport/
裡面參數除了 lock-tables default FALSE,
還有 low-priority default FALSE, 但這只是優先權調整而已.
這是匯入,不是 restore.匯入的守備範圍較小.
因為MySQL常用的備份格式是產生SQL Command的方式,會先產生DDL,
定義物件,再使用 insert 方式匯入資料.
另外的方式,較為繁瑣.
1.先使用 mysqldump備份DDL, 使用 --no-data 參數.
2.再使用 mysqldump 的 tab 參數,搭配其他選項(--fields-terminated-by 等)
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#option_mysqldump_tab
這個方式有一定的限制,不能遠端,要有file 權限.
3.匯入時使用上面提到的mysqlimport.
這樣的方式,只是一種輔助的方式.
以上先提出一些工具的作法,提供老哥參考.
感恩殺豬大提攜指教，我會一一測試，再提出報告，因為資料量實在不小，別的版友將來也許會用到。
iThome鐵人賽
看影片追技術