MySQL数据库中有哪些备份方案

共计 4040 个字符，预计需要花费 11 分钟才能阅读完成。

MySQL 数据库中有哪些备份方案，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

1、冷备份：

一般主要用于非核心业务，这类业务一般都是允许业务中断的，冷备份的特点就是数度快，恢复时也最为简单。通常直接复物理文件来实现冷备份。

1.1 备份过程：

*** 关闭 mysql 服务

第二步就是把 datas 数据目录（包含 ibdata1）和日志目录 (包含 ib_logfile0,ib_logfile1,ib_logfile2) 复制到磁盘，或者本地的另一块磁盘里

1.2 恢复过程：

*** 使用复制的数据目录和日志目录替换原有的目录

第二就是启动 mysql

2、逻辑备份 MySQLdump

生产场景备份的命令：

生产场景不同引擎 mysqldump 备份命令

myisam 引擎企业生产备份命令（适合所有引擎或混合引擎）:

mysqldump -uroot -p123456 -A -B -F -R --master-data=2 -x --events|gzip  /opt/all.sql.gz

提示：- F 也可以不用，与 –master-data 有些重复。

innodb 引擎企业生产备份命令: 推荐使用的

mysqldump -uroot -p123456 -A -B -F -R --master-data=2 --default-character-set=utf8 --events --single-transaction |gzip  /opt/all.sql.gz

提示：- F 也可以不用。与 –master-data 有些重复。

–default-character-set=utf8 指定备份出的 sql 数据的字符集为 utf8，当然，这个要提前知道线上的 mysql 库全部采用的字符集都是 utf8，否则在恢复到线上的库会出现字符集不一致的问题出现乱码

提示：逻辑备份一般是数据迁移或者数据量很小时采用，逻辑备份采用的是数据导出的方式

2.1mysqldump 库表等备份说明：

全备：

Mysqldump  ndash;uroot  ndash;p -q  ndash;single-transaction  ndash;A  all.sql

导出多个数据库：

Mysqldump  ndash;uroot  ndash;p -q  ndash;single-transaction  ndash;B test01 wjw01 test02  test01_wjw01_test02.sql

导出一个 test 库的某张 wjw01 表：

Mysqldump  ndash;uroot  ndash;p -q  ndash;single-transaction  ndash;b test wjw01 test_wjw01.sql

只导出表结构：

Mysqldump  ndash;uroot  ndash;q  ndash;d --skip-triggers

只需要导出储存过程时：

Mysqldump  ndash;uroot  ndash;q -Rtdn --skip-triggers

只需要导出触发器：

Mysqldump  ndash;uroot  ndash;p  ndash;q  ndash;tdn  ndash;triggers

只需要导出事件：

Mysqldump  ndash;uroot  ndash;p  ndash;q  ndash;Etdn  ndash;skip-triggers

只需要导出数据：

Mysqldump  ndash;uroot  ndash;p  ndash;q --skip-transaction --skip-triggers  ndash;t

要想在线上建立一台新的 slave，则在 master 上执行如下命令：

Mysqldump  ndash;uroot  ndash;p  ndash;q  ndash;single-transaction  ndash;master-data=2 -A  all.sql

提示：在 mysql5.5 里增加了一个新的参数：–dump-slave, 使用该参数可在 slave 端 dump 数据，建立新的 slave，其目的是为了防止对主库造成过大的压力。

在 slave 上执行以下命令：

Mysqldump -uroot  ndash;p -A  ndash;dump-slave=2  ndash;q  ndash;single-transaction  /tmp/all.sql

查看 alls.sql 里面会记录 slave 上的那个点。

注意：–dump-slave 用于在 slave 上 dump 数据，建立新的 slave

2.2Mysqldump 优缺点以及使用场景

优点：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

恢复简单，可以使用管道将他们输入到 mysql

与存储引擎无关，因为是从 MySQL 服务器中提取数据而生成的，所以消除了底层数据存储的不同

有助于避免数据损坏。若磁盘驱动器有故障而要复制原始文件时，此时将得到一个损坏的备份

缺点：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

