Mysql技术内幕（四）--Mysql事务和备份

Mysql技术内幕系列文章

Mysql技术内幕系列文章

一.事务

首先回顾一下事务的相关知识点：

数据库系统引入事务的目的：
**事务会把数据库从一种一致状态转换为另一种一致状态。**在数据库提交工作的时候，可以确保要么所有修改已经保存了，要么所有修改都不保存。

InnoDB存储引擎中的事务符合ACID特性

• 原子性：事务是不可分割的工作单位。
• 一致性：事务会把数据库从一种一致状态转换为另一种一致状态。
• 隔离性：要求每个读写事务的对象和其他事务的操作对象能够相互分离，事务提交之前对其他事务不可见。
• 持久性：事务一旦提交，其结果就是永久性的。

事务的分类：

• 扁平事务：

最简单也是使用最频繁的一种，所有操作都处于同一个层次。
由Begin Work开始，Commit Work或者Rollback Work结束，其间的操作都是原子性的，要么都执行要么都回滚。

• 带有保存点的扁平事务：允许事务执行过程中回滚到同一个事务中较早的一个状态。

意思是，一个事务的执行过程中出现的错误并不会导致所有的操作都无效，放弃整个事务并不符合要求，开销太大。
那么就用保存点（Savepoint）来通知系统应该记住事务当前的状态，以便之后发生错误时，事务可以回到保存点当时的状态。

• 链事务：保存点模式的一种变种。

提交一个事务的时候，释放不需要的数据对象，将必要的处理上下文隐式地传给下一个要开始的事务。

• 嵌套事务：是一个层次结构框架。

由一个顶层事务控制着各个层次的事务，顶层事务之下嵌套的事务被称为子事务，其控制每一个局部的变换。

• 分布式事务：通常是在一个分布式环境下运行的扁平事务。

事务的实现：

1. 事务的原子性是通过undo log来实现，也就是所谓的回滚操作。 undo log记录了数据被修改之前的信息以及新增、删除的信息。undo log就是通过生成操作相反的sql语句来实现。
2. 事务的持久性：
   1.redolog在提交commit前会写一次数据，顺序存储。
   2.InnoDB的二次写以及自带的buffer pool。
3. 事务的隔离性则通过4种隔离级别来实现。
4. 事务的一致性：其实现依赖于以上3个特性的实现、即回滚、恢复、隔离机制。

1.1 purge

其实本来在这一章需要将redo和undo的，我把他放到第一篇博客中去讲了。因此这里就不再叙述，来讲purge操作。Mysql技术内幕（一）–Mysql体系和InnoDB存储引擎

purge用于最终完成delete和update操作。因为InnoDB存储引擎支持MVCC，所以记录不能再事务提交的时候立即进行处理，因为这个时候其他的事务肯呢个正在读取这一行数据，因此需要InnoDB保存记录之前的版本。而是否删除该记录则是通过purge来进行判断。

为了节省存储空间，InnoDB存储引擎的undo log的设计为：

1. 一个页上允许多个事务的undo log存在，后面的事务产生的undo log总是在最后。
2. 用一个history列表，根据事务提交的顺序，将undo log进行链接。
3. purge的时候，先从history list中寻找undo log，然后再从undo page中找undo log。

当InnoDB存储引擎压力非常大的时候，并不能高效的进行purge操作，那么history list的长度会变的越来越长，因此可以使用全局动态参数innodb_max_purge_lag来控制history list的长度，如果长度大于该参数的时候，会延缓DML的操作。

其算法为：

# delay单位是毫秒，并且其作用对象是一行数据，而不是一个DML操作。
# 例如，一个update操作需要更新5行数据，那么每行数据的操作都会被delay，因此总的延长时间为5*delay
delay=（（length（history list）-innodb_max_purge_lag）*10）-5；

1.2 group commit

