读书笔记《MySQL技术内幕：InnoDB存储引擎》第8章 备份与恢复

第8章　备份与恢复

本章主要介绍对InnoDB存储引擎的备份，应该知道MySQL数据库提供的大多数工具（如mysqldump、ibbackup、replication）都能很好地完成备份的工作，当然也可以通过第三方的一些工具来完成，如xtrabacup、LVM快照备份等。DBA应该根据自己的业务要求，设计出损失最小、对于数据库影响最小的备份策略。

8.1　备份与恢复概述

根据备份的方法不同可以将备份分为：
❑Hot Backup（热备）
❑Cold Backup（冷备）
❑Warm Backup（温备）

Hot Backup是指数据库运行中直接备份，对正在运行的数据库操作没有任何的影响。这种方式在MySQL官方手册中称为Online Backup（在线备份）。

Cold Backup是指备份操作是在数据库停止的情况下，这种备份最为简单，一般只需要复制相关的数据库物理文件即可。这种方式在MySQL官方手册中称为Offline Backup（离线备份）。

Warm Backup备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性。

按照备份后文件的内容，备份又可以分为：
❑逻辑备份
❑裸文件备份

在MySQL数据库中，逻辑备份是指备份出的文件内容是可读的，一般是文本文件。内容一般是由一条条SQL语句，或者是表内实际数据组成。如mysqldump和SELECT*INTO OUTFILE的方法。
这类方法的好处是可以观察导出文件的内容，一般适用于数据库的升级、迁移等工作。
但其缺点是恢复所需要的时间往往较长。

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

若按照备份数据库的内容来分，备份又可以分为：
❑完全备份
❑增量备份
❑日志备份

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

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

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

对于MySQL数据库来说，官方没有提供真正的增量备份的方法，大部分是通过二进制日志完成增量备份的工作。这种备份较之真正的增量备份来说，效率还是很低的。假设有一个100GB的数据库，要通过二进制日志完成备份，可能同一个页需要执行多次的SQL语句完成重做的工作。但是对于真正的增量备份来说，只需要记录当前每页最后的检查点的LSN，如果大于之前全备时的LSN，则备份该页，否则不用备份，这大大加快了备份的速度和恢复的时间，同时这也是xtrabackup工具增量备份的原理。

此外还需要理解数据库备份的一致性，这种备份要求在备份的时候数据在这一时间点上是一致的。举例来说，在一个网络游戏中有一个玩家购买了道具，这个事务的过程是：先扣除相应的金钱，然后向其装备表中插入道具，确保扣费和得到道具是互相一致的。否则，在恢复时，可能出现金钱被扣除了而装备丢失的问题。

对于InnoDB存储引擎来说，因为其支持MVCC功能，因此实现一致的备份比较简单。用户可以先开启一个事务，然后导出一组相关的表，最后提交。当然用户的事务隔离级别必须设置为REPEATABLE READ，这样的做法就可以给出一个完美的一致性备份。然而这个方法的前提是需要用户正确地设计应用程序。对于上述的购买道具的过程，不可以分为两个事务来完成，如一个完成扣费，一个完成道具的购买。若备份这时发生在这两者之间，则由于逻辑设计的问题，导致备份出的数据依然不是一致的。

对于mysqldump备份工具来说，可以通过添加–single-transaction选项获得InnoDB存储引擎的一致性备份，原理和之前所说的相同。需要了解的是，这时的备份是在一个执行时间很长的事务中完成的。另外，对于InnoDB存储引擎的备份，务必加上–single-transaction的选项（虽然是mysqldump的一个可选选项，但是我找不出任何不加的理由）。

同时我建议每个公司要根据自己的备份策略编写一个备份的应用程序，这个程序可以方便地设置备份的方法及监控备份的结果，并且通过第三方接口实时地通知DBA，这样才能真正地做到24×7的备份监控。久游网开发过一套DAO（Database Admin Online）系统，这套系统完全由DBA开发完成，整个平台用Python语言编写，Web操作界面采用Django。通过这个系统DBA可以方便地对几百台MySQL数据库服务器进行备份，同时查看备份完成后备份文件的状态。之后DBA又对其进行了扩展，不仅可以完成备份的工作，也可以实时监控数据库的状态、系统的状态和硬件的状态，当发生问题时，通过飞信接口在第一时间以短信的方式告知DBA。

最后，任何时候都需要做好远程异地备份，也就是容灾的防范。只是同一机房的两台服务器的备份是远远不够的。我曾经遇到的情况是，公司在2008年的汶川地震中发生一个机房可能被淹的的情况，这时远程异地备份显得就至关重要了。

8.2　冷备

对于InnoDB存储引擎的冷备非常简单，只需要备份MySQL数据库的frm文件，共享表空间文件，独立表空间文件（*.ibd），重做日志文件。另外建议定期备份MySQL数据库的配置文件my.cnf，这样有利于恢复的操作。

