共计 3779 个字符，预计需要花费 10 分钟才能阅读完成。
分享点自己近 4 年来接触 MySQL 数据备份这一块的小经验。数据是一个互联网公司的命脉，数据库的安全以及备案的完整性是至关重要的，所以我们需要在工作中要很熟练的掌握数据的备份与恢复, 这也是一个合格的运维 DBA 必须具有的职业技能。
我这里简单总结 MySQL 的备份分为 3 种
分为 冷备份，逻辑备份，热备份
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 –uroot –p -q –single-transaction –A >>all.sql
导出多个数据库：
Mysqldump –uroot –p -q –single-transaction –B test01 wjw01 test02 >test01_wjw01_test02.sql
导出一个 test 库的某张 wjw01 表：
Mysqldump –uroot –p -q –single-transaction –b test wjw01>>test_wjw01.sql
只导出表结构：
Mysqldump –uroot –q –d –skip-triggers
只需要导出储存过程时：
Mysqldump –uroot –q -Rtdn –skip-triggers
只需要导出触发器：
Mysqldump –uroot –p –q –tdn –triggers
只需要导出事件：
Mysqldump –uroot –p –q –Etdn –skip-triggers
只需要导出数据：
Mysqldump –uroot –p –q –skip-transaction –skip-triggers –t
要想在线上建立一台新的 slave，则在 master 上执行如下命令：
Mysqldump –uroot –p –q –single-transaction –master-data=2 -A >>all.sql
提示：在 mysql5.5 里增加了一个新的参数：–dump-slave, 使用该参数可在 slave 端 dump 数据，建立新的 slave，其目的是为了防止对主库造成过大的压力。
在 slave 上执行以下命令：
Mysqldump -uroot –p -A –dump-slave=2 –q –single-transaction >/tmp/all.sql
查看 alls.sql 里面会记录 slave 上的那个点。
注意：–dump-slave 用于在 slave 上 dump 数据，建立新的 slave
2.2Mysqldump 优缺点以及使用场景
优点：
1、恢复简单，可以使用管道将他们输入到 mysql
2、与存储引擎无关，因为是从 MySQL 服务器中提取数据而生成的，所以消除了底层数据存储的不同
3、有助于避免数据损坏。若磁盘驱动器有故障而要复制原始文件时，此时将得到一个损坏的备份
缺点：
1、必须有数据库服务器完成逻辑工作，需要更多地 cpu 周期
2、逻辑备份还原速度慢：需要 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 的基本流程如下：
1. 开启 redo 日志拷贝线程，从最新的检查点开始顺序拷贝 redo 日志；
2. 开启 idb 文件拷贝线程，拷贝 innodb 表的数据
3.idb 文件拷贝结束，通知调用 FTWRL，获取一致性位点
4. 备份非 innodb 表(系统表) 和 frm 文件
5. 由于此时没有新事务提交，等待 redo 日志拷贝完成
6. 最新的 redo 日志拷贝完成后，相当于此时的 innodb 表和非 innodb 表数据都是最新的
7. 获取 binlog 位点，此时数据库的状态是一致的。
8. 释放锁，备份结束。
具体使用请参考：https://www.linuxidc.com/Linux/2018-08/153635.htm
关于 MySQL 数据库的备份方案到此处就介绍完了，如有不对，请及时指出。欢迎一起交流学习。
：
