目录
为什么我们需要备份数据库
备份建议
备份和恢复类型
数据库常见的备份方法
使用mysqldump进行备份
使用物理冷备份进行数据库备份恢复
使用二进制日志增量恢复
使用第三方工具实现热备份与恢复
环境：
系统版本：centos 7.3
数据版本：MySQL 5.6.36
1.1为什么我们需要备份
备份的主要目的是为了灾难恢复，备份还可以用测试应用、回滚数据修改、查询历史数据、审计等。
之前我们学习过安装mysql,本节将从生产运维的角度介绍备份策略的制订。由于生产环境大多是主从架构，因此本节内容将基于这种架构，阐述备份的一些策略和方式。
如果是拥有大规模数据库集群的公司，你可能需要专门规划执行数据库备份的机器和海量分布式数据库系统，以存储备份，你可能还需要有专门的检测系统、调度系统、恢复测试系统、预警系统和保留策略，以应对大量数据库备份。
1.2备份建议
如下是一些备份建议。
· 对于数据量很大的数据库，物理备份更合适。
对于数据量较小的数据库，用逻辑备份即可，这样也很简单；对于数据量很大的数据库，逻辑备份恢复速度太慢，也可能会出错，建议选择物理备份。由于数据量比较大的数据库每天全备成本太高，使用增量备份或保留副本也许是最好的选择。
· 我建议大家跟数据库的重要程度保留多个副本
· 主库应该开启二进制日志，以便搭建从库，以及跟数据库做时间点及位置恢复。Expire_logs_days参数至少要跨越1-2个备份。还要考虑备份的安全等。
1.3备份和恢复类型
1.3.1物理备份
物理备份由存储数据库内容的目录和文件的原始副本组成。这种类型的备份适用于需要在出现问题时需要快速恢复的大型重要数据库。
物理备份方法具有以下特点：
· 备份由数据库目录和文件的精确副本组成。通常这是MySQL数据目录的全部或部分的副本。
· 物理备份方法比逻辑快，因为它们仅涉及不转换的文件复制。
· 输出比逻辑备份更紧凑。
· 由于备份速度和紧凑性对于繁忙业务数据库很重要，
· 备份和还原粒度范围从整个数据目录的级别到单个文件的级别。
· 除数据库外，备份还可以包括任何相关文件，如日志或配置文件。
· MEMORY表中的数据很难备份，因为它们的内容不存储在磁盘上。
· 备份只能用于具有相同或相似硬件特性的其他机器。
· 可以在MySQL服务器不运行时执行备份。如果服务器正在运行，则需要执行相关的锁表，以便服务器在备份期间不会更改数据库内容。
1.3.2逻辑备份
逻辑备份表示为逻辑数据库结构(， 语句)和内容(语句或分隔文本文件)的信息。这种类型的备份适用于可以编辑数据值或表结构较小数据量，或者在不同的机器体系结构上重新创建数据。
逻辑备份方法具有以下特点：
· 通过查询MySQL服务器来获取数据库结构和内容信息来完成备份。
· 备份比物理方法慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。
· 输出大于物理备份，特别是以文本格式保存时。
· 备份和恢复粒度在服务器级别(所有数据库)，数据库级别(特定数据库中的所有表)或表级别都可用。无论存储引擎如何，都是如此。
· 备份不包括数据库的日志或配置文件或其他与数据库相关文件。
· 以逻辑格式存储的备份与机器无关并且高度便携。
· MySQL服务器正在运行逻辑备份。服务器不会脱机。
· 逻辑备份工具包括 程序和语句。这些工作适用于任何存储引擎，
· 要恢复逻辑备份，可以使用客户端处理SQL格式转储文件。要加载分隔文本文件，请使用该语句或 客户端。
1.3.3在线与离线备份
在MySQL服务器运行时需要进行在线备份，从而不会影响数据库正常业务主读或写。服务器停止在进行脱机备份时。这种区分可以被描述为"热"对"冷"备份;"热"备份是在服务器保持运行的情况下进行，但会锁定，以防止从外部访问数据库时修改数据。
在线备份方式有以下特点：
· 备份对其他客户端的侵扰性较小，可以在备份期间连接到MySQL服务器，并且可以根据需要执行相应的操作。
· 必须注意相关锁表，以便防止不会发生损害备份完整性的数据修改。MySQL企业备份产品会自动进行此类锁定。
离线备份方法具有以下特点：
· 客户端可能会受到不利影响，因为服务器在备份期间不可用。因此，这种备份通常可以取自脱机而不损害可用性的复制的从属服务器。
· 备份过程更简单，因为客户端活动不会有干扰。
在线和离线之间的类似区别适用于恢复操作，并且类似的特征也适用。但是，由于恢复需要表锁，因此客户端更可能会受到联机恢复的影响。在备份期间，客户端可以在备份时读取数据和修改数据，
1.3.4快照备份
某些文件系统实现启用"快照"备份。这些提供文件系统在给定时间创建副本，而不需要整个文件系统的物理副本。(例如，实现可能会用到写时复制技术，以便在快照时间之后修改的部分文件系统才能被复制。)MySQL本身不提供采取文件系统快照的功能。它可以通过第三方解决方案，如阿里云虚拟机、kvm、vmware等。
1.3.5完全与增量备份
完全备份，是在指定某一个时间点上进行所有数据的复制。实际应用中就是用一个很大磁盘对整个系统进行完全备份，包括其中的系统和所有的数据。这种备份方式最大的好处就是只要用一块大磁盘，就可以恢复丢失的数据。因此大大加快了系统或数据的恢复时间。然而它的不足之处也很明显，比如需要更多更大的磁盘空间及大量的冗余数据，另外每次备份需要更长的时间等。
增量备份，是指在一次全备份或上一次增量备份后，以后每次的备份只需要备份与前一次相比增加或被修改的binlog日志文件。这就意味着，第一次增量备份的对象是进行全备后又增加和修改的binlog文件；第二次增量备份的对象是进行第一次增量备份后所增加和修改的binlog日志文件，依次类推。这种备份方式最显著的优点就是：没有重复的备份数据，因此备份的数据量不大，备份所需的时间很短。但增量备份的数据恢复是比较麻烦的。你必须具有上次全备份和所有增量备份的磁盘存储文件，如果一但丢失或损坏，都会导致恢复失败，并且它们必须沿着从全备份到依次增量备份时间顺序逐个反推恢复，因此这就极大的延长了恢复时间。
1.3.6完全与时间点(增量)恢复
完全恢复来自完整备份的所有数据。这将使服务器实例恢复到备份前所处的状态。如果数据不够完整，则完全恢复后可以恢复自完全备份以后进行的增量恢复，以使数据库处于数据完整的状态。
增量恢复是在给定时间段内进行改动的新数据。同时这也称为时间点及位置恢复，因为它能使数据库的状态数据达到一致。时间点恢复基于二进制日志，通常遵循备份文件的完全备份，将备份文件恢复到备份时的状态。然后将二进制日志文件中写入的数据更改作为增量恢复应用于重做数据的恢复，并使数据库达到完全一致的状态。
1.4数据库常见的备份方法
1.4.1使用mysqldump或mysqlhotcopy进行备份
mysqldump程序和 脚本可以做备份。更为通用，因为它可以备份各种表。仅适用于某些存储引擎。
mysqldump是客户端常用逻辑备份程序，能够产生一组被执行以再现原始数据库对象定义和表数据的SQL语句。它转储一个或多个MySQL数据库进行备份或传输到远程SQL服务器。mysqldump 命令也可以以CSV方式输出，或其他以分隔符的文本或XML格式。
mysqlhotcopy是由Tim Bunce最初编写和贡献的Perl脚本。它使用 FLUSH TABLES， LOCK TABLES和，cp或scp进行数据库备份。对数据库或单个表进行备份是一种快速的方法，但只能在数据库目录所在的同一台机器上运行。mysqlhotcopy仅用于备份 MyISAM和ARCHIVE表。它只能运行在Unix或linux上。要使用mysqlhotcopy，您必须具有对要备份的表的文件的读取权限 SELECT，这些表的RELOAD权限，特权(能够执行FLUSH TABLES)以及LOCK TABLES特权(能够锁定表)。因为使用范围很小，因此本文中只做介绍，如果同学们有兴趣可以在课下研究。
1.4.2使用物理冷备份
此时需要在数据库处于关闭状态下，能够较好的保证数据库的完整性。
物理冷备份一般用于非核心业务，这类业务一般都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简单的。通常通过直接复制物理文件来实现备份。
1.4.3通过启用二进制日志进行增量备份
MySQL支持增量备份：您必须在mysql配置文件中加入选项启用二进制日志;二进制日志文件为您提供复制对执行备份点后进行的数据库更改所需的信息进行恢复。目前，您想进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改)，并且需要刷新二进制日志。
1.4.4通过第三方工具percona XtraBackup物理热备份恢复
Percona XtraBackup是一个免费的MySQL热备份软件，支持在线热备份Innodb和XtraDB,也可以支持MySQL表备份，不过MyISAM表的备份要在表锁的情况进行。本节对于Percona XtraBackupr的叙述是基于2.4版本的。
Percona XtrBackup有三个主要的工具：Xtrabackup、innobackupex、xbstream。它们的特点分别如下：
1) xtrabackup:是一个编译了的c二进制文件，只能备份Innodb/Xtradb数据文件。
2) innodbackupex：是一个封装了xtrabackup的Perl脚本，除了可以备份Innodb/Xtradb之外，还可以备份MySIAM。
3) xbstream:是一个新组件，能够允许将文件转成xbstream格式或从xbstream转到文件格式。
你可以单独使用Xtrabackup工具，但文中推荐使用innobackupex来进行备份，因为innobackupex本身就已经包含了xtrabackup的所有功能。
Xtrabackup是基于Innodb的灾难恢复功能进行设计的，备份工具复制Innodb的数据文件，但是，由于不锁表，这样复制出来的数据就会不一致。Innodb维护了一个重做日志，包含Innodb数据的所有改动情况。在xtrabackup备份Innodb的数据同时，xtrabackup还有另外一个线程监视着重做日志，一但日志发生变化，就把发生变化的日志数据复制走。这样就可以利用重做日志做灾难恢复了。
以上是备份过程，如果我们需要恢复数据，则在准备阶段，xtrabackup就需要使用之前复制的重做日志对备份出来的Innodb数据文件进行灾难恢复，此阶段完成之后，数据库就可以进行重建还原了。
Percona XtraBackup对MySIAM的复制，是按这样的一个顺序进行的：先锁定表，然后复制，再解锁表。
1.5使用mysqldump进行备份
逻辑备份一般用于数据迁移或者数据量很小时，逻辑备份采用的是数据导出的备份方式。
1.4.1逻辑备份恢复过程
· 备份过程
如果需要导出所有库，命令如下：
mysqldump –uroot –p -q --single-transaction –A > all.sql
参数详解：
--quick，-q 该选项在导出大表时很有用，它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。
--single-transaction 该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。本选项和 --lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用 --quick 选项
如果需要备份个别库，命令如下：
mysqldump –uroot –p -q --single-transaction test > test.sql
如果需要备份个别库中的单个表，命令如下：
mysqldump –uroot –p -q --single-transaction test 表名 > test.表名.sql
· 恢复过程
通过下命令实现:
[root@mysql-test01 ~]# mysql -u root -p < all.sql //恢复所有库
[root@mysql-test01 ~]# mysql -u root -p test < test.sql //恢复单个库
[root@mysql-test01 ~]# mysql -u root -p test 表名< test.表名.sql //恢复单个库中的表
1.6使用物理冷备份进行数据库备份恢复
1.6.1备份过程
· 关闭MySQL服务进程。命令如下：
[root@mysql-test01 ~]# systemctl stop mysql
· 把data数据目录和日志目录复制到带恢复盘
1.6.2恢复过程
· 用复制的数据目录和日志目录替换原有目录。
· 启动mysql服务进程。命令如下：
[root@mysql-test01 ~]# systemctl start mysql
1.7使用二进制日志增量恢复
基于binlog的恢复需要建立在全量备份恢复基础上。使用mysqldump的全量备份的方法在我前面的章节里有详细介绍，可以参考1.4节。
首先，我们需要开启MySQL服务器的二进制日志功能，其实现方法有很多种，最常用的是在MySQL的配置文件的mysqld项中加入log-bin=[filepath]项；也可以使用mysqld –log-bin=[filepath]重新启动MySQL服务器。
在启用bin_log选项时并在第一次重启会有一个数据库初始化动作。为了测试方便我们直接用test库做演示，如下图所示：
1.7.1修改数据备份过程
1)开始插入数据：
mysql> create table test.cs (id int,name char(20));
mysql> insert into test.cs(id,name) values('1','zhansan');
2)使用mysqldump对数据库进行完全备份，它可以实现对数据库的联机，非阻塞的热备份，不会影响其他进程对数据库的读写操作。
mysqldump –uroot –p -q --single-transaction –A > all.sql
1.7.2增量恢复数据过程
3)删除mysql自带的mysql和test库，尝试用备份的文件恢复数据库试试
4)执行恢复操作：
[root@mysql-test01 ~]# mysql -u root -p < all.sql
查看数据是否正常，如下面截图：
5)使用flush logs指令刷新创建新的二进制日志
mysql>flush logs
6)手动执行这个flush logs，MySQL会重新创建新的日志文件进行记录，执行后会多出一个二进制日志文件，如下图所示：
7)继续往cs表中插入一条数据(因为一会要运行增量备份脚本做演示)。
mysql> insert into test.cs(id,name) values('1','lishi');
8)再执行flush logs，依然会多出一个二进制日志文件mysql-bin.000004
mysql> flush logs;
如果发生接下来的几种情况，需要恢复上面所创建的数据该怎么？
· 操作系统崩溃
· 电源(检测)失败
· 文件系统崩溃
· 硬件问题(硬盘，主板等)
我们先将MySQL数据库先恢复到全备时的状态。
9)执行上次全量备份恢复操作：
[root@mysql-test01 ~]# mysql -u root -p < all.sql
10)再次用二进制日志增量文件恢复数据库，因为增量恢复时需要用到了二进制日志文件。所以我们需要恢复mysql-bin.000003。
[root@mysql-test01 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000003| mysql -u root -p
上图已经说明数据恢复成功。
当然用MySQL的二进制日志文件远不止基于完整的恢复。它还可以基于时间点的恢复和基于位置的恢复。这里不作太多的说明，只列一下参考命令。
· 通过事件的时间来恢复:
我们可以通过参数--start-datetime 和 --stop-datetime指定恢复binlog日志的起止时间点，时间使用DATETIME格式。比如在时间点2005-04-20 10:00:00我们删除掉一个库，我们要恢复该时间点前的所有日志.
[root@mysql-test01 ~]# mysqlbinlog --stop-datetime="2017-06-04 9:59:59" /var/lib/mysql/mysql-bin.000003 | mysql -u root –p
我们可能几个小时后才发现该错误，后面又有一系列的增删查改等操作，我们还需要恢复后续的binlog，我们可以指定起始时间
[root@mysql-test01 ~]# mysqlbinlog --start-datetime="2017-06-04 10:01:00" /var/lib/mysql/mysql-bin.000003 | mysql -u root -p
· 通过事件的位置来恢复:
我们可以通过参数--start-position 和 --stop-position指定恢复binlog日志的起止位置点
[root@mysql-test01 ~]# mysqlbinlog --no-defaults /var/lib/mysql/mysql-bin.000003 --start-position="134350543" --stop-position="152617018" | mysql -f -uroot –p
1.8使用第三方工具实现热备份与恢复
Xtrabackup是Percona公司的开源项目，用以实现类似Innodb官方热备份工具功能，它能非常快速地备份和恢复数据库。Xtrabackup中包含两个工具：
Xtrabackup是将用于热备份Innodb及xtraDB表中数据工具，不能备份其他类型的表，也不能备份数据表结构。
Innobackupex是将xtrabackup进行封装的perl脚本，它提供了备份MyISAM表的能力。由于innobackupex的功能更为完善，所以一般选择innobackupex来进行备份。
1.8.1下面来看一下安装方法，安装命令如下：
下载地址：http://www.percona.com/downloads/XtraBackup/
[root@mysql-test01 ~]# yum install -y perl-DBD-MySQL perl-Digest-MD5 libev //依赖包
[root@mysql-test01 ~]# rpm -ivh percona-xtrabackup-*
1.7.2进行全量备份命令如下：
[root@mysql-test01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=test /bak
说明：
--user:指定连接数据库用户名。
--password:指定连接数据密码。
--defaults-file:指定数据库的配置文件
/bak :是备份文件的存放位置
备份成功后会提示如下：
170604 21:58:41 Backup created in directory '/bak/2017-06-04_21-57-25/'
MySQL binlog position: filename 'mysql-bin.000006', position '120'
170604 21:58:41 [00] Writing backup-my.cnf
170604 21:58:41 [00] ...done
170604 21:58:41 [00] Writing xtrabackup_info
170604 21:58:41 [00] ...done
xtrabackup: Transaction log of lsn (3286364276) to (3286364276) was copied.
170604 21:58:41 completed OK!
1.7.3、全量备份恢复
全量恢复的操作步骤如下：
1)停止mysql数据库。命令如下：
[root@mysql-test01 ~]# systemctl stop mysql
2)删除老数据库中的数据文件和事务日志文件。
[root@mysql-test01 ~]# rm -rf /var/lib/mysql/*
3)将备份文件中的日志应用到备份文件中的数据文件上，命令如下：
[root@mysql-test01 ~]# innobackupex --defaults=/etc/my.cnf --apply-log /bak/2017-06-04_21-57-25
事务日志恢复成功后会提示:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 3286364712
4)将备份文件中的数据恢复到数据库中，命令如下：
innobackupex --defaults-file=/etc/my.cnf --copy-back /bak/2017-06-04_21-57-25
物理文件和事务日志恢复成功后会提示：
170604 22:17:03 [01] Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
170604 22:17:03 [01] ...done
170604 22:17:03 [01] Copying ./xtrabackup_binlog_pos_innodb to /var/lib/mysql/xtrabackup_binlog_pos_innodb
170604 22:17:03 [01] ...done
170604 22:17:03 [01] Copying ./ibtmp1 to /var/lib/mysql/ibtmp1
170604 22:17:03 [01] ...done
170604 22:17:03 completed OK!
5)数据恢复完成后，需要修改相关文件的权限，命令如下：
[root@mysql-test01 mysql]# chown -R mysql.mysql /var/lib/mysql/
6)重新启动mysql数据库，命令如下：
[root@mysql-test01 mysql]# systemctl start mysql
[root@mysql-test01 mysql]# netstat -anput | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 3190/mysqld
以上成功启动登录mysql数据库，说明恢复成功。
1.7.4、增量备份恢复
进行增量备份的前提是必须已经做完全量备份，如下操作：
· 增量备份
1) 先进行全量备份，命令如下：
[root@mysql-test01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root -pbdqn /bak2)再进行增量备份
[root@mysql-test01 ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=bdqn --incremental /bak/incrementbak --incremental-basedir=/bak/2017-06-06_15-34-38/增量备份成功后会有以下提示：
170606 15:36:02 Executing UNLOCK TABLES
170606 15:36:02 All tables unlocked
170606 15:36:02 Backup created in directory '/bak/incrementbak/2017-06-06_15-35-56/'
MySQL binlog position: filename 'mysql-bin.000010', position '320'
170606 15:36:02 [00] Writing backup-my.cnf170606 15:36:02 [00] ...done
170606 15:36:02 [00] Writing xtrabackup_info
170606 15:36:02 [00] ...done
xtrabackup: Transaction log of lsn (1626107) to (1626107) was copied.
170606 15:36:02 completed OK!· 增量恢复
2) 先恢复增量自务日志，命令如下:
[root@mysql-test01 ~]# innobackupex --user=root --password=bdqn --apply-log-only /bak/2017-06-06_15-34-38/ --incremental-dir=/bak/incrementbak/2017-06-06_15-35-56/
3) 再恢复全量事务日志，命令如下：
[root@mysql-test01 ~]# innobackupex --copy-back --datadir=/usr/local/mysql/data/ /bak/2017-06-06_15-34-38/
增量恢复成功后会有以下提示：
170606 15:50:00 [01] Copying ./wxbaoming/db.opt to /usr/local/mysql/data/wxbaoming/db.opt
170606 15:50:00 [01] ...done
170606 15:50:00 [01] Copying ./benet/db.opt to /usr/local/mysql/data/benet/db.opt
170606 15:50:00 [01] ...done
170606 15:50:00 [01] Copying ./test01/db.opt to /usr/local/mysql/data/test01/db.opt
170606 15:50:00 [01] ...done
170606 15:50:00 [01] Copying ./xtrabackup_info to /usr/local/mysql/data/xtrabackup_info
170606 15:50:00 [01] ...done
170606 15:50:00 [01] Copying ./2017-06-06_15-43-34/xtrabackup_info to /usr/local/mysql/data/2017-06-06_15-43-34/xtrabackup_info
170606 15:50:00 [01] ...done
170606 15:50:00 [01] Copying ./ibtmp1 to /usr/local/mysql/data/ibtmp1
170606 15:50:00 [01] ...done
170606 15:50:00 completed OK!
