数据库集群

主从复制集群

单机数据库的问题

• 容量有限
• 读写压力过大，QPS过大，此时数据库的CPU很容易达到100%
• 可用性低，宕机就全完

单机到集群

• 读写压力过大—通过多机集群—实行主从复制+读写分离
• 可用性—通过故障转移实现高可用—实行主从切换

主从复制

• 主从复制原理
  1.主库写bin log
  2.从库读主库bin log得到relay log
  3.从库通过relay log复现主库所有的DML
  
• 主从复制的演进
  1.2000年，MySQL 3.23.15版本引入了复制
  2.2002年，MySQL 4.0.2版本分离了IO与SQL线程，引入了relay log
  3.2010年，MySQL 5.5版本引入了半同步复制
  4.2016年，MySQL 5.7.17中引入Innodb Group Replication(MySQL Group Replication MGR )

传统主从复制

异步复制

• 主数据库不知道从数据库的同步情况，完全异步
  

半同步复制

• 需要引入插件，目前MySQL各个版本已经默认包含且关闭，需要手动开启
• 多个从数据库只要有一个回复了收到主库的bin log并转为从库自身的relay log的确认信号时，主数据库就会认为当前事务可以提交
• 当超过一定时间，所有从数据库都没有回应确认信号给主数据库时，半同步复制会退化为异步复制
  

组复制（MGR MySQL Group Replication MGR ）

• 不区分主从，每一个数据库节点都是对等的，每一次DML操作都会同时发给所有数据库
• 通过certify步骤（具体算法实现为Paxos分布式协议）判断每次的DML谁充当主数据库，其它充当从数据库
• 同样的也是通过certify步骤保证数据的一致性
• 组复制可以选择性的配置为单主模式（传统的主从模式），也可以配置为多主模式（现在的分布式模式）
  
• MGR的优点
  1.高一致性：基于分布式Paxos协议实现组复制，保证数据一致性
  2.高容错性：自动监测机制，只要不是大多节点都宕机就可以继续工作，内置防脑裂保护机制
  3.高扩展性：节点的增加与移除会自动更新组成员信息，新节点加入后，自动从其他书剑节点同步增量数据，知道数据一致
  4.高灵活性：提供单主和多主模式，单主模式下能够自动选主，多主模式下支持多节点写入

脑裂: MySQL集群出现数据库集群之间的失联，将一个数据库集群分成了多个小数据库集群，但同时这些数据库集群都没有与业务系统失联，导致多个小集群之间出现数据不一致

主从复制演示

准备两个MySQL服务实例

安装两个数据库，比如数据库A（MySQL_A）、数据库B（MySQL_B），一个设置为主数据库，一个设置为从数据库

修改主数据库MySQL_A的my.ini中的[mysqld]配置项下的配置如下

1. bind-address = 127.0.0.1
2. port = 3316
3. server_id = 1
4. datadir = C:/ProgramData/MySQL_A/MySQL Server 8.0\Data
5. #basedir = ./
6. socket = C:/tmp/mysql3316.sock
7. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
8. log_bin=mysql-bin
9. binlog-format=Row
10. //对于8.0及以上的版本MySQL数据库，主从配置时会涉及到权限验证，此时需要修改默认的验证插件，用于修改对密码的加解密方式
11. default_authentication_plugin=mysql_native_password

复制代码

修改从数据库MySQL_B的my.ini中的[mysqld]配置项下的配置如下

1. bind-address = 127.0.0.1
2. port = 3326
3. server_id = 2
4. datadir = C:/ProgramData/MySQL_B/MySQL Server 8.0\Data
5. #basedir = ./
6. socket = C:/tmp/mysql3326.sock
7. sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
8. log_bin=mysql-bin
9. binlog-format=Row
10. //对于8.0及以上的版本MySQL数据库，主从配置时会涉及到权限验证，此时需要修改默认的验证插件，用于修改对密码的加解密方式
11. default_authentication_plugin=mysql_native_password

复制代码

初始化和启动数据库

• 如果MySQL_A、MySQL_B均为空数据库
  1.需要在MySQL_A的客户端中执行mysqld --defaults --file=C:\ProgramData\MySQL_A\MySQL Server 8.0\my.ini --initialize-insecure 进行初始化
  2.需要在MySQL_B的客户端中执行mysqld --defaults --file=C:\ProgramData\MySQL_B\MySQL Server 8.0\my.ini --initialize-insecure 进行初始化
  分别启动主和从，在在两个数据库的命令行下分别直接执行mysqld或start mysqld命令即可。

