Mysql之备份与恢复

数据库备份和恢复是一项最基本的操作与工作。在意外情况下（如服务器宕机、磁盘损坏、RAID卡损坏等）要保证数据不丢失，或者是最小程度地丢失，我们应该每时每刻关心所负责的数据库备份情况。

备份的分类

根据备份的方法不同，分为：

• 热备
  指数据库运行中直接备份，对正在运行的数据库操作没有任何影响。
• 冷备
  指备份操作是在数据库停止的情况下，这种备份最为简单，一般只需要复制相关的数据库物理文件即可。
• 温备
  指在数据库运行中进行的，但是会对当前数据库的操作有影响，如加一个全局读锁以保证备份数据的一致性。

根据备份后文件的内容，分为：

• 逻辑备份
  指备份出的文件是可读的，一般是文本文件。内容一般是由一条条SQL语句，或者是表内实际数据组成。如mysqldump和SELECT * INTO OUTFILE fileName FROM tableName WHERE …。
  此类方法的好处是可以观察导出文件的内容，一般适用于数据库的升级、迁移等工作。但其缺点是恢复所需要的时间往往很长。
• 裸文件备份
  指复制数据库的物理文件，即可以是在数据库运行中的复制（ibbackup、xtrabackup这类工具），也可以是在数据库停止运行时直接的数据文件复制。
  这类备份的恢复时间往往比逻辑备份短很多。

根据备份数据库的内容，分为：

• 完全备份
  指对数据库进行一个完整的备份。
• 增量备份
  指在上一次完全备份的基础上，对于更改的数据进行备份。
• 日志备份
  主要是指对Mysql数据库二进制日志的备 份，通过对一个完全备份进行二进制日志的重做来完成数据库的point-in-time的恢复工作。

冷备与恢复

对于InnoDB存储引擎的冷备非常简单，只需要备份Mysql数据库的frm文件，共享表空间文件，独立表空间文件（*.ibd），重做日志文件。

冷备的优点

• 备份简单，只要复制相关文件即可
• 备份文件易于在不同操作系统，不同Mysql版本上进行恢复
• 恢复相当简单，只需要把文件恢复到指定位置即可
• 恢复速度快，不需要执行任何SQL语句，也不需要重建索引

冷备的缺点

• InnoDB存储引擎冷备的文件通常比逻辑文件大很多，因为表空间中存放着很多其他的数据，如undo段，插入缓冲等信息
• 冷备也不总是可以轻易地跨平台。操作系统、Mysql的版本、文件大小写敏感和浮点数格式都会成为问题

逻辑备份与恢复

逻辑备份一般有两种方式：mysqldump和SELECT … INTO OUTFILE…。

mysqldump备份方式

mysqldump语法：mysqldump [args] >file_name

常用示例如下：

• 备份所有的数据库

mysqldump --all -databases >dump.sql

• 备份指定的数据库

mysqldump -databases db1 db2 db3 >dump.sql

• 对test架构进行备份

mysqldump --single -transaction test >test_backup.sql

• 导出单个表数据，例如：导出test架构下表a且表a中字段b大于2的数据

mysqldump --single -transaction --where 'b>2' test a >a.sql

当然mysqldump的参数选项还有很多，这里介绍几个重要参数：

• –single-transaction
  在备份前，先执行START TRANSACTION命令，以此来获得备份的一致性：，当前参数只对InnoDB存储引擎有效。当启用该参数并进行备份时，确保没有其他任何的DDL语句执行，因为一致性并不能隔离DDL操作。

• –lock-table（-l）
  在备份中，依次锁住每个架构下的所有表。一般用于MyISAM存储引擎，当备份时只能对数据库进行读取操作，不过备份依然可以保证一致性。对于InnoDB存储引擎，不需要使用该参数，用–single -transaction即可。并且–lock-table和–single -transaction是互斥的，如果用户的Mysql数据库中，既有InnoDB，又有MyISAM存储引擎，那么这时用户就只能使用–lock-table了。需要注意的是，–lock-table选项是依次对每个架构中的表上锁的，因此只能保证每个架构下表备份的一致性，而不能保证所有架构下表的一致性。

• –lock-all-tables（-x）
  在备份过程中，对所有架构中的所有表上锁。这个可以避免之前说的–lock-tables参数不能同时锁住所有表的问题。

• –add-drop-database
  此参数表示在CREATE DATABASES前先运行DROP DATABASE。这个参数需要和–all-databases或者–databases选项一起使用。

• maste-data [=value]
  通过该参数产生的备份转存文件主要用来建立一个replication。当value为1时，转存文件中记录CHANGE MASTER语句。当value为2，CHANGE MASTER语句被写出SQL注释。
  需要注意的是，maste-data会自动忽略–lock-tables选项。如果没有使用–single-transaction选项，则会自动使用–lock-all-tables选项。

• –tal=path
  产生TAB分割的数据文件，对于每张表，mysqldump创建一个包含CREATE TABLE语句的table_name.sql文件和包含数据的tbl_name.text文件，当然也会生产.frm文件和.ibd文件。语法示例：
  mysqldump --single-transaction --add-drop-database --tab="/usr/local/mysql/data/test" test;

