引言

在数据库管理中，MySQL的备份与恢复是确保数据安全性和完整性的重要手段。无论是逻辑备份还是物理备份，掌握其技巧和方法都能帮助我们更好地应对各种突发情况。本文将为您详细解析MySQL的两种备份方式：逻辑备份（mysqldump）和物理备份（xtrabackup），并通过实例展示具体的操作步骤。

一、逻辑备份（mysqldump）

逻辑备份是通过复制数据库的逻辑结构和数据来创建备份文件的过程。它适用于大多数场景，包括数据迁移、版本控制等。

1. 备份单表

使用mysqldump命令备份单表，并通过gzip进行压缩，以节省存储空间。

mysqldump -u -p -h --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=OFF -q -f dbname tablename | gzip > dbname_dump_$(date +%Y%m%d%H%M).sql.gz

2. 备份单个库

备份整个数据库时，可以使用mysqldump命令并指定数据库名称。

mysqldump -u -p -h --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=OFF -f -q -R --triggers -B dbname > dbname_dump_$(date +%Y%m%d%H%M).sql.gz

3. 备份存储过程

备份存储过程时，需要使用-t选项。

mysqldump -h -u -p --default-character-set=utf8mb4 -d -t -R dbname > dbname_dump_$(date +%Y%m%d%H%M).sql.gz

4. 备份表结构

备份表结构时，需要使用-R选项。

mysqldump -h -u -p --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF -q -R --triggers -d -B dbname > dbname_dump_$(date +%Y%m%d%H%M).sql

5. 只备份表数据，不备份表结构

只备份表数据时，使用-t选项。

mysqldump -h -u -p --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF -q -R --triggers -t dbname > dbname_dump_$(date +%Y%m%d%H%M).sql

6. 加条件备份

根据特定条件备份数据。

mysqldump -h1.1.1.1 -u -p --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF -q dbname table --where="colname between '2022-08-01' and '2022-09-30'" > dbname_table_col_dump.sql

7. 全参数备份

备份所有数据库对象，包括存储过程、触发器等。

mysqldump --login-path=bak --routines --events --triggers --single-transaction --databases ${DBNAME} -q --log-error=/data/mysqlbackup/mysqlbackup_${date +%Y%m%d%H%M}.err --result-file=/data/mysqlbackup/${DBNAME}_dump_${date +%Y%m%d%H%M}.sql

二、物理备份（xtrabackup）

物理备份是通过复制数据库的物理文件来创建备份文件的过程。它适用于对数据一致性要求极高的场景。

1. 全量备份

进行全量备份并进行压缩和打包至本地。

xtrabackup --backup --default-file=/etc/mysql/3306.cnf --host='127.0.0.1' --port=3306 --user=test --password='pwd' --stream=xbstream --compress --compress-threads=6 --parallel=6 --target-dir=/db/temp/bak_xtrabackup_test/

2. 单表备份

备份单个表。

xtrabackup --backup --default-file=/etc/mysql/3306.cnf --host='127.0.0.1' --port=3306 --user=test --password='pwd' --stream=xbstream --compress --compress-threads=6 --parallel=6 --tables=db.tb1,db.tb2 --target-dir=/db/mysql_backup/

3. 解包备份

解包备份文件。

mkdir -p /db/recover/3306nohup
xbstream --parallel=6 -v -x < ./127.0.0.1_3306.stream -C /db/recover/3306 > ./import_3306.log 2>&1

4. 解压数据文件

解压数据文件。

nohup xtrabackup --decompress --default-file=/etc/mysql/3306.cnf --parallel=6 --target-dir=/db/recover/3306 > decompress_3306_$(date +%Y%m%d-%H%M).log 2>&1

5. 恢复redo日志

恢复redo日志。

nohup xtrabackup --prepare --default-file=/etc/mysql/3306.cnf --parallel=6 --target-dir=/db/recover/3306 > prepare_3306_$(date +%Y%m%d-%H%M).log 2>&1

6. 拷贝至数据目录

将备份数据拷贝至数据目录。

nohup xtrabackup --default-file=/etc/mysql/3306.cnf --move-back --target-dir=/db/temp/20231218/3306 --datadir=/db/3306/data > move-back_3306_$(date +%Y%m%d-%H%M).log 2>&1

结语

MySQL备份与恢复是数据库管理中的重要环节。通过本文的介绍，相信您已经掌握了逻辑备份和物理备份的基本方法和技巧。在实际应用中，您可以根据具体需求选择合适的备份方式，并结合实际情况进行调整和优化。希望本文能为您的数据库管理工作带来帮助。