第一種  mysqldump:

這是mysql自帶的備份命令,提供了很多擴充套件引數可供選擇,工作中常用此方式

常用語法:

   mysqldump[options] database [tables,多個表用空格隔開]

                          --databases [options] BD1 [DB2..多個庫用空格隔開]

                          --all-databases [options]

匯出(備份):  匯出庫 > SQL檔案(本地匯入匯出不需要-P3306 -h 192.168.1.25)(-q –e寫法可以合併)

mysqldump -P 3306 -h 192.168.1.25 -uroot -p -q -e base_push > C:\Users\thinkive\Desktop\base_push20170921.sql

-P 3306 -h 192.168.1.25      是遠端mysql地址和埠

-uroot                                    是遠端使用者名稱為root

base_push                            是遠端庫名

以上是匯入匯出資料的語句，該方法15分鐘匯出1.6億條記錄，匯出的檔案中平均7070條記錄拼成一個insert語句，通過source進行批量插入，匯入1.6億條資料耗時將近5小時。平均速度：3200W條/h。後來嘗試加上--single-transaction引數，結果影響不大。另外，若在匯出時增加-w引數，表示對匯出資料進行篩選，那麼匯入匯出的速度基本不變，篩選出的資料量越大，時間越慢而已。對於其中的引數這裡進行說明：

[options]引數	含義
-q   , --quick	在匯出大表時很有用，它強制 mysqldump 從伺服器查詢取得記錄直接輸出而不是取得所有記錄後將它們快取到記憶體中
-c	是在insert中增加具體的欄位名。這樣對目的表結構不同原表，情況下更有用
-e  , --extended-insert	用具有多個VALUES列的INSERT語法。這樣使匯出檔案更小，並加速匯入時的速度。預設為開啟狀態，使用--skip-extended-insert取消選項
-t	僅匯出表資料，不匯出表結構
--opt –d	僅匯出表結構
-R , --routines	匯出儲存過程以及自定義函式。
--triggers	匯出觸發器,預設啟用
-E  ,  --events	匯出事件
--single-transaction	該選項在匯出資料之前提交一個BEGIN SQL語句，BEGIN 不會阻塞任何應用程式且能保證匯出時資料庫的一致性狀態。它只適用於多版本儲存引擎，僅InnoDB。本選項和--lock-tables 選項是互斥的，因為LOCK TABLES 會使任何掛起的事務隱含提交。要想匯出大表的話，應結合使用--quick 選項。在本例子中沒有起到加快速度的作用 mysqldump -uroot -p --host=localhost --all-databases --single-transaction
--master-data=[1/2]	如果值等於1，就會新增一個CHANGE MASTER語句(二進位制檔案的名稱和位置)   如果值等於2，就會在CHANGE MASTER語句前新增註釋  -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;   如果加入此配置,mysql說Binlogging on server not active,解決方案如下: Linux:需要在/etc/my.cnf裡的[mysqlid]下方加入: log-bin=mysql-bin  再重啟 Windows:mysql安裝目錄下my.ini檔案,如上操作
--all-databases  , -A	匯出全部資料庫
--add-drop-database	每個資料庫建立之前新增drop資料庫語句。
--add-drop-table	每個資料表建立之前新增drop資料表語句。(預設為開啟狀態，使用--skip-add-drop-table取消選項)
-w  ,  --where	過濾條件,只支援單表資料條件匯出 mysqldump –ubackup –p –master-data=2 –where "id>10 and id<20" orderdb order > order.sql

如果想要看更多的擴充套件引數可以看官網介紹

[all options] https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

匯入: 庫 < SQL檔案(本地匯入匯出不需要-P 3306 -h 192.168.1.25)

mysql -P 3306 -h 127.0.0.1 -uroot -p base_push < C:\Users\thinkive\Desktop\base_push.sql

            遠端埠  遠端ip      使用者名稱       庫名            匯入檔案路徑

第二種 mysqlpump:

與mysqldump相比:

支援基於表的並行匯出功能(引數--default-parallelism，預設為2，引數--parallel-schemas，控制並行匯出的庫)

匯出的時候帶有進度條（引數--watch-progress，預設開啟）

支援直接壓縮匯出匯入（壓縮演算法引數--compress-output=zlib或lz4 (生成同樣字尾名檔案,zlib速度較lz4快,體積也較小,解壓縮:zlib_decompress input_file(如intput.zlib) output_file(如output.sql)   lz4類似(為mysql5.7.10命令) 不推薦使用,解壓縮後資料不完全)

