MySQL技术内幕InnoDB存储引擎学习笔记(第八章)

                       第八章 备份与恢复

一、实验环境

宿主机系统：windows7

虚拟机：OracleVMVirtualBox

Linux:ubuntukylin-14.04.1-amd64.iso

jdk:1.7.0_101

mysql:5.7.12

书上的mysql版本：5.6.6

二、备份分类

根据备份方法不同可分为：

hot backup（热备）：在线备份，数据库运行中直接备份，对正在运行的数据库无影响。

cold backup(冷备)：离线备份，数据库停止的状态下进行备份，只需要复制相关物理文件即可。

需要备份的文件为：frm文件、共享表空间文件，独立表空间文件（*.ibd），重做日志文件，my.cnf

冷备优点：备份简单，易于在不同操作系统，不同mysql版本上进行恢复。恢复简单，速度快，不需要执行语句，不需要重建索引。

冷备缺点：文件大。不总是可以轻易的跨平台。操作系统，mysql的版本，文件大小写敏感和浮点数格式都会成为问题。

warm backup(温备)：数据库运行中进行备份，对当前数据库的操作有所影响。如加一个全局读锁以保证数据的一致性。

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

逻辑备份：备份出来的文件内容是可读的，一般是文本文件。内容由一条条sql语句或者是表内实际数据组成，如mysqldump和select * into outfile的方法。优点：可以观察导出文件内容，适用于数据库的升级、迁移等工作。缺点：恢复时间较长。

裸文件备份：复制数据库的物理文件，可以使在数据库运行中的复制，如（ibbackup、xtrabackup这类工具），也可以是在数据库停止运行时直接的数据文件的复制。恢复时间短。

按照备份数据库的内容，可分为：

完全备份：对数据库进行完整备份

增量备份：在上次完全备份的基础上，对更改的数据进行备份。

日志备份：对mysql数据库二进制日志的备份，通过对一个完全备份进行二进制日志的重做来完成数据库的point-in-time恢复工作。replication的原理就是异步实时将二进制日志重做传送并应用到从数据库（slave/standby）。

mysql官方未提供增量备份的方法，大部分通过二进制日志完成增量备份工作。

数据库备份一致性：

1.innodb支持MVCC，用户可先开启一个事务，然后到处一组相关的表，最后提交。用户事务的隔离级别需要设置为REPEATABLE READ.

2.mysqldump工具：通过添加--single-transaction选项获得innoDB存储引擎的一致性备份。这时的备份是在一个很长的事务中完成的。

容灾防范：远程异地备份。

三、各种备份方式介绍。

1.冷备（略）

2.逻辑备份

mysqldump（工具）

注：mysqldump不能导出视图。

备份所有数据库：mysqldump --all-databases >dump.sql

备份指定数据库：mysqldump --databases db1 db2 db3 >dump.sql

备份架构test:mysqldump --single-transaction test>test_backup.sql

查看参数：mysqldump --help

参数：--single-transaction备份一致性，只对innodb有效。一致性读不隔离DDL操作。

参数：--lock-tables(-l) 在备份中依次锁住每个架构下的所有的表。一般用于myisam引擎。不能和single-transaction同时使用。

参数：--lock-all-tables(-x)对所有架构中的所有表上锁。

参数：--add-drop-database:需要和--all-databases或者-databases选项一起使用。

参数：--master-data【=value】：通过该参数产生的备份转存文件主要用来建立一个replication。当value=1时，转存文件中记录change master 语句，当value=2时，change master被写出sql注释。在默认情况下value的值为空。

参数：--events(-E):备份事件调度器

参数：--routines(-R):备份存储过程和函数

参数：--triggers:备份触发器

参数：--hex-blob:将BINARY、VARBINARY、BLOG、BIT列类型备份为十六进制的格式。

参数：--tab=path(-T path):产生TAB分割的数据文件。对于每张表，mysqldump创建一个包含CREATE TABLE语句的table_name.sql文件，和包含数据的tb1_name.txt文件。

参数：--where=''导出指定条件的数据。

select...intooutfile

导出a表文件：select * into outfile'/home/mysql/a.txt' from a;  

恢复

1.mysql -uroot -p <test_backup.sql

2.source /home/mysql/test_backup.sql

3若通过mysqldump -tab或通过select  into outfile导出，恢复时用load  data infile。

4.mysqlimport --use-threads=2   test   /home/mysql/t.txt /home/mysql/s.txt

查看mysql数据库线程列表：show fullprocesslist;

3.二进制日志备份与恢复

启用二进制日志：log-bin=mysql-bin

sync_binlog=1

innodb_support_xa=1

在备份二进制文件前，可以通过flush logs来生成一个新的二进制日志文件。

恢复一个：mysqlbinlog binlog. 0000001 |mysql -uroot -p test

恢复多个：mysqlbinlog binlog.[0-10]*  |mysql -uroot -p test

4.热备

ibbackup（收费）

官方提供热备工具，可以同时备份MyISAM存储引擎和innodb存储引擎表。

原理：

1.记录备份开始时，innodb存储引擎重做日志文件检查点的LSN.

2.复制共享表空间文件以及独立表空间文件。

3.记录复制完表空间文件后，innodb存储引擎重做日志文件检查点的LSN.

4.复制在备份时产生的重做日志。

优点：

1.在线备份，不阻塞任何的SQL语句。

2.备份性能好，备份的实质是复制数据库文件和重做日志文件。

3.支持压缩备份，通过选项，可以支持不同级别的压缩。

4.跨平台支持，可以运行在linux，windows等主流平台上。

恢复步骤：

1.恢复表空间文件。重做日志文件。

2.应用重做日志文件。

xtrabackup（开源）

官网地址：https://launchpad.net/percona-xtrabackup

xtrabackup--backup | --prepare

完全备份：./ xtrabackup--backup

增量备份：

原理：

1.首先完成一个全备，并记录下此时检查点的LSN.

2.在进行增量备份时，比较表空间中每个页的LSN是否大于上次备份时的LSN,如果是，则备份该页，同时记录当前检查点的LSN.

5.快照备份

通过文件系统支持的快照功能对数据库进行备份。

把innodDB存储引擎相关的文件如共享表空间、独立表空间、重做日志文件等当道同一个逻辑卷中，然后对这个逻辑卷进行快照备份即可。做快照时，数据库无需关闭。

6.复制（异步实时）

工作原理：完全备份+二进制日志备份的还原

1.主服务器（master）把数据更改记录到二进制日志中。

2.从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中

3.从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

show slave status

show master status

7.快照+复制

1.数据分布，对宽带要求低，可以在不同的数据中心之间实现数据的复制。

2.读取的负载平衡。通过建立多个从服务器，可以将读取平均的分布到这些从服务器中，并且减少了主服务器的压力。通过DNS的Round-Robin和Linux的LVS都可以实现。

3.数据库备份。

4.高可用性和故障转移。减少故障的停机时间和恢复时间。

将从服务器上的数据库所在分区做快照，一次来避免误操作对复制造成的影响。当主服务器发生误操作，只需要将从服务器上的快照进行恢复，然后根据二进制日志进行point-in-time的恢复即可。

建议从服务器上启用read-only选项，这样能保证从服务器上的数据仅与主服务器进行同步，避免其他线程修改数据。