mysql 匯入大資料sql檔案

標籤：

匯出Sql檔案

在匯出時合理使用幾個參數，可以大大加快匯入的速度。

-e 使用包括幾個VALUES列表的多行INSERT文法;
–max_allowed_packet=XXX 用戶端/伺服器之間通訊的緩衝區的最大大小;
–net_buffer_length=XXX TCP/IP和通訊端通訊緩衝區大小,建立長度達net_buffer_length的行

注意：max_allowed_packet和net_buffer_length不能比目標資料庫的配置數值大，否則可能出錯。
例子：

mysql>mysqldump -uroot -p discuz  -e --max_allowed_packet=1048576 --net_buffer_length=16384 > discuz.sql

查看設定檔路徑

如果不清楚MySQL當前使用的設定檔路徑，可以嘗試這樣查看:

which mysqld/usr/sbin/mysqld/usr/sbin/mysqld --verbose --help |grep -A 1 ‘Default options‘

從可以看出, 伺服器首先會讀取/etc/my.cnf檔案，如果發現該檔案不存在，再依次嘗試從後面的幾個路徑進行讀取。

SHOW STATUS; #伺服器狀態變數，運行伺服器的統計和狀態指標SHOW VARIABLES; #伺服器系統變數，實際上使用的變數的值SHOW STATUS LIKE  ‘%變數名%‘

max_allowed_packet

用戶端/伺服器之間通訊的緩衝區的最大大小,適量調大可以加快mysql匯入資料的速度。

show VARIABLES like ‘%max_allowed_packet%‘;

1. 可以編輯my.cnf來修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段進行修改。(在[mysqld]下加上max_allowed_packet=16M，儲存重啟mysql後)
   max_allowed_packet = 20M
   如果找不到my.cnf可以通過
   mysql --help | grep my.cnf
   去尋找my.cnf檔案。

1. 進入mysql server
   在mysql 命令列中運行
   set global max_allowed_packet = 2*1024*1024*10
   然後關閉掉這此mysql server連結，再進入。
   show VARIABLES like ‘%max_allowed_packet%‘;
   查看下max_allowed_packet是否編輯成功

innodb_flush_log_at_trx_commit

配置有0,1,2三種配置

• 如果innodb_flush_log_at_trx_commit的值為0, log buffer每秒就會被刷寫記錄檔到磁碟，提交事務的時候不做任何操作。（執行是由mysql的master thread線程來執行的。主線程中每秒會將重做日誌緩衝寫入磁碟的重做記錄檔(REDO LOG)中。不論事務是否已經提交。）預設的記錄檔是ib_logfile0,ib_logfile1

• 當設為預設值1的時候，每次提交事務的時候，都會將log buffer刷寫到日誌。

• 如果設為2,每次提交事務都會寫日誌，但並不會執行刷的操作。每秒定時會刷到記錄檔。要注意的是，並不能保證100%每秒一定都會刷到磁碟，這要取決於進程的調度。每次事務提交的時候將資料寫入交易記錄，而這裡的寫入僅是調用了檔案系統的寫入操作，而檔案系統是有 緩衝的，所以這個寫入並不能保證資料已經寫入到物理磁碟

• 預設值1是為了保證完整的ACID。當然，你可以將這個配置項設為1以外的值來換取更高的效能，但是在系統崩潰的時候，你將會丟失1秒的資料。設為0的話，mysqld進程崩潰的時候，就會丟失最後1秒的事務。設為2,只有在作業系統崩潰或者斷電的時候才會丟失最後1秒的資料。InnoDB在做恢複的時候會忽略這個值。

故在匯入資料時可以暫時設定為0，以獲得更大的效能，加快匯入。

加快插入資料方法

[footnote][footnote2]Bulk Data Loading for InnoDB Tables

SET autocommit=0;SET UNIQUE_CHECKS=0;SET FOREIGN_KEY_CHECKS=0;insert into tablename values (...),(...),(...)SET UNIQUE_CHECKS=1;SET FOREIGN_KEY_CHECKS=1;COMMIT;

innodb_autoinc_lock_mode變數設定為2

即使insert採用了上述那麼多策略但是速度依舊沒有load [local] data infile快

[^footnote]: Bulk Data Loading for InnoDB Tables

[^footnote2]:Speed of INSERT Statements

mysql 匯入大資料sql檔案