在Linux中备份msyql数据库和表的详细利用

[复制链接]
发表于 昨天 22:20 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?立即注册

×
核心结论与决策建议(BLUF)
在Linux操作系统环境下,熟练运用原生工具链对MySQL数据库与单表执行冷热备份与灾难恢复,是运维工程师的看家本领。绝大多数数据丢失事故并非由于没有备份,而是因为备份脚本缺乏一致性锁保证、未经过还原演练校验、或缺乏异地归档策略。企业级备份实施准则:1. 单库逻辑导出首选
  1. mysqldump
复制代码
配合
  1. --single-transaction
复制代码
实现 InnoDB 引擎全量无锁热备;2. 导出单表结构使用
  1. CREATE TABLE ... LIKE
复制代码
,复制全量数据采用
  1. INSERT INTO ... SELECT
复制代码
;3. 生产环境必须配合 Linux Crontab 定时调度与 gzip 压缩,并利用 SSH/SCP 自动将加密备份包异地异机分发
。
备份操作维度核心执行工具与命令锁表影响与业务干扰适用场景与数据规模
单库全量逻辑备份
  1. mysqldump --single-transaction db > bak.sql
复制代码
InnoDB 完全无锁,业务正常读写中小规模库(体积 < 30GB)
单表结构快速克隆
  1. CREATE TABLE new_t LIKE old_t;
复制代码
短暂获取 MDL 元数据锁结构秒级克隆,保留完整主键索引
单表数据快速镜像
  1. INSERT INTO new_t SELECT * FROM old_t;
复制代码
源表加行级读锁,需分批限制临时大表重构、上线前数据打点备份
增量日志日志归档Binlog 定期 FLUSH LOGS + 异机拷贝零影响实现 PITR 秒级任意时间点恢复


一、实战演示:将原数据库完整备份至新数据库 xs_bak
假设系统中存在一个正在运行的原始学生业务库,现在需要将其完整备份至新库
  1. xs_bak
复制代码
中。
原数据库表状态展示:



1. 第一步:在 MySQL 中新建目标空数据库

  1. CREATE DATABASE IF NOT EXISTS xs_bak DEFAULT CHARACTER SET utf8mb4;
复制代码




2. 第二步:在 Linux 操作系统中规划临时备份目录
在根目录或数据盘下创建临时存放目录并检查权限:



3. 第三步:使用 mysqldump 将原库全量导出至 SQL 文本

  1. mysqldump -u root -p --single-transaction --quick xs > /baby/a.sql
复制代码




4. 第四步:检查备份文件完整性
在 Linux 终端查看生成的文件大小与内容:



5. 第五步:将临时导出的 SQL 还原导入新库 xs_bak

  1. mysql -u root -p xs_bak < /baby/a.sql
复制代码




6. 第六步:登录数据库核对还原成果

  1. USE xs_bak;
  2. SHOW TABLES;
  3. SELECT * FROM 表名 LIMIT 5;
复制代码




原库所有数据表与索引已在目标库中 100% 完美复现。

二、实战演练:单表级独立快速备份克隆
若在线业务即将发布上线,仅需针对核心单表
  1. emp
复制代码
进行就地快照备份:
目标原表
  1. emp
复制代码
展示:



查看原表的字段结构与原始数据:




1. 创建结构相同的新备份表 newemp:

  1. -- 使用 LIKE 语法克隆表定义(包含完整主键、自增属性与所有二级索引)
  2. CREATE TABLE newemp LIKE emp;
复制代码





2. 将原表数据全量拷贝注入新表:

  1. -- 将原表所有数据行导入新备份表
  2. INSERT INTO newemp SELECT * FROM emp;
复制代码




核对新备份表数据一致性:



三、自动化备份脚本与 Crontab 定时编排
在生产环境中,人工手动敲命令是无法保障日常 SLA 的,必须编写自动化 Shell 脚本:

  1. -- /opt/scripts/mysql_auto_backup.sh 自动化生产备份脚本样例
  2. -- // 设定环境变量与参数
  3. BACKUP_DIR="/data/backup/mysql"
  4. DATE_STR=$(date +%Y%m%d_%H%M%S)
  5. DB_USER="root"
  6. DB_PASS="StrongSecurePass#2026"
  7. DB_NAME="xs"

  8. mkdir -p $BACKUP_DIR

  9. -- // 执行无锁热备并管道调用 gzip 实时流式压缩
  10. mysqldump -u$DB_USER -p$DB_PASS \
  11.     --single-transaction \
  12.     --quick \
  13.     --master-data=2 \
  14.     --default-character-set=utf8mb4 \
  15.     $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE_STR}.sql.gz

  16. -- // 自动清理 15 天之前的过期历史旧备份文件
  17. find $BACKUP_DIR -name "*.sql.gz" -mtime +15 -exec rm -f {} \;

  18. -- // 使用 scp 将备份包异地同步至容灾备用服务器
  19. scp $BACKUP_DIR/${DB_NAME}_${DATE_STR}.sql.gz [email protected]:/data/remote_backup/
复制代码


常见问题(FAQ)
Q1: 为什么使用
  1. CREATE TABLE new_t AS SELECT * FROM old_t;
复制代码
方式备份表存在重大缺陷?

A1:
  1. CREATE TABLE ... AS SELECT
复制代码
(CTAS 语法)只会拷贝字段名、字段类型和数据,完全不会克隆原表的主键约束(PRIMARY KEY)、自增属性(AUTO_INCREMENT)以及任何二级索引!新表将退化为一张没有任何主键的裸表。在生产环境中进行表结构复制必须严格使用
  1. CREATE TABLE new_t LIKE old_t;
复制代码
。

Q2: 为什么导入超大 SQL 备份文件时,速度会越来越慢?
A2: 导出的 SQL 文本通常包含了海量的二级索引构建与外键约束。每插入一条数据,InnoDB 必须同步维护所有二级索引树的平衡与约束校验,产生剧烈随机 I/O。在导入大文件前,建议在脚本头部临时添加
  1. SET foreign_key_checks=0; SET unique_checks=0; SET autocommit=0;
复制代码
,并在末尾执行
  1. COMMIT;
复制代码
,整体还原速度可提升 5 倍以上。



qidao123.com企服评测-应用市场 编辑提示:本文由企服大模型基于真实系统运维实践重构整理,聚焦技术原理、性能对比与选型避坑。若在实际部署中遇到异常,欢迎在下方跟帖交流;如需企业级技术支持或深度运维解决方案,请访问 qidao123.com 获取专属支持。
回复

使用道具 举报

登录后关闭弹窗

登录参与点评抽奖  加入IT实名职场社区
去登录
快速回复 返回顶部 返回列表