通过mysqldump命令备份出的文件内容就是表结构和数据，所以这些都是用SQL语句方式表示。文件开始和结束的注释部分是用来设置Mysql数据库的各项参数，一般用来使还原工作更有效和准确的进行。之后的部分显示create table语句，接着就是insert 的sql语句

除了mysqldump方式外，很多人也喜欢用SELECT…INTO OUTFILE方式导出一张表，但是通过mysqldump一样可以完成，而且可以一次完成多张表的导出，并且实现导出数据的一致性。

SELECT…INTO OUTFILE备份方式

在逻辑备份时，SELECT…INTO OUTFILE导出的是一张表中的数据。语法：

select [column 1] ,[column 2] ...
into
outfile 'fileName'
from tableName
where ...

fileName表示导出的文件，但文件所在的路径的权限必须是mysql：mysql的，否者Mysql会报没有权限导出。

上面我们介绍了如何进行逻辑备份，但是懂得如何备份其实只是完成了整个备份工程的一小部分，能正确的恢复备份才是整个备份工程中的重中之重，下面我们讲一下逻辑备份的恢复。

mysqldump的恢复

mysqldump的恢复操作比较简单，因为备份的文件就是导出的sql语句，一般只需要执行这个文件就可以了，可以通过以下的方法：

mysql -uroot -p <test-backup.sql

如果在导出时包含了创建和删除数据库的SQL语句，那必须确保删除架构时，架构目录下没有其他与数据库相关的文件，否则会报错。
通过mysqldump可以恢复数据库，但是经常发生一个问题：mysldump可以导出存储过程、导出触发器、导出事件、导出数据，但是却不能导出视图。因此，如果用户的数据库中还使用了视图，那么在用mysqldump备份完数据库后还需要导出视图的定义，或者备份视图定义的frm文件，并在恢复时进行导入，这样才能保证mysqldump数据库的完全恢复。

LOAD DATA INFILE恢复

如果通过mysqldump-tab，或者SELECT…INTO OUTFILE导出的数据需要恢复，这时可以通过命令LOAD DATA INFILE来进行恢复。具体语法：

 load data infile '/home/msql/a.txt' into table a;

当然要对服务器文件使用LOAD DATA INFILE，必须拥有文件的权限。

mysqlimport

mysqlimport是Mysql数据库提供的一个命令行程序，从本质上来说，是LOAD DATA INFILE的命令接口，而且大多数的选项和LOAD DATA INFILE语法相同。语法格式：

mysqlimport [options] db_name textfile1 textfile2...;

和LOAD DATA INFILE不同的是，mysqlimport命令可以用来导入多张表。并且通过–user-thread参数并发地导入不同的文件。这里的并发是指并发导入多个文件，而不是指mysqlimport可以并发的导入一个文件。示例：

mysqlimport --user-thread=2 test /home/mysql/t.txt /home/mysql/s.txt 

二进制日志备份与恢复

二进制日志非常关键，用户可以通过它完成point-in-time的恢复工作。mysql数据库的replication童谣需要二进制日志。
对于InnoDB存储引擎只简单启用二进制日志是不够的，还需要启用一些其他的参数来保证最为安全和正确的记录二进制日志，推荐的二进制日志如下：

log-bin=mysql-bin
sync_binlog=1
innodb_support_xa=1

在备份二进制日志文件前，可以通过FLUSH LOGS命令来生成一个新的二进制文件，然后备份之前的二进制日志。
要恢复二进制日志也是非常的简单，通过mysqlbinlog即可。语法：

mysqlbinlog [options] log_file...

常见示例如下：

• 恢复binlog.0000001

mysqlbinlog binlog.0000001 | mysql -uroot -p test;

• 恢复多个二进制日志文件

mysqlbinlog binlog.[0-10]* | mysql -u root -p test;

• 从二进制日志文件的某个偏移量进行恢复

mysqlbinlog --start-position=107856 binlog.0000001 | mysql -uroot -p test;

几个重要的可选参数：

• –start-position=value：从指定的偏移量开始恢复
• –stop-position=value：恢复到指定的偏移量为止
• –start-datetime=value：可以用来指定二进制日志的某个时间点来进行恢复
• –stop-datetime=value可以用来指定恢复到二进制日志的某个时间点为止

热备与恢复

ibbackup是InnoDB存储引擎官方提供的热备工具，可以同时备份MyISAM存储引擎和InnoDB存储引擎表。对于InnoDB存储引擎其备份工作原理如下：

1. 记录备份开始时，InnoDB存储引擎重做日志文件检查点的LSN
2. 复制共享表空间问价以及独立表空间文件
3. 记录复制完表空间文件后，InnoDB存储引擎重做日志文件检查点的LSN
4. 复制在备份时产生的重做日志

对于事务型的数据库，热备的原理大致和上述都相同。可以发现，在热备期间不会对数据库本身有任何影响，所做的操作这时候复制数据库文件，因此任何对数据库的操作都是允许的，不会阻塞任何操作。
ibbackup的优点：

