DBA必知的mysql备份与还原的几大方法

博主QQ：819594300

平房网站制作公司哪家好，找创新互联！从网页设计、网站建设、微信开发、APP开发、响应式网站建设等网站项目制作，到程序开发，运营维护。创新互联自2013年起到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联。

博客地址：http://zpf666.blog.51cto.com/

有什么疑问的朋友可以联系博主，博主会帮你们解答，谢谢支持！一、MySQLdump备份结合binlog日志恢复

说明：MySQL备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。

1、binlog介绍

1）该日志记录着数据库的所有增、删、改的操作日志，还包括这些操作的执行时间。

Binlog功能默认是关闭的，没有开启。

查看binlog，用mysqlbinlog  -v  mysql-bin.000001

Binlog的用途：1：主从同步          2：恢复数据库

开启binary log功能：通过编辑my.cnf中的log-bin选项可以开启二进制日志；形式如右：log-bin[=DIR/[filename]] ，注释：每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增，除了生成上述的文件外还会生成一个名为filename.index的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

2）查看产生的binary log   注：查看binlog内容是为了恢复数据

说明：bin-log因为是二进制文件，不能通过文件内容查看命令直接打开查看，mysql提供两种方式查看方式。

①在介绍之前，我们先对数据库进行一下增删改的操作，否则log里边数据有点空。

②重新开始一个新的日志文件

③查看MySQL Server上的二进制日志

查看指定的二进制日志中的事件：

该命令还包含其他选项以便灵活查看：

总结：上述方式可以查看到服务器上存在的二进制日志文件及文件中的事件，但是想查看到文件中具体的内容并应于恢复场景还得借助mysqlbinlog这个工具。

语法格式：mysqlbinlog  [options]  log_file ...

输出内容会因日志文件的格式以及mysqlbinlog工具使用的选项不同而略不同。

mysqlbinlog的可用选项可参考man手册。

说明：无论是本地二进制日志文件还是远程服务器上的二进制日志文件，无论是行模式、语句模式还是混合模式的二进制日志文件，被mysqlbinlog工具解析后都可直接应用与MySQL Server进行基于时间点、位置或数据库的恢复。

下面我们就来演示如何使用binlog恢复之前删除数据（id=2那条记录）

注意：在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。

①查看binlog文件，从中找出delete from bdqn.test where id=2

# cd/usr/local/mysql/data/

# mysqlbinlog  -v mysql-bin.000002

显示结果如下：

图片看不清楚的可以看下面复制的日志：
# at 219

#170316 21:52:28 server id 1  end_log_pos 287 CRC32 0xff83a85b    Query   thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1489672348/*!*/;

SET @@session.pseudo_thread_id=2/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 287

#170316 21:52:28 server id 1  end_log_pos 337 CRC32 0x343e7343   Table_map: `bdqn`.`test` mapped to number 108

# at 337

#170316 21:52:28 server id 1  end_log_pos 382 CRC32 0xa3d1ce0d    Delete_rows: table id 108 flags: STMT_END_F

BINLOG '

nJjKWBMBAAAAMgAAAFEBAAAAAGwAAAAAAAEABGJkcW4ABHRlc3QAAgMPAjwAAkNzPjQ=

nJjKWCABAAAALQAAAH4BAAAAAGwAAAAAAAEAAgAC//wCAAAABGxpc2kNztGj

'/*!*/;

### DELETE FROM `bdqn`.`test`

### WHERE

###   @1=2

###   @2='lisi'

# at 382

#170316 21:52:28 server id 1  end_log_pos 413 CRC32 0x257e7073    Xid = 10

COMMIT/*!*/;

说明：可以从上图可以看出来delete时间发生position是287，事件结束position是413。

②恢复流程：直接用bin-log日志将数据库恢复到删除位置287前,然后跳过故障点,再进行恢复下面所有的操作,命令如下

由于之前没有做过全库备份，所以要使用所有binlog日志恢复，所以生产环境中需要很长时间恢复，导出相关binlog文件。

③删除bdqn数据库（删除bdqn和恢复数据之前，要关闭binlog功能）

④利用binlog恢复数据

⑤恢复完成后，我们检查下表的数据是否完整

2、mysqldump介绍

作用：mysqldump是mysql自带的备份和数据转移的工具。

特点：它只产生sql语句（即sql命令）封装在文件，而不是真实的数据。

Mysqldump是逻辑备份，不是物理备份，备份的是SQL语句，而不是数据文件。

Mysqldump适用于小型数据库，数据容量一般是在几个G大小，当数据量很大的情况下，不建议使用mysqldump。

导出对象：可以针对单个表、多个表、单个数据库、多个数据库、所有数据库。

格式：

#mysqldump   [选项]   库名   [表名1]  [表名2]  … > /备份路径/备份文件名

//导出指定数据库的单个或多个表

