二进制日志备份与恢复

二进制日志非常关键，我们可以通过它来完成point-in-time的恢复工作。MySQL数据库的复制同样需要二进制日志。默认情况下并不启用二进制日志，要使用二进制日志，首先必须启用它，在配置文件中进行如下设置：

[mysqld]

log-bin

对于InnoDB存储引擎只是简单启用二进制日志是不够的，还需要启用一些其他参数来保证安全和正确地记录二进制日志，推荐的二进制日志的服务器配置应该是：

[mysqld]

log-bin

sync_binlog=1

innodb_support_xa=1

备份二进制日志文件前，可以通过FLUSH LOGS命令来生成一个新的二进制日志文件，然后备份之前的二进制日志。

要恢复二进制日志也非常简单，通过mysqlbinlog即可，mysqlbinlog的使用方法如下：

mysqlbinlog [options] log_file……

例如，要还原binlog.0000001，可以使用如下命令：

mysqlbinlog binlog.0000001 | mysql -uroot -p123456 test

如果需要恢复多个二进制日志文件，最正确的做法应该是同时恢复多个二进制日志文件，而不是一个一个地恢复，如：

mysqlbinlog binlog.[0-10]* | mysql -uroot -p123456 test

也可以先通过mysqlbinlog命令导出到一个文件，然后再通过SOURCE命令来导入。

这种做法的好处是，可以对导出的文件进行修改后再导入，如：

mysqlbinlog binlog.000001>/tmp/statements.sql

mysqlbinlog binlog.000002>>/tmp/statements.sql

mysql-u root-p123456 -e "source/tmp/statements.sql"

--start-position和--stop-position选项可以用来指定从二进制日志的某个偏移量来进行恢复，这样可以跳过某些不正确的语句，如：

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

--start-datetime和--stop-datetime选项可以用来指定从二进制日志的某个时间点来进行恢复，用法和--start-position和--stop-position选项基本相同。

快照备份

MySQL数据库本身并不支持快照功能，因此快照备份是指通过文件系统支持的快照功能对数据库进行备份。备份的前提是将所有数据库文件放在同一文件分区中，然后对该分区执行快照工作。支持快照功能的文件系统和设备包括FreeBSD的UFS文件系统，Solaris的ZFS文件系统，GNU/Linux的逻辑卷管理器（Logical Volume Manager、LVM）等。这里以LVM为例进行介绍，UFS和ZFS的快照实现大致和LVM相似。

LVM是Linux系统下对磁盘分区进行管理的一种机制。LVM在硬盘和分区之上建立一个逻辑层来提高磁盘分区管理的灵活性。管理员可以通过LVM系统轻松管理磁盘分区，例如，将若干个磁盘分区连接为一个整块的卷组（Volume Group），形成一个存储池。管理员可以在卷组上随意创建逻辑卷（Logical Volume），并进一步在逻辑卷上创建文件系统。管理员通过LVM可以方便地调整卷组的大小，并且可以对磁盘存储按照组的方式进行命名、管理和分配。简单地说，我们可以通过LVM由物理块设备（如硬盘等）创建物理卷，由一个或多个物理卷创建卷组，最后从卷组中创建任意数量的逻辑卷（不超过卷组大小），如下图所示。

下图显示了由多块磁盘组成的逻辑卷LV0。

通过vgdisplay命令查看系统中有哪些卷组，如：

vgdisplay

---Volume group---

VG Name rep

System ID

Format lvm2

Metadata Areas 1

Metadata Sequence No 1873

VG Access read/write

VG Status resizable

MAX LV 0

Cur LV 3

Open LV 1

Max PV 0

Cur PV 1

Act PV 1

VG Size 260. 77 GB

PE Size 4. 00 MB

Total PE 66758

Alloc PE/Size 66560/260. 00 GB

Free PE/Size 198/792. 00 MB

VG UUID MQJiye-j4NN-LbZG-F3CQ-UdTU-fo9D-RRfXD5

vgdisplay命令的输出结果显示当前系统有一个rep的卷组，大小为260.77GB，该卷组访问权限是read/write等。

可以用命令lvdisplay来查看当前系统中有哪些逻辑卷：

lvdisplay

---Logical volume---

LV Name/dev/rep/repdata

VG Name rep

LV UUID 7tOlDt-seKZ-ChpY-QMXC-WaFD-zXAl-MRbofK

LV Write Access read/write

LV snapshot status source of

/dev/rep/dho_datasnapshot100805143507[active]

/dev/rep/dho_datasnapshot100805163504[active]

LV Status available

#open 1

LV Size 100.00 GB

Current LE 25600

Segments 1

Allocation inherit

Read ahead sectors auto

-currently set to 256

Block device 253：0

可以看到，一共有3个逻辑卷，都属于卷组rep，每个逻辑卷的大小都是100GB。/dev/rep/repdata这个逻辑卷有两个只读快照，并且当前都是激活状态的。

