mysql 數(shù)據(jù)庫(kù)備份的多種實(shí)現(xiàn)方式總結(jié)

本文實(shí)例講述了mysql 數(shù)據(jù)庫(kù)備份的多種實(shí)現(xiàn)方式。分享給大家供大家參考，具體如下：

一、使用mysqldump進(jìn)行備份

1、完整備份所有數(shù)據(jù)庫(kù)

mysqldump -u root -p --all-databases > E:/all.sql

在mysql8之前，存儲(chǔ)過(guò)程和事件存儲(chǔ)在mysql.proc和mysql.event表中。

從mysql8開(kāi)始，相應(yīng)對(duì)象的定義存儲(chǔ)在數(shù)據(jù)字典中，這些表不會(huì)被備份。

要將存儲(chǔ)過(guò)程和事件也包含，請(qǐng)使用如下語(yǔ)句：

mysqldump -u root -p --all-databases --routines --events > E:/all.sql

2、時(shí)間點(diǎn)恢復(fù)

要獲得時(shí)間點(diǎn)恢復(fù)，應(yīng)該指定--single-transaction 和 --master-data

--single-transaction 在備份之前，會(huì)將事務(wù)隔離級(jí)別設(shè)為REPEATABLE READ模式，并執(zhí)行 START TRANSACTION 來(lái)提供一致的備份。

--master-data 將服務(wù)器的二進(jìn)制日志的位置輸出到 sql 文件。

mysqldump -u root -p --all-databases --routines --events --single-transaction --master-data > E:/all.sql

--master-data = 2表示在導(dǎo)出過(guò)程中，記錄當(dāng)前庫(kù)的binlog和pos點(diǎn)，并在導(dǎo)出文件中注釋這一行。

--master-data = 1表示在導(dǎo)出過(guò)程中，記錄當(dāng)前庫(kù)的binlog和pos點(diǎn)，并在導(dǎo)出文件中不注釋這一行。

3、在從庫(kù)導(dǎo)出時(shí)，記錄主庫(kù)的二進(jìn)制日志位置

mysqldump -u root -p --all-databases --routines --events --single-transaction --dump-slave > E:/all.sql

--dump-slave = 2表示在導(dǎo)出過(guò)程中，記錄主庫(kù)的binlog和pos點(diǎn)，并在導(dǎo)出文件中注釋這一行。

--dump-slave = 1表示在導(dǎo)出過(guò)程中，記錄主庫(kù)的binlog和pos點(diǎn)，并在導(dǎo)出文件中不注釋這一行。

4、指定數(shù)據(jù)庫(kù)和表導(dǎo)出

mysqldump -u root -p --databases 數(shù)據(jù)庫(kù) > E:/bak.sql
mysqldump -u root -p --databases 數(shù)據(jù)庫(kù) --tables 數(shù)據(jù)表 > E:/bak.sql

5、忽略表

mysqldump -u root -p --databases 數(shù)據(jù)庫(kù) --ignore-table=數(shù)據(jù)庫(kù).數(shù)據(jù)表 > E:/bak.sql

6、指定行

mysqldump -u root -p --databases 數(shù)據(jù)庫(kù) --tables 數(shù)據(jù)表 --where="條件" > E:/bak.sql

或者用limit限制結(jié)果集

mysqldump -u root -p --databases 數(shù)據(jù)庫(kù) --tables 數(shù)據(jù)表 --where="條件 LIMIT 條數(shù)" > E:/bak.sql

7、導(dǎo)出遠(yuǎn)程服務(wù)器

mysqldump -u root -p -h 主機(jī)IP --all-databases --routines --events --triggers > E:/all.sql

8、用于與其他服務(wù)器合并數(shù)據(jù)的備份

mysqldump -u root -p --databases 數(shù)據(jù)庫(kù) --skip-add-drop-table --replace > E:/bak.sql

--skip-add-drop-table: 不會(huì)將drop table語(yǔ)句寫(xiě)入導(dǎo)出文件中。

--replace：將使用replace into語(yǔ)句而不是insert語(yǔ)句導(dǎo)出。

二、使用mysqlpump進(jìn)行備份

1、并行處理，通過(guò)指定線程數(shù)量加速備份過(guò)程

mysqlpump --default-parallelism=8 > E:/all.sql

2、也可以指定每個(gè)數(shù)據(jù)庫(kù)的線程數(shù)

mysqlpump -u root -p --parallel-schemas=4:數(shù)據(jù)庫(kù) --default-parallelism=2 > E:/all.sql

3、排除或包含數(shù)據(jù)庫(kù)