为了提高磁盘的fsync（持久化到日志）效率，当前数据库都提供了group commit的功能，即一次fsync可以刷新确保多个多个事务日志被写入文件。 对于InnoDB存储引擎来说，事务提交的时候会进行两个阶段的操作：

1. 修改内存中事务对应的信息，并且将日志写入重做日志缓冲中。
2. 调用fsync确保日志都从重做日志缓冲中写入到磁盘。

步骤2是一个较慢的过程（因为需要存储引擎需要和磁盘打交道），因此如果通过group commit使用一次fsync刷新到磁盘，可以大大减少了磁盘的压力。Mysql在5.6之后，采用了BLGC的实现方式（Binary Log Group Commit），将事务提交的过程分为几个步骤来完成：

在Mysql数据库上层进行提交时首先按顺序将其放入一个队列中，队列中的第一个事务称为leader，其他事务乘坐follower，leader控制着follower的行为。BLGL的步骤分为三个阶段：

1. Flush阶段：将每个事务的二进制日志写入文件中。
2. Sync阶段：将内存中的二进制日志刷新到磁盘，若队列有多个事务，那么仅一次fsync操作就可以完成二进制日志的写入，这就是BLGC。
3. Commit阶段：leader根据顺序调用存储引擎层事务的提交。

1.3 事务控制语句

Mysql默认配置下，事务都是自动提交的（auto commit），即执行SQL语句后会马上执行commit操作。接下来看一看还有哪些有关事务的控制语句：

• Start | Begin Transaction：显式地开启一个事务。
• Commit：提交事务，并使得已对数据库做的所有修改成为永久性的。
• Rollback：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
• Savepoint identifier：Savepoint允许在事务中创建一个保存点，一个事务中可以有多个Savepoint。
• Release Savepoint identifier：删除一个事务的保存点。
• Rollback To [Savepoint] identifier：可以把事务回滚到保存点。
• Set Transaction：设置事务的隔离级别。

案例：
1.先创建表和添加数据：

CREATE TABLE tt(
	a INT,
	PRIMARY KEY(a)
)ENGINE=INNODB;
INSERT INTO tt SELECT 1;

此时的表中数据有：

2.开始事务和保存点测试：

BEGIN;
INSERT INTO tt SELECT 2;
SAVEPOINT t1;
INSERT INTO tt SELECT 3;
INSERT INTO tt SELECT 4;
INSERT INTO tt SELECT 5;
INSERT INTO tt SELECT 6;
SAVEPOINT t2;
SELECT * FROM tt;

此时结果：

3.回滚至保存点t1：

ROLLBACK TO SAVEPOINT t1;# 回滚至保存点t1
SELECT * FROM tt;

再次查询的结果为：

注意：回滚至保存点的时候，事务并不会真正的回滚，即没有结束，需要运行Rollback后，事务才会完整的回滚。

---

接下来再讲一下一些会隐式提交事务的SQL语句：

• DDL语句。

Alter Database | Event | Procedure | Table | View等
Create Database | Event | Index | Trigger | View | Table等
Drop Database | Event | Index | Trigger | View | Table等

• 用来隐式地修改Mysql架构的语句。

Create | Drop | Rename User 、Grant、 Revoke、Set Password。

• 管理语句。

Analyze Table、Cache Index、Check Table、Load Index Into Cache等

1.4 事务隔离级别

事务的隔离级别有4个：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

注意的点：

1. InnoDB存储引擎默认的隔离级别是REPEATABLE READ，并且在该隔离级别下，使用Next-Key Lock锁的算法，避免幻读的产生。
2. 隔离级别越低，事务请求的锁越少或者保持锁的时间越短。
3. SERIALIZABLE的事务隔离级别，InnoDB存储引擎会对每个Select语句后自动加上Lock In Share Mode。
4. 在READ COMMITTED的隔离级别下，除了唯一性的约束检查以及外键约束的检查需要gap lock，InnoDB存储引擎不会使用gap lock的锁算法。

查看当前会话的事务隔离级别：

SELECT @@tx_isolation;

查看全局的事务隔离级别：