• 在线备份，不阻塞任何SQL语句
• 备份性能好，备份的是指是复制数据库文件和重做日志文件
• 支持压缩备份，通过选项，可以支持不同级别的压缩
• 跨平台支持，ibbackup可以运行在Linux、windows以及主流的UNIX系统平台上

ibbackup的恢复：

• 恢复表空间文件
• 应用重做日志文件

ibbackup是一种高性能的热备方式，也是InnoDB存储引擎的首选方式。可惜的是它是收费的。不过好消息是Percona公司给我们带来了开源、免费XtraBackup热备工具，它实现了ibbackup的所有功能，并且扩展支持了真正的增量备份功能。因此，推荐使用XtraBackup工具完成热备的工作。

需要注意的是，使用XtraBackup备份工具需要我们使用Mysql5.0以上版本。下载地址：https://launchpad.net/percona-xtrabackup。XtraBackup语法：
xtrabackup --backup | --prepare [options]

xtrabackup实现完全备份

全量备份的执行过程：

后期补充

xtrabackup实现增量备份

我们知道，Mysql数据库本身提供的工具并不支持真正的增量备份，更准确地说，二进制日志的恢复应该是point-in-time的恢复而不是增量备份。而xtrabackup工具支持对于InnoDB存储引擎的增量备份，其工作原理如下：

• 首先完成一个全备，并记录下此时检查点的LSN
• 在进行增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN，如果是，则备份该页，同时记录当前检查点的LSN

增量备份的执行过程：

后期补充

快照备份与恢复

Mysql数据库本身并不支持快照功能，因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的前提是将所有数据库文件放在同一个文件分区中，然后对该分区进行快照操作。支持快照功能的文件系统和设备包括FreeBSD的UFS文件系统、Solaris的ZFS文件系统，Linux的逻辑管理器LVM等。
LVM是linux系统下对磁盘分区进行管理的一种机制。LVM使用了写时复制（Copy-on0write）技术来创建快照。当创建一个快照时，进复制原始卷中数据的元数据，并不会有数据的物理操作，因此快照的创建过程是非常快的。当快照创建完成，原始卷上有写操作时，快照会跟踪原始卷块的改变，将要改变的数据在改变之前复制到快照预留的空间里，因此这个原理的实现叫做写时复制。而对于快照的读取操作，如果读取的数据块是创建快照后没有修改过的，那么会将读操作直接重定向到原始卷上，如果要读取的是已经修改过的块，则将读取保存在快照中该块在原始卷上改变之前的数据。因此，采用写时复制机制保证了读取快照时得到额数据与快照创建时一致。

创建快照

命令lvcreate可以用来创建一个快照，–permission r表示创建的快照是只读的：

lvcreate --size 100G --snapshot --permission r -n datasnapshot /dev/rep/repdata

其中datasnapshot 为逻辑卷数据快照名称。
在快照制作完成后可以用lvdisplay命令查看，输出中的COW-table size字段表示该快照最大的空间大小，Allocated to snapshot字段表示该快照目前空间的使用状况。
快照在最初创建时总是很小，当数据来源卷的数据不断被修改时，这些数据库才会放入快照空间，这是快照的大小才会慢慢增大。
用LVM快照备份InnoDB存储引擎表相当简单，只要把与InnoDB存储引擎相关的文件如共享表空间、独立表空间、重做日志文件等放在同一个逻辑卷中，然后对这个逻辑卷做快照备份即可。
在对InnoDB文件做快照时，数据库无须关闭，即可以进行在线备份。虽然此时数据库中可能还有任务需要王磁盘上写数据，但这不会妨碍备份的准确性。因为InnoDB是事务安全的引擎，在下次恢复时，数据库会自动检查表空间中页的状态，并决定是否应用重做日志，恢复就好像数据库被意外重启了。

快照+复制的备份架构

复制可以用作备份，但功能不仅限于备份，并且复制也绝对不能代替备份，其主要功能如下：

• 数据分布。由于Mysql数据库提供的复制并不需要很大的带宽要求，因此可以在不同的数据中心之间实现数据额复制。
• 读取的负载均衡。通过建立多个从服务器，可将读取平均地分布到这些从服务器中，并且减少了主服务器的压力。
• 数据库备份。复制对备份很有帮助，但是从服务器不是备份，不能完全代替备份。
• 高可用性和故障转移。通过复制建立的从服务器有助于故障转移，减少故障的停机时间和恢复时间。

那为什么复制不能代替备份呢？
原因是，假设一个引用采用了主从的复制架构，并且把从服务器作为备份。这时，一个小伙伴执行了误操作，如drop database（删除数据库）或drop table（删除表），这时从服务器也跟着运行了。这是就无法根据从服务器进行恢复了。
因此，一个比较好的方法是通过对从服务器上的数据库所在分区做快照，以此来避免误操作对复制造成影响。当发生主服务器上的误操作时，只需要将从服务器上的快照进行恢复，然后再根据二进制日志进行point-in-time的恢复即可。