通常DBA会写一个脚本来进行冷备的操作，DBA可能还会对备份完的数据库进行打包和压缩，这都并不是难事。关键在于不要遗漏原本需要备份的物理文件，如共享表空间和重做日志文件，少了这些文件可能数据库都无法启动。另外一种经常发生的情况是由于磁盘空间已满而导致的备份失败，DBA可能习惯性地认为运行脚本的备份是没有问题的，少了检验的机制。

正如前面所说的，在同一台机器上对数据库进行冷备是远远不够的，至少还需要将本地产生的备份存放到一台远程的服务器中，确保不会因为本地数据库的宕机而影响备份文件的使用。

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

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

8.3　逻辑备份

8.3.1　mysqldump

mysqldump备份工具最初由Igor Romanenko编写完成，通常用来完成转存（dump）数据库的备份及不同数据库之间的移植，如从MySQL低版本数据库升级到MySQL高版本数据库，又或者从MySQL数据库移植到Oracle、Microsoft SQL Server数据库等。

使用: 略,专题,工作原理

8.3.2　SELECT…INTO OUTFILE

SELECT…INTO语句也是一种逻辑备份的方法，更准确地说是导出一张表中的数据。
用法: 略,专题,工作原理

8.5　热备

8.5.1　ibbackup

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

对于InnoDB存储引擎表其备份工作原理如下：
1）记录备份开始时，InnoDB存储引擎重做日志文件检查点的LSN。
2）复制共享表空间文件以及独立表空间文件。
3）记录复制完表空间文件后，InnoDB存储引擎重做日志文件检查点的LSN。
4）复制在备份时产生的重做日志。

对于事务的数据库，如Microsoft SQL Server数据库和Oracle数据库，热备的原理大致和上述相同。可以发现，在备份期间不会对数据库本身有任何影响，所做的操作只是复制数据库文件，因此任何对数据库的操作都是允许的，不会阻塞任何操作。

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

ibbackup对InnoDB存储引擎表的恢复步骤为：
❑恢复表空间文件。
❑应用重做日志文件。

ibbackup提供了一种高性能的热备方式，是InnoDB存储引擎备份的首选方式。不过它是收费软件，并非免费的软件。好在开源的魅力就在于社区的力量，Percona公司给用户带来了开源、免费的XtraBackup热备工具，它实现所有ibbackup的功能，并且扩展支持了真正的增量备份功能。因此，更好的选择是使用XtraBackup来完成热备的工作。

8.5.2　XtraBackup

XtraBackup备份工具是由Percona公司开发的开源热备工具。支持MySQL5.0以上的版本。

XtraBackup在GPL v2开源下发布，官网地址是：https://launchpad.net/percona-xtrabackup。

xtrabackup命令的使用方法如下：
略

8.6　快照备份

MySQL数据库本身并不支持快照功能，因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。
备份的前提是将所有数据库文件放在同一文件分区中，然后对该分区进行快照操作。支持快照功能的文件系统和设备包括FreeBSD的UFS文件系统，Solaris的ZFS文件系统，GNU/Linux的逻辑管理器（Logical Volume Manager，LVM）等。

使用: 略,专题

8.7　复制

8.7.1　复制的工作原理

复制（replication）是MySQL数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。

总体来说，replication的工作原理分为以下3个步骤：
1）主服务器（master）把数据更改记录到二进制日志（binlog）中。
2）从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。
3）从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

复制的工作原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是，复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。复制的工作原理如图8-4所示。

图　8-4　MySQL数据库的复制工作原理

从服务器有2个线程，一个是I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL线程，复制执行中继日志。MySQL4.0版本之前，从服务器只有1个线程，既负责读取二进制日志，又负责执行二进制日志中的SQL语句。这种方式不符合高性能的要求，目前已淘汰。因此如果查看一个从服务器的状态，应该可以看到类似如下内容：

mysql＞SHOW FULL PROCESSLIST\G;
***************************1.row***************************
Id:1
User:system user
Host:
db:NULL
Command:Connect
Time:6501
State:Waiting for master to send event
Info:NULL
***************************2.row***************************
Id:2
User:system user
Host:
db:NULL
Command:Connect
Time:0
State:Has read all relay log;waiting for the slave I/O thread to update it
Info:NULL
***************************3.row***************************
Id:206
User:root
Host:localhost
db:NULL
Command:Query
Time:0
State:NULL
Info:SHOW FULL PROCESSLIST
3 rows in set(0.00 sec)

可以看到ID为1的线程就是I/O线程，目前的状态是等待主服务器发送二进制日志。ID为2的线程是SQL线程，负责读取中继日志并执行。目前的状态是已读取所有的中继日志，等待中继日志被I/O线程更新。

