MySQL灾难恢复与备份（下篇）

  xiaorenwutest.blog.51cto.com

   MySQL灾难恢复与备份（下篇）

前言：在上次的讲解中我们了解了数据库通过mysqldump工具和binlog日志结合回复数据库当中的数据。但是有一个弊端。那就是mysqldump工具在回复数据量小的情况下好使，一旦数据量规模庞大的时候回复该怎么办？相信在公司当中几乎不会有DBA使用mysqldump工具恢复数据。

另一个问题是，当我们的备份工具有了的时候那么我们该怎么备份呢？难道要手动一次又一次的去备份吗？要是晚上备份该怎麽办呢？难道要晚上跑到公司去先备个份在回家休息。这就不太现实了。

今天我们就以这两个问题为主题进行探讨，并且给出解决方案。接下来进入今天的案例。

1）周期性备份方案：

比如说公司规定让DBA周日的凌晨1点全库备份；

周一到周六凌晨每隔4小时增量备份一次

我们可以通过crond设置任务计划进行备份方案

#crontab  -e

周日备份计划：

0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1

#周一到周六每隔4个小时增量备份一次

0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1

脚本的内容以及编写：

（一）首先是全库备份{也就是周日备份}

上面提到了脚本名称为：mysqlfullbackup.sh

#!/bin/bash

#定义数据库目录

mysqlDir=/usr/local/mysql

#定义用户和密码

user=root

userpwd=123456

dbname=test_db

#定义备份目录

databackupdir=/opt/mysqlbackup

#判断上条语句是否存在，如果不存在则创建

[ ! -d $databackupdir ] && mkdir $databackupdir

#定义邮件文件

emailfile=$databackupdir/email.txt

#收件地址为本机

email=root@localhost.localdomain

#定义备份日志文件

logfile=$databackupdir/mysqlbackup.log

#定义时间

DATE=$(date -I)

echo "" > $emailfile

echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile

cd $databackupdir

#定义备份文件名

dumpfile=mysql_$DATE.sql

gzdumpfile=mysql_$DATE.sql.tar.gz

#使用mysqldump工具备份数据库

$mysqlDir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile

#压缩备份文件

if [ $? -eq 0 ];then

tar zcf $gzdumpfile $dumpfile >> $emailfile 2>&1

echo "BackupFileName:$gzdumpfile" >> $emailfile

echo "DataBase Backup Success!" >> $emailfile

rm -f $dumpfile

else

echo "DataBackup Fail!" >> $emailfile

fi

#开始写入日志文件

echo "---------------" >> $logfile

cat $emailfile >> $logfile

#发送邮件通知

cat $emailfile | mail -s "Mysql Backup" $email

（二）：    另一个脚本文件

mysqldailybackup.sh{属于周一到周六每隔4小时备份}

#!/bin/bash

#定义数据库目录

mysqlDir=/usr/local/mysql

#定义用户和密码

user=root

userpwd=123456

dbname=test_db

#定义备份目录

databackupdir=/opt/mysqlbackup

#判断上条语句是否存在，如果不存在则创建

[ ! -d $databackupdir ] && mkdir $databackupdir

#定义邮件文件

emailfile=$databackupdir/email.txt

#收件地址为本机

email=root@localhost.localdomain

#定义备份日志文件

logfile=$databackupdir/mysqlbackup.log

#定义时间

DATE=$(date -I)

echo "" > $emailfile

echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile

cd $databackupdir

#定义备份文件名

dumpfile=mysql_$DATE.sql

gzdumpfile=mysql_$DATE.sql.tar.gz

#使用mysqldump工具备份数据库

$mysqlDir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile

#压缩备份文件

if [ $? -eq 0 ];then

tar zcf $gzdumpfile $dumpfile >> $emailfile 2>&1

echo "BackupFileName:$gzdumpfile" >> $emailfile

echo "DataBase Backup Success!" >> $emailfile

rm -f $dumpfile

else

echo "DataBackup Fail!" >> $emailfile

fi

#开始写入日志文件

echo "---------------" >> $logfile

cat $emailfile >> $logfile

#发送邮件通知

cat $emailfile | mail -s "Mysql Backup" $email

总结：通过以上的两个编写脚本的案例来做出对数据库的备份，而且是周期性的不用人工操作，自动执行。

优点：1）减少运维人员或DBA的工作量

      2）较少备份的失误次数

      3）效率高，速度快

2):刚才对数据库介绍了周期性的备份和任务计划的结合使用；那么我们接下来对今天的第二个问题进行探讨：{备份工具}

Xtrabackup实现是物理备份，而且是物理热备

目前主流的有两个工具可以实现物理热备：ibbackup和xtrabackup；ibbackup是商业软件，需要授权，非常昂贵。而xtrabackup功能比ibbackup还要强大，但却是开源的。因此我们这里就来介绍xtrabackup的使用。

Xtrabackup提供了两种命令行工具：

xtrabackup：专用于备份InnoDB和XtraDB引擎的数据；

innobackupex：这是一个perl脚本，在执行过程中会调用xtrabackup命令，这样用该命令即可以实现备份InnoDB，也可以备份MyISAM引擎的对象。

注：大家不要弄混了: xtrabackup：用于备份innodb和xtradb

                innobackupex：用于备份innodb和myisam

优点：

Xtrabackup是由percona提供的mysql数据库备份工具，特点：

(1)备份过程快速、可靠；

(2)备份过程不会打断正在执行的事务；

(3)能够基于压缩等功能节约磁盘空间和流量；

