第二十三天：MYSQL集群Cluster

一、MySQL 主从复制

 1、主从复制架构和原理

读写分离

复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制

2、复制架构

（1）一主一从复制架构

 （2）一主多从复制架构

3、主从复制原理

 

 主从复制相关线程

主节点：

dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events

从节点：

I/O Thread：向Master请求二进制日志事件，并保存于中继日志中

SQL Thread：从中继日志中读取日志事件，在本地完成重放

跟复制功能相关的文件：

master.info：用于保存slave连接至master时的相关信息，例如账号、密码、服务器地址等

relay-log.info：保存在当前slave节点上已经复制的当前二进制日志和本地relay log日志的对应关系

mysql-relay-bin.00000#: 中继日志,保存从主节点复制过来的二进制日志,本质就是二进制日志 

4、各种复制架构

　　一Master/一Slave

　　一主多从 

　　从服务器还可以再有从服务器

　　Master/Master

　　一从多主:适用于多个不同数据库

　　环状复制

复制需要考虑二进制日志事件记录格式

　　STATEMENT（5.0之前）, Mariadb5.5 默认使用此格式

　　ROW（5.1之后，推荐）,MySQL 8.0 默认使用此格式

　　MIXED: Mariadb10.3 默认使用此格式

5、实现主从复制配置
 （1）主节点配置：

 　　启用二进制日志

[mysqld]
log_bin=/data/logbin/mysql-bin

　为当前节点设置一个全局惟一的ID号

[mysqld]
server-id=#
log-basename=master  #可选项，设置datadir中日志名称，确保不依赖主机名

　查看从二进制日志的文件和位置开始进行复制

 　　SHOW MASTER STATUS;

 创建有复制权限的用户账号

GRANT REPLICATION SLAVE  ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
#MySQL8.0 分成两步实现
mysql> create user repluser@'10.0.0.%' identified by '123456';
mysql> grant replication slave on *.* to repluser@'10.0.0.%';

（2）从节点配置：

 启动中继日志

[mysqld]
server_id=# #为当前节点设置一个全局惟的ID号
log-bin
read_only=ON #设置数据库只读，针对supper user无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index  #默认值hostname-relay-bin.index

使用有复制权限的用户账号连接至主服务器，并启动复制线程

CHANGE MASTER TO MASTER_HOST='masterhost', 
MASTER_USER='repluser', 
MASTER_PASSWORD='replpass', 
MASTER_LOG_FILE='mariadb-bin.xxxxxx', 
MASTER_LOG_POS=#
MASTER_DELAY = interval; #可指定延迟复制实现访问误操作,单位秒

START SLAVE [IO_THREAD|SQL_THREAD];
SHOW SLAVE STATUS;

#查看 relaylog 事件
SHOW RELAYLOG EVENTS in 'relay-bin.00000x';

如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点

　　通过备份恢复数据至从服务器

　　复制起始位置为备份时，二进制日志文件及其POS

 

6、主从复制相关

（1）限制从服务器为只读

read_only=ON
#注意：此限制对拥有SUPER权限的用户均无效

（2）在从节点清除信息

RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE  ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和
PASSWORD 等

（3）复制错误解决方法

可以在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID

注意: Centos 8.1以上版本上的MariaDB10.3主从节点同时建同名的库和表不会冲突，建主键记录会产

生冲突

#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL

（4）START SLAVE 语句，指定执到特定的点