#mysqldump   [选项]   --databases  库名1 [库名2]  … >  /备份路径/备份文件名

//导出指定的数据库或多个数据库

#mysqldump   [选项]   --all-databases  >  /备份路径/备份文件名

//导出所有的数据库

#mysqldump   -uroot  -p123456 --flush-logs   bdqn  > /opt/bdqn.sql

//导出数据库bdqn,其中“—flush-logs”这个选项是完整备份完毕后开启一个新的binlog

#mysql -uroot  -p123456  bdqn <  /opt/bdqn.sql

//从备份文件导入数据库bdqn

下面用一个具体的实验说明用mysqldump实现全库备份+binlog的数据恢复

1）开启binlog功能并重启服务

2）创建备份目录

3）创建实验数据

4）开始全库备份（注意：全库备份不会备份binlog日志文件）

5）备份mysqldump全库备份之前的所有的binlog日志文件（注意：真是生产环境中可能不止一个binlog文件）

6）因为全库备份之前的binlog已经备份了，现在就删除它们（即新产生的binlog之前的所有的binlog删除）

7）模拟误操作，删除了数据，并且新增加了新的数据

8）备份自mysqldump之后的binlog日志文件

9）使用mysqldump的全库备份+binlog来恢复数据

①使用mysqldump的备份进行全库恢复（即恢复到全部备份时候的所有数据）

②分析新开启的binlog日志文件（我这里是mysql-bin.000002）里面误操作的事件的起始位置和终止位置，只要跳过这一段事件即可

图片看不清楚的可以看下面复制的日志：

# at 219

#170318 21:14:42 server id 1  end_log_pos 291 CRC32 0xddbf8eff    Query   thread_id=5 exec_time=0 error_code=0

SET TIMESTAMP=1489842882/*!*/;

SET @@session.pseudo_thread_id=5/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 291

#170318 21:14:42 server id 1  end_log_pos 339 CRC32 0x4a9ec8f2   Table_map: `bdqn`.`it` mapped to number 108

# at 339

#170318 21:14:42 server id 1  end_log_pos 388 CRC32 0x2e8a3da8    Delete_rows: table id 108 flags: STMT_END_F

BINLOG '

wjLNWBMBAAAAMAAAAFMBAAAAAGwAAAAAAAEABGJkcW4AAml0AAIDDwI8AALyyJ5K

wjLNWCABAAAAMQAAAIQBAAAAAGwAAAAAAAEAAgAC//wBAAAACHpoYW5nc2FuqD2KLg==

'/*!*/;

### DELETE FROM `bdqn`.`it`

### WHERE

###   @1=1

###  @2='zhangsan'

# at 388

#170318 21:14:42 server id 1  end_log_pos 419 CRC32 0xa1c06a4f    Xid = 43

COMMIT/*!*/;

③开始使用全库备份后的增量备份的binlog日志文件备份文件进行对全库恢复后的增量数据的恢复

10）查看恢复结果

总结：从上图显示可以看出数据恢复到正常状态，实际生产环境中mysql数据库的备份是周期性重复操作，所有通常是要编写脚本实现，通过crond计划任务周期性执行备份脚本。

通过crontad计划任务周期性执行备份脚本

1）制定mysqldump备份方案

周日凌晨1点全库备份；

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

设置crontab任务，每天执行备份脚本：

2）编写mysqlfullbackup.sh脚本（即mysql全库备份脚本）

图片看不清楚的可以看下面复制的脚本原文件：

#!/bin/bash

#Name:mysqlFullBackup.sh

#定义数据库目录

mysqlDir=/usr/local/mysql

#定义用于备份数据库的用户名和密码

user=root

userpwd=123456

dbname=bdqn

#定义备份目录

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 zcvf$gzdumpfile $dumpfile >> $emailfile 2>&1

   echo"BackupFileName:$gzdumpfile" >> $emailfile

   echo"DataBase Backup Success!" >> $emailfile

   rm -rf$dumpfile

else

   echo"DataBase Backup Fail!" >> $emailfile

fi

#写日志文件

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

cat $emailfile >> $logfile

#发送邮件通知

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

2）编写mysqldailybackup.sh脚本（即mysql增量备份脚本）

图片看不清楚的可以看下面复制的脚本原文件：

#!/bin/bash

#Name:mysqlDailyBackup.sh

#定义数据库目录和数据目录

mysqldir=/usr/local/mysql

datadir=$mysqldir/data

#定义用于备份数据库的用户名和密码

user=root

userpwd=123456

#定义备份目录、每日备份文件备份到$databackupdir/daily

databackupdir=/opt/mysqlbackup

dailybackupdir=$databackupdir/daily

#定义邮件正文文件

emailfile=$databackupdir/email.txt

#定义邮件地址

email=root@localhost.localdomain

#定义日志文件

logfile=$databackupdir/mysqlbackup.log

