MySQL 逻辑备份与物理备份

备份方式分为逻辑备份和物理备份，两者在备份速度、恢复效率、可移植性方面各有特点。

两种备份方式对比

逻辑备份

-- mysqldump 逻辑备份
mysqldump -u root -p db_name > db_backup.sql

-- 备份单个表
mysqldump -u root -p db_name table_name > table_backup.sql

-- 备份多个数据库
mysqldump -u root -p --databases db1 db2 > dbs_backup.sql

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

-- 备份表结构不含数据
mysqldump -u root -p --no-data db_name > schema.sql

-- 备份数据不含表结构
mysqldump -u root -p --no-create-info db_name > data.sql

逻辑恢复

-- 恢复数据库
mysql -u root -p db_name < db_backup.sql

-- 恢复所有数据库
mysql -u root -p < all_backup.sql

-- 在MySQL内恢复
mysql> SOURCE /path/to/backup.sql;

物理备份

冷备份（停服务）

# 1. 停止MySQL服务
systemctl stop mysql

# 2. 复制数据目录
cp -r /var/lib/mysql /backup/mysql_20240115

# 3. 启动MySQL服务
systemctl start mysql

热备份（服务运行）

# 使用 xtrabackup（InnoDB热备份）
# 安装
yum install percona-xtrabackup

# 全量备份
xtrabackup --backup --target-dir=/backup/full

# 准备备份（使数据一致）
xtrabackup --prepare --target-dir=/backup/full

# 恢复
xtrabackup --copy-back --target-dir=/backup/full

物理恢复

# 1. 停止服务
systemctl stop mysql

# 2. 清空数据目录（谨慎操作）
rm -rf /var/lib/mysql/*

# 3. 复制备份文件
cp -r /backup/mysql_20240115 /var/lib/mysql

# 4. 设置权限
chown -R mysql:mysql /var/lib/mysql

# 5. 启动服务
systemctl start mysql

选择建议

-- 小数据库（<10GB）：逻辑备份
mysqldump -u root -p db > backup.sql

-- 大数据库（>10GB）：物理备份
xtrabackup --backup --target-dir=/backup/full

-- 需跨平台迁移：逻辑备份
mysqldump -u root -p db > backup.sql

-- 快速恢复场景：物理备份
xtrabackup --copy-back --target-dir=/backup/full

生产环境推荐使用 xtrabackup 进行热物理备份。

要点总结

• 逻辑备份导出SQL，恢复需执行SQL，速度慢但灵活
• 物理备份复制文件，恢复直接复制，速度快
• 小库用mysqldump，大库用xtrabackup
• 物理备份依赖平台和MySQL版本，迁移需注意