START SLAVE [thread_types]
START SLAVE [SQL_THREAD] UNTIL   MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = 
log_pos
START SLAVE [SQL_THREAD] UNTIL   RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = 
log_pos
thread_types:
   [thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD

（5）保证主从复制的事务安全

在master节点启用参数：

sync_binlog=1 #每次写后立即同步二进制日志到磁盘，性能差
#如果用到的为InnoDB存储引擎：
innodb_flush_log_at_trx_commit=1 #每次事务提交立即同步日志写磁盘
sync_master_info=# #次事件后master.info同步到磁盘

在slave节点启用服务器选项：

skip-slave-start=ON #不自动启动slave

在slave节点启用参数：

sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=# #次事务后同步relay-log.info到磁盘

7、实现级联复制

 需要在中间的从服务器启用以下配置 ，实现中间slave节点能将master的二进制日志在本机进行数据库

更新，并且也同时更新本机的二进制，从而实现级联复制

 

[mysqld]
server-id=18
log_bin
log_slave_updates      #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加,其它
版本默认不开启
read-only

8、主主复制

主主复制：两个节点，都可以更新数据，并且互为主从

容易产生的问题：数据不一致；因此慎用

考虑要点：自动增长id

配置一个节点使用奇数id

auto_increment_offset=1   #开始点
auto_increment_increment=2 #增长幅度

另一个节点使用偶数id

auto_increment_offset=2
auto_increment_increment=2

主主复制的配置步骤：

(1) 各节点使用一个惟一server_id

(2) 都启动binary log和relay log

(3) 创建拥有复制权限的用户账号

(4) 定义自动增长id字段的数值范围各为奇偶

(5) 均把对方指定为主节点，并启动复制线程

9、半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失

半同步复制默认设置

rpl_semi_sync_master_wait_point=after_commit

 缺点

缺点1： 幻读
当用户提交一个事务，该事务已经写入redo日志和binlog日志，但该事务还没写入从库，此时处在waiting slave dump处，此时另一个用户可以读取到这条数据，而他自己却不能；
缺点2：数据丢失一个提交的事务在waiting slave dump处crash后，主库将比从库多一条数据

增强半同步复制(MySQL5.7新增功能)

#MySQL8.0的默认值
rpl_semi_rsync_master_wait_point=after_sync

 优点

改善1：解决幻读
当用户发起一个事务，该事务先写入二进制后，再向从库进行同步，由于还没有完成提交，此时其他用户无法读取到该数据，解决了幻读
改善2：解决数据丢失一个事务在waiting slave dump处crash掉后，可以通过观察从库上是否存在主库的last gtid值，如果
存在，这条数据正常恢复，如果不存在则删除主库的那条多余的GTID值，然后恢复，保证了数据的完整性

#查看插件文件
[root@centos8 ~]#rpm -ql mysql-server |grep semisync
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
#master服务器配置
[root@master ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=8
log-bin
rpl_semi_sync_master_enabled=ON     #修改此行,需要先安装semisync_master.so插件后,再重启,否则无法启动
rpl_semi_sync_master_timeout=3000   #设置3s内无法同步，也将返回成功信息给客户端
#slave服务器配置
[root@slave1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=18
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动
[root@slave2 ~]#vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=28
rpl_semi_sync_slave_enabled=ON #修改此行,需要先安装semisync_slave.so插件后,再重启,否则无法启动
#主服务器配置:
mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; #永久安装插件
mysql>UNINSTALL PLUGIN rpl_semi_sync_master ;
mysql>SHOW PLUGINS; #查看插件
mysql>SET GLOBAL rpl_semi_sync_master_enabled=1; #临时修改变量
mysql>SET GLOBAL rpl_semi_sync_master_timeout = 3000;  #超时长1s,默认值为10s
mysql>SHOW GLOBAL VARIABLES LIKE '%semi%';

　　 

10、复制过滤器

让从节点仅复制指定的数据库，或指定数据库的指定表

复制过滤器两种实现方式：

(1) 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件

缺点：基于二进制还原将无法实现；不建议使用

优点: 只需要在主节点配置一次即可

注意：此项和 binlog_format相关

vim /etc/my.cnf
binlog-do-db=db1 #数据库白名单列表，不支持同时指定多个值，如果想实现多个数据库需多行实现
binlog-do-db=db2 
binlog-ignore-db= #数据库黑名单列表

(2) 从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本

地

缺点：会造成网络及磁盘IO浪费,在所有从节点都要配置

优点: 不影响二进制备份还原

从服务器上的复制过滤器相关变量

项不支持多值,只能分别写多行实现
replicate_ignore_db= #指定复制库黑名单
replicate_do_table= #指定复制表的白名单
replicate_ignore_table= #指定复制表的黑名单
replicate_wild_do_table= foo%.bar%  #支持通配符
replicate_wild_ignore_table=

11、GTID复制

GTID（Global Transaction ID 全局事务标识符） MySQL 5.6 版本开始支持,但不太成熟,建议使用

MySQL5.7以上版本的GTID功能

MySQL5.6版本出现没有默认开启，5.7中即使不开启也有匿名的GTID记录。

开启GTID功能可以支持多DUMP线程的并发复制,而且在MySQL5.6实现了基于库级别多SQL线程并发。在MySQL5.7利用 GTID的 Logic clock 逻辑时钟。保证了同库级别下的事务顺序问题。即可以实现基于事务级别的并发回放。从而大大减少了同步的延迟

同时GTID具有幂等性特性,即多次执行结果是一样的

利用 GTID复制不像传统的复制方式（异步复制、半同步复制）需要找到binlog文件名和POS点，只需知道master的IP、端口、账号、密码即可。开启GTID后，执行change master tomaster_auto_postion=1即可，它会自动寻找到相应的位置开始同步

GTID 优点:

　　保证事务全局统一

　　截取日志更加方便。跨多文件，判断起点终点更加方便

　　判断主从工作状态更加方便

　　传输日志，可以并发传输。SQL回放可以更高并发

　　主从复制构建更加方便

GTID 架构

　　

 GTID服务器相关选项

gtid_mode #gtid模式
enforce_gtid_consistency #保证GTID安全的参数

GTID配置范例

1. 主服务器 

vim /etc/my.cnf
server-id=1
gtid_mode=ON
enforce_gtid_consistency
log-bin=mysql-bin  #可选
systemctl restart mysqld
mysql> grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by 
'magedu';

2. 从服务器

vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce_gtid_consistency
systemctl restart mysqld
#如果主服务器和从服务器数据不一致,需要先将主库数据备份还原至从库,再执行下面操作
mysqldump -A --master-data=2 > /backup/full.sql
mysql>CHANGE MASTER TO MASTER_HOST='10.0.0.100',
 MASTER_USER='repluser',
 MASTER_PASSWORD='magedu',
 MASTER_PORT=3306,
 MASTER_AUTO_POSITION=1;  #使用GTID
  
mysql>start slave;
#注意观察:Retrieved_Gtid_set和Executed_Gtid_Set这两个值,对比主节点执行show master 
status的值,如果相同表示同步完成

12、复制的监控和维护

（1）清理日志

PURGE { BINARY | MASTER } LOGS   { TO 'log_name' | BEFORE datetime_expr }
RESET MASTER TO # #mysql 不支持
RESET SLAVE [ALL]

（2）复制监控

SHOW MASTER STATUS
SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW SLAVE STATUS
SHOW PROCESSLIST

（3）从服务器是否落后于主服务

Seconds_Behind_Master：0

（4）如何确定主从节点数据是否一致 

percona-toolkit

 

二、MySQL 中间件代理服务器

1、关系型数据库和 NoSQL 数据库

数据库主要分为两大类：关系型数据库与 NoSQL 数据库。

关系型数据库，是建立在关系模型基础上的数据库，其借助于集合代数等数学概念和方法来处理数据库中的数据。主流的 MySQL、Oracle、MS SQL Server 和 DB2 都属于这类传统数据库。

NoSQL 数据库，全称为 Not Only SQL，意思就是适用关系型数据库的时候就使用关系型数据库，不适用的时候也没有必要非使用关系型数据库不可，可以考虑使用更加合适的数据存储。主要分为临时性键

值存储（Redis、memcached）、永久性键值存储（ROMA、Redis）、面向文档的数据库

（MongoDB、CouchDB）、面向列的数据库（Cassandra、HBase），每种 NoSQL 都有其特有的使用场景及优点。

Oracle，mysql 等传统的关系数据库非常成熟并且已大规模商用，为什么还要用 NoSQL 数据库呢？主要是由于随着互联网发展，数据量越来越大，对性能要求越来越高，传统数据库存在着先天性的缺陷，

即单机（单库）性能瓶颈，并且扩展困难。这样既有单机单库瓶颈，却又扩展困难，自然无法满足日益增长的海量数据存储及其性能要求，所以才会出现了各种不同的 NoSQL 产品，NoSQL 根本性的优势在

于在云计算时代，简单、易于大规模分布式扩展，并且读写性能非常高

RDBMS和NOSQL的特点及优缺点：

 2、数据库切分方式

 简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机） 上面，以达到分散单台设备负载的效果。

数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式。

一种是按照不同的表（或者 Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；另外一种则是根据 表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分

到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分。

垂直切分的最大特点就是规则简单，实施也更为方便，尤其适合各业务之间的耦合度非常低，相互影响很小， 业务逻辑非常清晰的系统。在这种系统中，可以很容易做到将不同业务模块所使用的表分拆到不

同的数据库中。 根据不同的表来进行拆分，对应用程序的影响也更小，拆分规则也会比较简单清晰。

水平切分于垂直切分相比，相对来说稍微复杂一些。因为要将同一个表中的不同数据拆分到不同的数据库中， 对于应用程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂

一些。

（1）垂直切分

 一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图： 

 垂直切分的优缺点：

优点：

　　拆分后业务清晰，拆分规则明确

　　系统之间整合或扩展容易

　　数据维护简单

缺点：

　　部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度

　　受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高

　　事务处理复杂 

（2）水平切分

 既然数据做了拆分有优点也就优缺点。

优点：

　　拆分规则抽象良好，join 操作基本都可以数据库完成

　　不存在单库大数据，高并发的性能瓶颈

　　应用端改造较少

　　提高了系统的稳定性跟负载能力

缺点：

　　拆分规则难以抽象

　　分片事务一致性难以解决

　　数据多次扩展难度跟维护量极大

　　跨库 join 性能较差 

3、MySQL 中间件各种应用 

 

 

mysql-proxy：Oracle，https://downloads.mysql.com/archives/proxy/

Atlas：Qihoo，https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md

dbproxy：美团，https://github.com/Meituan-Dianping/DBProxy

Cetus：网易乐得，https://github.com/Lede-Inc/cetus

Amoeba：https://sourceforge.net/projects/amoeba/

Cobar：阿里巴巴，Amoeba的升级版， https://github.com/alibaba/cobar

Mycat：基于Cobar http://www.mycat.io/ (原网站)

　　　　　　http://www.mycat.org.cn/

　　　　　　https://github.com/MyCATApache/Mycat-Server

ProxySQL：https://proxysql.com/

MaxScale: 是 MariaDB 开发的一个数据库智能代理服务，允许根据数据库 SQL 语句将请求转向目标一个到多个服务器，可设定各种复杂程度的转向规则。MaxScale 设计用于透明的提供数据库

的负载均衡和高可用性，同时提供高度可伸缩和灵活的架构，支持不同的协议和路由决策。MaxScale 使用 C 语言开发，利用 Linux 下的异步 I/O 功能。使用 epoll 作为事件驱动框架。

https://mariadb.com/kb/en/maxscale/ 

 

4、Mycat

　　在整个IT系统架构中，数据库是非常重要，通常又是访问压力较大的一个服务，除了在程序开发的本身做优化，如：SQL语句优化、代码优化，数据库的处理本身优化也是非常重要的。主从、热备、分表分

库等都是系统发展迟早会遇到的技术问题问题。Mycat是一个广受好评的数据库中间件，已经在很多产品上进行使用了。

　　Mycat是一个开源的分布式数据库系统，是一个实现了MySQL协议的服务器，前端用户可以把它看作是一个数据库代理（类似于Mysql Proxy），用MySQL客户端工具和命令行访问，而其后端可以用MySQL

原生协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里。

　　Mycat发展到目前的版本，已经不是一个单纯的MySQL代理了，它的后端可以支持MySQL、SQLServer、Oracle、DB2、PostgreSQL等主流数据库，也支持MongoDB这种新型NoSQL方式的存储，未

来还会支持更多类型的存储。而在最终用户看来，无论是那种存储方式，在MyCat里，都是一个传统的数据库表，支持标准的SQL语句进行数据的操作，这样一来，对前端业务系统来说，可以大幅降低开发

难度，提升开发速度

 Mycat 可以简单概括为

　　一个彻底开源的，面向企业应用开发的大数据库集群

　　支持事务、ACID、可以替代MySQL的加强版数据库

　　一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群

　　一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server

　　结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品

　　一个新颖的数据库中间件产品

Mycat 官网：http://www.mycat.org.cn/

Mycat工作原理

 （1）mycat安装

下载安装JDK

yum -y install java
#确认安装成功
java -version
openjdk version "1.8.0_201"
OpenJDK Runtime Environment (build 1.8.0_201-b09)
OpenJDK 64-Bit Server VM (build 25.201-b09, mixed mode)

下载安装mycat

wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-
1.6.7.4-release-20200105164103-linux.tar.gz
mkdir /apps
tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz  -C /apps
ls /apps/mycat/
bin catlet conf lib logs version.txt

mycat安装目录结构：

　　bin mycat命令，启动、重启、停止等

　　catlet catlet为Mycat的一个扩展功能

　　conf Mycat 配置信息,重点关注

　　lib Mycat引用的jar包，Mycat是java开发的

　　logs 日志文件，包括Mycat启动的日志和运行的日志

　　version.txt mycat版本说明

logs目录:

　　wrapper.log mycat启动日志

　　mycat.log mycat详细工作日志

Mycat的配置文件都在conf目录里面，这里介绍几个常用的文件：

　　server.xml Mycat软件本身相关的配置文件，设置账号、参数等

　　schema.xml Mycat对应的物理数据库和数据库表的配置,读写分离、高可用、分布式策略定制、

节点控制

　　rule.xml Mycat分片（分库分表）规则配置文件,记录分片规则列表、使用方法等

启动和连接 

#配置环境变量
vim /etc/profile.d/mycat.sh
PATH=/apps/mycat/bin:$PATH
source /etc/profile.d/mycat.sh
#启动
mycat start
#查看日志，确定成功
cat /app/mycat/logs/wrapper.log 
...省略...
INFO   | jvm 1   | 2019/11/01 21:41:02 | MyCAT Server startup successfully. see 
logs in logs/mycat.log
#连接mycat：
mysql -uroot -p123456 -h 127.0.0.1 -P8066

（2）Mycat 主要配置文件说明 

server.xml

存放Mycat软件本身相关的配置文件，比如：连接Mycat的用户，密码，数据库名称等

server.xml文件中配置的参数解释说明： 

　　user 用户配置节点

　　name 客户端登录MyCAT的用户名，也就是客户端用来连接Mycat的用户名。

　　password 客户端登录MyCAT的密码

　　schemas 数据库名，这里会和schema.xml中的配置关联，多个用逗号分开，例如:db1,db2

　　privileges 配置用户针对表的增删改查的权限

　　readOnly mycat逻辑库所具有的权限。true为只读，false为读写都有，默认为false

schema.xml

是最主要的配置项，此文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物

理服务器，此文件就是用来连接MySQL服务器的schema.xml文件中配置的参数解释说明：

参数 说明

　　schema 数据库设置，此数据库为逻辑数据库，name与server.xml中schema对应

　　dataNode 分片信息，也就是分库相关配置

　　dataHost 物理数据库，真正存储数据的数据库

配置说明

　　name属性唯一标识dataHost标签，供上层的标签使用。

　　maxCon属性指定每个读写实例连接池的最大连接。也就是说，标签内嵌套的writeHost、readHost标签都会使用这个属性的值来实例化出连接池的最大连接数

　　minCon属性指定每个读写实例连接池的最小连接，初始化连接池的大小 

 

5、ProxySQL

 （1）ProxySQL 介绍

 ProxySQL： MySQL中间件

两个版本：官方版和percona版，percona版是基于官方版基础上修改

C++语言开发，轻量级但性能优异，支持处理千亿级数据

具有中间件所需的绝大多数功能，包括：

　　多种方式的读/写分离

　　定制基于用户、基于schema、基于语句的规则对SQL语句进行路由

　　缓存查询结果

　　后端节点监控

官方站点：https://proxysql.com/

官方手册：https://github.com/sysown/proxysql/wiki 

（2）ProxySQL 安装

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
#基于RPM下载安装：https://github.com/sysown/proxysql/releases
yum install proxysql

ProxySQL组成

　　服务脚本：/etc/init.d/proxysql

　　配置文件：/etc/proxysql.cnf

　　主程序：/usr/bin/proxysql

　　基于SQLITE的数据库文件：/var/lib/proxysql/

启动ProxySQL： 

service proxysql start

启动后会监听两个默认端口

　　6032：ProxySQL的管理端口

　　6033：ProxySQL对外提供服务的端口

连接ProxySQL的管理端口

使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin： 

mysql -uadmin -padmin -P6032 -h127.0.0.1

数据库说明：

　　main 是默认的"数据库"名，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime开头的表示

　　proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载

　　disk 是持久化到硬盘的配置，sqlite数据文件

　　stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间，等等

　　monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查

说明：

　　在main和monitor数据库中的表， runtime开头的是运行时的配置，不能修改，只能修改非runtime表

　　修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效

　　执行save … to disk 才将配置持久化保存到磁盘，即保存在proxysql.db文件中

　　global_variables 有许多变量可以设置，其中就包括监听的端口、管理账号等

　　参考: https://github.com/sysown/proxysql/wiki/Global-variables 

 

三、MySQL 高可用

 1、 MySQL 高可用解决方案

 MySQL官方和社区里推出了很多高可用的解决方案，大体如下，仅供参考（数据引用自Percona） 

 MMM: Multi-Master Replication Manager for MySQL，Mysql主主复制管理器是一套灵活的脚本程序，基于perl实现，用来对mysql replication进行监控和故障迁移，并能管理mysql Master-Master复制的配置(同一时间只有一个节点是可写的)

官网： http://www.mysql-mmm.org

　　　　https://code.google.com/archive/p/mysql-master-master/downloads

MHA：Master High Availability，对主节点进行监控，可实现自动故障转移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，出于机器成本的考虑，淘宝进行了

改造，目前淘宝TMHA已经支持一主一从

官方网站：https://code.google.com/archive/p/mysql-master-ha/

　　　　https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads

　　　　https://github.com/yoshinorim/mha4mysql-manager/releases

　　　　https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58 

以下技术可以达到金融级的高可用性要求

　　Galera Cluster：wsrep(MySQL extended with the Write Set Replication)　　通过wsrep协议在全局实现复制；任何一节点都可读写，不需要主从复制，实现多主读写

　　GR（Group Replication）：MySQL官方提供的组复制技术(MySQL 5.7.17引入的技术)，基于原生复制技术Paxos算法，实现了多主更新，复制组由多个server成员构成，组中的每个server可独立地执行事务，但所有读写事务只在冲突检测成功后才会提交

 

2、MHA Master High Availability

（1）工作原理

　　　　1. MHA利用 SELECT 1 As Value 指令判断master服务器的健康性,一旦master 宕机,MHA 从宕机崩

溃的master保存二进制日志事件（binlog events）

　　　　2. 识别含有最新更新的slave

　　　　3. 应用差异的中继日志（relay log）到其他的slave

　　　　4. 应用从master保存的二进制日志事件（binlog events）到所有slave节点

　　　　5. 提升一个slave为新的master

　　　　6. 使其他的slave连接新的master进行复制

　　　　7. 故障服务器自动被剔除集群(masterha_conf_host),将配置信息去掉

　　　　8. 旧的Master的 VIP 漂移到新的master上，用户应用就可以访问新的Master

　　　　9. MHA是一次性的高可用性解决方案,Manager会自动退出

（2）选举新的Master

　　如果设定权重(candidate_master=1),按照权重强制指定新主,但是默认情况下如果一个slave落后

master 二进制日志超过100M的relay logs，即使有权重,也会失效.如果设置check_repl_delay=0,即使落后很多日志,也强制选择其为新主

　　如果从库数据之间有差异,最接近于Master的slave成为新主

　　如果所有从库数据都一致,按照配置文件顺序最前面的当新主 

（3）数据恢复

　　当主服务器的SSH还能连接,从库对比主库position 或者GTID号,将二进制日志保存至各个从节点并且应用(执行save_binary_logs 实现)

　　当主服务器的SSH不能连接, 对比从库之间的relaylog的差异(执行apply_diff_relay_logs[实现])

 

 3、Galera Cluster

 Galera Cluster：集成了Galera插件的MySQL集群，是一种新型的，数据不共享的，高度冗余的高可用方案，目前Galera Cluster有两个版本，分别是Percona Xtradb Cluster及MariaDB Cluster，Galera本

身是具有多主特性的，即采用multi-master的集群架构，是一个既稳健，又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案

Galera Cluster特点

　　多主架构：真正的多点读写的集群，在任何时候读写数据，都是最新的

　　同步复制：改善了主从复制延迟问题，基本上达到了实时同步

　　并发复制：从节点APPLY数据时，支持并行执行，更好的性能

　　故障切换：在出现数据库故障时，因支持多点写入，切换容易

　　热插拔：在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务时间就会非常少。在节点故障期间，节点本身对集群的影响非常小 

　　自动节点克隆：在新增节点，或者停机维护时，增量数据或者基础数据不需要人工手动备份提供，Galera Cluster会自动拉取在线节点数据，最终集群会变为一致

　　对应用透明：集群的维护，对应用程序是透明的

Galera Cluster 缺点

任何更新的事务都需要全局验证通过，才会在其他节点上执行，则集群性能由集群中最差性能节点决定（一般集群节点配置都是一样的）

新节点加入或延后较大的节点重新加入需全量拷贝数据(SST，State Snapshot Transfer),作为donor( 贡献者，如： 同步数据时的提供者)的节点在同步过程中无法提供读写

只支持innodb存储引擎的表

Galera Cluster工作过程

 

Galera Cluster 包括两个组件

　　Galera replication library (galera-3)

　　WSREP：MySQL extended with the Write Set Replication

WSREP复制实现：

　　PXC：Percona XtraDB Cluster，是Percona对Galera的实现

参考仓库：

　　https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch 

MariaDB Galera Cluster：

　　https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.X/yum/centos7-amd64/  

PXC 原理

　　

　　

 PXC最常使用如下4个端口号：

　　3306：数据库对外服务的端口号

　　4444：请求SST的端口号

　　4567：组成员之间进行沟通的端口号

　　4568：用于传输IST的端口号 

PXC中涉及到的重要概念和核心参数：

（1）集群中节点的数量：整个集群中节点数量应该控制在最少3个、最多8个的范围内。最少3个节点是

为了防止出现脑裂现象，因为只有在2个节点下才会出现此现象。脑裂现象的标志就是输入任何命令，

返回的结果都是unknown command。节点在集群中，会因新节点的加入或故障、同步失效等原因发

生状态的切换。

（2）节点状态的变化阶段：

　　open：节点启动成功，尝试连接到集群时的状态

　　primary：节点已处于集群中，在新节点加入并选取donor进行数据同步时的状态

　　joiner：节点处于等待接收同步文件时的状态

　　joined：节点完成数据同步工作，尝试保持和集群进度一致时的状态

　　synced：节点正常提供服务时的状态，表示已经同步完成并和集群进度保持一致

　　donor：节点处于为新加入的节点提供全量数据时的状态

（3）节点的数据传输方式：

　　SST：State Snapshot Transfer，全量数据传输

　　IST：Incremental State Transfer，增量数据传输

　　SST数据传输有xtrabackup、mysqldump和rsync三种方式，而增量数据传输就只有一种方式

　　xtrabackup，但生产环境中一般数据量较小时，可以使用SST全量数据传输，但也只使用xtrabackup方法。

（4）GCache模块：在PXC中一个特别重要的模块，它的核心功能就是为每个节点缓存当前最新的写集。如果有新节点加入进来，就可以把新数据的增量传递给新节点，而不需要再使用SST传输方式，这

样可以让节点更快地加入集群中，

涉及如下参数：

　　gcache.size：缓存写集增量信息的大小，它的默认大小是128MB，通过wsrep_provider_options参数设置，建议调整为2GB~4GB范围，足够的空间便于缓存更多的增量信息。

　　gcache.mem_size：GCache中内存缓存的大小，适度调大可以提高整个集群的性能

　　gcache.page_size：如果内存不够用（GCache不足），就直接将写集写入磁盘文件中

（5）在各个节点上分别配置mysql及集群配置文件

/etc/my.cnf为主配置文件，当前版本中，其余的配置文件都放在/etc/percona-xtradb-cluster.conf.d目录里，包括mysqld.cnf，mysqld_safe.cnf，wsrep.cnf 三个文件

配置文件各项配置意义

 

 4、TiDB 概述

 

TiDB 是 PingCAP 公司受 Google Spanner / F1 论文启发而设计的开源分布式 HTAP (Hybrid

Transactional and Analytical Processing) 数据库，结合了传统的 RDBMS 和NoSQL 的最佳特性。

TiDB 兼容 MySQL，支持无限的水平扩展，具备强一致性和高可用性。TiDB和MySQL几乎完全兼容 

TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和

MySQL 协议，具有数据强一致的高可用特性，是一个不仅适合 OLTP 场景还适合 OLAP 场景的混合数

据库。TiDB年可用性达到99.95%

TiDB 的目标是为 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 场

景提供一站式的解决方案。

（1）TiDB 核心特点

1. 高度兼容 MySQL 大多数情况下，无需修改代码即可从 MySQL 轻松迁移至 TiDB，分库分表后的MySQL 集群亦可通过 TiDB 工具进行实时迁移

2. 水平弹性扩展 通过简单地增加新节点即可实现 TiDB 的水平扩展，按需扩展吞吐或存储，轻松应对高并发、海量数据场景

3. 分布式事务 TiDB 100% 支持标准的 ACID 事务

4. 真正金融级高可用 相比于传统主从 (M-S) 复制方案，基于 Raft 的多数派选举协议可以提供金融级的 100% 数据强一致性保证，且在不丢失大多数副本的前提下，可实现故障的自动恢复 (auto-failover)，无需人工介入

5. 一站式 HTAP 解决方案 TiDB 作为典型的 OLTP 行存数据库，同时兼具强大的 OLAP 性能，配合TiSpark，可提供一站式 HTAP解决方案，一份存储同时处理OLTP & OLAP(OLAP、OLTP的介绍和比较 )无需传统繁琐的 ETL 过程

6. 云原生 SQL 数据库 TiDB 是为云而设计的数据库，同 Kubernetes 深度耦合，支持公有云、私有云和混合云，使部署、配置和维护变得十分简单。TiDB 的设计目标是 100% 的 OLTP 场景和 80%的 OLAP 场景，更复杂的 OLAP 分析可以通过 TiSpark 项目来完成。 TiDB 对业务没有任何侵入性，能优雅的替换传统的数据库中间件、数据库分库分表等 Sharding 方案。同时它也让开发运维人员不用关注数据库 Scale 的细节问题，专注于业务开发，极大的提升研发的生产力

（2）TiDB整体架构

TiDB Server

TiDB Server 负责接收SQL请求，处理SQL相关的逻辑，并通过PD找到存储计算所需数据的TiKV地址，

与TiKV交互获取数据，最终返回结果。TiDB Server 是无状态的，其本身并不存储数据，只负责计算，

可以无限水平扩展，可以通过负载均衡组件（LVS、HAProxy或F5）对外提供统一的接入地址。

PD Server

Placement Driver（简称PD）是整个集群的管理模块，其主要工作有三个：一是存储集群的元信息

（某个Key存储在那个TiKV节点）；二是对TiKV集群进行调度和负载均衡（如数据的迁移、Raft group

leader的迁移等）；三是分配全局唯一且递增的事务ID

PD 是一个集群，需要部署奇数个节点，一般线上推荐至少部署3个节点。PD在选举的过程中无法对外

提供服务，这个时间大约是3秒

TiKV Server

TiKV Server 负责存储数据，从外部看TiKV是一个分布式的提供事务的Key-Value存储引擎。存储数据的

基本单位是Region，每个Region负责存储一个Key Range（从StartKey到EndKey的左闭右开区间）的

数据，每个TiKV节点会负责多个Region。TiKV使用Raft协议做复制，保持数据的一致性和容灾。副本以

Region为单位进行管理，不同节点上的多个Region构成一个Raft Group，互为副本。数据在多个TiKV

之间的负载均衡由PD调度，这里也就是以Region为单位进行调度 

 

四、压力测试

1、压力测试工具

 （1）常见 MySQL 压力测试工具

 mysqlslap

Sysbench：功能强大，官网： https://github.com/akopytov/sysbench

tpcc-mysql

MySQL Benchmark Suite

MySQL super-smack

MyBench

 （2）mysqlslap

mysqlslap：来自于mysql或mariadb包，测试的过程默认生成一个mysqlslap的schema,生成测试表

t1，查询和插入测试数据，mysqlslap库自动生成，如果已经存在则先删除。用--only-print来打印实际

的测试过程，整个测试完成后不会在数据库中留下痕迹

使用格式：

mysqlslap [options]

2、MySQL配置最佳实践

高并发大数据的互联网业务，架构设计思路是"解放数据库CPU，将计算转移到服务层"，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现"增机器

就加性能"

参考资料：

阿里巴巴Java开发手册：https://developer.aliyun.com/topic/java2020

58到家数据库30条军规解读：http://zhuanlan.51cto.com/art/201702/531364.htm

以下规范适用场景：并发量大、数据量大的互联网业务

 基础规范

(1)必须使用InnoDB存储引擎

解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高

(2)使用UTF8MB4字符集

解读：万国码，无需转码，无乱码风险，节省空间，支持表情包及生僻字

(3)数据表、数据字段必须加入中文注释

解读：N年后谁知道这个r1,r2,r3字段是干嘛的

(4)禁止使用存储过程、视图、触发器、Event

解读：高并发大数据的互联网业务，架构设计思路是"解放数据库CPU，将计算转移到服务层"，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实

现"增机器就加性能"。数据库擅长存储与索引，CPU计算还是上移吧！

(5)禁止存储大文件或者大照片

解读：为何要让数据库做它不擅长的事情?大文件和照片存储在文件系统，数据库里存URI多好。

等等。。。