注:

   mysqldump匯出的檔案沒有庫的限制(以及建庫語句等),所以可以遷入與牽出庫名不同.

   mysqlpump匯出的庫建表有庫名的限制(預設也含建庫語句),所以遷入庫不需要建立(但需要與遷出庫名相同)

 

對比測試：
1.mysqlpump(支援多程式)壓縮備份test資料庫(21G)三個併發執行緒備份，消耗時間：2m42.515s，gzip壓縮率要比LZ4大
mysqlpump -uroot -p -h127.0.0.1 --single-transaction--default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3 -Btest | gzip > d:\temp\mysqlpump.test.gz

2.mysqldump(單程式)壓縮備份test資料庫(21G)，消耗時間：28m9.930s，gzip壓縮率要比LZ4大(gzip命令需要在linux環境使用)
mysqldump -uroot -p -h127.0.0.1 --default-character-set=utf8 -P3306 --skip-opt--add-drop-table --create-options  --quick --extended-insert--single-transaction -B test | gzip >/data/mysql/mysql3306/data/test_db.sql.gz

3.mydumper壓縮備份test資料庫(21G)，開三個併發執行緒，消耗時間：10m10.207s 
mydumper -u test -h 127.0.0.1 -p safe2016 -P 3306 -t 3 -c -B test -o /data/mysql/mysql3306/data/

4.mydumper壓縮備份test資料庫(21G)，三個併發執行緒備份，並且開啟對一張表多個執行緒以chunk的方式批量匯出，消耗時間：10m9.518s
mydumper -u test -h 127.0.0.1 -p safe2016 -P 3306 -t 3 -r 300000 -c -B test -o/data/mysql/mysql3306/data/

 

從上面看出，mysqlpump的備份效率是最快的，mydumper次之，mysqldump最差。所以在IO允許的情況下，能用多執行緒就別用單執行緒備份。並且mysqlpump還支援多資料庫的並行備份，而mydumper要麼備份一個庫，要麼就備份所有庫。
由於實際情況不同，測試給出的速度提升只是參考。到底開啟多少個並行備份的執行緒，這個看磁碟IO的承受能力，若該伺服器只進行備份任務，可以最大限制的來利用磁碟.

 

mysqlpump的並行匯出功能的架構為：佇列+執行緒，允許有多個佇列，每個佇列下有多個執行緒，而一個佇列可以繫結1個或者多個資料庫。但是，對於每張表的匯出只能是單個執行緒的

mydumper支援一張表多個執行緒以chunk的方式批量匯出，這在主鍵是隨機的情況下，匯出速度還能有提升

MySQL 5.7.11版本解決了一致性備份問題，推薦線上環境使用

 

mysql壓縮成gz方式匯入與匯出(linux環境)     一定資料:匯出成sql格式:63MB, gz格式:2.78MB

mysqlpump [options] –B database_name | gzip> /opt/database_name.sql.gz

gunzip < backupfile.sql.gz | mysql -u使用者名稱 -p密碼（也可不輸入）資料庫名

 

命令示例(--compress-output=zlib壓縮引數必須相應生成壓縮檔案,linux環境`>`前可加`| gzip`生成相應gz檔案,不然為sql):

mysqlpump –P 3306 -h192.168.1.189 -uroot -p--single-transaction --default-character-set=utf8 --default-parallelism=3

-B thinkive_base_push > D:\Administrator\Desktop\thinkive.sql

[all options] https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

