MySQL作为主流的关系型数据库，其数据备份与恢复操作是每一位开发者和运维人员都必须掌握的基础技能，当遇到服务器故障、误操作删除数据或者需要迁移数据库时，正确的备份恢复操作能够快速挽回损失。
MySQL备份的常用方式
逻辑备份（使用mysqldump工具）
逻辑备份是通过导出数据库的SQL语句来实现备份，适合中小型数据库，操作灵活且兼容性好。mysqldump是MySQL官方自带的备份工具，无需额外安装。
备份单个数据库的命令如下：
# 备份test_db数据库，输出到test_db_backup.sql文件 mysqldump -u root -p test_db > /data/backup/test_db_backup.sql # 输入数据库密码后执行，生成的文件包含所有表结构和数据插入语句
如果需要备份所有数据库，可以添加--all-databases参数：
# 备份所有数据库 mysqldump -u root -p --all-databases > /data/backup/all_db_backup.sql
如果需要同时备份多个指定数据库，使用--databases参数：
# 备份db1和db2两个数据库 mysqldump -u root -p --databases db1 db2 > /data/backup/multi_db_backup.sql
物理备份（直接复制数据文件）
物理备份是直接复制MySQL的数据目录文件，适合大型数据库，备份和恢复速度更快，但需要停止MySQL服务或者保证数据一致性。
操作步骤：
- 停止MySQL服务：
systemctl stop mysqld - 复制数据目录：默认数据目录为
/var/lib/mysql，将其复制到备份目录cp -r /var/lib/mysql /data/backup/mysql_data_backup - 启动MySQL服务：
systemctl start mysqld
MySQL数据恢复操作
逻辑备份的恢复方法
使用mysqldump导出的SQL文件，恢复时直接通过mysql命令执行即可。
恢复单个数据库的步骤：
# 首先登录mysql创建空数据库（如果原数据库已删除） mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS test_db" # 执行备份文件恢复数据 mysql -u root -p test_db < /data/backup/test_db_backup.sql
如果是恢复所有数据库的备份文件，直接执行以下命令：
mysql -u root -p < /data/backup/all_db_backup.sql
物理备份的恢复方法
物理备份恢复需要关闭MySQL服务，替换数据目录后重启服务：
- 停止MySQL服务：
systemctl stop mysqld - 备份当前数据目录（防止操作失误）：
mv /var/lib/mysql /var/lib/mysql_old - 复制备份的数据目录到原路径：
cp -r /data/backup/mysql_data_backup /var/lib/mysql - 修改数据目录权限：
chown -R mysql:mysql /var/lib/mysql - 启动MySQL服务：
systemctl start mysqld
备份策略建议
为了保证数据安全，建议制定合理的备份策略：
- 中小型数据库每天做一次全量逻辑备份，备份文件存储到异地服务器
- 大型数据库每周做一次全量物理备份，每天做增量备份
- 定期测试备份文件的可恢复性，避免备份文件损坏无法使用
- 备份时添加时间戳，方便区分不同时间的备份文件，例如
test_db_20240501.sql
常见注意事项
备份和恢复过程中需要注意以下问题：
- 逻辑备份时如果数据库正在写入数据，建议添加--single-transaction参数，保证备份数据的一致性，该参数只对InnoDB引擎有效
- 恢复数据前一定要确认备份文件的完整性，避免恢复损坏的数据
- 生产环境操作前建议在测试环境先验证流程，避免误操作影响业务
- 物理备份需要保证MySQL版本一致，不同版本的MySQL数据文件可能存在兼容性问题
示例：完整备份恢复流程演示
以下是一个完整的备份恢复示例，演示test_db数据库的备份和恢复过程：
# 1. 备份test_db数据库 mysqldump -u root -p test_db > /data/backup/test_db_20240501.sql # 2. 模拟误操作删除数据库 mysql -u root -p -e "DROP DATABASE test_db" # 3. 恢复数据库 mysql -u root -p -e "CREATE DATABASE test_db" mysql -u root -p test_db < /data/backup/test_db_20240501.sql # 4. 验证数据是否恢复 mysql -u root -p -e "SELECT COUNT(*) FROM test_db.user_table"