1 全量备份与增量备份
1.1 全量备份
全量数据是数据库中所有数据，全量备份是把所有数据进行备份
--- 备份所有数据库的所有数据
mysqldump -B --master-data=2 --single-transaction -A | gzip > /bak/all.sql.gz
--- 备份ocean数据库的所有数据
mysqldump -B --master-data=2 --single-transaction ocean | gzip > /bak/ocean.sql.gz1.2 增量备份
增量数据是上一次全量备份之后至下一次全量备份之前的更新数据，增量数据是MySQL的binlog日志，因此binlog日志备份可以称之为增量备份。
1.3 全量与增量结合备份
方案一：每日定时全备一次，一天增量数据
| 周一全量备份 | 周二全量备份 | 周三全量备份 | 周四全量备份 | ...... |
| Mon.sql.gz | Tue.sql.gz | Wed.sql.gz | Thur.sql.gz | ...... |
| 周一增量数据 | 周二增量数据 | 周三增量数据 | 周四增量数据 | ...... |
| ocean-bin.000001 ocean-bin.000002 ocean-bin.000003 ocean-bin.000004 ocean-bin.000005 ...... | ocean-bin.000011 ocean-bin.000012 ocean-bin.000013 ocean-bin.000014 ocean-bin.000015 ...... | ocean-bin.000021 ocean-bin.000022 ocean-bin.000023 ocean-bin.000024 ocean-bin.000025 ..... | ocean-bin.000031 ocean-bin.000032 ocean-bin.000033 ocean-bin.000034 ocean-bin.000035 ...... | ...... |
优劣如下
优点：恢复数据时需要的数据少，恢复时间短，维护成本低
缺点：每日一个全备文件，占用系统资源大
方案二：每周定时全备一次，一周增量数据
| 周一全量备份 | ||||
| week1.sql.gz | ||||
| 周一增量数据 | 周二增量数据 | 周三增量数据 | 周四增量数据 | ...... |
| ocean-bin.000001 ocean-bin.000002 ocean-bin.000003 ocean-bin.000004 ocean-bin.000005 ...... | ocean-bin.000011 ocean-bin.000012 ocean-bin.000013 ocean-bin.000014 ocean-bin.000015 ...... | ocean-bin.000021 ocean-bin.000022 ocean-bin.000023 ocean-bin.000024 ocean-bin.000025 ..... | ocean-bin.000031 ocean-bin.000032 ocean-bin.000033 ocean-bin.000034 ocean-bin.000035 ...... | ...... |
优劣如下
优点：每周一次全量备份，系统资源占用，备份次数少
缺点：恢复时数据文件多，恢复时间长，维护成本高
总结
1.中小型企业数据库较少，每日全量备份耗时短，能够在非生产时间段完成备份，根据需求保留足够多的数据备份，普遍采用每日全量备份策略
2.大型企业数据量大，每日全量备份耗时长，非生产时间无法完成备份导致系统资源占用高，最终影响客户体验，因此会采用每周全量备份的策略
2 MySQL常用的备份方式
2.1 逻辑备份
MySQL逻辑备份是使用mysqldump把数据以SQL语句的形式导出或备份成文件。恢复数据库通过mysql或source将存储SQL语句的文件数据还原到MySQL数据库中
--- 备份所有数据库
mysqldump -A -B --master-data=2 --single-transaction | gzip > /bak/all.sql.gz
--- 恢复数据库
zcat /bak/all.sql.gz | mysql
--- 恢复增量数据
mysqlbinlog ocean-bin.000001 ocean-bin.000002 > bin.sql
mysql < bin.sql逻辑备份的优劣
优势：操作简便，可夸平台、版本恢复，可实现分库分表备份
劣势：备份速度慢，恢复效率低
应用场景
备份文件不超过30GB，建议使用逻辑备份。夸平台、版本迁移时，只能使用逻辑备份。
2.2 物理备份
物理备份通常有两种方式，冷备份和热备份。
- 冷备份通过cp、rsync、tar、ftp等工具复制MySQL数据库文件，备份期间数据写入会导致数据丢失，确保数据一致性，数据冷备份期间会停止数据库服务。生产系统是无法频繁停机的，所以这种备份方式很少被应用于生产环境
- 热备份通过热备份工具（Xtrabackup）实现物理全备及增量备份
物理备份的优劣
优势：速度快，效率高
劣势：不易跨平台、版本，能够实现分库分表备份，但恢复复杂度高
应用场景
备份数据超过30GB，建议使用Xtrabackup备份，以提高备份效率
3 逻辑备份与物理备份对比
| 逻辑备份 | 物理备份 | |
| 备份原理 | SQL语句的形式存储 | 复制物理文件、非SQL语句的备份文件 |
| 备份工具 | mysqldump、mysql、mysqlbinlog | cp、rsync、tar、ftp、Xtrabackup |
| 满足要求 | 锁表，不停库 | 冷备份：锁表或停库 热备份：事务备份方案 |
| 配置要求 | 恢复目标库，无需考虑系统版本、mysql版本、数据库配置等 | 物理复制需要系统、配置、版本一致 |
| 性能分析 | 速度慢 | 速度快 |
| 易用性 | 安全、易操作、不易丢失数据 | 冷备份：易操作、应用场景极少 热备份：操作复杂，难度高 |
4 逻辑备份的应用场景
4.1 中小企业数据备份
使用mysqldump备份脚本
#!/bin/bash
export PATH=/app/mysql5.7/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/bak
[ ! -d $bak_path ] && mkdir -p $bak_path
mysqldump -B -A --master-data=2 | gzip > $bak_path/${file_name}.sql.gz
#rsync all data to backup server
rsync -az $bak_path/ rsync_backup@node2::mysql/ --password-file=/etc/rsync.password
#del expiries file
find $bak_path/ -type -f -name "*.sql.gz" -mtime +7 | xargs rm -f复杂脚本
#!/bin/bash
export PATH=/app/mysql5.7/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/bak
[ ! -d $bak_path ] && mkdir -p $bak_path
#if wiik is 6 ten bak other file name.
if [ $(date +%w) -eq 6 ]
then
file_name=bak_$(date +%w_%F)
else
file_name=bak_$(date +%F)
fi
mysqldump -B -A --master-data=2 | gzip > $bak_path/${file_name}.sql.gz
md5sum $bak_path/${file_name}.sql.gz > $bak_path/${file_name}.flag
#rsync all data to backup server
rsync -az $bak_path/ rsync_backup@node2::mysql/ --password-file=/etc/rsync.password
#del expiries file
find $bak_path/ -type -f -name "*.sql.gz" -mtime +7 | xargs rm -f参考 http://edu.51cto.com/course/course_id-8198.html
备份策略建议
MySQL数据库配置一主多从的架构，采取在一个从服务器做全量、增量备份。备份方案可选mysqldump或Xtrabackup。
4.2 增量恢复
完全恢复数据的必要条件
- 具备全量备份（mysqldump）
- 全量备份之后产生的所有binlog日志
--- 停止数据服务端口
iptables -I INPUT -p tcp --dport 3306 ! -s 10.6.0.243 -j DROP
仅10.6.0.243可以访问数据库
--- 解压全量备份
gzip -cd all.sql.gz > all.sql
--- 解析binlog文件
sed -n '22p' all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='ocean-bin.000006',MASTER_LOG_POS=6658;
--- ocean-bin.000006的6658位置恢复
mysqlbinlog -d ocean ocean-bin.000006 --start-position=6658 -r bin.sql
mysqlbinlog -d ocean ocean-bin.000007 ocean-bin.000008 -r bin.sql
--- 踢出误操作语句
grep -w drop bin.sql
sed -i '/drop database ocean/d' bin.sql
--- 恢复全量备份
mysql < /bak/all.sql
--- 恢复增量备份
mysql ocean < /bak/bin.sql5 分库分表备份
实际生产环境中，一个实例中可能存在多个生产库。恢复时可能仅需要一个库，甚至某套库中的某个表，这时就需要分库分表备份
5.1 分库备份
抽取实例中所有库名，对每个库执行备份
#!/bin/bash
export PATH=/app/mysql5.7/bin:/usr/local/bin:/usr/sbin/usr/bin
bak_path=/bak/$(date +%F)
[ ! -d $bak_path ] && mkdir -p $bak_path
for dbname in `mysql -e "show databases" | sed '1,2d' | grep -v _schema`
do
mysqldump -B --master-data=2 | gzip > $bak_path/${dbname}_$(date +%F).sql.gz
done5.2 分表备份
循环数据库中的所有表执行备份
#!/bin/bash
export PATH=/app/mysql5.7/bin:/usr/local/bin:/usr/sbin/usr/bin
bak_path=/bak/$(date +%F)
[ ! -d $bak_path ] && mkdir -p $bak_path
for dbname in `mysql -e "show databases" | sed '1,2d' | grep -v _schema`
do
for tablename in `mysql -e "show tables from $dbname;" | sed 'id'`
do
mysqldump -B --master-data=2 | gzip > $bak_path/${dbname}_${tablename}_$(date +%F).sql.gz
done
done注：分库分表备份，恢复单库很方便，不建议用于完整恢复。binlog会有写入操作，破坏数据一致性
6 生产备份架构
一般生产环境多是一主多从架构，在不对外提供服务的从库上配置备份策略。备份策略一般是周日凌晨2点执行全量备份，然后依据日期推送至异地备份服务器，备份保留有效期为30天。
参考 《MySQL 5.7 Reference Manual》、《MySQL Source-Configuration Options》
相关链接
2066
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
