MySQL數據備份多種參數介紹及簡單示例

上篇文章中，我們介紹了幾種 MySQL 數據備份方式的原理，本文我們將和大家來共同探討這些數據備份方式如何來使用。關于數據備份的使用攻略，各路大神都有示例文章，本文對此做了簡單的總結和匯總。

Mysqldump

Mysqldump 是 MySQL 自帶的很好用的備份工具，常用于 MySQL 數據庫的邏輯備份，將 MySQL 服務器中的數據庫以標準的 sql 語言的方式導出，并保存到文件中。其語法為： mysqldump （選項）。

常用的語法有：

--add-drop-table ：在每個創建數據庫表語句前添加刪除數據庫表的語句；

--add-locks ：備份數據庫表時鎖定數據庫表；

--all-databases ：備份 MySQL 服務器上的所有數據庫；

--comments ：添加注釋信息；

--compact ：壓縮模式，產生更少的輸出；

--complete-insert ：輸出完成的插入語句；

--databases ：指定要備份的數據庫；

--default-character-set ：指定默認字符集；

--force ：當出現錯誤時仍然繼續備份操作；

--host ：指定要備份數據庫的服務器；

--lock-tables ：備份前，鎖定所有數據庫表；

--no-create-db ：禁止生成創建數據庫語句；

--no-create-info ：禁止生成創建數據庫庫表語句；

--password ：連接 MySQL 服務器的密碼；

--port ： MySQL 服務器的端口號；

--user ：連接 MySQL 服務器的用戶名。

數據備份的簡單實例：

mysqldump 導出整個數據庫 :

mysqldump -u 用戶名 -p 數據庫名 > 導出的文件名

例： mysqldump -u linuxde -p smgp_apps_linuxde > linuxde.sql

mysqldump 導出一個表

mysqldump -u 用戶名 -p 數據庫名 表名 > 導出的文件名

例： mysqldump -u linuxde -p smgp_apps_linuxde users > linuxde_users.sql

mysqldump 導出一個數據庫結構

mysqldump -u linuxde -p -d --add-drop-table smgp_apps_linuxde > linuxde_db.sql

Mydumper

mydumper 是 MySQL 數據庫備份的一個輕量級第三方開源工具，備份方式為邏輯備份，支持多線程，備份速度遠高于原生態的 mysqldump 。用法為： mydumper [OPTION...] multi-threaded MySQL dumping 

常用的語法：

-B, --database              需要備份的庫  

-T, --tables-list           需要備份的表，用逗號分隔  

-o, --outputdir             輸出文件的目錄  

-s, --statement-size        生成插入語句的字節數, 默認 1000000

-r, --rows                  分裂成很多行塊表  

-c, --compress              壓縮輸出文件  

-e, --build-empty-files     即使表沒有數據，還是產生一個空文件  

-x, --regex                 正則表達式: 'db.table'  

-i, --ignore-engines        忽略的存儲引擎，用逗號分隔  

-m, --no-schemas            不導出表結構  

-k, --no-locks              不執行共享讀鎖 警告：這將導致不一致的備份  

-l, --long-query-guard      設置長查詢時間,默認60秒， 

--kill-long-queries         kill掉長時間執行的查詢   

-b, --binlogs               導出binlog  

-D, --daemon                啟用守護進程模式  

-I, --snapshot-interval     dump快照間隔時間，默認60s，需要在daemon模式下  

-L, --logfile               日志文件  

-t, --threads               使用的線程數，默認4  

-C, --compress-protocol     在mysql連接上使用壓縮協議  

-V, --version               Show the program version and exit  

-v, --verbose               更多輸出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

數據備份的簡單示例

1 備份單個庫  

# mydumper -u 用戶名 -p 密碼 -B 需要備份的庫名 -o /tmp/bak

2 備份所有數據庫：全庫備份期間除了information_schema與performance_schema之外的庫都會被備份

# mydumper -u 用戶名 -p 密碼 -o /tmp/bak

 -o 輸出文件的目錄（備份輸出指定的目錄）

3 備份單表

# mydumper -u 用戶名 -p 密碼 -B 庫名 -T 表名 -o /tmp/bak

-T 需要備份的表，多表用逗號分隔 -o指定輸出備份文件路徑 

4 備份多表

# mydumper -u 用戶名 -p 密碼 -B 庫名 -T 表1,表2 -o /tmp/bak

5、當前目錄自動生成備份日期時間文件夾,不指定-o參數及值時默認為：export-20150703-145806

mydumper -u 用戶名 -p 密碼 -B 數據庫名字 -T 表名

6、不帶表結構備份表

# mydumper -u 用戶名 -p 密碼 -B 數據名字 -T 表名 -m

-m 不導出表結構

7、備份特定表

# mydumper -u 用戶名 -p 密碼 -B 數據庫名字  --regex=actor* -o /tmp/bak

只備份以actor*開頭的表

Xtrabackup