SELECT @@global.tx_isolation;

二.备份与恢复

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

• Hot Backup（热备）：数据库运行时直接备份，对正在运行放的数据库操作没有任何的影响。
• Cold Backup（冷备）：在数据库停止的情况下备份，只需要复制相关的数据库物理文件即可。
• Warm Backup（温备）：运行时备份，但是会对当前数据库的操作有影响。

2.按照备份后文件的内容可以将备份划分为：

• 逻辑备份：指的是文件内容可读，一般是文本文件，内容为SQL语句或者实际表内数据。
• 裸文件备份：指的是复制数据库的物理文件，这种备份的恢复时间往往要短很多。

3.按照备份数据库的内容可以将备份划分为：

• 完全备份：指的是对数据库进行一个完整的备份。
• 增量备份：指的是在上次完全备份的基础上，对于更改的数据进行备份。
• 日志备份：主要是针对二进制日志的备份。

2.1 冷备和逻辑备份

对于InnoDB存储引擎的冷备非常简单，只需要备份Mysql数据库中的frm文件、共享表空间文件、独立表空间文件（*.ibd）、重做日志文件。
冷备的优点：

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

冷备的缺点：

• InnoDB存储引擎冷备的文件通常比逻辑文件大很多，因为表空间中存放着很多其他的数据。

---

逻辑备份：使用mysqldump
例子：想要对test数据库做一个备份：

mysqldump -uroot -p --single-transaction test > test_backup.sql     

结果：

文件的部分内容：

mysqldump的重要参数：

参数	含义
--single-transaction	在备份开始前，先执行Start Transaction命令，以此来获得备份的一致性（该参数只对InnoDB有效）
--lock-tables(-l)	在备份中，依次锁住每个架构下的所有表。
--lock-all-tables(-x)	在备份过程中，对所有架构中的表上锁。
--add-drop-database	在Create Database之前线运行Drop Database。
--master-data [=value]	通过该参数产生的备份转存文件主要用来建立一个replication，该参数会自动忽略 --lock-tables选项。
--events(-E)	备份时间调度器
--rountines（-R）	备份存储过程和函数
--triggers	备份触发器
--hex-blob	将BitNary、VarBinary、Blog、Bit列类型备份为十六进制的格式。因为mysqldump导出的文件一般是文本文件，而如果导出的数据包含上述类型，在文本文件模式下可能有些字符不可见，加上这个参数，可以以十六进制的方式显示。
--where='[条件语句]'	例如：mysqldump --where=‘b>2’ test a > a.sql意思是导出数据库test下表a中列b>2的数据

---

上面讲了如何使用mysqldump进行备份，那么来看看具体数据库中如何操作：
直接使用该文件即可。

mysql -uroot -p < test_backup.sql

2.2 热备

这里讲一下ibbackup，不过不会讲具体的操作方式（百度都有）。

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

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

其优点：

• 在线备份，不阻塞任何的SQL语句。
• 备份性能好，备份的实质是复制数据库文件和重做日志文件。
• 支持压缩备份。

而ibbackup对InnoDB存储引擎的恢复步骤大概为2步：

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

2.3 复制（主从架构）

复制（replication）是Mysql数据库提供的一种高可用高性能的解决方案。其工作原理分为3个步骤：

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

总的来说就是一个完全备份加上二进制日志备份的还原。

注意：复制的流程不是完全实时的同步进行的，而是异步实时。其工作原理图如下：

从服务器有2个线程：

• IO线程：负责读取主服务器的二进制日志，并把它保存为自己的中继日志。
• SQL线程：负责执行中继日志。

用户可以通过Show Slave Status来查看当前复制的运行状态，变量表如下：

与之对应的，则可以使用Show Master Status来查看主服务器中二进制日志的状态。

---

复制除了可以用来作为备份，还可以有其他的功能，如下：

• 数据分布。
• 读取的负载均衡。
• 数据库备份。
• 高可用性和故障转移。

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