mysqlpump -u root -p --include-databases=%t > E:/bak.sql

對(duì)以 t 結(jié)尾的所有數(shù)據(jù)庫(kù)進(jìn)行備份，多個(gè)數(shù)據(jù)庫(kù)用逗號(hào)分隔，數(shù)據(jù)庫(kù)名可以使用%或_通配符。

除此之外，還有類(lèi)似--include-events，--include-routines，--include-tables，--include-triggers，--include-users等

mysqlpump -u root -p --exclude-databases=a% > E:/bak.sql

排除以 a 開(kāi)頭的數(shù)據(jù)庫(kù)進(jìn)行備份，多個(gè)數(shù)據(jù)庫(kù)用逗號(hào)分隔，數(shù)據(jù)庫(kù)名可以使用%或_通配符。

除此之外，還有類(lèi)似--exclude-events，--exclude-routines，--exclude-tables，--exclude-triggers，--exclude-users等

4、備份用戶

mysqlpump -u root -p --exclude-databases=% --users > E:/user.sql

可以通過(guò)--exclude-users來(lái)排除某些用戶

mysqlpump --exclude-databases=% --exclude-users=root --users > E:/user.sql

5、壓縮備份

通過(guò)使用--compress-output = lz4 或 --compress-output = zlib

mysqlpump -u root -p --compress-output=lz4 > E:/all.lz4
mysqlpump -u root -p --compress-output=zlib > E:/all.zlib

通過(guò)如下語(yǔ)句進(jìn)行解壓

lz4_decompress E:/all.lz4 all.sql
zlib_decompress E:/all.zlib all.sql

三、使用mydumper進(jìn)行備份

mydumper需要單獨(dú)安裝，官網(wǎng)：https://github.com/maxbube/mydumper/releases

1、完全備份

mydumper -u root --password=密碼 --outputdir 導(dǎo)出路徑

2、備份單獨(dú)的表

mydumper -u root --password=密碼 -B 數(shù)據(jù)庫(kù) -T 數(shù)據(jù)表 --triggers --events --routines --outputdir 導(dǎo)出路徑

3、使用正則表達(dá)式來(lái)備份特定數(shù)據(jù)庫(kù)

mydumper -u root --passoword=密碼 --regex '^(?!(mysql|test))' --outputdir 導(dǎo)出路徑

從備份中排除mysql和test數(shù)據(jù)庫(kù)。

4、備份大表

mydumper -u root --password=密碼 -B 數(shù)據(jù)庫(kù) -T 數(shù)據(jù)表 --triggers --events --routines --rows=100000 -t 8 --trx-consistency-only --outputdir 導(dǎo)出路徑

--rows 表示將表分成多少行的塊

--trx-consistency-only 如果是innodb，將使鎖定最小化。

-t 指定線程數(shù)量

5、壓縮備份

mydumper -u root --password=密碼 -B 數(shù)據(jù)庫(kù) -T 數(shù)據(jù)表 -t 8 --trx-consistency-only --compress --outputdir 導(dǎo)出路徑

6、僅備份數(shù)據(jù)

通過(guò)--no-schemas選項(xiàng)來(lái)跳過(guò) schema 并且僅備份數(shù)據(jù)

mydumper -u root --password=密碼 -B 數(shù)據(jù)庫(kù) -T 數(shù)據(jù)表 -t 8 --no-schemas --compress --trx-consistency-only --outputdir 導(dǎo)出路徑

四、使用普通文件進(jìn)行備份

可以通過(guò)直接復(fù)制數(shù)據(jù)目錄中的文件來(lái)進(jìn)行備份，需先關(guān)閉mysql，復(fù)制文件，然后啟動(dòng)mysql。

五、使用xtrabackup進(jìn)行備份

https://www.percona.com/downloads/XtraBackup/LATEST/

1、全量備份

xtrabackup --defaults-file=/etc/my.cnf --host=主機(jī)IP --user=用戶名 --password=密碼 --port=端口 --backup --parallel=3 --target-dir=備份目錄

--defaults-file 數(shù)據(jù)庫(kù)配置文件

--backup 執(zhí)行備份操作

--parallel 備份時(shí)并發(fā)的線程數(shù)

--target-dir 備份文件的目錄

2、增量備份

xtrabackup --defaults-file=/etc/my.cnf \
--host=主機(jī)IP \
--user=用戶名 \
--password=密碼 \
--port=3306 \
--backup \
--parallel=3 \
--target-dir=增量備份目錄 \
--incremental-basedir=全量備份目錄 \

增量備份是基于全量備份的，--incremental-basedir 指向全量備份目錄

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL索引操作技巧匯總》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。