给一个网站做需求分析,建筑模板尺寸规格及价格,宿州网站建设工作室,优化系统的软件一 Mycat概述
1 什么是Mycat 什么是Mycat
Mycat是数据库中间件#xff0c;所谓数据库中间件是连接Java应用程序和数据库中间的软件。 为什么要用Mycat 遇到问题#xff1a; Java与数据库的紧耦合高访问量高并发对数据库的压力读写请求数据不一致 2 Mycat与其他中间件区别 目…一 Mycat概述
1 什么是Mycat 什么是Mycat
Mycat是数据库中间件所谓数据库中间件是连接Java应用程序和数据库中间的软件。 为什么要用Mycat 遇到问题 Java与数据库的紧耦合高访问量高并发对数据库的压力读写请求数据不一致 2 Mycat与其他中间件区别 目前的数据库中间件有很多并从各个维度将其与 Mycat进行对比。
Mycat优势 性能可靠稳定
基于阿里开源的Cobar产品而研发Cobar的稳定性、可靠性、优秀 的架构和性能以及众多成熟的使用案例使得MYCAT一开始就拥有一 个很好的起点站在巨人的肩膀上我们能看到更远。
强大的技术团队
MyCat现在由一支强大的技术团队维护 , 吸引和聚集了一大批业内 大数据和云计算方面的资深工程师、架构师、DBA优秀的团队保 障了MyCat的稳定高效运行。而且MyCat不依托于任何商业公司 而且得到大批开源爱好者的支持。
体系完善
MyCat已经形成了一系列的周边产品,比较有名的是 Mycat-web、 Mycat-NIO、Mycat-Balance 等,已经形成了一个比较完整的解决方案,而不仅仅是一个中间件。
3 Mycat应用场景 Mycat还形成了一系列周边产品比较有名的是 Mycat-web、 Mycat-NIO、Mycat-Balance等已成为一个比较完整的数据处理解决方案而不仅仅是中间件。
高可用性与MySQL读写分离
利用Mycat可以轻松实现热备份当一台服务器停机时可以由双机或集群中的另一台服务器自动接管其业务从而在无须人工干预的情况下保证系统持续提供服务。这个切换动作由Mycat自动完成。 注意 Mycat 的读写分离及自动切换都依赖于数据库产品的主从数据同步。 100亿大表水平分表、集群并行计算
数据切分是Mycat的核心功能是指通过某种特定的条件将存放在同一个数据库中的数据分散存放在多个数据库主机中以达到分散单台设备负载的效果。 数据切分有两种切分模式 按照不同的表将数据切分到不同的数据库中这种切分可以叫作数据的垂直切分。根据表中数据的逻辑关系将同一个表中的数据按照某种条件拆分到多个数据库中这种切分叫作数据的水平切分。当数据量超过800万行且需要做分片时可以利用Mycat实现数据切分。 数据库路由器
Mycat基于MySQL 实例的连接池复用机制可以让每个应用最大程度地共享一个MySQL实例的所有连接池让数据库的并发访问能力大大提升。
整合多种数据源
当一个项目需要用到多种数据源如Oracle、MySQL、SQL Server、 PostgreSQL时可以利用Mycat进行整合只需访问Mycat 这一个数据源就行。 4 核心概念详解 逻辑库schema
业务开发人员通常在实际应用中并不需要知道中间件的存在只需要关注数据库所以数据库中间件可以被当作一个或多个数据库集群构成的逻辑库。 注意 逻辑库与MySQL中的Database数据库对应⼀个逻辑库中定义了所包括的Table。 逻辑表table
既然有逻辑库就会有逻辑表。在分布式数据库中对于应用来说读写数据的表就是逻辑表。逻辑表可以分布在一个或多个分片库中也可以不分片。 注意 Table表即物理数据库中存储的某⼀张表与传统数据库不同这⾥的表格需要声明其所存储的逻辑数据节点DataNode。 分片节点DataNode
数据切分后一个大表被分到不同的分片数据库上面每个表分片所在的数据库就是分片节点dataNode。 节点主机DataHost
数据切分后每个分片节点(dataNode)不一定都会独占一台机器 同一机器上面可以有多个分片数据库这样一个或多个分片节点 (dataNode)所在的机器就是节点主机(dataHost),为了规避单节点主机并发数限制尽量将读写压力高的分片节点(dataNode)均衡的放 在不同的节点主机(dataHost)。
用户
MyCat的用户(类似于MySQL的用户支持多用户 5 Mycat原理 Mycat 的原理中最重要的一个动词是“拦截”它拦截了用户发送过来的SQL 语句。 流程 首先对 SQL 语句做了一些特定的分析如分片分析、路由分析、读写分离分析、缓存分析等然后将此 SQL 发往后端的真实数据库并将返回的结果做适当的处理最终再返回给用户。 流程示例 解析SQL语句涉及的表。查看表的定义如果表存在分片规则则获取SQL语句的分片字段。将SQL语句发送到相应的分片去执行。最后处理所有分片返回的数据并返回给客户端。
二 Mycat部署安装
1 MySQL主从复制概述 为什么要主从复制 注意 MySQL是现在普遍使用的数据库但是如果宕机了必然会造成数据丢失。为了保证MySQL数据库的可靠性。就要会一些提高可靠性的技术。 如何解决性能问题
生活中有很形象的例子比如你在自助咖啡厅买咖啡如果只有一 台咖啡机) 如果有多台咖啡机很明显大家买咖啡的效率就上去了 MySQL主从复制原理 原理 主从复制的原理则是采用binlog文件进行复制我们都知道 MySQL的数据库会产生一个二进制日志就是binlog该日志文件记录了数据的任何修改,所以我们的从机Slave会往主机读取二进制的binlog日志到本机的I/O thread中,然后写入一个Relay log文件中,从机开启一个SQL thread 定时检查Realy log 文件,如果发现有更新立即把更新的内容在本地的数据库上执行。 binlog 日志的三种格式
Statement每一条会修改数据的sql都会记录在binlog中Row: 仅保存哪条记录被修改Mixed: 以上两种的混合使用一般的语句修改用statement全表更新使用Row但是无法使用 host name。 二进制日志管理
二进制日志位置
在my.cnf文件中使用 log-bin 指定命名规则为 mysqlbin.000000 后为6位数字
mysql show variables like %log_bin% ;查看二进制日志格式
mysql show variables like %format%;查看当前使用的二进制日志文件
mysql show master status;2 搭建MySQL主从复制 安装MySQL数据库
下载YUM库
wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm安装YUM库
rpm -ivh mysql57-community-release-el7-10.noarch.rpm安装MySQL服务
yum -y install mysql-community-server遇到报错
Failing package is: mysql-community-libscompat-5.7.37-1.el7.x86_64GPG Keys are configured as:
file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql失败原因 GPG对于包的源key的验证没有通过。 解决办法
在yum install 版本后面加上 --nogpgcheck
yum -y install mysql-community-server --nogpgcheck完成安装重启mysql
systemctl restart mysqld日志文件中找出密码
grep password /var/log/mysqld.log修改密码策略
set global validate_password_policy0;
set global validate_password_policyLOW;
set global validate_password_length6;密码策略修改完以后可以修改密码
ALTER USER rootlocalhost IDENTIFIED BY 123456;开启远程连接
use mysql;
update user set host% where userroot;刷新信息 flush privileges;开启bin-log日志
修改主服务器Master
修改配置文件vim /etc/my.cnf
#主服务器唯一ID
server-id1
#启用二进制日志
log-binmysql-bin修改从服务器slave
修改配置文件vim /etc/my.cnf
#从服务器唯一ID
server-id2
#启用中继日志,从主服务器上同步日志文件记录到本地
relay-logmysql-relay重启两台服务器的mysql
service mysqld restart在主服务器上建立帐户并授权slave
mysqlGRANT REPLICATION SLAVE ON *.* to slave% identified by 123456;注意 mysql权限管理语法GRANT 权限 ON 数据库.表 TO 用户名登录主机 IDENTIFIED BY “密码” 登陆主机 字段含义%匹配所有主机 权限列表 权 限作用范围作 用replication slave服务器主从复制 一般不用root帐号“%”表示所有客户端都可能连只要帐号 密码正确此处可用具体客户端IP代替如192.168.145.226 加强安全。 查询Master的状态
mysqlshow master status;
-------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-------------------------------------------------------------------------------
| mysql-bin.000001 | 430 | | | |
------------------------------------------------------------------------------- 注意 执行完此步骤后不要再操作主服务器MYSQL防止主服务器状态值变化。 配置从服务器Slave
mysqlchange master to master_host192.168.66.111,master_userslave,master_password123456,master_log_filemysql-bin.000001,master_log_pos430;注意 注意不要断开430数字前后无单引号。 启动从服务器复制功能
mysqlstart slave;检查从服务器复制功能状态
show slave status \G;注意 Slave_IO及Slave_SQL进程必须正常运行即YES状态否则都是错误的状态(如其中一个NO均属错误)。 主从服务器测试
主服务器Mysql建立数据库并在这个库中建表插入一条数据
mysql create database test;
Query OK, 1 row affected (0.00 sec)mysql use test;
Database changedmysql create table dog(id int(3),name varchar(255));
Query OK, 0 rows affected (0.00 sec)mysql insert into dog values(001,zhuti);
Query OK, 1 row affected (0.00 sec)从服务器Mysql查询
mysqlshow databases;
mysqluse test;
mysqlselect * from dog; 3 基于Docker搭建MySQL主从复制 首先拉取Docker镜像
docker pull mysql:5.7启动两个容器
#启动Master
docker run -p 3350:3306 --name master -e MYSQL_ROOT_PASSWORD123456 -d mysql:5.7
#启动Slave
docker run -p 3340:3306 --name slave -e MYSQL_ROOT_PASSWORD123456 -d mysql:5.7 注意 –name为容器指定名称这里是master-p将容器的指定端口映射到主机的指定端口这里是将容器的3306端口映射到主机的3306 端口-e设置环境变量这里是指定root账号的密码为123456-d后台运行容器并返回容器ID 获取两个容器IP地址
[rootlocalhost mycat]# docker inspect master
[rootlocalhost mycat]# docker inspect slave检验是否启动成功
docker ps -a 显示所有的容器包括未运行的注意 是UP状态表示正在运行中。 配置Master
进入master容器内部
docker exec -it master /bin/bash修改mysqld.cnf配置
#切换目录
vim /etc/mysql/mysql.conf.d/mysqld.cnf
#修改配置文件加入
[mysqld]
## 同一局域网内注意要唯一
server-id100
## 开启二进制日志功能可以随便取关键
log-binmysql-bin注意 vi my.cnf 对my.cnf进行编辑。此时会报出 bash: vi: command not found 需要我们在docker容器内部自行安装vim。使用 apt-get install vim 命令安装vim 如果报错 Reading package lists… Done Building dependency tree Reading state information… Done E: Unable to locate package vim 更新apt-get apt-get update 安装vim apt-get install vim CentOS5.4 # apt-get install vim bash: apt-get: command not found 解答 CentOS的软件安装工具不是apt-get 是yum # yum -y install vim 重启mysql服务
service mysql restart
docker start master在Master数据库创建数据同步用户
mysql GRANT replication SLAVE ON *.* TO slave % IDENTIFIED BY 123456;配置Slave
和配置Master(主)一样在Slave配置文件mysqld.cnf中添加如下配置
#切换目录
vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
## 设置server_id,注意要唯一
server-id101
## 开启二进制日志功能以备Slave作为其它Slave的Master时使用
log-binmysql-slave-bin
## relay_log配置中继日志
relay_logedu-mysql-relay-bin注意 配置完成后也需要重启mysql服务和docker容器操作和配置 Master(主)一致。 连接接Master(主)和Slave(从)
在Master进入mysql
show master status;在Slave 中进入 mysql
mysqlchange master to master_host172.17.0.2,master_userslave,master_password123456,master_log_filemysql-bin.000001,master_log_pos443;参数 master_host: Master 的IP地址master_user: 在 Master 中授权的用于数据同步的用户master_password: 同步数据的用户的密码master_port: Master 的数据库的端口号master_log_file: 指定 Slave 从哪个日志文件开始复制数据即上文中提到的 File 字段的值master_log_pos: 从哪个 Position 开始读即上文中提到的 Position 字段的值master_connect_retry: 当重新建立主从连接时如果连接失败重试的时间间隔单位是秒默认是60秒。 在Slave中开启主从复制
mysql start slave;在 Slave 的 MySQL 终端执行查看主从同步状态
show slave status \G;4 Mycat安装以及管理命令详解 由于mycat是java语言编写所以得先安装jdk
解压jdk安装包
tar -zxvf jdk-8u201-linux-x64.tar.gz -C /usr/local配置环境变量
vim /etc/profile
底部加入如下配置
export JAVA_HOME/usr/local/jdk
export PATH$PATH:$JAVA_HOME/bin生效环境变量
source /etc/profile下载解压Mycat安装包
MyCAT 有提供编译好的安装包支持 windows、Linux、Mac、 Solaris 等系统上安装与运行。
tar -zxvf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /usr/local将MyCat配置到环境变量中
vim /etc/profile
#增加如下配置
export MYCAT_HOME/usr/local/mycat
export PATH$PATH:$MYCAT_HOME/binMyCat常用操作命令
mycat start #启动MyCat
mycat stop #停止MyCat
mycat console #前台运行MyCat带控制台输出
mycat restart #重启MyCat
mycat pause #暂停MyCat
mycat status #查看启动状态使用MySQL命令进行MyCat的连接
Mycat 提供类似数据库的管理监控方式可以通过MySQL命令行登录管理端口(9066执行相应的SQL语句进行管理也可以通过 JDBC方式进行远程连接管理。 管理端口
9066用于查看MyCat的运行状态
mysql -uroot -p123456 -h 192.168.66.111 -P9066 -DTESTDB参数 -h后面是主机。 即当前Mycat安装的主机IP地址。-uMycat server.xml中配置逻辑库的用户。-pMycat server.xml中配置逻辑库的密码。-P后面是管理端口号。注意P是大写。-dMycat server.xml中配置逻辑库。 数据端口
8066用于进行数据的CRUD操作
mysql -uroot -p123456 -h 192.168.66.111 -P8066 -DTESTDBMyCat的连接用户名相关信息在配置文件 /usr/local/mycat/conf/server.xml 中查 看编辑
user namerootproperty namepassword123456/propertyproperty nameschemasTESTDB/property
/userShow命令
-- 查看所有的可用命令
show help;
-- 查看所有的逻辑数据库
show database;
-- 查看所有的数据节点
show datanode;
-- 查看所有的数据源
show datasource;5 核心配置文件及目录结构 目录结构 说明 bin可执行命令conf配置文件lib存放jar包logs日志信息tmlogs临时日志version.txt版本信息 三个配置文件
schema.xml定义逻辑库表、分片节点等内容rule.xml定义分片规则server.xml定义用户以及系统相关变量如端口等
三 Mycat高级特性
1 读写分离概述 什么是读写分离
读写分离基本的原理是让主数据库处理事务性增、改、删操作 而从数据库处理查询操作。
为什么使用读写分离
从集中到分布最基本的一个需求不是数据存储的瓶颈而是在于计算的瓶颈即 SQL 查询的瓶颈我们知道正常情况下Insert SQL 就是几十个毫秒的时间内写入完成而系统中的大多数 Select SQL 则要几秒到几分钟才能有结果很多复杂的 SQL其消耗服务器 CPU 的能力超强不亚于死循环的威力。
读写分离方案
MyCat的读写分离是建立在MySQL主从复制基础之上实现的所以必须先搭建MySQL的主从复制。数据库读写分离对于大型系统或者访问量很高的互联网应用来说是必不可少的⼀个重要功能。 注意 Mycat实现的读写分离和自动切换机制需要MySQL的主从复制机制配合。 Mysql 主从复制的常用拓扑结构
一主一从 注意 最基础的复制结构用来分担之前单台数据库服务器的压力 可以进行读写分离。 一主多从 注意 一台 Slave 承受不住读请求压力时可以添加多台进行负载均衡分散读压力。 双主复制 注意 双主结构就是用来解决这个问题的互相将对方作为自己的 Master自己作为对方的 Slave 来进行复制但对外来讲还是一个主和一个从。 级联复制 注意 级联结构就是通过减少直接从属于 Master 的 Slave 数量减轻 Master 的压力分散复制请求从而提高整体的复制效率。 双主级联 注意 Mysql 的复制结构有很多种方式复制的最大问题是数据延时选择复制结构时需要根据自己的具体情况并评估好目标结构的延时对系统的影响。 2 搭建读写分离 修改配置文件schema.xml
schema nameTESTDB checkSQLschemafalse dataNodedb_node sqlMaxLimit100
/schema参数 schema逻辑库name 逻辑库名称sqlMaxLimit一次取多少条数据table逻辑表dataNode分片节点 对应 dataNode标签rule分片规则 对应 rule.xmlprimaryKey: 分片主键 可缓存 分片配置
dataNode namedb_node dataHostdb_host databasedb_test /参数 name分片名字dataHost节点主机database真实的数据库 配置读写分离
dataHost namehost1 maxCon1000 minCon10 balance2
writeType0 dbTypemysql dbDrivernative switchType1
slaveThreshold100writeHost host url user password/writeHost
/dataHost参数 dataHost数据主机节点主机dbType数据库驱动native:MySQL JDBC: oracle SQLServerswitchType 是否主动读 1 Balance参数设置 balance“0”, 所有读操作都发送到当前可⽤的writeHost上。balance“1”所有读操作都随机的发送到readHost。balance“2”所有读操作都随机的在writeHost、readhost上分发 WriteType参数设置 writeType“0”, 所有写操作都发送到可⽤的writeHost上。writeType“1”所有写操作都随机的发送到readHost。writeType“2”所有写操作都随机的在writeHost、readhost分上发。 witchType参数设置 switchType“1”, 主从自动切换switchType“2”从机延时超过slaveThreshold值时切换为主读 启动Mycat
mycat restart配置文件
?xml version1.0?
!DOCTYPE mycat:schema SYSTEM schema.dtd
mycat:schema xmlns:mycathttp://io.mycat/!-- 数据库配置 与server.xml 中的数据库对应 --schema nameTESTDB checkSQLschemafalse dataNodedb_node sqlMaxLimit100 /schema!-- 分片配置 --!-- dataNode的database对应物理数据库实际存在 --!-- dataNode里设置的database为我们做主从同步的数据库名 --dataNode namedb_node dataHostdb_host databasetest /!-- 物理数据库配置 --dataHost namedb_host maxCon1000 minCon10 balance1writeType0 dbTypemysql dbDrivernative switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hosthostM1 url192.168.66.111:3350 userroot password123456readHost hosthostS2 url192.168.66.111:3340 userroot password123456 //writeHost/dataHost/mycat:schema
验证读写分离
在mycat中插入
insert into dog values(1,hostname);注意 代表系统变量。 在mycat查询
select * from dog;结果为writeHost的主机名 3 MySQL双主双从原理 一主一从 注意 是最基础的复制结构用来分担之前单台数据库服务器的压力可以进行读写分离。 一主多从 问题 一台 Slave 承受不住读请求压力时可以添加多台进行负载均衡分散读压力。 如何解决 介绍 互相将对方作为自己的 Master自己作为对方的 Slave 来进行复制但对外来讲还是一个主和一个从。 4 搭建双主双从 环境准备
编号角色IP地址端口机器名1Master1192.168.66.1113350node-12Slave1192.168.66.1113360node-23Master2192.168.66.1113370node-34Slave2192.168.66.1113380node-4
创建docker容器
#启动第一台
docker run -d -p 3350:3306 -e MYSQL_ROOT_PASSWORD123456 --namemaster1 mysql:5.7
#启动第二台
docker run -d -p 3360:3306 -e MYSQL_ROOT_PASSWORD123456 --nameslave1 mysql:5.7
#启动第三台
docker run -d -p 3370:3306 -e MYSQL_ROOT_PASSWORD123456 --namemaster2 mysql:5.7
#启动第四台
docker run -d -p 3380:3306 -e MYSQL_ROOT_PASSWORD123456 --nameslave2 mysql:5.7
修改容器内MySQL配置文件
vim /etc/mysql/mysql.conf.d/mysqld.cnf
1、修改master1配置文件
[mysqld]
#主服务器唯一ID
server-id1
#启用二进制日志
log-binmysql-bin# 设置不要复制的数据库(可设置多个)
binlog-ignore-dbmysql
binlog-ignore-dbinformation_schema#设置需要复制的数据库
binlog-do-dbtest#设置logbin格式
binlog_formatSTATEMENT# 在作为从数据库的时候有写入操作也要更新二进制日志文件
# 由于主机宕机后会变成备用主机所以有写操作的时候也需要更新日志
log-slave-updates#指自增字段的起始值其默认值是1取值范围是1 .. 65535
auto-increment-increment2# 指字段一次递增多少他的取值范围是1 .. 65535
auto-increment-offset12、修改master2配置文件
[mysqld]
#主服务器唯一ID
server-id3
#启用二进制日志
log-binmysql-bin# 设置不要复制的数据库(可设置多个)
binlog-ignore-dbmysql
binlog-ignore-dbinformation_schema#设置需要复制的数据库
binlog-do-dbtest#设置logbin格式
binlog_formatSTATEMENT# 在作为从数据库的时候有写入操作也要更新二进制日志文件
# 由于主机宕机后会变成备用主机所以有写操作的时候也需要更新日志
log-slave-updates#指自增字段的起始值其默认值是1取值范围是1 .. 65535
auto-increment-increment2# 指字段一次递增多少他的取值范围是1 .. 65535
auto-increment-offset13、修改slave1配置文件
[mysqld]
#从服务器唯一ID
server-id2
#启用中继日志
relay-logmysql-relay4、修改slave2配置文件
[mysqld]
#从服务器唯一ID
server-id4
#启用中继日志
relay-logmysql-relay双主双从重启服务
service mysql restart
docker start master配置数据库
1、分别在两个主库中执行创建从库连接账号命令
GRANT replication SLAVE ON *.* TO slave % IDENTIFIED BY 123456;2、查看两主库的master状态
show master status;两个从库连接到主库
# 在slave1中执行
change master to
master_host192.168.66.111,
master_userslave,
master_port3350,
master_password123456,
master_log_filemysql-bin.000001,
master_log_pos443;# 在slave2中执行
change master to
master_host192.168.66.111,
master_userslave,
master_port3370,
master_password123456,
master_log_filemysql-bin.000001,
master_log_pos443;参数 master_host这里的ip就是mysql所在服务器对应的ipmaster_user就是在第一步配置的账号master_portmysql的端口master_password配置的密码master_log_filefile参数master_log_posPosition参数 两个从库启动复制功能
start slave;查看连接状态
show slave status \G;两个主库再互相成为对方的从库
# 在master1上执行
change master to
master_host 192.168.66.111,
master_user slave,
master_password 123456,
master_port 3370,
master_log_file mysql-bin.000001,
master_log_pos 443;# 在master2上执行
change master to
master_host 192.168.66.111,
master_user slave,
master_password 123456,
master_port 3350,
master_log_file mysql-bin.000001,
master_log_pos 443;两个主库启动复制功能
start slave;查看连接状态
show slave status \G;双主双从配置MyCat
vim schema.xml
?xml version1.0?
!DOCTYPE mycat:schema SYSTEM schema.dtd
mycat:schema xmlns:mycathttp://io.mycat/!-- 数据库配置 与server.xml 中的数据库对应 --schema nameTESTDB checkSQLschemafalse dataNodedb_node sqlMaxLimit100 /schema!-- 分片配置 --dataNode namedb_node dataHostdb_host databasetest /!-- 物理数据库配置 --dataHost namedb_host maxCon1000 minCon10 balance1 writeType0 dbTypemysql dbDrivernative switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hosthostM1 url192.168.66.111:3350 userroot password123456readHost hosthostS1 url192.168.66.111:3360 userroot password123456 //writeHostwriteHost hosthostM2 url192.168.66.111:3370 userroot password123456readHost hosthostS2 url192.168.66.111:3380 userroot password123456 //writeHost/dataHost/mycat:schema 注意 writeType“0”所有写操作发送到配置的第一个 writeHost第一个挂了切到还生存的第二个writeHost重新启动后已切换后的为准切换记录在配置文件中:dnindex.properties.writeType“1”所有写操作都随机的发送到配置的 writeHost1.5 以后废弃不推荐。switchType“-1” 表示不自动切换 mysql 实例switchType“1” 默认值自动切换 四 Mycat分片技术
1 垂直拆分 前沿
目前很多互联网系统都存在单表数据量过大的问题这就降低了查询速度影响了客户体验。为了提高查询速度我们可以优化sql语句优化表结构和索引不过对那些百万级千万级的数据库表即便是优化过后查询速度还是满足不了要求。
垂直分割
垂直拆分是指数据表列的拆分把一张列比较多的表拆分为多张表。表的记录并不多但是字段却很长表占用空间很大检索表的时候需要执行大量的IO严重降低了性能。这时需要把大的字段拆分到另一个表并且该表与原表是一对一的关系。 拆分原则 把不常用的字段单独放在一张表把textblob等大字段拆分出来放在附表中经常组合查询的列放在一张表中 举个例子 拆分思路 如果我们只想查询id为8的学生的分数select 分数 from 答题表 where id 8;虽然知识查询分数但是题目和回答这两个大字段也是要被扫描的很消耗性能。但是我们只关心分数并不想查询题目和回答。这就可以使用垂直分割。 实现分库
环境准备
编号IP地址端口机器名1192.168.66.1113350node-12192.168.66.1113360node-2
创建docker容器
#启动第一台
docker run -d -p 3350:3306 -e MYSQL_ROOT_PASSWORD123456 --namenode-1 mysql:5.7
#启动第二台
docker run -d -p 3360:3306 -e MYSQL_ROOT_PASSWORD123456 --namenode-2 mysql:5.7修改schema.xml配置文件
?xml version1.0?
!DOCTYPE mycat:schema SYSTEM schema.dtd
mycat:schema xmlns:mycathttp://io.mycat/schema nameTESTDB checkSQLschemafalse dataNodedn1 sqlMaxLimit100table namecustomer dataNodedn2//schemadataNode namedn1 dataHosthost1 databaseorders /dataNode namedn2 dataHosthost2 databaseorders /dataHost namehost1 maxCon1000 minCon10 balance0writeType0 dbTypemysql dbDrivernative switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hosthostM1 url192.168.66.111:3350 userroot password123456/writeHost/dataHostdataHost namehost2 maxCon1000 minCon10 balance0writeType0 dbTypemysql dbDrivernative switchType1 slaveThreshold100heartbeatselect user()/heartbeatwriteHost hosthostM2 url192.168.66.111:3360 userroot password123456/writeHost/dataHost/mycat:schema
新增两个空白库
分库操作不是在原来的老数据库上进行操作需要准备两台机器分别安装新的数据库
#在数据节点 dn1、dn2 上分别创建数据库 orders
CREATE DATABASE orders;启动Mycat
mycat startMycat进行分库
创建表
mysql -uroot -p123456 -h 192.168.66.111 -P8066mysql use TESTDB;# 使用 mycat 创建四个表
create table customer(id int auto_increment,name varchar(200),primary key(id)
);
create table orders(id int auto_increment,order_type int,customer_id int,amount decimal(10,2),primary key(id)
);
create table orders_detail(id int auto_increment,order_id int,detail varchar(200),primary key(id)
);
create table dict_order_type(id int auto_increment,order_type varchar(200),primary key(id)
);查看结果
在node-1中
mysql use orders;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql show tables;
------------------
| Tables_in_orders |
------------------
| dict_order_type |
| orders |
| orders_detail |
------------------
3 rows in set (0.00 sec)在node-2中
mysql use orders;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql show tables;
------------------
| Tables_in_orders |
------------------
| customer |
------------------
1 row in set (0.00 sec)2 水平拆分 前言
上面谈到垂直切分只是把表按模块划分到不同数据库但没有解决单表大数据量的问题。
水平拆分
相对于垂直拆分水平拆分不是将表做分类而是按照某个字段的某种规则来分散到多个库之中每个表中包含一部分数据。 理解 我们可以将数据的水平切分理解为是按照数据行的切分就是将表中的某些行切分到一个数据库而另外的某些行又切分到其他的数据库中。 实现分表
1、 选择要拆分的表
MySQL 单表存储数据条数是有瓶颈的单表达到 1000 万条数据就达到了瓶颈会影响查询效率需要进行水平拆分分表进行优化。
2、分表字段
以 orders 表为例可以根据不同字段进行分表
编号分表字段效果1id查询订单注重时效历史订单被查询的次数少如此分片会造成一个节点访问多一个访问少不平均。2customer_id根据客户 id 去分两个节点访问平均
3、 修改配置文件 schema.xml
为 orders 表设置数据节点为 dn1、dn2并指定分片规则为 mod_rule自定义的名字
table nameorders dataNodedn1,dn2 rulemod_rule /table4、修改配置文件rule.xml 分片规则 在 rule 配置文件里新增分片规则 mod_rule并指定规则适用字段为 customer_id 还有选择分片算法 mod-long对字段求模运算customer_id 对两个节点求模根据结果分片配置算法 mod-long 参数 count 为 2两个节点。 tableRule namemod_rulerulecolumnscustomer_id/columnsalgorithmmod-long/algorithm/rule
/tableRule
function namemod-long classio.mycat.route.function.PartitionByMod!-- 物理主机节点数量 --property namecount2/property
/function5、 在数据节点dn2上建orders表注意承接上文中的环境因为dn1中已经有orders表
create table orders(id int auto_increment,order_type int,customer_id int,amount decimal(10,2),primary key(id)
);6、 重启Mycat让配置生效
mycat restart7、 访问Mycat实现分片
mysql -uroot -p123456 -h 192.168.66.111 -P8066mysql use TESTDB;INSERT INTO
orders(id,order_type,customer_id,amount)
VALUES (1,101,100,100100);
INSERT INTO
orders(id,order_type,customer_id,amount)
VALUES(2,101,100,100300);
INSERT INTO
orders(id,order_type,customer_id,amount)
VALUES(3,101,101,120000);
INSERT INTO
orders(id,order_type,customer_id,amount)
VALUES(4,101,101,103000);
INSERT INTO
orders(id,order_type,customer_id,amount)
VALUES(5,102,101,100400);
INSERT INTO
orders(id,order_type,customer_id,amount)
VALUES(6,102,100,100020);
查看结果
在node-1中
mysql select * from orders;
----------------------------------------
| id | order_type | customer_id | amount |
----------------------------------------
| 1 | 101 | 100 | 100100.00 |
| 2 | 101 | 100 | 100300.00 |
| 6 | 102 | 100 | 100020.00 |
----------------------------------------
3 rows in set (0.00 sec)在node-2中
mysql select * from orders;
----------------------------------------
| id | order_type | customer_id | amount |
----------------------------------------
| 3 | 101 | 101 | 120000.00 |
| 4 | 101 | 101 | 103000.00 |
| 5 | 102 | 101 | 100400.00 |
----------------------------------------
3 rows in set (0.00 sec)3 ER表 分片 join 遇到问题 orders表分片了,那和他相关的orders_detail 表未分片, join联查的时候, node1正常查询出结果, node2上由于没有 orders_detail 表,则报错, 最后聚合结果肯定也是错误的。 ER 表 将子表的存储位置依赖于主表并且物理上紧邻存放因此彻底解决了JION的效率和性能问题根据这一思路提出了基于E-R关系的数据分片策略子表的记录与所关联的父表记录存放在同一个数据分片上。
修改配置文件schema.xml
table nameorders dataNodedn1,dn2 rulemod_rule childTable nameorders_detail primaryKeyid joinKeyorder_id parentKeyid /
/table参数 childTable子表 node2上没有订单详情表创建一下
create table orders_detail(
id int auto_increment,
order_id int,
detail varchar(200),
primary key(id)
);重启Mycat服务
mycat restartMycat服务添加数据
mysql -uroot -p123456 -h 192.168.66.111 -P8066mysql use TESTDB;insert into orders_detail(detail, order_id)
values(detail1,1);
insert into orders_detail(detail, order_id)
values(detail1,2);
insert into orders_detail(detail, order_id)
values(detail1,3);
insert into orders_detail(detail, order_id)
values(detail1,4);
insert into orders_detail(detail, order_id)
values(detail1,5);测试数据
select a.*,b.detail from orders a join orders_detail b on a.idb.order_id;4 全局表 全局表描述
在分片的情况下当业务表因为规模而进行分片以后业务表与这些附属的字典表之间的关联就成了比较棘手的问题考虑到字典表具有以下几个特性
变动不频繁数据量总体变化不大数据规模不大很少有超过数十万条记录。
全局表特征
全局表的插入更新操作会实时在所有节点上执行,保持各个分片的数据一致。全局表的查询操作,只从一个节点获取。全局表可以和任何一个表进行 JOIN 操作。 注意 将字典表或者符合字典表特性的一些表定义为全局表则从另外一个方面很好的解决了数据JOIN 的难题。通过全局表基于E-R 关系的分片策略Mycat 可以满足 80%以上的企业应用开发。 配置全局表
!--全局表配置比较简单不用写 Rule 规则修改schema.xml如下配置即可--
table namedict_order_type dataNodedn1,dn2 typeglobal/table在dn2创建 dict_order_type 表
#订单类型字典表
create table dict_order_type(id int auto_increment,order_type varchar(200),primary key(id)
);
重启Mycat
mycat restartMycat上添加数据
mysql -uroot -p123456 -h 192.168.66.111 -P8066mysql use TESTDB;insert into
dict_order_type(id,order_type)values(101,type1);
insert into
dict_order_type(id,order_type)values(102,type2);
测试数据
select * from dict_order_type;五 Mycat分片规则
1 取模 实现方式
取模分片就是根据数据表的某一个字段通常是某一个整数型的字段对其进行十进制的求模运算将运算结果作为Mycat的路由结果。 注意 优点这种策略可以很好的分散数据库写的压力。 缺点出现了范围查询就需要MyCAT去合并结果当数据量偏高的时候这种跨库查询 合并结果消耗的时间有可能会增加很多尤其是还出现了order by的时候。 tableRule 标签
这个标签定义表规则。
tableRule namemod-longrulecolumnsuser_id/columnsalgorithmmod-long/algorithm/rule
/tableRule参数 name 属性指定唯一的名字用于标识不同的表规则。 内嵌的 rule 标签则指定对物理表中的哪一列进行拆分和使用什么路由算法。columns 内指定要拆分的列名字。algorithm使用 function 标签中的 name 属性。连接表规则和具体路由算法。当然多个表规则可以连接到同一个路由算法上。 function标签
定义具体路由算法
function namemod-long classio.mycat.route.function.PartitionByMod!-- how many data nodes --property namecount2/property
/function注意 name 指定算法的名字。class 制定路由算法具体的类名字。property 为具体算法需要用到的一些属性。count表示需要取模的最大值将数据分成该配置的切片。 2 分片枚举 实现原理
有些业务需要按照省份或区县来做保存这类业务使用本条规则。
实现过程
在这里需定义三个值规则均是在rule.xml中定义。
tableRulefunctionmapFile
创建示例表
#订单归属区域信息表
CREATE TABLE orders_ware_info(id INT AUTO_INCREMENT comment 编号,order_id INT comment 订单编号,address VARCHAR(200) comment 地址,areacode VARCHAR(20) comment 区域编号,PRIMARY KEY(id)
);修改schema.xml配置文件
table nameorders_ware_info dataNodedn1,dn2 rulesharding-by-intfile/table定义tableRule
tableRule namesharding-by-intfilerulecolumnsareacode/columnsalgorithmhash-int/algorithm/rule
/tableRule注意 其中sharding-by-intfile是规则名会在schema.xml中 用到。columns指的是对省份进行分片。algorithm是算法名 该算法必须在function中定义。 定义funtion
function namehash-int classio.mycat.route.function.PartitionByFileMapproperty namemapFilepartition-hashint.txt/propertyproperty nametype1/propertyproperty namedefaultNode0/property/function注意 mapFile指的是配置文件名type默认值为00表示Integer非零表示String。因为我接下来的测试是基于省份分片 所以需type指定为1。defaultNode 默认节点小于0表示不设置默认节点大于等于0表示设置默认节点 默认节点的作用枚举分片时如果碰到不识别的枚举值就让它路由到默认节点。 修改partition-hash-int.txt配置文件
1100
1201注意 其中110会被分发到第一个节点中120分发被第二个节点中。 重启Mycat
mycat restart插入数据
mysql -uroot -p123456 -h 192.168.66.111 -P8066mysql use TESTDB;INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (1,1,beijing,110);
INSERT INTO orders_ware_info(id,order_id,address,areacode) VALUES (2,2,tianjing,120);3 范围约定 实现原理
此分片适用于提前规划好分片字段某个范围属于哪个分片。
举个例子
比如将id在0-500W的数据分片在第一个节点上面将id在500W1000W的数据分片在第二个节点上依次类推下去。 优缺点 优点适用于想明确知道某个分片字段的某个范围具体在哪一个节点缺点如果短时间内有大量的批量插入操作那么某个分片节点可能一下子会承受比较大的 数据库压力而别的分片节点此时可能处于闲置状态无法利用其它节点进行分担压力热点数据问题); 实现过程
创建示例表
#支付信息表
CREATE TABLE payment_info
(
id INT AUTO_INCREMENT comment 编号,
order_id INT comment 订单编号,
payment_status INT comment 支付状态,
PRIMARY KEY(id)
);修改schema.xml配置文件
table namepayment_info dataNodedn1,dn2 ruleauto-sharding-long /table定义tableRule
tableRule nameauto-sharding-longrulecolumnsorder_id/columnsalgorithmrang-long/algorithm/rule
/tableRule注意 其中auto_sharding_long是规则名会在schema.xml中用到。columns指的是对订单id进行分片。algorithm是算法名 该算法必须在function中定义。 定义function
function namerang-long classio.mycat.route.function.AutoPartitionByLongproperty namemapFileautopartitionlong.txt/propertyproperty namedefaultNode0/property
/function 注意 mapFile指的是配置文件名type默认值为00表示Integer非零表示String。defaultNode 默认节点小于0表示不设置默认节点大于等于0表示设置默认节点 修改autopartition-long.txt配置文件
0-1020
103-2001重启Mycat
mycat restart插入数据
mysql -uroot -p123456 -h 192.168.66.111 -P8066mysql use TESTDB;INSERT INTO payment_info
(id,order_id,payment_status) VALUES (1,101,0);
INSERT INTO payment_info
(id,order_id,payment_status) VALUES (2,102,1);
INSERT INTO payment_info
(id,order_id,payment_status) VALUES (3,103,0);
INSERT INTO payment_info
(id,order_id,payment_status) VALUES(4,104,1);4 按日期天分片 实现原理
此规则为按天分片设定时间格式、范围。
实现过程
创建示例表
#用户信息表
create table login_info(id int auto_increment comment 编号,user_id int comment 用户编号,login_date date comment 登录时间,primary key(id)
);修改schema.xml配置文件
table namelogin_info dataNodedn1,dn2 rulesharding-by-date /table修改rule.xml配置文件
tableRule namesharding-by-daterulecolumnslogin_date/columnsalgorithmshardingByDate/algorithm/rule
/tableRule参数: columns分片字段algorithm分片函数 定义function
function nameshardingByDate classio.mycat.route.function.PartitionByDateproperty namedateFormatyyyy-MM-dd/propertyproperty namesBeginDate2040-01-01/propertyproperty namesEndDate2040-01-04/propertyproperty namesPartionDay2/property
/function参数 dateFormat 日期格式sBeginDate 开始日期sEndDate结束日期,则代表数据达到了这个日期的分片后循环从开始分片插入sPartionDay 分区天数即默认从开始日期算起分隔 2 天一个分区 重启Mycat
mycat restart插入数据
mysql -uroot -p123456 -h 192.168.66.111 -P8066mysql use TESTDB;insert into login_info(id,user_id,login_date)
values(1,101,2040-01-01);
insert into login_info(id,user_id,login_date)
values(2,102,2040-01-02);
insert into login_info(id,user_id,login_date)
values(3,103,2040-01-03);
insert into login_info(id,user_id,login_date)
values(4,104,2040-01-04);
insert into login_info(id,user_id,login_date)
values(5,105,2040-01-05);
insert into login_info(id,user_id,login_date)
values(6,106,2040-01-06);
5 全局序列 在实现分库分表的情况下数据库自增主键已无法保证全局唯一。
解决方案
本地文件
此方式Mycat将sequence配置到文件中当使用到 sequence中的配置后Mycat会更下classpath中的 sequence_conf.properties 文件中sequence当前的值。 注意 优点本地加载读取速度较快缺点抗风险能力差Mycat 所在主机宕机后无法读取本地文件。 本地时间戳方式
全局序列ID64位二进制 (42(毫秒)5(机器 ID)5(业务编码)12(重 复累加) 换算成十进制为18位数的long类型每毫秒可以并发12位 二进制的累加。 **优缺点 ** 优点配置简单缺点18位ID过长 数据库方式
利用数据库一个表来进行计数累加。但是并不是每次生成序列都读写数据库这样效率太低。Mycat 会预加载一部分号段到 Mycat 的内存中这样大部分读写序列都是在内存中完成的。如果内存中的号段用完了 Mycat 会再向数据库要一次。 原理 在数据库中建立一张表存放 sequence 名称(name) sequence 当前值(current_value)步长(increment int 类型每次读取多少个 sequence假设为 K)等信息 数据库解决全局序列
修改Mycat配置文件server.xml
# 全局序列类型0-本地文件1-数据库方式2-时间戳方式。此处应该修改成1。
property namesequnceHandlerType1/property修改Mycat配置文件schema.xml
table nametest primaryKeyid autoIncrementtrue dataNodedn1,dn2 rulemod-long/
table namemycat_sequence primaryKeyname dataNodedn2/修改Mycat 配置文件 sequence_db_conf.properties
最下面添加MYCATdn2
GLOBALdn1
COMPANYdn1
CUSTOMERdn1
ORDERSdn1
MYCATdn2在dn2节点的orders数据库中添加 MYCAT_SEQUENCE表
DROP TABLE IF EXISTS MYCAT_SEQUENCE;CREATE TABLE MYCAT_SEQUENCE (name VARCHAR(50) NOT NULL,current_value INT NOT NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(name))
ENGINEInnoDB;MYCAT_SEQUENCE 表插入sequence初始记录
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES (mycat, -99, 100);注意 代表插入了一个名为mycat的sequence当前值为-99步长为 100。 创建全局序列所需存储过程
#获取当前sequence的值
DELIMITER $$
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50))
RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval-999999999,null;
SELECT CONCAT(CAST(current_value AS CHAR),,,CAST(increment AS CHAR))
INTO retval
FROM MYCAT_SEQUENCE
WHERE NAME seq_name;
RETURN retval;
END $$
DELIMITER ;#设置sequence值
DELIMITER $$
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER)
RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value VALUE
WHERE NAME seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;#获取下一个sequence值
DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50))
RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value current_value increment
WHERE NAME seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;重启Mycat
mycat restart向dn1,dn2添加test表
create table test(id int,name varchar(10));在Mycat中向test表中添加测试数据
mysql -uroot -p123456 -h 192.168.66.111 -P8066mysql use TESTDB;insert into test(id,name) values(next value for MYCATSEQ_MYCAT,(select database()));查询数据验证
SELECT * FROM test order by id asc;六 Mycat高可用
1 Mycat高可用概述 MyCat实现读写分离架构
通过MyCat来实现MySQL的读写分离, 从而完成MySQL集群的负载均衡 , 如下面的结构图: 问题 但是以上架构存在问题 , 由于MyCat中间件是单节点的服务, 前端客户端所有的压力过来都直接请求这一台MyCat , 存在单点故障。所以这个时候 我们就需要考虑MyCat的集群 MyCat集群架构
通过MyCat来实现后端MySQL的负载均衡 通过HAProxy再实现 MyCat集群的负载均衡。 介绍 HAProxy负责将请求分发到MyCat上起到负载均衡的作用 同时 HAProxy也能检测到MyCat是否存活HAProxy只会将请求转发到存活的 MyCat 上。如果一台MyCat服务器宕机 HAPorxy 转发请求时不会转发到宕机的MyCat 上所以 MyCat 依然可用。 HAProxy介绍
HAProxy是一个开源的、高性能的基于TCP(第四层)和HTTP(第七层) 应用的负载均衡软件。 使用HAProxy可以快速、可靠地实现基于 TCP与HTTP应用的负载均衡解决方案。 问题 因为所以的客户端请求都是先到达HAProxy, 由HAProxy再将请求再向下分发, 如果HAProxy宕机的话, 就会造成整个MyCat集 群不能正常运行, 依然存在单点故障。 MyCat的高可用集群 图解说明 HAProxy实现了MyCat多节点的集群高可用和负载均衡而HAProxy自身的高可用则可以通过Keepalived来实现。因此HAProxy主机上要同时安装 HAProxy和Keepalived Keepalived负责为该服务器抢占vip虚拟 ip抢占到vip后对该主机的访问可以通过原来的ip访问也可以直接通过vip访问。HAProxy负责将对vip的请求分发到MyCat集群节点上起到负载均衡的作用。同时HAProxy 也能检测到MyCat是否存活HAProxy只会将请求转发到存活的MyCat 上。如果KeepalivedHAProxy高可用集群中的一台服务器宕机集群中另外一台服务器上的 Keepalived会立刻抢占vip 并接管服务此时抢占了 vip 的HAProxy节点可以继续提供服务。如果一台MyCat服务器宕机HAPorxy 转发请求时不会转发到宕机的 MyCat 上所以 MyCat 依然可用。 keepalived介绍
Keepalived是一种基于VRRP协议来实现的高可用方案,可以利用其 来避免单点故障。 通常有两台甚至多台服务器运行Keepalived一 台为主服务器(Master), 其他为备份服务器, 但是对外表现为一个虚 拟IP(VIP), 主服务器会发送特定的消息给备份服务器, 当备份服务器 接收不到这个消息时, 即认为主服务器宕机, 备份服务器就会接管虚 拟IP, 继续提供服务, 从而保证了整个集群的高可用。
2 安装配置HAProxy 安装配置HAProxy
查看列表
yum list | grep haproxyyum安装
yum -y install haproxy修改配置文件
$ vim /etc/haproxy/haproxy.cfg启动HAProxy
$ haproxy -f /etc/haproxy/haproxy.cfgHAProxy配置文件
HAProxy配置文件主要由全局设定和代理设定两部分组成包含5个 域global、default、frontend、backend、listen。
global
# 全局配置定义haproxy进程的工作特性和全局配置
globallog 127.0.0.1 local2chroot /var/lib/haproxy #chroot运行的路径pidfile /var/run/haproxy.pid #haproxy pid的存放位置maxconn 4000 #最大连接数user haproxy #haproxy的运行用户group haproxy #haproxy的运行用户的所属组daemon #守护进程的方式在后台工作# turn on stats unix socketstats socket /var/lib/haproxy/stats注意 全局配置通常是一些进程级别的配置与操作系统相关。 default
defaultsmode http #默认使用的七层协议也可以是tcp四层协议如果配置为health则表示健康检查返回oklog globaloption httplog #详细记录tcp日志option dontlognull #不记录健康检查的日志信息option http-server-closeoption forwardfor except 127.0.0.0/8option redispatchretries 3 #重试次数为3次失败3次以后则表示服务不可用timeout http-request 10s #http请求超时时间客户端建立连接5s但不请求数据的时候关闭客户端连接timeout queue 1m #等待最大时间表示等待最大时长为1stimeout connect 10s #连接超时时间表示客户端请求转发至服务器所等待的时长为10stimeout client 1m #客户端超时时间表示客户端非活跃状态的时间为1mintimeout server 1m #服务器超时时间表示客户端与服务器建立连接后等待服务器的超时时间为1mintimeout http-keep-alive 10s #持久连接超时时间表示保持连接的超时时长为10stimeout check 10s #心跳检测超时时间表示健康状态监测时的超时时间为10smaxconn 3000参数 默认参数配置主要是涉及的公共配置在 defaults 中一次性添加。 frontend 、 backend 、 listen 未配置时都可以默认 defaults 中的参数配置。若配置了会覆盖。 frontend backend frontend main *:5000acl url_static path_beg -i /static /images /javascript /stylesheetsacl url_static path_end -i .jpg .gif .png .css .jsuse_backend static if url_staticdefault_backend appbackend staticbalance roundrobinserver static 127.0.0.1:4331 checkbackend appbalance roundrobinserver app1 127.0.0.1:5001 checkserver app2 127.0.0.1:5002 checkserver app3 127.0.0.1:5003 checkserver app4 127.0.0.1:5004 check参数 frontend 可以看作是前端接收请求的部分内部指定后端backend 可以看作是后端服务接收请求的部分 listen listen 是frontend和backend的组合haproxy的监控ui可以通过这个进行配置。 向配置文件中插入以下配置信息,并保存
globallog 127.0.0.1 local0#log 127.0.0.1 local1 notice#log loghost local0 infomaxconn 4096chroot /var/lib/haproxypidfile /var/run/haproxy.pid#uid 99#gid 99daemon#debug#quietdefaultslog globalmode tcpoption abortoncloseoption redispatchretries 3maxconn 2000timeout connect 5000timeout client 50000timeout server 50000listen proxy_statusbind :48066mode tcpbalance roundrobinserver mycat_1 192.168.66.111:8066 check inter 10sserver mycat_2 192.168.66.112:8066 check inter 10sfrontend admin_statsbind :7777mode httpstats enableoption httplogmaxconn 10stats refresh 30sstats uri /adminstats auth admin:123123stats hide-versionstats admin if TRUE启动验证
haproxy -f /etc/haproxy/haproxy.cfg查看HAProxy进程
ps -ef|grep haproxy打开浏览器访问
http://192.168.66.111:7777/admin
在弹出框输入用户名
admin密码123123验证负载均衡通过HAProxy访问Mycat
mysql -uroot -p123456 -h 192.168.66.111 -P 480663 安装配置Keepalived 高可用架构 查看列表
yum list | grep keepalivedyum安装
yum install -y keepalived查看yum安装的配置文件
rpm -ql keepalived修改主节点配置文件
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {router_id baizhan
}
vrrp_script chk_haproxy {script /etc/keepalived/haproxy_check.shinterval 2weight 2
}
vrrp_instance VI_1 {state MASTERinterface ens33virtual_router_id 35priority 120nopreemptadvert_int 1authentication {auth_type PASSauth_pass 1111}track_script {chk_haproxy}virtual_ipaddress {192.168.66.200}
}带注释如下
! Configuration File for keepalived
global_defs {
## keepalived 自带的邮件提醒需要开启 sendmail 服务。建议用独立的监控或第三方 SMTProuter_id baizhan ## 标识本节点的字条串通常为 hostname
}## keepalived 会定时执行脚本并对脚本执行的结果进行分析动态调整 vrrp_instance 的优先级。
## 如果脚本执行结果为 0并且 weight 配置的值大于0则优先级相应的增加。
## 如果脚本执行结果非 0并且 weight 配置的值小于0则优先级相应的减少。
## 其他情况维持原本配置的优先级即配置文件中priority 对应的值。
vrrp_script chk_haproxy {script /etc/keepalived/haproxy_check.sh ## 检测haproxy 状态的脚本路径interval 2 ## 检测时间间隔weight 2 ## 如果条件成立权重2
}## 定义虚拟路由 VI_1 为虚拟路由的标示符自己定义名称
vrrp_instance VI_1 {state MASTER ## 默认主设备priority 值大的和备用设备priority 值小的都设置为 BACKUP## 由 priority 来控制同时启动情况下的默认主备否则先启动的为主设备interface ens33 ## 绑定虚拟 IP 的网络接口与本机 IP 地址所在的网络接口相同virtual_router_id 35 ## 虚拟路由的 ID 号两个节点设置必须一样可选 IP 最后一段使用,## 相同的 VRID 为一个组他将决定多播的 MAC 地址priority 120 ## 节点优先级值范围 0-254MASTER 要比 BACKUP 高nopreempt ## 主设备priority 值大的配置一定要加上 nopreempt否则非抢占也不起作用advert_int 1 ## 组播信息发送间隔两个节点设置必须一样默认 1s## 设置验证信息两个节点必须一致authentication {auth_type PASSauth_pass 1111 ## 真实生产按需求对应该过来}## 将 track_script 块加入 instance 配置块track_script {chk_haproxy ## 检查 HAProxy 服务是否存活}## 虚拟 IP 池, 两个节点设置必须一样virtual_ipaddress {192.168.66.200}
}修改备用节点配置文件
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {router_id baizhan
}
vrrp_script chk_haproxy {script /etc/keepalived/haproxy_check.shinterval 2weight 2
}
vrrp_instance VI_1 {state BACKUPinterface ens33virtual_router_id 35priority 110advert_int 1authentication {auth_type PASSauth_pass 1111}track_script {chk_haproxy}virtual_ipaddress {192.168.66.200}
}
编写 Haproxy 状态检测脚本
我们编写的脚本为/etc/keepalived/haproxy_check.sh (已在 keepalived.conf 中配置)
脚本要求如果 haproxy 停止运行尝试启动如果无法启动则杀死本机的 keepalived 进程keepalied将虚拟 ip 绑定到 BACKUP 机器上。
内容如下
mkdir -p /usr/local/keepalived/log
vi /etc/keepalived/haproxy_check.sh
#!/bin/bash
START_HAPROXY/usr/sbin/haproxy start
STOP_HAPROXY/usr/sbin/haproxy stop
LOG_FILE/usr/local/keepalived/log/haproxycheck.log
HAPSps -C haproxy --no-header |wc -l
date %Y-%m-%d %H:%M:%S $LOG_FILE
echo check haproxy status $LOG_FILE
if [ $HAPS -eq 0 ];then
echo $START_HAPROXY $LOG_FILE
$START_HAPROXY $LOG_FILE 21
sleep 3
if [ ps -C haproxy --no-header |wc -l -eq
0 ];then
echo start haproxy failed, killall
keepalived $LOG_FILE
killall keepalived
fi
fi注意添加权限 chmod x /etc/keepalived/haproxy_check.sh启动Keepalived
service keepalived start七 Mycat安全设置
1 user标签权限控制 目前 Mycat 对于中间件的连接控制并没有做太复杂的控制目前只做了中间件逻辑库级别的读写权限控制。是通过 server.xml 的 user 标签进行配置。
#server.xml配置文件user部分
user namemycatproperty namepassword123456/propertyproperty nameschemasTESTDB/property
/user
user nameuserproperty namepassworduser/propertyproperty nameschemasTESTDB/propertyproperty namereadOnlytrue/property
/user 参数 name应用连接中间件逻辑库的用户名password该用户对应的密码schemas 应用当前连接的逻辑库。readOnly应用连接中间件逻辑库所具有的权限。true 为只读false 为读写都有默认为 false 测试案例
使用user用户权限为只读readOnlytrue验证是否可以查 询出数据验证是否可以写入数据。
1、用user用户登录运行命令如下 mysql -uuser -puser -h 192.168.140.128 -P80662、切换到TESTDB数据库查询orders表数据如下
use TESTDB
select * from orders;3、执行插入数据sql
insert into
orders(id,order_type,customer_id,amount)
values(7,101,101,10000);4、可看到运行结果插入失败只有只读权限
mysql insert into orders(id,order_type,customer_id,amount) values(7,101,101,10000);
ERROR 1495 (HY000): User readonly2 privileges标签权限控制 在 user 标签下的 privileges 标签可以对逻辑库schema、表 table进行精细化的 DML 权限控制。
!--server.xml配置文件privileges部分--
!--#配置orders表没有增删改查权限--
user namemycatproperty namepassword123456/propertyproperty nameschemasTESTDB/property!-- 表级 DML 权限设置 --privileges checktrueschema nameTESTDB dml1111 table nameorders dml0000/table!--table nametb02 dml1111/table--/schema/privileges
/user
配置说明
DML权限新增更新查询删除0000禁止禁止禁止禁止0010禁止禁止可以禁止1110可以禁止禁止禁止1111可以可以可以可以
测试案例
使用mycat用户privileges配置orders表权限为禁止增删改查 dml“0000” 验证是否可以查询出数据验证是否可以写入数据。
1、重启mycat用mycat用户登录运行命令如下
mysql -umycat -p123456 -h 192.168.66.101 -P80662、切换到TESTDB数据库查询orders表数据如下
use TESTDB
select * from orders;3、禁止该用户查询数据
mysql use TESTDB
Database changed
mysql select* from orders;
ERROR 3012(HY000): The statement DML privilege check is not passed,reject for user
mycat4、执行插入数据sql如下
insert into orders(id,order_type,customer_id,amount) values(8,101,101,10000);5、可看到运行结果禁止该用户插入数据
mysql insert into orders(id,order_type,customer_id,amount) values(8,101,101,10000);
ERROR 3012 (HY000): The statement DMLprivilege check is not passedreject for use3 SQL拦截白名单 firewall标签用来定义防火墙firewall下whitehost标签用来定义IP 白名单 blacklist 用来定义SQL 黑名单。
白名单
可以通过设置白名单实现某主机某用户可以访问 Mycat而其他主机用户禁止访问。
#设置白名单
#server.xml配置文件firewall标签
#配置只有192.168.66.101主机可以通过mycat用户访问
firewallwhitehosthost host192.168.66.101 usermycat//whitehost
/firewall4 SQL拦截黑名单 可以通过设置黑名单实现Mycat对具体SQL操作的拦截如增删改查等操作的拦截。
设置黑名单
#server.xml配置文件firewall标签
#配置禁止mycat用户进行删除操作
firewallwhitehosthost host192.168.140.128 usermycat//whitehostblacklist checktrueproperty namedeleteAllowfalse/property
/blacklist
/firewall
黑名单 SQL 拦截功能列表 八 Mycat性能监控
1 Mycat-web安装 Mycat-web是Mycat可视化运维的管理和监控平台。Mycat-web引 入了 ZooKeeper作为配置中心可以管理多个节点。 注意 Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和 内存等具备IP白名单、邮件告警等模块还可以统计SQL并分 析慢SQL和高频 SQL等。为优化SQL提供依据。 Docker安装Zookeeper
docker run --name myzk -d -p 2181:2181 zookeeper解压Mycat-web
tar -zxvf Mycat-web-1.0-SNAPSHOT20170102153329-linux.tar.gz -C /usr/local设置Zookeeper的地址
在/usr/local/mycat-web/mycat-web/WEBINF/classes/mycat.properties文件中配置 zookeeper192.168.66.101:2181进入mycat-web的目录下运行启动命令
cd /usr/local/mycat-web/
./start.sh 查看服务已经启动
lsof -i:8082通过地址访问服务 http://192.168.66.101:8082/mycat/九 Mycat性能优化 JVM调优
Java堆内存和直接内存映射建议堆内存大小适度直接映射的内存尽可能大总计占用操作系统50%67%的内存。
示例
下面以16GB内存的服务器为例Mycat堆内存为4GB直接内存映 射为6GB修改文件 /usr/local/mycat/bin/startup_nowrap.sh
JAVA_OPTS-server -Xms4G -Xmx2G -XX:MaxPermSize64M -XX:AggressiveOpts -XX:MaxDirectMemorySize6G
Mycat调优
MyCAT所有的调优参数都可以才server.xml中找到
主要讨论两个内容:
processors数值的影响范围
systemproperty nameprocessors1/propertyproperty nameprocessorExecutor16/property
/system参数 processorsCPU核心数越多可以越大当发现系统CPU压力很小的情况下可以适当调 大此参数如4核心的4CPU可以设置为1624核的可以最大设置为128processorExecutor每个processor的线程池大小建议可以是16-64根据系统能力来测试和确定。 设置连接数
dataHost namelocalhostl maxCon500 mincon10 balanceO
dbTypeMySQLdbDrivernative banlance0注意 最大连接池maxCon的值可以改为1000~2000同一个MySQL 实例上的所有dataNode节点共享本dataHost上的所有物理连接。 十 Mycat实施指南 分表分库原则
原则一能不分就不分原则二分片数量尽量少原则三分片规则需要慎重选择原则四尽量不要在一个事务中的SQL跨越多个分片原则五查询条件尽量优化 注意 这里特别强调一下分片规则的选择问题如果某个表的数据有明显的时间特征比如订单、交易记录等则他们通常比较合 适用时间范围分片因为具有时效性的数据。 数据拆分原则
达到一定数量级才拆分800 万不到 800 万但跟大表超 800 万的表有关联查询的表也要拆分在此称为大表关联表 小于100万的使用全局表大于100万小于800 万跟大表使用同样的拆分策略。