配置主节点

mysql命令登录到主节点MySQL_A：mysql -uroot -P3316

1. ## 'repl'@'%' 表示：用户名为 repl，@后跟该用户的IP，'%'表示任意的客户端（任意IP）
2. ## 完整的意思是 创建一个用户名为repl的用户，该用户可以从任意客户端连接到本SQLServer，且密码固定为123456
3. mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
4. Query OK, 0 rows affected (0.11 sec)
5. ## GRANT 后跟 赋予的权限 ， ON 后跟被赋予权限的那些表的表名，*.*表示该数据库的所有表
6. ## 完整的意思是 赋予所有连接该SQLServer的客户端中，只要是用户名为repl的用户，都能获得作为从节点的权利
7. mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
8. Query OK, 0 rows affected (0.12 sec)
10. mysql> flush privileges;
11. Query OK, 0 rows affected (0.10 sec)
13. mysql> show master status;
14. +------------------+----------+--------------+------------------+-------------------+
15. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
16. +------------------+----------+--------------+------------------+-------------------+
17. | mysql-bin.000003 | 305 | | | |
18. +------------------+----------+--------------+------------------+-------------------+
19. 1 row in set (0.00 sec)

复制代码

创建数据库：create schema db;

配置从节点

mysql命令登录到从节点MySQL_B：mysql -uroot -P3326

1. CHANGE MASTER TO
2. MASTER_HOST = 'localhost',
3. MASTER_PORT = 3326,
4. MASTER_USER = 'repl',
5. MASTER_PASSWORD = '123456',
6. //mysql-bin.000003为从库从主库重写为relay log的主库中的binlog的文件名
7. MASTER_LOG_FILE = 'mysql-bin.000003',
8. //position=305,表示从库从主库的binlog的305位置开始重写
9. MASTER_LOG_POS = 305;
10. //MASTER_AUTO_POSITION = 1;

复制代码

创建数据库：create schema db;

验证操作

在主库MySQL_A执行：

1. mysql> use db
2. Database changed
3. mysql> create table t1(id int);
4. Query OK, 0 rows affected (0.17 sec)
6. mysql>
7. mysql>
8. mysql> insert into t1(id) values(1),(2);
9. Query OK, 2 rows affected (0.04 sec)

复制代码

在从库MySQL_B查看数据同步情况：

1. mysql> start slave;
2. mysql> use db;
3. Database changed
4. mysql>
5. mysql>
6. mysql> show tables;
7. +--------------+
8. | Tables_in_db |
9. +--------------+
10. | t1 |
11. +--------------+
12. 1 row in set (0.00 sec)
14. mysql>
15. mysql>
16. mysql> select * from t1;
17. +------+
18. | id |
19. +------+
20. | 1 |
21. | 2 |
22. +------+
23. 2 rows in set (0.00 sec)

复制代码

会用到的命令

• 可以通过show master status\G, show slave status\G 查看状态
• 可以通过stop slave;、start slave;来启动停止复制。
• 如果前一句SQL出现warning，可以再该句SQL后紧接着使用show warning;
• 查看mysql的用户验证方式：select * from mysql.user;
• 修改密码的加解密方式（一般出现在主从数据库场景下的权限验证失败的情况，此时需要在主库执行该命令）：alter user '账户名'@'%' IDENTIFIED with mysql_native_password by '密码';
• 对于主数据库上不想被从数据库复制的操作，可以在这些SQL指令操作前执行set SQL_LOG_BIN=0;关闭binlog，在执行完所有不想被复制的操作以后就可以最后执行set SQL_LOG_BIN=1;

其他

• GTID与复制

• 半同步复制

• 组复制

读写分离与高可用

• MySQL通过主从复制集群了两大能力
  1.读写分离
  2.高可用
• 读写分离：提升读的处理能力，将数据库的读取压力分摊到多个从库上，仅在主库上做DML操作
• 高可用：通过切换主库实现故障转移，提供failover能力，提升系统的容灾能力，再加上业务侧的连接池心跳充实、断线重连实现业务不间断，减低RTO（Recovery Time Objective)恢复时间、提前RPO（Recovery Point Object)恢复时间点

容灾：提升系统的容灾能力主要有两种手段：热备和冷备
热备：类似数据库集群的主从切换，备用者在灾难发生前已经有在使用，灾难发生后发生了角色和作用的转变
冷备：备用者在灾难发生前处于待机或待启动状态，当灾难发生后才正式启用

实现读写分离

动态切换数据源

