MySQL 数据备份

1. 逻辑备份

原理：将数据库中的数据导出为 .sql 文本文件（包含 CREATE TABLE, INSERT 等 SQL 语句）。
适用场景：数据量较小（通常建议几十GB以内）、需要跨版本或跨平台迁移数据、仅需要备份单个库或单个表。

使用 mysqldump (全量逻辑备份)

这是 MySQL 自带的最常用的逻辑备份工具。

备份所有数据库：

mysqldump -u root -p –all-databases > /all_db.sql

备份单个数据库 (如 mydb)：

mysqldump -u root -p work > /mydb.sql

备份单个表 (如 mydb 的 users 表)：

mysqldump -u root -p work employee > /work.sql

仅备份表结构 (不含数据)：

mysqldump -u root -p –no-data work > /work1.sql

恢复方法：

# 恢复单库或单表
mysql -u root -p work < /work2.sql

 

2. 冷备份

原理：直接复制数据库的物理文件（数据文件、配置文件等）。
适用场景：允许停机维护的小型业务、个人测试环境。

操作步骤：

1. 停止MySQL 服务（确保内存中的脏数据全部刷写到磁盘）：

• systemctl stop mysqld

  

2. 打包备份数据目录：
   首先确认MySQL 的数据目录位置（一般在 /var/lib/mysql，可通过 mysql -e “SHOW VARIABLES LIKE ‘datadir'” 查看）。

• tar -czvf /backup/mysql_cold_backup_$(date +%F).tar.gz /www/server/data/

  

  

3. 重启MySQL 服务：

• systemctl start mysqld

  

恢复方法：

1. 停止MySQL 服务。
2. 清空当前数据目录（或移走）。
3. 将备份的压缩包解压到原数据目录。
4. 修改文件所属主为mysql:mysql (chown -R mysql:mysql /www/server/data/)。
5. 启动MySQL 服务。

3. 热备份

原理：在数据库不中断服务、不影响读写的情况下进行备份。
适用场景：7×24小时在线的生产环境、核心业务数据库。

3.1 物理热备工具：Percona XtraBackup

这是目前 MySQL (尤其是 InnoDB 引擎) 行业标准的免费热备工具。它通过监控 InnoDB 的内部机制和 Redo Log（重做日志）来实现在线备份。

基础安装 (以 Debian 为例)：

wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb

3.2 使用 XtraBackup 进行全量热备

创建备份目录并执行备份：

mkdir -p /backup/xtrabackup
xtrabackup –backup -u root -p –target-dir=/backup/xtrabackup/full_$(date +%F)

(注：备份期间数据库可正常读写，该工具会自动处理锁和一致性。)

4. 全量与增量备份 (基于 Percona XtraBackup)

在大型生产环境中，为了节省磁盘空间和缩短备份时间，通常采用 周日一次全量，周一到周六每天一次增量 的策略。

4.1 准备工作

确保 MySQL 开启了 innodb_file_per_table=ON（独立表空间，目前 MySQL 8.0 默认开启）。

4.2 周日：全量备份

xtrabackup –backup –user=root –password=’zxd814293′ –target-dir=/backup/base_full

4.3 周一：第一次增量备份

基于周日的全量备份 base_full 进行增量，将增量数据保存到 inc1。

xtrabackup –backup –user=root –password=’zxd814293′ –target-dir=/backup/inc1 –incremental-basedir=/backup/base_full

4.4 周二：第二次增量备份

基于周一的增量备份 inc1 继续进行增量，保存到 inc2。

xtrabackup –backup –user=root –password=’zxd814293′ –target-dir=/backup/inc2 –incremental-basedir=/backup/inc1

4.5 恢复实战 (模拟周三数据库崩溃)

恢复增量备份的过程是一个“重放日志”的合并过程。步骤为：准备全量 -> 依次合并增量 -> 拷贝回数据目录。

第一步：准备基础全量备份 (加上 –apply-log-only 防止回滚阶段提前结束)

xtrabackup –prepare –apply-log-only –target-dir=/backup/base_full

第二步：将周一的增量 (inc1) 合并到全量中

xtrabackup –prepare –apply-log-only –target-dir=/backup/base_full –incremental-dir=/backup/inc1

第三步：将周二的增量 (inc2) 合并到全量中 (最后一次合并不需要加 –apply-log-only)

xtrabackup –prepare –target-dir=/backup/base_full –incremental-dir=/backup/inc2

第四步：停止数据库，清空数据目录，将合并后的数据拷贝回去

systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup –copy-back –target-dir=/backup/base_full –datadir=/www/server/data

第五步：修复权限并启动

chown -R mysql:mysql /www/server/data
systemctl start mysqld

此时数据库已成功恢复到周二备份时的状态！

---

广告：

• 高性能海外服务器低至200元/年