LVM使用了写时复制（Copy-on-write）技术来创建快照。当创建一个快照时，仅拷贝原始卷里数据的元数据，并不会有数据的物理操作，因此快照的创建过程是非常快的。当快照创建完成后，原始卷上有写操作时，快照会跟踪原始卷块的改变，将要改变的数据在改变之前拷贝到快照预留的空间里，因此这个原理的实现叫做写时复制。而对于快照的读取操作，如果读取的数据块是创建快照后没有修改过的，那么会将读操作直接重定向到原始卷上；如果要读取的是已经修改过的块，则将读取保存在快照中的原始数据。因此，采用写时复制机制保证了读取快照时得到的数据与快照创建时的数据是一致的。

显示了LVM的快照读取，可见B区块被修改了，因此历史数据放入了快照区域。读取快照数据时，A、C、D块还是从原有卷中读取，而B块就需要从快照读取了。

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

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

Logical volume "datasnapshot" created

在快照制作完成后，可以用lvdisplay命令来查看，输出中的COW-table size字段表示该快照最大空间的大小，Allocated to snapshot字段表示该快照当前空间的使用状况：

lvdisplay

……

---Logical volume---

LV Name/dev/rep/dho_datasnapshot100805163504

VG Name rep

LV UUID 3B9NP1-qWVG-pfJY-Bdgm-DIdD-dUMu-s2L6qJ

LV Write Access read only

LV snapshot status active destination for/dev/rep/repdata

LV Status available

#open 0

LV Size 100.00 GB

Current LE 25600

COW-table size 80.00 GB

COW-table LE 20480

Allocated to snapshot 0.04%

Snapshot chunk size 4.00 KB

Segments 1

Allocation inherit

Read ahead sectors auto

-currently set to 256

Block device 253：4

可以看到，当前快照只使用了0.04%的空间。快照在最初创建时总是很小的，只有当源数据卷的数据不断被修改，这些数据库才会放入快照空间，这时快照的大小才会慢慢增大。

用LVM快照备份InnoDB存储引擎表相当简单，只要把与InnoDB存储引擎相关的文件如共享表空间、独立表空间、重做日志文件等放在同一个逻辑卷中，然后对这个逻辑卷进行快照备份即可。

在对InnoDB存储引擎文件做快照时，数据库无须关闭，可以进行在线备份。虽然此时数据库中可能还有任务需要往磁盘上写数据，但这不会妨碍备份的正常进行。因为InnoDB存储引擎是事务安全的引擎，在下次恢复时，数据库会自动检查表空间中页的状态，并决定是否应用重做日志，恢复就好像数据库被意外重启了。

启用LVM快照需要规划以下几个方面。

1. 快照空间大小的划分。如果源逻辑卷的大小是100G，快照最大可能生产的空间是100GB，但是如果每4个小时生成一份快照，则无须划分100G的空间，只要判断在4小时内最多产生的快照空间即可。
2. 快照启用后磁盘的性能。启用快照后，磁盘的性能必定会有所下降，因为第一次修改一个数据块时会将该块复制到快照空间。虽然之后的修改不会再复制到快照空间，但磁盘的开销显然还是有所增大。需要判断这些性能上的损失数据库是否可以承担。
3. 不能把快照备份当作完全备份来使用，因为当数据库所在服务器发生硬件故障时，如RAID卡损坏，这时快照备份是不能进行恢复的。快照备份更偏向于对误操作的防范，可以将数据库迅速地恢复到快照产生的时间点，然后再根据二进制日志执行point-in-time的恢复。通常把LVM的备份放在replication的Slave服务器上。

复制

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

总体来说，复制的工作原理分为以下三个步骤：

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

（2）从服务器把主服务器的二进制日志拷贝到自己的中继日志（Relay Log）中。

（3）从服务器重做中继日志中的时间，把更新应用到自己的数据库上。

工作原理并不复杂，其实就是完全备份和二进制日志备份的还原。不同的是，这个二进制日志的还原操作基本上是实时进行的。注意，不是完全的实时，而是异步的实时。其中存在主从服务器之间的执行延时，如果主服务器的压力很大，则这个延时可能更长。其工作原理如图所示。

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

在MySQL 4.0版本之前，从服务器只有1个线程，既负责读取二进制日志，又负责执行二进制日志中的SQL语句。这种方式不符合高性能的要求，已被淘汰。

因此，如果查看一个从服务器的状态，应该可以看到类似如下的内容。

show full processlist\G

***************************1.row***************************Id：1User：system userHost：db：NULLCommand：ConnectTime：6501State：Waiting for master to send eventInfo：NULL***************************2.row***************************Id：2User：system userHost：db：NULLCommand：ConnectTime：0State：Has read all relay log；waiting for the slave I/O thread to update itInfo：NULL

可以看到，ID为1的线程就是I/O线程，目前的状态是等待主服务器发送二进制日志。