1.基于Spring/Spring Boot配置多个数据源
2.根据service方法或DAO/Mapper注入不同的数据源
3.作为2的改进，基于AbstractRoutingDataSource和自定义注解实现自动切换数据源，在该种方式下支持配置多个从库且支持从库的读负载均衡
4.缺点在于对业务系统的侵入性很强
5.如果通过拦截器或过滤器直接在ORM层面实现对DML、DQL的数据源切换能降低侵入性，但是会产生“写完读”不一致的问题（因为主从同步的延迟导致）

写完读：更新完记录后，再查询该条被更新的记录

利用已有的支持多数据源切换的ORM框架

1.基于Spring/Spring Boot配置多个数据源
2.引入第三方框架ShardingSphere-jdbc的Master-Slave功能

• 优点
  1.对SQL解析和事务管理自动实现读写分离
  2.通过对于同一事务DML之后的DQL不切换数据库的方式解决了“写完读”不一致的问题
• 缺点
  1.对业务系统还是有入侵，比如增加数据源配置
  2.引入第三方ORM框架需要修改规则、SQL等

利用数据库中间件

1.为中间件配置数据源，并配置用于业务系统访问中间件的固定端口
2.通过使用中间件MyCat或ShardingSphere-Proxy的Master-slave功能，对于业务系统而言访问数据库其实就是访问中间件固定端口提供的虚拟数据库

• 优点
  1.仅需要部署一个中间件，在中间件中进行规则配置
  2.中间件模拟一个mysql服务器实现对业务系统无入侵，业务系统只需要修改数据源
• 缺点
  1.系统可用性降低
  2.系统复杂度变高

实现高可用

• 高可用常见的策略
  1.多个数据库实例不在一个主机/机架上
  2.跨机房和可用区部署数据库实例
  3.两地三中心容灾高可用方案
• 高可用的定义：高可用意味着业务系统更少的不可服务时间，一般用SLA/SLO衡量，比如二9的高可用就是说全年有1%的时间不可用、3.3个9的高可用就表示全年有99.95%的时间可用
• 高可用计算方法：几个9主要通过 -lg [1-（可用时间/全年时间）]得到

主从手动切换1.0

• 如果主节点挂掉，手动重新配置某个从节点为主节点并修改数据源配置后再重新部署应用
• 缺点：可能造成数据不一致、需要人工干预、对代码和配置有侵入性

主从手动切换2.0

1.使用DNS+KEEPALIVED+LVS+VIP
2.KEEPALIVED+LVS实现多个数据库实例的负载均衡+探活+请求路由
3.配置相应的VIP或DNS实现业务系统配置不变更，对业务系统无入侵修改

• 缺点
  1.通过修改VIP或DSN的配置实现手工处理主从切换，将空出来的VIP或DNS绑定到用来当新主库的原从数据库
  2.准备大量的VIP或DNS的配置和脚本定义用于快速修改VIP或DSN的配置

MHA(Master High Availability)

1.MHA(Master High Availability)目前是MySQL高可用方面成熟的的解决方案，同时也作为MySQL高可用环境下的故障切换和主从性能提升的高可用软件
2.MHA软件基于Perl语言开发，一般能在30s内实现主从切换，切换时需要通过SSH复制主节点的日志，然后对比从节点的rely log让被选为主节点的从节点恢复到前主节点宕机那一刻的转态

• 缺点
  1.需要配置SSH信息，因为从节点所在的主机需要从前主节点所在主机中读取日志
  2.使用MHA需要至少3台数据库主机
  

MGR(MySQL Group Replication)

1.基于MySQL 5.7及之后自带的组复制，在MySQL内部实现高可用，即如果主节点挂点，将自动选择某个从节点改为主节点
2.无需人工干预，基于组复制能保证数据一致性

• 缺点
  1.因为这是MySQL内部实现的，所以没有对外提供接口以知道主从的切换和状态，但是MySQL为这些数据库新增了转态表，外部获取主从状态需要读取数据库查询这些表
  2.外部需要配合使用中间层LVS 或 VIP配置，因为这些数据库逻辑上是同一个，但是物理上是不同的
  
• MGR其他适用场景：1.弹性复制、2.高可用分片（每个片都可以配置单独的主从集群）

MySQL Innodb Cluster

• 主要由三部分组成：MySQL Group Replication组复制、MySQL Router、Mysql Shell
  
  
  

Orchestrator

主从复制集群的问题

