MySQL 数据库备份

以下是 MySQL 数据库备份的详细步骤和方法，结合了知识库中的多种备份策略和工具：

一、MySQL 备份方法分类

根据备份方式的不同，MySQL 备份主要分为以下两类：

1. 逻辑备份（导出 SQL 文件）

   • 使用 mysqldump 工具将数据库对象和数据导出为 SQL 脚本文件。
   • 优点：跨平台、可恢复单个表或数据库。
   • 缺点：备份和恢复速度较慢，占用资源较多。

2. 物理备份（直接复制数据文件）

   • 直接复制 MySQL 的数据目录（如 InnoDB 表空间文件、MyISAM 的 .MYD 和 .MYI 文件）。
   • 优点：备份和恢复速度快，适合大型数据库。
   • 缺点：依赖特定存储引擎（如 InnoDB），且备份文件不可读。

二、常用备份方法及命令

1. 逻辑备份（mysqldump）

适用场景：中小型数据库、单表备份、跨平台恢复。

(1) 全库备份

# 备份所有数据库
mysqldump -u root -p --all-databases > all_databases.sql

# 指定数据库备份
mysqldump -u root -p --databases dbname1 dbname2 > specific_databases.sql

(2) 单表备份

# 备份单个表
mysqldump -u root -p dbname tablename > table_backup.sql

(3) 部分数据备份

# 备份表中满足条件的数据
mysqldump -u root -p dbname tablename --where="id < 100" > partial_data.sql

(4) 带参数优化备份

# 增加-B参数（锁表时间更短）和压缩
mysqldump -u root -p --single-transaction --quick --lock-tables=false --databases dbname | gzip > backup.sql.gz

2. 物理备份（XtraBackup）

适用场景：大型数据库、热备份（备份期间不影响数据库读写）。

(1) 安装 XtraBackup

# CentOS/RHEL 系统示例
wget https://downloads.percona.com/downloads/percona-xtrabackup/8.0.34/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.34-14.el8.x86_64.rpm
sudo rpm -Uvh percona-xtrabackup-80-8.0.34-14.el8.x86_64.rpm

(2) 全量备份

# 使用 innobackupex（XtraBackup 的封装工具）
innobackupex --user=root --password=your_password --defaults-file=/etc/my.cnf --target-dir=/backup/fulldata

(3) 增量备份

# 第一次增量备份（基于全量备份）
innobackupex --incremental --incremental-basedir=/backup/fulldata --target-dir=/backup/incr1

# 后续增量备份（基于上一次增量备份）
innobackupex --incremental --incremental-basedir=/backup/incr1 --target-dir=/backup/incr2

3. 其他备份方法

(1) CP 全量备份（非生产环境）

# 直接复制数据目录（需停止 MySQL 服务）
sudo systemctl stop mysql
sudo cp -r /var/lib/mysql /backup/mysql_data
sudo systemctl start mysql

(2) 二进制日志备份

# 开启二进制日志（需在 my.cnf 中配置）
[mysqld]
log-bin=mysql-bin
server-id=1

# 备份二进制日志文件
sudo cp /var/lib/mysql/mysql-bin.* /backup/binlog/

三、备份恢复方法

1. 逻辑备份恢复（SQL 文件）

# 恢复整个数据库
mysql -u root -p < all_databases.sql

# 指定数据库恢复（若备份文件未包含 USE 数据库语句）
mysql -u root -p dbname < specific_database.sql

# 使用 source 命令（交互式恢复）
mysql> USE dbname;
mysql> SOURCE /path/to/backup.sql;

2. 物理备份恢复（XtraBackup）

# 准备备份数据
xtrabackup --prepare --target-dir=/backup/fulldata

# 还原数据到 MySQL 数据目录
xtrabackup --copy-back --target-dir=/backup/fulldata

# 重置 MySQL 权限（若需要）
chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

四、备份策略建议

1. 定期备份：

   • 全量备份：每周一次（如周一）。
   • 增量备份：每天一次（周二至周日）。
   • 使用 crontab 自动化：

     0 2 * * * /path/to/backup_script.sh >> /var/log/backup.log 2>&1
     

2. 备份验证：

   • 定期测试备份文件能否恢复。
   • 检查备份文件大小和完整性：

     du -sh /backup/*.sql
     

3. 存储与保留：

   • 将备份文件存储在异地或云存储中。
   • 保留至少 7-30 天的备份（根据业务需求）。

五、注意事项

1. 锁表问题：

   • 使用 --single-transaction（InnoDB）避免锁表。
   • MyISAM 表需 LOCK TABLES，可能导致业务中断。

2. 热备份支持：

   • XtraBackup 支持 InnoDB 热备份，无需停机。
   • 物理备份需确保 MySQL 配置为 innodb_file_per_table=1。

3. 二进制日志：

   • 结合全量备份和二进制日志可实现 点时间恢复（PITR）。

六、工具选择总结

根据你的数据库规模、存储引擎和业务需求选择合适的备份方案，并定期测试备份的可恢复性！