MySQL复制

前言

在上一篇文章,我们整理总结MySQL的常用知识点,在这里,我们尝试学习MySQL复制的相关概念。

什么是MySQL的复制

MySQL的复制允许将主服务器的数据复制到一个或多个从服务器中。默认情况下,复制是异步的。从服务器不需要永久连接就可以从主服务器获取最新的数据。根据配置,可以在数据库中复制所有的数据库,选定数据库,甚至是选定的表。

为什么要实现MySQL复制

MySQL的复制有以下优点:

  1. 在多个从服务器中做负载均衡提升性能
  2. 数据安全性,可以在多个从服务器复制数据,而不会影响主服务器。
  3. 离线数据分析,可以在从服务器上做实时数据分析。
  4. 远程数据分发,为网站创建本地的副本。

MySQL复制方式

MySQL支持不同的复制方式。传统的方方式是基于从主二进制日志记录和位置进行同步。新的基于全局事务标示符GTIDs(Global Transaction Identifiers 是事务性的,因此不需要关注日志文件或位置,极大地简化了许多常见的复制任务。

同步 vs 半同步

MySQL的复制支持不同类型的同步。最初的同步是单向异步复制,也就是说一个服务器充当主服务器,一个或多个服务器充当从服务器。

异步复制的机制是主服务器将事件写入其二进制日志,但不知道从服务器是否或合适检索和处理这些事件。使用异步复制是,如果主服务器出现奔溃,它提交的事务可能不会传输到任何从服务器。因此,在这种情况下,把一个从服务器提升为主服务器,可能会缺少奔溃的主服务器丢失的部分事务。

半同步复制,可用做异步复制的升级版:

  1. 从服务器链接到主服务器时会显示是否具有半同步的能力。
  2. 如果主服务器启用了半同步复制,并且至少有一个半同步从服务器,那么在主服务器上执行事务提交的线程,会等待至少一个从服务器确认接受到事务的全部事件,或者直到超时发生。
  3. 只有将事件写入其中继日志并刷新到磁盘,从服务器才会确认收到事务的事件。
  4. 如果在没有任何从服务器确认事务的情况下发生超时,则主服务器将恢复为异步复制。当至少有一个版同步从服务器赶上时,主服务器将恢复版同步复制。
  5. 必须在主服务器和从服务器都启用版同步复制。如果在主服务器上禁用了版同步复制,或者在主服务器上启用了但没有在从服务器上启用半同步复制,则主服务器使用同步复制。

配置参数

server-id

指定服务器ID.当启用二进制日志记录时,需要为每一个服务器设置一个唯一的服务器ID,范围为1到232 − 1。唯一意味着每个ID必须与任何其他复制主或者从服务器使用的ID不同。

如果设置0 ,则意味着主服务器拒绝从服务器连接,从服务器会拒绝连接到主服务器。

binlog-do-db

设置需要记录二进制日志的数据库。多个数据库,需要设置多行记录,由于MySQL的数据库名称可以包含逗号,因此带有逗号分割列表,被认为是一个数据库。

binlog-ignore-db

设置不需要二进制日记记录的数据库。

日志

MySQL自定义较多的日志概念,这里尝试整理总结下。

binary log

二进制日志包含描述数据库更改,如表创建操作或表数据更改的事件。它包含可能进行更改的语句事件,还包含有关每个语句花费更新数据的时间信息。二进制日志有2个重要的目的:

  1. 对于复制,主复制服务器上的二进制日志提供要发送到从服务器的数据更改记录。
  2. 部分数据恢复操作需要使用二进制日志。

relay log

中继日志由从主服务器的二进制日志读取并由I/O线程写入的事件构成。中继日志中的事件作为SQL线程的一部分在从服务器上执行。

undo log

撤销日志,用于数据一致性读取,或者用于事务的回滚和撤销。存在与撤销表空间,保存由当前事务修改的数据副本的存储区域。如果另外一个事务需要作为一致性读取操作的一部分,则返回该区域检索到的未经修改的数据。

redo log

在服务器奔溃恢复期间使用的一种基于磁盘的数据结构,用于更正由不完全事务写入的数据。在意外之前没有完成的数据文件更新的修改将自动重放。

参考链接

  1. MySQL Replication
  2. MySQL Semisynchronous Replication
  3. Server System Variables
  4. The Binary Log

MySQL必知必会

什么是MySQL

MySQL是一个开源的关系型数据库管理系统,是非常流行的开源架构中LNMP的常用组件之一,MySQL几乎已经成为开源的标准数据库。在这篇文章,对MySQL的常用知识点进行整理。

MySQL架构

MySQL服务器架构,由以下几个组件构成,如客户端、连接池、查询缓存、分析器、优化器、存储引擎。整体步骤分为3步:

  1. 客户端发起请求->连接/线程处理->分析器/解析器
  2. 分析器/解析器->查询缓存->查询优化器
  3. 优化器从存储引擎查询/更新数据

整体流程见下图:

ACID

大多数数据库都会要求事务需要实现ACID特性,所谓的ACID就是:

  1. 原子性(atomicity):事务中的所有操作要么全部提交成功,要么全部失败回滚。
  2. 一致性(consistency):数据库总是从一个一致性状态转换到另一个一致性状态。
  3. 隔离性(isolation):一个事务所做的修改在提交之前对其它事务是不可见的。
  4. 持久性(durability):一旦事务提交,其所做的修改便会永久保存在数据库中。

隔离级别

InnoDB的默认隔离级别是REPEATABLE READ 可重读。

  1. READ UNCOMMITTED 可以读取未提交的事务
  2. READ COMMITED 只能读取已经提交的事务
  3. REPEATABLE READ 重复读取已提交的事务
  4. SERIALIZABLE 对事务进行加锁读
隔离级 脏读可能性 不可重复读可能性 幻读可能性 加锁读
READ UNCOMMITED
READ COMMITED
REPEATABLE READ
SERIALIZABLE

MVVC

InnoDB通过MVVC的并发控制解决了幻读问题。所谓的幻读就是,2个不同的线程,线程A执行查询操作时,得到结果集1,这时,线程B执行插入数据的操作并提交事务,线程A再次执行查询操作时,得到结果集2,会发现结果集2中出现新的插入的行,新增返回的行,就是所谓的幻影行。

通常数据的操作有3种,插入、更新、删除,删除内部与更新实现一致,特定位置标记为删除。通过在表的每一行添加3个字段记录相关回滚操作:DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID。

  1. DB_TRX_ID字段指示插入或更新该行的最后一个事务的事务标识符。
  2. DB_ROLL_PTR 滚动指针指向写入回滚段的撤消日志记录。如果更新了行,则撤消日志记录包含在更新行之前重建行内容所需的信息。
  3. DB_ROW_ID字段包含在插入新行时单独增加的行ID。

InnoDB支持很多种锁,例如共享锁、排它锁、意向锁、行锁、间隙锁、下一键锁、插入意向锁等。

SELECT ... FROM 默认无锁,在 SERIALIZABLE 隔离级别会加锁,使用唯一索引查询唯一的行会要求索引记录锁定。

SELECT ... FROM FOR UPDATE
SELECT ... FROM LOCK IN SHARE MODE

对于唯一搜索条件的唯一索引,锁定找到的索引记录。对于其他搜索条件和非唯一索引,InnoDB锁定扫描的索引范围。

SELECT ... FROM FOR UPDATE会阻塞其他会话的共享锁,如LOCK IN SHARE MODE。

UPDATE WHERE 设置一个搜索匹配的每一行记录上添加排它的下一键锁定。对于唯一搜索条件的唯一索引,锁定找到的索引记录。

当UPDATE修改聚集索引记录是,对次要的索引记录进行隐式锁定。

DELETE FROM WHERE设置一个搜索匹配的每一行记录上添加排它的下一键锁定。对于唯一搜索条件的唯一索引,锁定找到的索引记录。

INSERT 在插入的行上设置独占锁,是索引记录锁,不会组织其他会话在插入行之前插入间隙。

INSERT ... ON DUPLICATE KEY UPDATE 与INSERT一致,不同在于发生重复键错误时,在要更新的行上采用独占锁而不是共享锁。对重复的主键值采用独占索引记录锁定,对于重复的唯一键值,采用独占的下一键锁定。

REPLACE 与INSERT一致,如果有唯一键冲突,将在要替换的行上放置专用的下一键锁。

INSERT INTO T SELECT ... FROM S WHERE ... 在要插入到T的每一行上设置独占索引记录锁。

REPLACE INTO t SELECT ... FROM s WHERE ...` or `UPDATE t ... WHERE col IN (SELECT ... FROM s ...) InnoDB设置下一键锁在行上。

AUTO_INCREMENT InnoDB在与自动增量列关联的索引末尾设置独占锁。

InnoDB对定义外键约束的条件的INSERT,UPDATE,DELETE,都会设置一个共享记录锁。

LOCK TABLES 设置一个表锁,由在InnoDB层更高的MySQL层设置的。

死锁的产生

MySQL官方文章,讲解一个死锁的产生案例。

  1. 客户端A请求S lock START TRANSACTION;SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
  2. 这时客户端B 开启一个事务,删除这条数据。START TRANSACTION;DELETE FROM t WHERE i = 1; 删除操作要一个排它锁 X lock。
  3. 然后,客户端A也尝试删除这条数据。DELETE FROM t WHERE i = 1;
  4. 这时就发生了死锁。ERROR 1213 (40001): Deadlock found when trying to get lock;try restarting transaction

InnoDB自动检测事务死锁并回滚一个或者多个事务以打破死锁,默认会回滚较小的事务,其中事务大小取决于插入、更新或者删除的行数。

索引的实现

MySQL的InnoDB存储引擎采用的是B+Tree数据结构实现数据的存储。B+Tree通过降低高度,将节点的索引存储在内存里,非索引的节点数据存储在硬盘上,提升读取效率。在数据结构与算法之美里,也讲解了不同数据结构之间的对比。

查找条件/数据结构 散列表 平衡二叉查找树 跳表 B+Tree 备注
根据某个值查找数据
根据区间值来查找某些数据
二叉树
m叉树 m预先计算

常用优化方法

在数据量达到一定级别,如TB级别,单库单表的容量比较大,会导致查询更新操作较慢。这时候,就需要对数据库进行物理级别的优化。

分库分表

优先独立拆分不同业务的数据库和表,方便后续管理维护,也就是所谓的垂直拆分。

  1. 对不同业务分离不同的数据库,如订单库、用户库、产品库等。
  2. 对较大的表,将常用的字段分离成不同的小表,不常用的字段分离单独的表,提升查询效率。

根据业务特点,将数据库特定的算法分布到不同的库和表,这就是水平拆分。

  1. 在同一个库里,根据hash取摸、范围切分、按时间、区域到不同的表。
  2. 大表拆分为小表,避免性能瓶颈。

范围分表是根据用户ID范围分布,比如1-10000一个表。

hash取模,例如根据用户ID或者订单ID对128取模,平均分布到128个表中。

按时间分表,最简单的是每天一个表,每天数据都存储在不同的表,也可以将3-6个月前的数据分离到其他的库表,毕竟历史数据查询概率较小,当前存储查询访问较大的热数据即可,这也就是所谓的冷热数据分离。

查询优化标准

让查询尽可能快的方法,就是尽可能命中索引。假设有这样的索引key(last_name ,first_name,dob),那么MySQL可以非常高效工作的索引类型如下:

  1. 匹配全部的列 last_name = ‘Allen’ and first_name =’Cuba’ and Dob=’1960-01-01′
  2. 匹配最左前缀 last_name =’Allen’
  3. 匹配列前缀 last_name like “J%”
  4. 匹配范围值 last_name >= “Allen” and last_name <= “Barry”
  5. 精确匹配一部分并且匹配某个范围中的另一部分 last_name = “Allen” and first_name like “K%”
  6. 只访问索引的查询。也就是索引覆盖了全部查询字段的查询。

在阿里巴巴Java开发手册里,有一个SQL性能优化的目标推荐标准:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts最好。

那这几个目标到底是啥意思呢?所谓的range是对索引进行范围检索。那到底什么是对索引进行范围检索呢?从上面例子,可以看出来,所谓的范围检索,就是对索引进行大于某个值且小于某个值的条件查找。

什么是索引全扫描呢?explain的结果就是type=index的查询类型,针对索引全部记录进行查找呗。这个与全表扫描类似,一个对索引所有记录进行扫描,一个是对表所有记录进行扫描。

ref级别,就是指命中最左前缀、主键、唯一索引等多行的查询,在explain显示type=ref。

const级别,指最多有一个匹配行的查询,比如命中主键、唯一索引的查询。

高可用

什么是高可用(High Availability)?人们通常是这样定义的,很少或者几乎没有服务不可用,就是高可用。这其实从逆向来定义的,它不是什么。那到底什么是高可用?有没有一个相对准确的定义?业界常用的测量系统的高可用性,是故障发生到恢复的时间,如下表:

系统可用性% 宕机时间/年 宕机时间/月 宕机时间/周 宕机时间/天
90% (1个9) 36.5 天 72 小时 16.8 小时 2.4 小时
99% (2个9) 3.65 天 7.20 小时 1.68 小时 14.4 分
99.9% (3个9) 8.76 小时 43.8 分 10.1 分钟 1.44 分
99.99% (4个9) 52.56 分 4.38 分 1.01 分钟 8.66 秒
99.999% (5个9) 5.26 分 25.9 秒 6.05 秒 0.87 秒

那MySQL如何实现高可用性呢?较为常用的工具有MHA(Master High Availability Manager and tools for MySQL)。部署MHA后,通过VIP对外提供服务,MHA服务会定期检查master的存活状态,一旦Master发生故障,会尝试如下流程:

  1. 通过SSH保存master的二进制日志binlogs
  2. 选择最新的slave节点
  3. 同步最新slave节点与其他slave节点的差异,也就是应用差异的中继日志到其他slave。
  4. 应用从master保存的二进制日志binlogs
  5. 提升最新的slave为master
  6. 其他的slave连接slave尝试复制。

负载均衡

大多数互联网应用都遵循一个28原则,也就是大约80%的请求都是读请求,20%是写入的请求。常见的采用HAProxy提供两个VIP提供服务,一个VIP负责读请求,一个VIP负责写请求。MySQL集群可以为主从、主主模式。

故障转移/恢复/容灾

针对故障转移这类,按照范围可以分为数据中心内部和外部。

内部一般由于服务器故障或网络等原因导致服务不可用,需要针对服务做自动转移,服务切换。

外部的话,比如光缆挖断了,地震了这类不可控的因素导致服务不可用,必须提前准备容灾措施。

由于金融行业对数据的要求较高,比如阿里云的两地三中心架构,两地指同城和异地,三中心指生产中心、同城容灾中心、异地容灾中心,提供超高可用的服务架构。这类金融行业的架构,成本和收益是成正比的。笔者所在的公司,采用了同地两中心,分别在两个数据中心部署了相关的服务,随时可切换到不同的数据中心,然后对核心数据进行异地的同步备份,定期备份核心业务数据,对监管要求较高的业务,需要保存5年以上的时间。

总体来说,对于同城的数据中心实时同步数据,异地数据进行定期备份。

参考链接

  1. 关于高可用的系统
  2. 金融交易系统异地灾备方案
  3. 金融云存储灾备之两地三中心