navicat連接資料庫的方法：navicat資料庫遷移報錯

今天本是晴天，到了下午天一下子暗起來了，不久便下起了小雨，沒辦法外出，就在家鼓搗起了MySQL資料庫服務。

前一段時間做內訓，演示資料庫複製服務，通過資料庫客戶端工具Navicat Premium導入樣例資料庫時，只導入了資料庫結構，數據卻一條都沒導入，當時就覺得納悶，好在不影響演示，打算有空了查一下。今天恰好有點時間，從下午四點多，一直鼓搗到晚上，差不多搞明白了原因，同時也嘗試了通過mysql資料庫服務的參數調優來提高大數據量的導入性能。

01 實驗環境

1、本地win10，需要安裝並配置好mysql，怎麼安裝百度查；

2、安裝客戶端工具Navicat Premium，怎麼安裝百度查；

3、華為雲&天翼雲上開通mysql資料庫服務，我選擇最低配1c2g，SAS盤，並配置了2M的EIP；

4、下載mysql示例資料庫，大概有近400萬條數據，具體下載地址忘了，如果需要可以@我。

02 Navicat 導入數據失敗

先從日誌里查找原因，提示是語法錯誤，不斷的去嘗試，發現在Navicat工具的mysql命令行窗口，僅僅輸入source命令居然也報錯，百思不得其解，各種百度，可能的原因是source命令是mysql客戶端的工具集，Navicat可能不支持，要導入還是要直接在命令行窗口執行。

03 本地批量數據導入

1、以系統管理員的許可權打開cmd窗口；

2、運行 net start mysql，顯示「服務已經啟動成功」，如下圖：

3、進入到employees樣例資料庫文件夾，執行導入資料庫命令：

mysql -h 125.124.74.17 -P 8635 -uroot -pTest@1234 < employees.sql

「<」符號表示導入資料庫文件，執行結果如下：

圖中導入數據成功，耗時4分42秒，通過Navicat工具能夠直觀的看到導入的資料庫、表、視圖及數據等，如下圖：

04 參數調優

通過不斷地嘗試，針對大數據量的批量導入，在華為雲&天翼雲服務端可以調優的參數有2個，分別是
innodb_flush_log_at_trx_commit和bulk_insert_buffer_size，我們可以在華為雲&天翼雲mysql服務的參數組界面修改參數，如下圖：

innodb_flush_log_at_trx_commit

該參數控制重做日誌寫入磁碟的過程，系統默認為1，表示事務提交時，會將重做日誌緩衝寫入磁碟，並且立即刷新.也即是說一旦事務完成，數據立即寫盤，以確保數據不會丟失。但對於批量導入大量數據的情況，會導致頻繁寫盤，影響導入性能。我們這裡將該參數設置為0，表示每間隔1秒再寫盤。這裡我們把參數從1改為0,如圖：

bulk_insert_buffer_size

用來緩存批量插入數據的時候臨時緩存寫入數據，大數據的sql文件能達到幾百M乃至上G，默認8M，確實有點小，這裡我們改到120M。

修改參數後保險期間需重啟資料庫實例。

05 性能優化效果

先是修改了
innodb_flush_log_at_trx_commit參數，導入數據耗時00:04:14，快了28秒；

然後再修改bulk_insert_buffer_size參數，耗時00:03:42，此次調優提升了32秒；

相比沒有調優之前，整體快了60秒，相當於性能提升了了27%！

06 總結

通常用戶在雲端開了RDS服務後，需要將本地私有雲的數據批量導入到雲端，對數據量達到千萬級的情況，需要綜合來考慮，比如數據可以分批導入，為提升導入性能，可以調整RDS的相關參數，以加快數據的導入速度，使客戶的業務中斷時間降低到最小。當然除了手動導入，也可以通過購買資料庫複製服務來實現在線的數據導入，好處是能夠避免人工導入帶來的疏忽，並能實現業務的平滑遷移。

最後附上導入的資料庫的相關表的數據條數：

SELECT count(*) from salaries
2844047
SELECT count(*) from employees
300024
SELECT count(*) from titles
443308
SELECT count(*) from dept_emp
331603