前兩種工具都是邏輯備份，而Xtrabackup是物理備份，是商業備份工具InnoDB Hotbackup的替代品，包含的兩個主要工具分別是xtrabackup和innobackupex。其中xtrabackup只能備份innodb和xtradb兩種引擎的表，而不能備份myisam引擎的表，而innobackupex是一個封裝了xtrabackup的Perl腳本，可同時備份innodb和myisam，但在對myisam備份時需要加一個全局的讀鎖，且myisam不支持增量備份。

另外，還有兩個相對比較小眾的工具分別是xbcrypt和xbstream ，xbcrypt是加解密用的，xbstream 類似于tar，是 Percona 自己實現的一種支持并發寫的流文件格式。

常用的參數

1) innobackupex 參數選項

--defaults-file=[MY.CNF]    //指定配置文件：只能從給定的文件中讀取默認選項。 且必須作為命令行上的第一個選項；必須是一個真實的文件，它不能是一個符號鏈接。

--databases=#    //指定備份的數據庫和表，格式為：--database="db1[.tb1] db2[.tb2]" 多個庫之間以空格隔開，如果此選項不被指定，將會備份所有的數據庫。

--include 傳遞給 xtrabackup --tables。

--tables-file=FILE    //此選項的參數需要是一個文件名，此文件中每行包含一個要備份的表的完整名稱，格式為databasename.tablename。該選項傳遞給 xtrabackup --tables-file，與--tables選項不同，只有要備份的表的庫才會被創建。

--compact    //創建緊湊型備份，忽略所有輔助索引頁，只備份data page；通過--apply-log中重建索引--rebuild-indexs。

--compress    //此選項指示xtrabackup壓縮備份的InnoDB數據文件，會生成 *.qp 文件。

--decompress    //解壓縮qp文件，為了解壓縮，必須安裝 qpress 工具。

--no-timestamp    //指定了這個選項備份將會直接存儲在 BACKUP-DIR 目錄，不再創建時間戳文件夾。

想查看更多的參數，請戳：

https://www.percona.com/doc/perconaxtrabackup/2.4/innobackupex/innobackupex_option_reference.html

2) xtrabackup 參數選項

--apply-log-only    //這個選項使在準備備份(prepare)時，只執行重做(redo)階段，這對于增量備份非常重要。

--backup   //進行備份并將其放入

--check-privileges   //此選項檢查Percona XtraBackup是否具有所有必需的權限。

--compact  //跳過輔助索引頁來創建壓縮備份。

想查看更多的參數，請戳: 

https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/xbk_option_reference.html

數據備份的簡單示例

innobackupex完全備份

#備份所有數據庫：備份目錄里生成日期命名的文件夾

innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123 /home/zhoujy/xtrabackup/

#備份所有數據庫：指定備份目錄

innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123 --no-timestamp /home/zhoujy/xtrabackup/

#備份指定數據庫名，多個數據庫用空格分開

innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123 --no-timestamp --databases="dba_test xtra_test" /home/zhoujy/xtrabackup/

#備份指定表

備份不同庫下的不同表

1：innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123 --no-timestamp --databases="dba_test.tb1 xtra_test.M" /home/zhoujy/xtrabackup/

備份一個庫下面的表，支持正則，如：--include='^mydatabase[.]mytable' 

2：innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123 --no-timestamp --include='xtra_test.I' /home/zhoujy/xtrabackup/

備份指定文件里的表，文件里每行的格式是：dbname.tbname

3：innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123 --no-timestamp --tables-file=/tmp/tbname.txt  /home/zhoujy/xtrabackup/

innobackupex 增量備份

#全量備份，這里舉例單個表，也可以是指定幾個庫，甚至所有庫

innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123 --no-timestamp --databases="xtra_test.I" /home/zhoujy/xtrabackup/

#增量備份1

innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123 --no-timestamp --databases="xtra_test.I" --incremental-basedir=/home/zhoujy/xtrabackup/  --incremental /home/zhoujy/increment_data/

#增量備份2

innobackupex --defaults-file=/etc/mysql/my.cnf --user=root --password=123 --no-timestamp --databases="xtra_test.I" --incremental-basedir=/home/zhoujy/increment_data/ --incremental /home/zhoujy/increment_data1/

Xtrabackup全量備份

1：指定--defaults-file

xtrabackup --defaults-file=/etc/mysql/my.cnf --user=root --password=123  --backup --target-dir=/home/zhoujy/xtrabackup/

2：用--datadir取代--defaults-file

xtrabackup --user=root --password=123  --backup --datadir=/var/lib/mysql/ --target-dir=/home/zhoujy/xtrabackup/

Xtrabackup增量備份

#備份，這里指定幾個庫和表，也可以是所有庫

1：庫全量備份

xtrabackup --user=root --password=123 --datadir=/var/lib/mysql/ --backup --parallel=3 --databases="xtra_test dba_test" --target-dir=/home/zhoujy/xtrabackup/

2：增量備份

xtrabackup --user=root --password=123 --datadir=/var/lib/mysql/ --backup --parallel=3 --databases="xtra_test dba_test" --target-dir=/home/zhoujy/xtrabackup1/ --incremental-basedir=/home/zhoujy/xtrabackup/

注意：要是有多個增量備份，第2個增量需要指定第一個增量的目錄。和innobackupex一樣。