選項	Description	介紹
--add-drop-database	在每個CREATE DATABASE語句之前新增DROP DATABASE語句
--add-drop-table	在每個CREATE TABLE語句之前新增DROP TABLE語句
--add-drop-user	在每個CREATE USER語句之前新增DROP USER語句
--add-locks	用LOCK TABLES和UNLOCK TABLES語句環繞每個錶轉儲
--all-databases	轉儲所有資料庫
--bind-address	使用指定的網路介面連線到MySQL伺服器
--character-sets-dir	安裝字符集的目錄
--complete-insert	使用包含列名稱的完整INSERT語句
--compress	壓縮客戶端和伺服器之間傳送的所有資訊
--compress-output	輸出壓縮演算法,zlib或lz4(生成同樣檔案,zlib速度較lz4快,體積也較小), 預設不壓縮,生成sql檔案; 如使用此引數,須指定生成的壓縮檔案副檔名
--databases / -B	匯出指定多個庫,如mysqlpump--databases db_name1 db_name2 ... 匯出指定庫的多個表,如mysqlpump db_name tbl_name1 tbl_name2
--debug	編寫除錯日誌
--debug-check	程式退出時列印除錯資訊
--debug-info	列印程式退出時的除錯資訊，記憶體和CPU統計資訊
--default-auth	身份驗證外掛使用
--default-character-set	指定預設字符集
--default-parallelism	並行處理的預設執行緒數(較mysqldump新增)
--defaults-extra-file	除了通常的選項檔案外，還要讀取已命名的選項
--defaults-file	只讀取命名的選項檔案
--defaults-group-suffix	選項組字尾值
--defer-table-indexes	對於重新載入，將索引建立推遲到載入錶行之後
--events	從轉儲的資料庫轉儲事件
--exclude-databases	要從轉儲中排除的資料庫
--exclude-events	要從轉儲中排除的事件
--exclude-routines	從轉儲中排除的例程
--exclude-tables	要從轉儲中排除的表
--exclude-triggers	觸發器從轉儲中排除
--exclude-users	使用者從轉儲中排除
--extended-insert	使用多行INSERT語法
--help	顯示幫助資訊並退出
--hex-blob	使用十六進位制符號轉儲二進位制列
--host	主機連線到（IP地址或主機名）
--include-databases	要包含在轉儲中的資料庫
--include-events	包含在轉儲中的事件
--include-routines	包含在轉儲中的例程
--include-tables	要包含在轉儲中的表
--include-triggers	觸發器包含在轉儲中
--include-users	使用者包含在轉儲中
--insert-ignore	寫INSERT IGNORE而不是INSERT語句
--log-error-file	將警告和錯誤附加到指定的檔案
--login-path	閱讀.mylogin.cnf中的登入路徑選項
--max-allowed-packet	傳送到伺服器或從伺服器接收的最大資料包長度
--net-buffer-length	TCP / IP和套接字通訊的緩衝區大小
--no-create-db	不要寫CREATE DATABASE語句(如果遷出庫名與原庫名不一致),預設包含建庫
--no-create-info	不要編寫重新建立每個轉儲表的CREATE TABLE語句
--no-defaults	讀取任何選項檔案
--parallel-schemas	指定模式處理並行性
--password	連線到伺服器時使用的密碼
--plugin-dir	安裝外掛的目錄
--port	用於連線的TCP / IP埠號
--print-defaults	列印預設選項
--protocol	使用連線協議
--replace	編寫REPLACE語句而不是INSERT語句
--result-file	直接輸出到給定的檔案
--routines	從轉儲的資料庫轉儲儲存的例程（過程和函式）
--secure-auth	不要以舊（4.1之前）格式傳送密碼到伺服器
--set-charset	新增SET NAMES default_character_set來輸出
--set-gtid-purged	是否新增SET @@ GLOBAL.GTID_PURGED輸出	5.7.18
--single-transaction	在單個事務中轉儲表(5.7.9後--default-parallelism多執行緒才能與其合用,且其與--add-locks互斥)
--skip-definer	從檢視和儲存的程式CREATE語句中刪除DEFINER和SQL SECURITY子句
--skip-dump-rows	不要轉儲錶行
--socket	用於連線到localhost，要使用的Unix套接字檔案
--ssl	啟用加密連線
--ssl-ca	包含受信任的SSL證照頒發機構列表的檔案
--ssl-capath	包含可信SSL證照頒發機構證照檔案的目錄
--ssl-cert	包含X509證照的檔案
--ssl-cipher	連線加密允許的密碼列表
--ssl-crl	包含證照吊銷列表的檔案
--ssl-crlpath	包含證照撤銷列表檔案的目錄
--ssl-key	包含X509金鑰的檔案
--ssl-mode	伺服器連線的安全狀態	5.7.11
--ssl-verify-server-cert	根據伺服器證照通用名稱身份驗證主機名稱
--tls-version	允許加密連線的協議	5.7.10
--triggers	每個轉儲表的轉儲觸發器
--tz-utc	新增SET TIME_ZONE ='+ 00:00'轉儲檔案
--user	連線到伺服器時使用的MySQL使用者名稱
--users	轉儲使用者帳戶
--version	顯示版本資訊並退出	5.7.9
--watch-progress	顯示進度指示器

第三種 通過data資料資料夾內容進行備份、還原:

直接複製data資料資料夾下相應庫和ibdata1檔案遷移到新庫中(新庫最好是空白的,以免資料覆蓋),筆者在遷移新電腦時 裝完mysql,就懶得把原機器mysql中的各個庫像前列方法那樣拷貝遷移,所以索性直接複製原機器的data下資料檔案替換到新電腦的mysql相應路徑,經測試,沒有問題.