echo "" > $emailfile

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

#刷新日志，使数据库使用新的二进制日志文件

$mysqldir/bin/mysqladmin -u$user -p$userpwd --flush-logs

cd $datadir

#得到二进制日志列表

filelist=`cat mysql-bin.index`

icounter=0

for file in $filelist

do

  icounter=`exper$icounter + 1`

done

nextnum=0

ifile=0

for file in $filelist

do

 binlogname=`basename $file`

  nextnum=`expr$nextnum + 1`

#跳过最后一个二进制日志（数据库当前使用的二进制日志文件）

if [ $nextnum -eq $icounter ]; then

  echo "Skiplastest!" > /dev/null

else

 dest=$dailybackupdir/$binlogname

#跳过已经备份的二进制日志文件

if [ -e $dest ]; then

  echo "Skipexist $binlogname!" > /dev/null

else

#备份日志文件到备份目录

cp $binlogname $dailybackupdir

if [ $? -eq 0 ]; then

ifile=`expr $ifile + 1`

echo "$binlogname backup success!" >>$emailfile

      fi

   fi

fi

done

if [ $ifile -eq 0 ]; then

   echo "NoBinlog Backup!" >> $emailfile

else

   echo"Backup $ifile File(s)." >> $emailfile

   echo"Backup MySQL Binlog OK!" >> $emailfile

fi

#发送邮件通知

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

#写日志文件

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

cat $emailfile >> $logfile

发送邮件测试：

安装libmysqlclient.so.18

再次测试：

二、  使用xtrabackup进行MySQL数据库备份

前面介绍mysqldump备份方式是采用逻辑备份，其最大的缺陷就是备份和恢复速度都慢，对于一个小于50G的数据库而言，这个速度还是能接受的，但如果数据库非常大，那再使用mysqldump备份就不太适合了。

这时就需要一种好用又高效的工具，xtrabackup就是其中一款，号称免费版的InnoDB HotBackup。

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

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

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

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

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

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

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

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

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

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

(5)还原速度快。

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

安装xtrabackup

1）下载xtrabackup

wgethttps://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

2）解压

3)进入解压目录

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

说明：Xtrabackup中主要包含两个工具：

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

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

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

5)安装相关插件

6)下载percona-toolkit并安装

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

至此就完成了xtrabackup的安装，下面就可以启动备份了。

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

1）开启binlog功能并重启mysqld服务

2）创建备份用的目录（full：全备存放的目录；inc：增量备份存放的目录）

3）创建实验用数据库、表、以及添加实验数据

4）开始完全备份

5）我们可以看一下备份后的文件

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

2）还可以加—database选项指定要备份的数据库，这里指定的数据库只对MyISAM表有效，对于InnoDB数据来说都是全备（所有数据库中的InnoDB数据都进行了备份，不是只备份指定的数据库，恢复时也一样）。

针对里面的各个文件的说明：

下面我们护体看一下这几个文件：

说明：xtrabackup_binlog_pos_innodb和xtrabackup_binary在这个版本里面没有了，因为版本较新，这两个文件在新版给删除了，只存在于老版本。

6）至此完全备份成功，然后我们开启一个新的binlog日志文件，并向mysql某个库插入几条数据。

7）模拟误操作，删除一条数据，同时再插入两条新数据

8）开始增量备份binlog日志文件

9）开始还原数据库

①模拟数据库损坏

我这里直接使用删除数据目录文件来模拟损坏。

②然后首先是还原完全备份，准备(prepare)一个完全备份

说明1：一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

说明2：在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。

innobakupex命令的--apply-log选项可用于实现上述功能。如下面的命令：

--apply-log指明是将日志应用到数据文件上，完成之后将备份文件中的数据恢复到数据库中：

说明3：在实现“准备”的过程中，innobackupex通常还可以使用--use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。

③正式开始还原完全备份的数据库

说明1：innobackupex命令的--copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。

④修改data目录的属组和属组为mysql:mysql，并重启mysqld服务。

⑤验证还原后的数据

⑥开始还原增量备份，但在此之前为了防止还原时产生大量的二进制日志，在还原时可临时关闭二进制日志后再还原。

⑦有误操作的，在开始还原增量备份之前，要去binlog备份文件把误操作事件删除

⑧正式开始还原增量备份

⑨重新启动二进制日志并验证还原数据

附：Xtrabackup的“流”及“备份压缩”功能

作用：Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认的直接保存至某备份目录中。

要使用此功能，仅需要使用--stream选项即可。如：

看不清截图的可以看下面复制粘贴的命令：

# innobackupex --user=root --password="123456"--stream=tar /opt/mysqlbackup/full/ | gzip >/opt/mysqlbackup/full/full_`date+%F_%H%M%S`.tar.gz

（再补充一句，现实生产环境中，基本上都要用流与备份压缩功能，因为这样可以很大程度上节省空间）