MySQL企业级备份

3.1.2.逻辑备份的特点

逻辑备份的优点为操作简单、方便、可靠，并且备份的数据可以跨平台、跨版本、甚至跨软件、跨操作系统，还可以实现分库分表备份；逻辑备份也有一定的缺点，例如，备份速度比物理备份慢、恢复的效率也不是特别高等。

3.1.3.逻辑备份的常用工具

mysqldump是MySQL官方自带的最常用的逻辑备份工具，还能实现分表分库备份，还有一个mydumper工具，它是一个在GPL许可下发布的高性能MySQL备份和恢复工具集。

3.1.4.逻辑备份的企业应用场景

适用于数据量不是特别大的场景，打包前不大于30GB的数据库数据，30GB的值主要是考虑备份效率的问题，以及管理员使用复杂度的平滑。不过，在跨版本、跨软件升级或迁移数据的时候，此时物理备份一般就不能使用。

3.2.物理备份方式

3.2.1.物理备份

3.2.1.1.冷备方法

MySQL的物理备份方法之一是使用cp、rsync、tar、scp等复制工具把MySQL数据文件复制成多份，由于在备份期间数据仍然有写入操作，所以，直接复制的备份方式会引起数据丢失。另外在恢复数据库时，对新数据库的路径、配置也有要求，一般要和原库的配置保持一致（版本、路径、配置尽可能一样）。

为了确保备份期间数据的一致性，可以选择人工停库或者锁库后再进行物理复制，而这在生产环境中一般是不允许的，除非是可以申请停机或锁表时间，所以使用传统Linux命令复制工具还是比较粗的冷备份方式，应避免使用。

一般在进行大规模数据库迁移时，先停库，然后物理迁移，这样做是很有效率的方案。

3.2.1.2.热备方法

除了在Linux命令行通过命令直接复制MySQL数据文件之外，还有一些其他的第三方的开源或商业物理热备份工具，如Xtrabackup。使用这个工具可以实现物理全备及增量备份。

3.2.2.物理备份的特点

物理备份的优缺点正好与逻辑备份相反，因此在企业里应根据需求，互补使用。

1、优点：速度快，效率高。
2、缺点：不容易跨平台、跨版本、跨软件、跨操作系统，可以实现分库分表备份，但恢复时会麻烦很多，软件的使用也比较复杂一些。

3.2.3.物理备份的常用工具或方法

Linux下冷备份工具为cp、tar，备份时需要锁表或者停库以确保数据的一致性；开源的热备份（基于InnoDB）工具则是Xtrabackup。

3.2.4.物理备份的企业应用场景

数据库总数据量超过30GB的，可使用Xtrabackup热备工具进行备份，以提升效率。

可以选择在数据库的从库上进行备份，备份时停止SQL线程应用数据到数据库，然后通过cp或tar打包备份，这也是一种不错的冷备方案，不会影响数据库的服务。

3.3.物理备份与逻辑备份的区别

物理备份与逻辑备份的对比：

4.1.中小企业的MySQL备份实战

4.1.1.中小企业全备备份策略与应用

中小企业一般会采用逻辑备份，常用的工具就是mysqldump命令，备份的策略一般是每日进行全量备份，备份会选择在数据库业务流量低估时执行，备份时可以锁表或者采用事务方式备份。

简单的备份脚步：

vim bak.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/bin
bak_path=/server/backup
[ ! -d $bak_path ] && mkdir -p $bak_path  #若备份路径不存在则创建
mysqldump -B -A --master-data=2 |gzip >$bak_path/${file_name}.sql.gz  #如果仅为innodb引擎，则可以再加上--single-transaction参数
rsync -az $bak_path/ rsync_backup@172.16.1.31::mysql/ --password-file=/etc/rsync.password
#备份完成后立刻推送至备份服务器，需要提前部署rsync服务
find $bak_path/ -type -f -name "*.sql.gz" -mtime +7|xargs rm -f  #删除本地的7天备份

稍微复杂点的脚步：

vim bak.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/bin
bak_path=/server/backup
[ ! -d $bak_path ] && mkdir -p $bak_path  #若备份路径不存在则创建
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  #做md5指纹的目的是用于未来检测备份及传输结果是否正常
rsync -az $bak_path/ rsync_backup@172.16.1.31::mysql/ --password-file=/etc/rsync.password
#备份完成后立刻推送至备份服务器，需要提前部署rsync服务
find $bak_path/ -type -f -name "*.sql.gz" -mtime +7|xargs rm -f  #删除本地的7天备份

配置定时任务，使其每日0点执行脚本：

crontab -e
bak mysql for oldboy at 20200515
00 00 * * * /bin/sh /server/scripts/bak.sh &>/dev/null