必须有数据库服务器完成逻辑工作，需要更多地 cpu 周期

  逻辑备份还原速度慢：需要 MySQL 加载和解释语句、转化存储格式、重建引擎

使用场景：对于 MySQL 数据库数据量不是很大的场景，建议使用。因为备份方便，简单灵活，易操作

当数据库特别的时候，比如 30G 以上时，备份 MySQL 数据时，也就建议不要采用 mysqldump 了，因为备份的时间以及恢复数据库的时间太长了，会对库表造成锁，对线上的业务影响还是比较大的

2.3 简单介绍下 MySQL 全量备份 + 增量备份 binlog

注意：生产上强烈建议 MySQL 开启 Row 格式来记录 binlog, 尽管这样对磁盘的 IO 以及磁盘的空间的消耗是比较大，但是和数据的安全性、完整性相比，磁盘资源那都是小事

全量备份脚本内容：

#!/bin/bash #mysql 全量备份脚本建议在 slave 从库上运行，从库 slave 上建议开启参数 log_slave_updates=1 mkdir /backup cd /backup dateDIR=$(date + %Y-%m-%d) mkdir -p $dateDIR/data path=/data/mysql/data for n in `mysql -uroot -p123456 -e  show databases |grep -v  Database ` do mysqldump -uroot -p123456 --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B $n|gzip  /backup/$dateDIR/data/${n}_$dateDIR.sql.gz done binlog_rm=$(tail -n 1 $path/mysql-bin.index|sed  s/.\///) mysql -uroot -p123456 -e  purge binary logs to  $binlog_rm

说明: 这个全量备份脚本，会在导出的时候锁住全局表，并且此时刷新产生一个新的 bin-log, 期间会产生写操作的等待，直到导出结束后才会写入新产生的 bin-log 文件，然后旧的 bin-log 文件会被删除删除掉，一般在晚上 2:00 业务低峰期执行操作

进行本地 bin-log 增量备份

在执行完全量备份脚本后，就可以执行增量备份脚本了。

增量备份脚本思路很简单：

先 mysqladmin -uroot -p123456 flush-logs 刷新新的 binlog 文件，此时 mysql 写入到新的 binlog 文件中。

然后把当前 mysql 数据库存放 binlog 的目录中抛去刚才 *** 生成的 binlog 文件，其余旧的 binlog 文件全部 cp 到本地服务器 /backup/binlog  binlog 的备份目录下。

*** 在登录 MySQL 清除当前的 binlog 文件数减一

mysql -uroot -p123456 -e  purge binary logs to  mysql-bin.(n-1)

3. 热备份与恢复

热备份的方式也是直接复制数据物理文件，和冷备份一样，但是热备份可以不停机直接复制，一般用于 7 *24 小时不间断的重要核心业务。Mysql 的社区版本热备份工具 InnoDB Hot Backup 是付费的，只能试用 30 天，商业版才可以 *** 的使用，

Percona 公司发布一个 xtrabackup 热备工具，和付费的工具一样，支持在线热备（备份时不影响数据的读写）是商业工具 InnoDB Hot Backup 的一个很好的替代。

xtrabackup 热备工具是一个开源的工具，他可以非常快速的备份和恢复 mysql 数据库。由于 Xtrabackup 支持备份 innodb 表，实际生产环境中我们使用的工具是 innobackupex，它是对 xtrabackup 的一层封装。innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，innobackupex 的基本流程如下：

鸿蒙官方战略合作共建——HarmonyOS 技术社区

开启 redo 日志拷贝线程，从 *** 的检查点开始顺序拷贝 redo 日志；

开启 idb 文件拷贝线程，拷贝 innodb 表的数据

idb 文件拷贝结束，通知调用 FTWRL，获取一致性位点

备份非 innodb 表 (系统表) 和 frm 文件

由于此时没有新事务提交，等待 redo 日志拷贝完成

*** 的 redo 日志拷贝完成后，相当于此时的 innodb 表和非 innodb 表数据都是 *** 的

获取 binlog 位点，此时数据库的状态是一致的。

释放锁，备份结束。

看完上述内容，你们掌握 MySQL 数据库中有哪些备份方案的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！