如何高效地使用MySQL数据库的备份和恢复命令？

备份命令

1. 使用mysqldump 工具进行逻辑备份

mysqldump 是 MySQL 自带的备份工具，可以将数据库导出为 SQL 脚本文件。

(1) 备份单个数据库

mysqldump -u [用户名] -p[密码] [数据库名] > [备份文件路径]

示例：

mysqldump -u root -p mypassword mydatabase > /path/to/backup/mydatabase_backup.sql

(2) 备份多个数据库

mysqldump -u [用户名] -p[密码] --databases [数据库1] [数据库2] ... > [备份文件路径]

示例：

mysqldump -u root -p mypassword --databases db1 db2 db3 > /path/to/backup/multiple_dbs_backup.sql

(3) 备份所有数据库

mysqldump -u [用户名] -p[密码] --all-databases > [备份文件路径]

示例：

mysqldump -u root -p mypassword --all-databases > /path/to/backup/all_databases_backup.sql

2. 使用xtrabackup 工具进行物理备份

xtrabackup 是一种高效的物理备份工具，可以对 InnoDB 和 XtraDB 存储引擎的表进行非阻塞式备份。

innobackupex /path/to/backup/directory

示例：

innobackupex /var/lib/mysql/backups/

恢复命令

1. 使用mysql 工具进行逻辑恢复

(1) 恢复单个数据库

mysql -u [用户名] -p[密码] [数据库名] < [备份文件路径]

示例：

mysql -u root -p mypassword mydatabase < /path/to/backup/mydatabase_backup.sql

(2) 恢复多个数据库（需要手动创建数据库）

首先创建要恢复的数据库：

mysql -u [用户名] -p[密码] -e "CREATE DATABASE db1; CREATE DATABASE db2;"

然后导入备份文件：

mysql -u [用户名] -p[密码] db1 < /path/to/backup/db1_backup.sql
mysql -u [用户名] -p[密码] db2 < /path/to/backup/db2_backup.sql

示例：

mysql -u root -p mypassword -e "CREATE DATABASE db1; CREATE DATABASE db2;"
mysql -u root -p mypassword db1 < /path/to/backup/db1_backup.sql
mysql -u root -p mypassword db2 < /path/to/backup/db2_backup.sql

(3) 恢复所有数据库（需要先停止 MySQL 服务）

mysqladmin -u [用户名] -p[密码] shutdown
tar xvf [备份文件].tar -C /var/lib/mysql/
mysqld --user=mysql --init-file=/path/to/restore/start.sql &

其中start.sql 包含启动 MySQL 服务的 SQL 语句。

2. 使用xtrabackup 工具进行物理恢复

(1) 准备恢复目录

cd /path/to/backup/directory
innobackupex --apply-log /path/to/backup/directory/2022-01-01_00-00-01

(2) 拷贝数据到数据目录并更改权限

sudo chown -R mysql:mysql /var/lib/mysql/
sudo service mysql start

示例：

cd /var/lib/mysql/backups/
innobackupex --apply-log /var/lib/mysql/backups/2022-01-01_00-00-01/
sudo chown -R mysql:mysql /var/lib/mysql/
sudo service mysql start

相关问题与解答

问题1: 如果备份文件非常大，如何分片处理？

解答: 对于非常大的数据库备份文件，可以使用split 命令将备份文件分割成多个小文件，然后在恢复时再合并这些文件。

split -b 1G /path/to/backup/large_backup.sql large_backup_part_

这将把大文件分割成每个大小为 1GB 的小文件，恢复时，使用cat 命令将这些文件合并：

cat large_backup_part_* > /path/to/combined_backup.sql
mysql -u [用户名] -p[密码] [数据库名] < /path/to/combined_backup.sql

问题2: MySQL 服务器崩溃，如何从物理备份中恢复数据？

解答: MySQL 服务器崩溃，可以从物理备份中恢复数据，步骤如下：

1、确保 MySQL 服务已停止。

2、将备份目录的内容复制到 MySQL 的数据目录（通常是/var/lib/mysql）。

3、应用事务日志以完成数据恢复。

4、更改数据目录的所有权和权限，确保它们属于 MySQL 用户和组。

5、启动 MySQL 服务。

示例命令：

sudo service mysql stop
sudo cp -r /path/to/backup/* /var/lib/mysql/
sudo chown -R mysql:mysql /var/lib/mysql/
sudo service mysql start

小伙伴们，上文介绍了“MySQL数据库备份和恢复常用的命令”的内容，你了解清楚吗？希望对你有所帮助，任何问题可以给我留言，让我们下期再见吧。