(4)自动实现备份检验；

(5)还原速度快。

大家如果有兴趣可以去官方上下载：

http://www.percona.com/software/percona-xtrabackup；可以下载源码编译安装，也可以下载适合的RPM包或使用yum进行安装或者下载二进制源码包。

1）首先解压下载的软件包：

# tar zxf percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

2)进入解压目录

# cd percona-xtrabackup-2.4.4-Linux-x86_64/

3)复制bin下的所有程序到/usr/bin

[root@localhost percona-xtrabackup-2.4.4-Linux-x86_64]# cp bin/* /usr/bin/

Xtrabackup中主要包含两个工具：

xtrabackup：是用于热备份innodb, xtradb表中数据的工具，支持在线热备份，可以在不加锁的情况下备份Innodb数据表，不过此工具不能操作Myisam引擎表；

innobackupex：是将xtrabackup进行封装的perl脚本，能同时处理Innodb和Myisam，但在处理Myisam时需要加一个读锁。

由于操作Myisam时需要加读锁，这会堵塞线上服务的写操作，而Innodb没有这样的限制，所以数据库中Innodb表类型所占的比例越大，则越有利。

4)安装相关插件

#yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5–y

5)下载percona-toolkit并安装

#wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm

# yum  -y  localinstall  percona-toolkit-2.2.19-1.noarch.rpm

上面属于准备工作，把需要的工具包装好，下面开始进入主题：

let's go

方案一：xtrabackup完全备份+binlog增量备份

1、备份

创建备份目录

# mkdir -p /opt/mysqlbackup/{full,inc}

full：全备存放的目录；inc：增量备份存放的目录

1)完全备份

基本语法：# innobackupex --user=DBUSER --password=DBUSERPASS  /path/to/BACKUP-DIR/

执行下面的命令进行完全备份：

# innobackupex --user=root --password=123456 /opt/mysqlbackup/full

注： --defaults-file=/etc/my.cnf 指定mysql的配置文件my.cfg，如果指定则必须是第一个参数。

/path/to/BACKUP-DIR/指定备份所存放的目标目录，备份过程会创建一个以当时备份时间命名的目录存放备份文件。

出现如下提示。表示成功

备份后的文件：

在备份的同时，备份数据会在备份目录下创建一个以当前日期时间为名字的目录存放备份文件：

数据库大家都了解；接下来对每隔文件进行下解释；

(1)xtrabackup_checkpoints ——备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；

每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

(3)xtrabackup_binlog_pos_innodb ——二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

(4)xtrabackup_binary ——备份中用到的xtrabackup的可执行文件；

(5)backup-my.cnf ——备份命令用到的配置选项信息；

在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据

注意：相关选项说明：

其中，--user指定连接数据库的用户名，--password指定连接数据库的密码，--defaults-file指定数据库的配置文件，innobackupex要从其中获取datadir等信息；--database指定要备份的数据库，这里指定的数据库只对MyISAM表有效，对于InnoDB 数据来说都是全备（所有数据库中的InnoDB数据都进行了备份，不是只备份指定的数据库，恢复时也一样）；/opt/mysqlbackup/full是备份文件的存放位置

现在的数据库有点空，数据量不够，接下来我们插入点数据，进行我们的增量备份

插入完成之后，让我们来验证一下；插入的数据是否成功：

2)增量备份二进制文件：

先进行查看二进制文件的上次备份位置

#mysqlbinlog --start-position=154 /usr/local/mysql/data/mysql_bin.000001 > /opt/mysqlbackup/inc/`date +%F`.sql

我们来看一下，增量备份是否成功了呢？

可以看得出来增量备份和完整备份都是以日期的形式进行备份的。

2）接下来我们将它们还原，看看这个备份工具究竟好不好使，有没有说的那么神奇；let's go 往下看

模拟数据库损坏：{这里我们直接将数据目录文件干掉}”

rm -rf /usr/local/mysql/data/*

这里说明了，mysql的数据目录已经被我们成功的删掉了，接下来让我们验证一下，之前备份的内容是否可以真的回复之前被删掉的内容了呢？

在这里要插一嘴，一般在备份之后数据尚且不能用于回复数据，因为备份数据中可能会包含尚未提交的事物或已经提交但尚未同步至数据文件中的事务。因此，数据在处理的时候可能导致数据不一致。

innobakupex命令的--apply-log选项可用于实现上述功能

那么让我们来看一下，数据是否真的成功回复了呢？

为什么还没有恢复回来了呢？

那只是一个完整备份，而不是数据的恢复，接下来还得进行还原数据库的sql语法： 加选项--copy-back

让我们再一次查看下数据是否回来：

从现实结果可以看到数据库等文件已经恢复回来了；但是还有点缺陷，那就是现在的用户是root而不是以前的MySQL用户，所以说，我们还要将属主：属组调整为mysql

重新启动mysql服务

进入数据库当中可以看到之前的全备份已经成功的恢复了回来，但是后续创建的表和内容还没有恢复回来，所以换需要进行增量备份的恢复。

主：为了防止在还原的时候产生大量的二进制文件，所以暂时的可以关闭二进制文件，等恢复成功之后可以再次的开启。

mysql> set sql_log_bin=0;===========关闭二进制文件

还原增量文件的方式：或者通过mysqlbinlog工具

mysql> source /opt/mysqlbackup/inc/2017-06-28.sql

完成之后再次开启二进制文件

让我们再次查看一下之前的表和数据是否已经回来了呢？

增量备份已经成功的恢复了之前的数据；此次备份与恢复成功！