ID为2的线程是SQL线程，负责执行读取中继日志并执行。目前的状态是已读取所有的中继日志，等待中继日志被I/O线程更新。

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

show full processlist\G

***************************65.row***************************Id：26541User：repHost：192.168.190.98：39549db：NULLCommand：Binlog DumpTime：6857State：Has sent all binlog to slave；waiting for binlog to be updatedInfo：NULL

MySQL的复制是异步同步的，并非完全的主从同步。查看当前的延迟，可以使用命令SHOW SLAVE STATUS和SHOW MASTER STATUS，如下所示：

show slave status\G

***************************1.row***************************Slave_IO_State：Waiting for master to send eventMaster_Host：192.168.190.10Master_User：repMaster_Port：3306Connect_Retry：60Master_Log_File：mysql-bin.000007Read_Master_Log_Pos：555176471Relay_Log_File：gamedb-relay-bin.000048Relay_Log_Pos：224355889Relay_Master_Log_File：mysql-bin.000007Slave_IO_Running：YesSlave_SQL_Running：YesReplicate_Do_DB：Replicate_Ignore_DB：Replicate_Do_Table：Replicate_Ignore_Table：Replicate_Wild_Do_Table：Replicate_Wild_Ignore_Table：mysql.%，DBA.%Last_Errno：0Last_Error：Skip_Counter：0Exec_Master_Log_Pos：555176471Relay_Log_Space：224356045Until_Condition：NoneUntil_Log_File：Until_Log_Pos：0Master_SSL_Allowed：NoMaster_SSL_CA_File：Master_SSL_CA_Path：Master_SSL_Cert：Master_SSL_Cipher：Master_SSL_Key：Seconds_Behind_Master：0Master_SSL_Verify_Server_Cert：NoLast_IO_Errno：0Last_IO_Error：Last_SQL_Errno：0Last_SQL_Error：

通过SHOW SLAVE STATUS命令可以观察当前复制的运行状态，主要它的参数有：

Slave_IO_State。显示当前IO线程的状态，上述状态显示的是等待主服务器发送二进制日志。

Master_Log_File。显示当前同步的主服务器的二进制日志，上述状态显示当前同步的是主服务器的mysql-bin.000007。

Read_Master_Log_Pos。显示当前同步到主服务器上二进制日志的偏移量位置，单位是字节。上述示例显示了当前同步到mysql-bin.000007的555176471偏移量位置，即已经同步了mysql-bin.000007这个二进制日志中529M（555176471/1024/1024）的内容。

Relay_Master_Log_File。当前中继日志同步的二进制日志。

Relay_Log_File。显示当前写入的中继日志。 

Relay_Log_Pos。显示当前执行到中继日志的偏移量位置。

Slave_IO_Running。从服务器中IO线程的运行状态，YES表示运行正常。

Slave_SQL_Running。从服务器中SQL线程的运行状态，YES表示运行正常。

Exec_Master_Log_Pos。表示同步到主服务器的二进制日志偏移量的位置。（Read_Master_Log_Pos-Exec_Master_Log_Pos）可以表示当前SQL线程运行的延时，单位是字节。上述示例显示当前主从服务器是完全同步的。

命令SHOW MASTER STATUS可以用来查看主服务器中二进制日志的状态，如：

show master status\G

***************************1.row***************************File：mysql-bin.000007Position：606181078Binlog_Do_DB：Binlog_Ignore_DB：

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

一个好的数据库复制监控不仅要监控从服务器上的I/O线程和SQL线程是否运行正常，而且还应该监控从服务器和主服务器之间的延迟，确保从服务器上的数据库状态总是非常接近主服务器上数据库的状态。

快照+复制的备份架构

复制可以用来作为备份，但其功能不仅限于备份，其主要功能如下：

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

可见，复制的设计目的不是简简单单用来备份的，并且只用复制来进行备份是远远不够的。

假设当前应用采用了主从式的复制架构，从服务器用来作为备份，一个不太有经验的DBA执行了误操作，如DROP DATABASE或者DROP TABLE，这时从服务器也跟着运行了，那这时如何从服务器进行恢复呢？一种比较好的方法是通过对从服务器上的数据库所在的分区做快照，以此来避免复制对误操作的处理能力。当主服务器上发生误操作时，只需要恢复从服务器上的快照，然后再根据二进制日志执行point-in-time的恢复即可。因此，快照+复制的备份架构如下图所示：

 

延时复制，即间歇性地开启从服务器上的同步功能，保证大约一小时的延迟。这的确也是一个方法，只是数据库在高峰和非高峰期间每小时产生的二进制日志量是不同的，很难精准地控制。另外，这种方法也不能完全防止误操作的发生。

从服务器上还可以启用read-only选项：

[mysqld]

read-only

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

insert into z select 2;

ERROR 1290（HY000）：The MySQL server is running with the--read-only option so

it cannot execute this statement