• 容量，主从结构的容量十分有限，因为无论是主还是从，其实都是一个数据库的容量
• 性能，由于关系型数据库大多采用 B+树类型的索引，在数据量超过阈值的情况下，索引深度的增加也将使得磁盘访问的 IO 次数增加，进而导致查询性能的下降，无法处理高并发访问请求
• 稳定性，业务系统本身是没有状态的，可以任意扩展，业务系统的状态主要通过存储在数据库中的数据表现，单一的数据节点，或者简单的主从架构数据库实例中的数据达到阈值以上，数据备份和恢复的时间成本都将随着数据量的大小而愈发不可控
• 无法执行 DDL，比如添加一列，或者增加索引，都会锁表直接影响线上业务，导致长时间的数据库无响应

数据库的扩展

• 数据库的扩展实际是对数据库的拆分
• 系统扩展大体分为三类
  
  1.X横向的扩展：典型代表为数据库集群的主从结构
  2.Y纵向的扩展：将系统按业务分类进行扩展，典型代表为垂直分库分表、分布式服务化与微服务
  3.Z水平方向的扩展：将系统按照数据特征进行扩展，典型代表为水平分库分表

垂直拆分

• 垂直拆分包括：
  1.垂直分库
  2.垂直分表

垂直分库

• 将一个数据库，拆分成多个提供不同业务数据处理能力的数据库
  例如：拆分所有订单和产品的数据，变成两个独立的库，这种方式对业务系统有极大的影响，因为数据结构本身发生了变化，SQL 和关联关系也必随之发生了改变。原来一个复杂 SQL 直接把一批订单和相关的产品都查了出来，现在这个 SQL 不能用了，得改写 SQL 和程序。先查询订单库数据，拿到这批订单对应的所有产品 id，再根据产品 id 集合去产品库查询所有的产品信息，最后再业务代码里进行组装

垂直分表

• 如果单表数据量过大，还可能需要对单表进行拆分
  例如：一个 200 列的订单主表，拆分成十几个子表：订单表、订单详情表、订单收件信息表、订单支付表、订单产品快照表等等。这个对业务系统的影响有时候可能会大到跟新做一个系统差不多。对于一个高并发的线上生产系统进行改造，就像是给心脑血管做手术，动的愈多，越核心，出现大故障的风险越高。所以，我们一般情况下，尽量少用这种办法

垂直拆分的优缺点

• 垂直拆分的优点：
  1.单库（单表）变小，便于管理和维护
  2.对性能和容量有提升作用
  3.改造后，系统和数据复杂度降低
  4.可以作为微服务改造的基础
• 垂直拆分的缺点：
  1.库、表变多，管理变复杂
  2.对业务系统有较强的侵入性
  3.改造过程复杂，容易出故障
  4.拆分到一定程度就无法继续拆分

水平拆分

• 与垂直拆分的区别
  
  
  
  
  -常见的水平分库分表方式
  1.水平拆分（按主键分库分表）：水平拆分就是直接对数据进行分片，有分库和分表两个具体方式，但是都只是降低单个节点数据量，但不改变数据本身的结构。这样对业务系统本身的代码逻辑来说，就不需要做特别大的改动，甚至可以基于一些中间件做到透明
  2.水平拆分（按时间分库分表）：很多时候，我们的数据是有时间属性的，所以自然可以按照时间维度来拆分。比如当前数据表和历史数据表，甚至按季度，按月，按天来划分不同的表。这样我们按照时间维度来查询数据时，就可以直接定位到当前的这个子表
  3.强制按条件指定分库分表：比如配置好某些用户的数据进入单独的库表，其他数据默认处理
  4.自定义方式分库分表：指定某些条件的数据进入到某些库或表
• 不建议分表，只建议分库的原因
  1.一些中间件，也只支持分库，不能分表
  2.表增多，导致管理困难，比如数据迁移、备份、DDL等
  3.一般需要分库分表都是因为这个单个数据库节点的网络IO、磁盘IO压力，单纯的分表，只是让单表的查询性能提升，不能解决网络IO、磁盘IO问题

水平拆分的优缺点

• 水平拆分的优点
  1、解决容量问题
  2、比垂直拆分对系统影响小
  3、部分提升性能和稳定性
• 水平拆分的缺点
  1、集群规模大，管理复杂
  2、复杂 SQL 支持问题（业务侵入性、性能）
  3、数据迁移问题
  4、一致性问题

水平拆分示例

1. 下载最新的 ShardingSphere-Proxy
2. 解压并修改如图所示的目录中的两个配置文件
   
3. 修改server.yml配置文件