保留最近7天的所有备份，同时保留每周六的全部备份：

find /server/backup/ -type f -name "bak_*" -mtime +7 ! -name "bak_6*"
find /server/backup/ -type f -name "bak_*" -mtime +7 ! -name "bak_6*" |xargs rm -f

4.1.2.全备的数据何时可以派上用场

使用mysqldump全备的数据什么时候可以派上用场：

1、迁移或者升级数据库时。
2、增加从库时。
3、人为执行DDL、DML语句破坏数据库数据时（此时若使用主从库就会无法防止数据丢失，因为所有库都会执行破坏语句）。
4、跨机房灾备时，此时需要将全备份复制到异地。

若是因为硬件或删除物理文件导致数据库故障，就不需要用备份数据恢复了，可以直接把主库关闭，在从库上配置好VIP等配置后，启动从库提供服务即可。

4.1.3.中小企业增量备份策略

中小企业增量备份就是备份binlog文件，在MySQL没有主从复制功能或主从复制功能不完善的时候，我们就曾采取定时或实时推binlog文件的方法。例如每分钟推一次binlog到备份服务器上，或者通过mysqlbinlog参数read-from-remote-server，在其他服务器上远程读取binlog。

但是这类方法都不是最佳的，因为有可能会丢失数据。比较好的binlog增量备份或MySQL备份方法就是为MySQL数据库配置异机主从复制功能（实时复制功能），即binlog会被实时地发送到从服务器上，这样效果才是最好的。当然，也要相应地在主从复制的从库上实现全备。

4.1.4.备份binlog增量文件何时可以派上用场

当需要完整恢复数据库数据的时候，就会需要binlog增量恢复。

4.1.5.企业里MySQL备份策略选择

大多数中小企业的数据库环境都为一主多从，因此，可采取在一个从库服务器上专门做全量以及增量备份（需要开启从库记录binlog日志功能），至于备份方法，采用mysqldump、Xtrabackup均可。

4.2.中小企业MySQL增量恢复案例

完整恢复数据库数据需要具备的条件：

1、具备全量备份（mysqldump）。
2、除全量备份以外，还有全量备份之后产生的所有binlog增量日志。

模拟0点开始对数据库oldboy数据进行全备：

mysqldump -B --master-data=2 --single-transaction oldboy|gzip >/data/backup/oldboy_$(date +%F).sql.gz

模拟0点全备后用户继续写入数据：

mysql -e "use oldboy;insert into test values(6,"bingbing");"
mysql -e "use oldboy;insert into test values(7,"xiaoting");"

模拟上午10点管理员删除oldboy数据库：

mysql -e "drop database oldboy;show databases;"

恢复前准备，移走所有binlog增量文件，防止二次破坏，并确认是否有全备：

cp -a /application/mysql/data/mysql-bin.* /data/backup/

开始恢复：

1、停止数据库对外访问。因为是通过drop命令删除数据库的，后面不会有写入操作，因此，可以不用额外停止写入。但如果是因为update导致的数据破坏，最好是停库处理或对外停止写入。这里采用iptables防火墙屏蔽所有应用程序的写入：
iptables -I INPUT -p tcp --dport 3306 ! -s 172.16.1.51 -j DROP  #非172.16.1.51禁止访问数据库3306端口。

2、解压全备的数据：
gzip -cd oldboy_2020-05-19.sql.gz >oldboy.sql

3、解析binlog文件增量数据：
sed -n "22p" oldboy.sql

从代码里可以看到，要从mysql-bin.000004文件的7181位置点开始恢复增量数据：
mysqlbinlog -d oldboy mysql-bin.000004 --start-position=7181 -r bin.sql

恢复后面的所有binlog文件：
mysqlbinlog -d oldboy mysql-bin.000005 mysql-bin.000006 -r bin1.sql

4、剔除误删除数据库的drop语句：
grep -w drop bin.sql  #过滤drop单词的行。
sed -i "/drop database oldboy/d" bin.sql  #删除drop数据库oldboy的语句。

恢复0点以前的全备数据：
mysql 

mysqldump -B --master-data=2 --single-transaction oldboy mysql|gzip>/data/backup/all.sql.gz

vi fenku.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/server/backup/$(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 $dbname|gzip >$bak_path/${dbname}_$(date +%F).sql.gz  #注意备份的名字
done

vi fenbiao.sh
#!/bin/bash
export PATH=/application/mysql/bin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
bak_path=/server/backup/$(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 "1d"`
    do
        mysqldump -B --master-data=2 $dbname$tablename|gzip >$bak_path/${dbname}_${tablename}_$(date +%F).sql.gz
    done
done