mysql数据库备份与恢复

一、备份类型

1. 逻辑备份

• 原理：导出数据库结构和数据的SQL语句。

• 工具：mysqldump、mysqlpump（MySQL 5.7+）。

• 特点：

  • 兼容性好，支持跨版本恢复。

  • 备份速度较慢，恢复时需执行SQL语句。

  • 适合中小型数据库。

2. 物理备份

• 原理：直接复制数据库文件（如.ibd、.frm、.MYD等）。

• 工具：文件系统命令（cp、rsync）、Percona XtraBackup（支持热备份）。

• 特点：

  • 备份和恢复速度快。

  • 需保证MySQL版本一致。

  • 适合大型数据库。

3. 增量备份

• 原理：基于二进制日志（binlog）或工具（如XtraBackup）记录增量变化。

• 场景：减少全量备份的频率，节省存储空间。

---

二、备份方法

1. 使用 mysqldump（逻辑备份）

• 全量备份：

  bash

  复制

  # 备份单个数据库
  mysqldump -u [用户名] -p[密码] --databases [数据库名] > backup.sql
  
  # 备份所有数据库
  mysqldump -u [用户名] -p[密码] --all-databases > full_backup.sql
  
  # 避免锁表（InnoDB）
  mysqldump -u [用户名] -p[密码] --single-transaction --databases [数据库名] > backup.sql

• 恢复数据：

  bash

  复制

  mysql -u [用户名] -p[密码] [数据库名] < backup.sql

2. 使用 Percona XtraBackup（物理备份）

• 全量备份：

  bash

  复制

  xtrabackup --backup --user=[用户名] --password=[密码] --target-dir=/path/to/backup

• 增量备份：

  bash

  复制

  xtrabackup --backup --user=[用户名] --password=[密码] --target-dir=/path/to/incremental --incremental-basedir=/path/to/full_backup

• 恢复步骤：

  1. 准备备份：

     bash

     复制

     xtrabackup --prepare --apply-log-only --target-dir=/path/to/full_backup
     xtrabackup --prepare --target-dir=/path/to/full_backup --incremental-dir=/path/to/incremental

  2. 替换数据目录：

     bash

     复制

     systemctl stop mysql
     rm -rf /var/lib/mysql/*
     xtrabackup --copy-back --target-dir=/path/to/full_backup
     chown -R mysql:mysql /var/lib/mysql
     systemctl start mysql

3. 基于二进制日志（binlog）的增量恢复

• 启用binlog（在my.cnf中配置）：

  ini

  复制

  [mysqld]
  log-bin=/var/lib/mysql/mysql-bin
  server-id=1

• 恢复步骤：

  1. 找到全量备份对应的binlog位置（查看备份文件中的CHANGE MASTER TO语句）。

  2. 应用binlog到指定时间点：

     bash

     复制

     mysqlbinlog /var/lib/mysql/mysql-bin.000001 --start-position=123 --stop-position=456 | mysql -u root -p

---

三、备份策略

1. 全量备份 + 增量备份：

   • 每周一次全量备份，每天一次增量备份。

2. binlog归档：

   • 定期备份binlog文件，用于时间点恢复。

3. 自动化脚本：

   • 使用cron定时任务执行备份脚本，例如：

     bash

     复制

     0 2 * * * /usr/bin/mysqldump -u root -p[密码] --all-databases > /backups/full_$(date +\%F).sql

---

四、恢复场景示例

场景1：误删表

1. 从逻辑备份恢复单个表：

   bash

   复制

   sed -n '/^-- Table structure for table `表名`/,/^-- Table structure/p' backup.sql | mysql -u root -p [数据库名]

场景2：时间点恢复

1. 恢复全量备份：

   bash

   复制

   mysql -u root -p < full_backup.sql

2. 应用binlog到故障前的时间点：

   bash

   复制

   mysqlbinlog mysql-bin.000001 --start-datetime="2023-10-01 00:00:00" --stop-datetime="2023-10-01 12:00:00" | mysql -u root -p

---

五、注意事项

1. 验证备份：定期检查备份文件的完整性和可恢复性。

2. 存储安全：备份文件加密并存储到异地或云存储。

3. 版本兼容：物理备份需确保MySQL版本一致。

4. 监控日志：关注备份过程中的错误日志。