1. authentication:
2. users:
3. root:
4. password: root
5. # sharding:
6. # password: sharding
7. # authorizedSchemas: sharding_db
9. props:
10. max-connections-size-per-query: 1
11. acceptor-size: 16 # The default value is available processors count * 2.
12. executor-size: 16 # Infinite by default.
13. proxy-frontend-flush-threshold: 128 # The default value is 128.
14. # LOCAL: Proxy will run with LOCAL transaction.
15. # XA: Proxy will run with XA transaction.
16. # BASE: Proxy will run with B.A.S.E transaction.
17. proxy-transaction-type: LOCAL
18. proxy-opentracing-enabled: false
19. proxy-hint-enabled: false
20. query-with-cipher-column: false
21. sql-show: true
22. check-table-metadata-enabled: false

复制代码

4. 修改 config-sharding.yaml 配置文件

2. schemaName: sharding_db #中间件对外展示的唯一数据库名
4. dataSourceCommon:
5. username: root
6. password:
7. connectionTimeoutMilliseconds: 30000
8. idleTimeoutMilliseconds: 60000
9. maxLifetimeMilliseconds: 1800000
10. maxPoolSize: 5
11. minPoolSize: 1
12. maintenanceIntervalMilliseconds: 30000
14. #实际有两个数据源分别包含 demo_ds_0 数据库 和 demo_ds_1 数据库，每个库分别包含两张表
15. dataSources:
16. ds_0:
17. url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
18. ds_1:
19. url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
21. rules:
22. - !SHARDING
23. tables:
24. t_order:
25. #这里表示总共有 ds_0.t_order_0、ds_0.t_order_1、ds_1.t_order_0、ds_1.t_order_1四张表，分别在两个库上
26. actualDataNodes: ds_${0..1}.t_order_${0..1}
27. tableStrategy:
28. standard:
29. #以order_id字段作为分表的字段，分表规则是t_order_inline
30. shardingColumn: order_id
31. shardingAlgorithmName: t_order_inline
32. keyGenerateStrategy:
33. # order_id 为主键，且由雪花算法自动生成，类比自增主键
34. column: order_id
35. keyGeneratorName: snowflake
36. bindingTables:
37. - t_order
38. defaultDatabaseStrategy:
39. #设置分库规则database_inline，以user_id作为分库字段
40. standard:
41. shardingColumn: user_id
42. shardingAlgorithmName: database_inline
43. defaultTableStrategy:
44. none:
46. shardingAlgorithms:
47. database_inline:
48. type: INLINE
49. props:
50. algorithm-expression: ds_${user_id % 2}
51. t_order_inline:
52. type: INLINE
53. props:
54. algorithm-expression: t_order_${order_id % 2}
56. keyGenerators:
57. #配置雪花算法
58. snowflake:
59. type: SNOWFLAKE
60. props:
61. worker-id: 123

复制代码

4. 启动中间件，上图目录下打开命令行窗口，执行bin/start.sh -P3307,表示中间件的虚拟数据库访问端口是3307，不指定也是默认3307，因为MySQL默认是3306
5. 连接中间件提供的虚拟数据库，在命令行窗口执行mysql -h 127.0.0.1 -P3307 -uroot -proot -A，也可以通过其他客户端连接，参数-A的作用是防止，因为mysql8.0以上客户端会预取元数据而导致的报错，提示如下所示，因为是中间件的虚拟数据库，所以一般都需要在连接时加上参数-A

1. Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

复制代码

6. 创建数据库，此时会自动创建ds_0.t_order_0、ds_0.t_order_1、ds_1.t_order_0、ds_1.t_order_1 四张表

1. CREATE TABLE t_order (
2. order_id BIGINT NOT NJLL AUTO_INCRENENT,
3. user_id INT NOT NULL,
4. status VARCHAR(50),
5. PRIMARY KEY (order_id)
6. );

复制代码

分库还是分表

• IO的压力：分库
• 单表的数据量过大导致的性能问题：分表
• 既有IO压力，又有单表性能问题：分库+分表

由分库分表延伸出的数据的分类管理

• 以订单数据为例
  1.定义一周内下单但未支付的数据为热数据，同时放到数据库和内存；
  2.定义三个月内的数据为温数据，放到数据库，提供正常的查询操作；
  3.定义 3 个月到 3 年的数据，为冷数据，从数据库删除，归档到一些便宜的磁盘，用压缩的方式（比如MySQL 的 tokuDB 引擎，可以压缩到几十分之一）存储，用户需要邮件或者提交工单来查询，我们导出后发给用户；
  4.定义 3 年以上的数据为冰数据，备份到磁带之类的介质上，不提供任何查询操作