如何使用 MySQL 為數據庫轉儲批處理多個插入語句？

我有一個帶有一些大表的 MySQL 數據庫。特別是，最大的表（到目前為止）目前有大約 11,000,000 行。根據我執行的查詢，表的時鐘大約為 1.7GB，但它是 MyISAM，顯然這個數字是不可靠的（但無論如何它是一個大表）。該數據庫每天使用mysqldump.

目前，從這個轉儲文件恢復數據庫大約需要 10 個小時，而且只會變得更糟。我正在使用該--skip-extended-insert選項，因為對於表中的所有行使用單個多值插入語句，恢復將失敗。我的理解是max_allowed_packet可以擴展到1GB，但這還不夠。

我認為這將是一個足夠普遍的問題，並且可能有一個普遍接受的解決方案，但我找不到任何東西。所以，我的問題是：

• 是否mysqldump有一個選項允許為每個表批量插入多值插入，例如，不是創建單個插入，而是為n行創建多個插入
• 如果沒有，是否有其他實用程序/替代方法來完成此操作，或者我是否必須滾動自己的備份腳本，n使用限制子句批量查詢表行並自己手動創建插入語句？

MySQL v5.5.40

表是 MyISAM 和 InnoDB 的混合體。備份在本地進行。

您的問題可能涉及作業系統或 MySQL 的配置設置

歷史例子

幾年前，當我在一家網站公司工作時，我使用 –skip-extended-insert 來載入具有 2GB RAM 的數據庫伺服器。這對我來說很有意義，因為我從中轉儲它的伺服器有 64GB 的 RAM，而客戶端希望將數據放在更小的機器上。

在我目前的工作崗位上，外部供應商從 SQL Server 轉儲數據並生成了與 mysqldump 兼容的轉儲文件。該轉儲文件的問題是每個表都有一個包含所有行的插入。一張桌子有 350 萬。它們無法載入到內部 VM 中，配置的 RAM 將小於 8GB。令人震驚的是，解決方案是將文件 scp 到可能的筆記型電腦，將其載入到我筆記型電腦上的 MySQL 5.5.37 中，從中獲取 mysqldump，將新的 mysqldump 發送回 Linux CentOS VM，然後載入 mysqldump。

順便說一句，350 萬行表在 4.5 小時內作為單個插入載入到我的 Windows 7 筆記型電腦中，而無需更改任何 MySQL 設置。老實說，我確實相信“在黑暗中射擊”會起作用，因為我沒有在筆記型電腦上重新配置 mysqldump 以使用轉儲。我的猜測是它可能已載入，因為 ibdata1 中的撤消日誌剛剛在磁碟上增長並保存了所有內容。

skip-extended-insert如果您在同一台機器上重新載入時轉儲，您不應該生活。

建議

在我輸入答案時，我看到您在問題中註入了以下內容

根據我執行的查詢，表的時鐘大約為 1.7GB，但它是 MyISAM，顯然這個數字是不可靠的（但無論如何它是一個大表）。

建議 #1

如果那個 MyISAM 表有 1.7GB 並且有 1100 萬行，我的建議是增加批量插入緩衝區的大小。批量插入緩衝區僅供 MyISAM 使用，並支持擴展插入。將bulk_insert_buffer_size設置為 256M。

轉到 my.cnf 或 my.ini 並添加這個

bulk_insert_buffer_size = 256M

然後，登錄 MySQL 並執行

mysql> SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

建議 #2

至於您的 InnoDB，您需要使用更大的日誌緩衝區。通過將其添加到 my.cnf 將其設置為 64M

innodb_log_buffer_size = 64M

您必須重新啟動 MySQL 才能使用該新值。

建議#3

通過增加innodb_log_file_size ，您可能還需要更大的 InnoDB 日誌。這需要手動干預，如下所示：

步驟 01：以 root 身份執行此命令

mysql> SET GLOBAL innodb_fast_shutdown = 0;

步驟 02：關閉 mysql

• Linux：service mysql stop
• Windows（作為管理員）：net stop mysql

步驟 03：將此選項添加到my.cnf或my.ini

innodb_log_file_size = 1G

步驟 04：在作業系統中，進入 ib_logfile0 和 ib_logfile1 所在的文件夾並執行

Linux

mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

視窗

rename ib_logfile0 ib_logfile0.bak
rename ib_logfile1 ib_logfile1.bak

步驟 05：啟動 mysql（重新創建日誌文件，因此啟動需要額外的 1-2 分鐘）

• Linux：service mysql start
• Windows（作為管理員）：net start mysql

在做出一項或多項建議的更改後，嘗試轉儲和重新載入。它變得更加順利。從剛開始SUGGESTION #1嘗試。嘗試其他兩個，看看它是否改善更多。

概括

您的 my.cnf 或 my.ini 中的目標 MySQL DB 伺服器需要這些選項

bulk_insert_buffer_size = 256M
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
max_allowed_packet = 1G

試一試 ！！！

引用自：https://dba.stackexchange.com/questions/82774