【读书笔记】MySQL存储引擎_第七天

一、概述

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

• Hot Backup(热备)
• Cold Backup(冷备)
• Warm Backup(温备)

Cold Backup是指备份操作是在数据库停止的情况下，这种备份最为简单，一般只需要复制相关的数据库物理文件即可。

Warm Backup备份同样是在数据库运行中运行的。

按照备份后文件的内容，备份可以分为：

• 逻辑备份
• 裸文件备份

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

若按照备份数据库的内容来分，备份又可以分为：

• 完全备份
• 增量备份
• 日志备份

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

MySQL数据库复制的原理就是异步实时地将二进制日志重做传送并应用到从数据库。

对于真正的增量备份来说，只需要记录当前每页最后的检查点的LSN，如果大于之前全备时的LSN，则备份该页，否则不用备份

二、冷备

innodb存储引擎是按照表空间来进行存储，表空间就是一个存放数据库所有数据的空间。如果使用共享表空间，就会创建ibdata1文件，用来储存所有数据表的数据，如果使用独立表空间，会创建一个文件夹，用来存储每个表的数据。

innodb_file_per_table参数用来决定是否使用独立表空间，on表示使用独立表空间
±-----------------------------±-----------------------+
| Variable_name | Value |
±-----------------------------±-----------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
±-----------------------------±-----------------------+
innodb_data_file_path表示共享表空间的每个文件的大小为12M，如果需要扩展会自动生成ibdata2，ibdata3……

InnoDB存储引擎冷备非常简单，只需要备份MySQL数据库的frm文件，共享表空间文件，独立表空间文件，重做日志文件。

冷备的优点是：

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

冷备的缺点：

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

三、逻辑备份

记住一些常用命令就行了
mysqldump的语法如下：

mysqldump [arguments] > file_name

如果想要备份所有的数据库，可以使用–all-database选项：

mysqldump --all-databases > dump.sql

如果想要备份指定的数据库，可以使用–database选项：

mysqldump --databases db1 db2 db3 > dump.sql

如果想要对test这个架构进行备份，可以使用如下语句：

mysqldump --single-transaction test > backup.sql

备份出来的 backup.sql是文本文件，通过文本编辑器可以查看内容。备份出的文件内容就是比表结构和数据，所有这些都是用SQL语句方式表示。文件开始和结束的注释部分是用来设置MySQL数据库的各项参数，一般用来使还原工作更有效和准确地执行。

mysqldump的参数选项有很多，可以通过msyqldump --help命令来查看所有的参数，有些参数有缩写形式，如–lock-tables的缩写形式-l。

比较重要的参数：

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

--lock-tables(-l)：在备份中，依次锁住每个架构下的所有表。一般用于MyISAM存储引擎，当备份时只能读数据库进行读取操作，可以保证一致性。–single-transaction与–lock-tables是互斥的，不能同时使用。如果要备份的数据库两种存储引擎都有，这时候只能选择–lock-tables，因此只能保证每个架构下表结构的一致性，而不能保证所有架构下表的一致性。

--lock-all-tables(-x):在备份过程中，对所有机构的所有表上锁。可以避免上个参数不能同事锁住所有表的问题。

--add-drop-database:默认情况下，导出的文本文件中不会有CREATE DATABASE，除非指定了这个参数后，在CREATE DATABASE前先运行DROP DATABASE。该参数要和-all-database或者–database选项一起使用。

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

–master-data会自动忽略–lock-tables选项。如果没有使用–single-transation选项，则会自动使用–lock-all-tables选项。

--events(-E)：备份实践调度器。

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

--triggers：备份接触器。

--hex-blob：将BINARY、VARBINARY、BLOG和BIT列类型备份为十六进制的格式。mysqldump导出的文件一般是文本文件，但是如果导出的数据中有上述这些类型，在文本文件模式下可能会有些字符不可见，若添加–hex-blob选项，结果会以十六进制的方式显示。

--tab=path(-T path):产生TAB分割的数据文件。对于每张表，mysqldump创建一个包含CREATE TABLE语句的table_name.sql文件，和包含数据的tbl_name.txt文件。可以使用–fields-terminated-by=…,–field-enclosed-by=…,–fields-optionally-enclosed-by=…,–fields-escaped-by…,–line-terminated-by=…来改变默认的分割符、换行符等。该参数和SELECT…INTO OUTFILE的方式来导出一张表，但是通过mysqldump一样可以完成工作，而且可以一次完成多张表的导出，并且实现导出数据的一致性。

--where='where_condition'(-w 'where_condition')：导出给定条件的数据。

逻辑备份的恢复

shell>mysql -uroot -p < /home/mysql/backup.sql

逻辑备份sql语句文件也可以通过SOURCE命令来执行导入

mysql>source /home/mysql/backup.sql

四、二进制日志备份与恢复

二进制日志非常关键，用户可以通过它完成point-in-time时间点的恢复工作。MySQL数据库的replication复制同样需要二进制文件。

推荐的二进制日志的服务器配置应该是：

[mysqld]
log-bin = mysql-bin
sync_binlog = 1
innodb_support_xa = 1

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

shell>mysqlbinlog [options] log_file……

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

mysqlbinlog binlog.0000001 | mysql-uroot -p test

批量恢复：

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

五、热备

对于InnoDB存储引擎表其备份工作原理如下：

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

恢复工具ibbackup的优点如下：

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

ibbackup对InnoDB存储引擎表的恢复步骤为：

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

六、快照备份

MySQL本身不支持快照功能，因此快照备份是指文件系统支持的快照功能对数据库进行备份。备份的前提是将所有文件库文件放在同一文件分区中，然后对文件分区进行快照操作。

LVM是LINUX系统下对磁盘分区进行管理的一种机制。LVM在硬盘和分区上建立一个逻辑层，来提高磁盘分区管理的灵活性。

LVM使用了写时复制技术来创建快照。当创建一个快照时，仅复制原始卷中数据的元数据，并不会有数据的物理操作，因此快照的创建过程非常快。

七、复制

7.1 复制

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

1. 主服务器（master）把数据更改记录到二进制日志（binlog）中。
2. 从服务器（slave）把主服务器的二进制日志复制到自己的中继日志（relay log）中。
3. 从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

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

从服务器由两个线程，一个IO线程，负责读取主服务器的二进制日志，并将其保存为中继日志；另一个是SQL线程，复制执行中继日志。MySQL4.0版本之前，从服务器只有一个线程，即负责都去二进制日志，又负责执行日志中的SQL语句。这种方式不符合高性能要求。目前被淘汰。

之前已经说过MySQL的复制是异步实时的，并非完全的主从同步，若用户要想得知当前的延迟，可以通过命令 SHOW SLAVE STATUS和 SHOW MASTER STATUS得知。因为slave的还原操作是单线程的，所以当master负载很大时，slave可能延迟增加。

7.2 快照 + 复制的备份架构

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

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

一个初级人员不小心进行了DROP DATABASE或者DROP TABLE，此时从库也跟着运行了。这时候咋办？——这就需要对从服务器数据库所在分区进行快照备份，以免误操作对数据造成的影响。

参考

• 《MySQL技术内幕——InnoDB存储引擎》
• https://www.cnblogs.com/jinli1771/p/13159163.html