非DBA人员从零到一，MySQL InnoDB数据库调优之路(四)-数据备份与迁移

上一篇【非DBA人员从零到一，MySQL InnoDB数据库调优之路(三)-分区表与分库分表】

我认为分表在数据库的调优中是一种加法，通过拆分单表数据到多个表中，减少单表的压力，从而达到调优的效果，那么这一篇博文会通过对数据库进行减法来达到调优的效果。

1 MySQL的备份，根据不同的类型可以划分出3个类型

1.1 根据备份方法不同

1.1.1 热备

在数据库正常运行下进行备份。备份期间，数据库读写均可正常进行。

• ibbackup

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

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

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

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

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

不过ibbackup是收费软件

• XtraBackup

它实现所有ibbackup的功能，并且扩展支持了真正的增量备份功能。因此，更好的选择是使用XtraBackup来完成热备的工作。支持MySQL5.0以上的版本

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

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

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

1.1.2 冷备

在备份期间，应用的读写操作不可进行。

对于InnoDB存储引擎而言，冷备只需要备份MySQL数据库的frm文件，共享表空间文件，独立表空间ibd文件，重做日志文件。

1.1.2.1 mysqldump

用于转存数据库的备份及不同数据库之间的移植。

1.1.2.2 SELECT...INTO OUTFILE

SELECT...INTO只能导出一张表数据。

冷备的优点是：

备份简单，只要复制相关文件即可。

备份文件易于在不同操作系统，不同MySQL版本上进行恢复。

恢复相当简单，只需要把文件恢复到指定位置即可。

恢复速度快，不需要执行任何SQL语句，也不需要重建索引。

冷备的缺点是：

InnoDB存储引擎冷备的文件通常比逻辑文件大很多，因为表空间中存放着很多其他的数据，如undo段，插入缓冲等信息。

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

1.1.3 温备

数据库可用性弱于热备，备份期间，数据库只能进行读操作，不能进行写操作。

1.2 根据备份后文件内容的不同

1.2.1 逻辑备份

逻辑备份是指备份出来的文件内容是可读的，一般是文本文件。内容一般是一条条sql语句。

在冷备中有两种常见方式进行逻辑备份，其中mysqldump备份工具进行备份是一种逻辑备份方式。另外SELECT...INTO,也是一种逻辑备份方式，通过SQL语句定向导出一张表内容。

1.2.2 裸文件备份

裸文件备份是指复制数据库的物理文件，即可以是在数据库运行中的复制，如热备中的(ibbackup、xtrabackup这类工具)，也可以是在数据库停止运行时直接的数据文件复制。

1.3 根据备份的数据库内容不同

1.3.1 完全备份

完全备份是指对数据库进行一次完整的备份。

1.3.2 增量备份

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

1.3.3 日志备份

日志备份主要是指对MySQL数据库二进制日志备份，其中二进制日志即binlog，记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。

通过对一个完全备份进行二进制日志重做来完成数据库的恢复工作，主从复制就是一个典型的日志备份。

1.3.3.1 复制的工作原理

复制（replication）是MySQL数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication的工作原理分为以下3个步骤：

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

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

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

复制的工作原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。

 从服务器有2个线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL线程，复制执行中继日志。

2 数据复制与数据迁移

回到本文的开头，这篇博文的讨论是如何通过对数据库做减法来实现数据库调优，这里不得不讨论数据复制与数据迁移的区别

数据库迁移：将数据从源数据库迁移到目标数据库，最终在迁移完成后会关闭源数据库系统。可以迁移整个数据集，也可以迁移部分数据集。

数据库复制：将数据从源数据库持续转移到目标数据库，最终不会关闭源数据库。数据库复制（有时称为“数据库流式传输”）是一个持续的过程。

使用数据库复制时，可以将数据从源数据库持续转移到目标数据库，且不会删除源数据库。这概念通常用于我们在进行高可用实现时，如1.3.3提到的主从数据库同步。

2.1 部分迁移与完全迁移

而在数据库迁移中，数据将从源数据库移动到目标数据库。在数据完全迁移后，删除源数据库并将客户端访问重定向到目标数据库。在我工作中会遇到两种数据迁移:

完全迁移在笔者工作前两份工作中非常常见，企业内部在新旧系统切换的时候，常常需要对SQLSERVER或者Oracle到Mysql的数据迁移。

而部分迁移就是这篇博文的主题，例如企业内部档案、财税的科目余额表，他们一年都会很多数据，但是他们一般只需要1年或者3年即可，所以在实际系统中，我们会以年为单位对旧数据进行迁移，保证线上数据库一直处于平稳的数据量，从而达到高速查询。

3 后感

一般来说对老数据的备份迁移只让数据库当中存储的都是活跃数据，如果备份后活跃数据还是百万及千万级，那么还是要像我第一篇及第二篇博文那些，在数据表设计当初字段设计得当，然后通过建立有效的索引从而达到较好的查询速度。

参考：《MySQL技术内幕：InnoDB存储引擎（第2版）》

数据库备份之热备，冷备，温备

数据库迁移：概念和原则