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

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

一、使用mysqldump進行備份

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

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

在mysql8之前，存儲過程和事件存儲在mysql.proc和mysql.event表中。

從mysql8開始，相應對象的定義存儲在數(shù)據(jù)字典中，這些表不會被備份。

要將存儲過程和事件也包含，請使用如下語句：

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

2、時間點恢復

要獲得時間點恢復，應該指定--single-transaction 和 --master-data

--single-transaction 在備份之前，會將事務隔離級別設為REPEATABLE READ模式，并執(zhí)行 START TRANSACTION 來提供一致的備份。

--master-data 將服務器的二進制日志的位置輸出到 sql 文件。

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

--master-data = 2表示在導出過程中，記錄當前庫的binlog和pos點，并在導出文件中注釋這一行。

--master-data = 1表示在導出過程中，記錄當前庫的binlog和pos點，并在導出文件中不注釋這一行。

3、在從庫導出時，記錄主庫的二進制日志位置

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

--dump-slave = 2表示在導出過程中，記錄主庫的binlog和pos點，并在導出文件中注釋這一行。

--dump-slave = 1表示在導出過程中，記錄主庫的binlog和pos點，并在導出文件中不注釋這一行。

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

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

5、忽略表

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

6、指定行

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

或者用limit限制結果集

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

7、導出遠程服務器

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

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

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

--skip-add-drop-table: 不會將drop table語句寫入導出文件中。

--replace：將使用replace into語句而不是insert語句導出。

二、使用mysqlpump進行備份

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

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

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

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

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

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

對以 t 結尾的所有數(shù)據(jù)庫進行備份，多個數(shù)據(jù)庫用逗號分隔，數(shù)據(jù)庫名可以使用%或_通配符。

除此之外，還有類似--include-events，--include-routines，--include-tables，--include-triggers，--include-users等

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

排除以 a 開頭的數(shù)據(jù)庫進行備份，多個數(shù)據(jù)庫用逗號分隔，數(shù)據(jù)庫名可以使用%或_通配符。

除此之外，還有類似--exclude-events，--exclude-routines，--exclude-tables，--exclude-triggers，--exclude-users等

4、備份用戶

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

可以通過--exclude-users來排除某些用戶

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

5、壓縮備份

通過使用--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

通過如下語句進行解壓

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

三、使用mydumper進行備份

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

1、完全備份

mydumper -u root --password=密碼 --outputdir 導出路徑

2、備份單獨的表

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

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

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

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

4、備份大表

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

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

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

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

5、壓縮備份

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

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

通過--no-schemas選項來跳過 schema 并且僅備份數(shù)據(jù)

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

四、使用普通文件進行備份

可以通過直接復制數(shù)據(jù)目錄中的文件來進行備份，需先關閉mysql，復制文件，然后啟動mysql。

五、使用xtrabackup進行備份

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

1、全量備份

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

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

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

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

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

2、增量備份

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

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

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

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