在replication的主服务器上应该可以看到一个线程负责发送二进制日志，类似内容如下：

mysql＞SHOW FULL PROCESSLIST\G;
……
***************************65.row***************************
Id:26541
User:rep
Host:192.168.190.98:39549
db:NULL
Command:Binlog Dump
Time:6857
State:Has sent all binlog to slave;waiting for binlog to be updated
Info:NULL
……

之前已经说过MySQL的复制是异步实时的，并非完全的主从同步。若用户要想得知当前的延迟，可以通过命令SHOW SLAVE STATUS和SHOW MASTER STATUS得知，如：

mysql＞SHOW SLAVE STATUS\G;
***************************1.row***************************
Slave_IO_State:Waiting for master to send event
Master_Host:192.168.190.10
Master_User:rep
Master_Port:3306
Connect_Retry:60
Master_Log_File:mysql-bin.000007
Read_Master_Log_Pos:555176471
Relay_Log_File:gamedb-relay-bin.000048
Relay_Log_Pos:224355889
Relay_Master_Log_File:mysql-bin.000007
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:mysql.%,DBA.%
Last_Errno:0
Last_Error:
Skip_Counter:0
Exec_Master_Log_Pos:555176471
Relay_Log_Space:224356045
Until_Condition:None
Until_Log_File:
Until_Log_Pos:0
Master_SSL_Allowed:No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master:0
Master_SSL_Verify_Server_Cert:No
Last_IO_Errno:0
Last_IO_Error:
Last_SQL_Errno:0
Last_SQL_Error:
1 row in set(0.00 sec)

通过SHOW SLAVE STATUS命令可以观察当前复制的运行状态，一些主要的变量如表8-1所示。
命令SHOW MASTER STATUS可以用来查看主服务器中二进制日志的状态，如：

mysql＞SHOW MASTER STATUS\G;
***************************1.row***************************
File:mysql-bin.000007
Position:606181078
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set(0.01 sec)

可以看到，当前二进制日志记录了偏移量606181078的位置，该值减去这一时间点时从服务器上的Read_Master_Log_Pos，就可以得知I/O线程的延时。

对于一个优秀的MySQL数据库复制的监控，用户不应该仅仅监控从服务器上I/O线程和SQL线程运行得是否正常，同时也应该监控从服务器和主服务器之间的延迟，确保从服务器上的数据库总是尽可能地接近于主服务器上数据库的状态。

8.7.2　快照+复制的备份架构

复制可以用来作为备份，但功能不仅限于备份，其主要功能如下：
❑数据分布。由于MySQL数据库提供的复制并不需要很大的带宽要求，因此可以在不同的数据中心之间实现数据的复制。
❑读取的负载平衡。通过建立多个从服务器，可将读取平均地分布到这些从服务器中，并且减少了主服务器的压力。一般通过DNS的Round-Robin和Linux的LVS功能都可以实现负载平衡。
❑数据库备份。复制对备份很有帮助，但是从服务器不是备份，不能完全代替备份。
❑高可用性和故障转移。通过复制建立的从服务器有助于故障转移，减少故障的停机时间和恢复时间。

可见，复制的设计不是简简单单用来备份的，并且只是用复制来进行备份是远远不够的。假设当前应用采用了主从的复制架构，从服务器作为备份。这时，一个初级DBA执行了误操作，如DROP DATABASE或DROP TABLE，这时从服务器也跟着运行了。这时用户怎样从服务器进行恢复呢？

因此，一个比较好的方法是通过对从服务器上的数据库所在分区做快照，以此来避免误操作对复制造成影响。当发生主服务器上的误操作时，只需要将从服务器上的快照进行恢复，然后再根据二进制日志进行point-in-time的恢复即可。因此快照+复制的备份架构如图8-5所示。

图　8-5　快照+复制的备份架构
还有一些其他的方法来调整复制，比如采用延时复制，即间歇性地开启从服务器上的同步，保证大约一小时的延时。这的确也是一个方法，只是数据库在高峰和非高峰期间每小时产生的二进制日志量是不同的，用户很难精准地控制。另外，这种方法也不能完全起到对误操作的防范作用。

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

[mysqld]
read-only

在启用read-only选项后，如果操作从服务器的用户没有SUPER权限，则对从服务器进行任何的修改操作会抛出一个错误，如：

mysql＞INSERT INTO z SELECT 2;
ERROR 1290(HY000):The MySQL server is running with the--read-only option so it cannot execute this statement

8.8　小结

本章中介绍了不同的备份类型，并介绍了MySQL数据库常用的一些备份方式。同时主要介绍了对于InnoDB存储引擎表的备份。不管是mysqldump还是xtrabackup工具，都可以对InnoDB存储引擎表进行很好的在线热备工作。最后，介绍了复制，通过快照和复制技术的结合，可以保证用户得到一个异步实时的在线MySQL备份解决方案。