1、是什么
Mycat是数据库中间件,连接java应用程序和数据库。
底层:由java语言编写。
功能:读写分离、数据分片(分库分表)、多数据源整合。
原理:拦截。
2、安装启动
1、解压后即可使用,解压缩文件拷贝到linux下/usr/local/
三个配置文件:
①schema.xml:定义逻辑库,表、分片节点等内容。
②rule.xml:定义分片规则。
③server.xml:定义用户以及系统相关变量,如端口等·
2、编辑配置文件
修改配置文件 server.xml
修改配置文件 schema.xml
验证数据库访问情况
mysql -umycat -p123123 -h ip -P 3306
3、启动程序
- 控制台启动 :去 mycat/bin 目录下执行 ./mycat console
- 后台启动 :去 mycat/bin 目录下 ./mycat start
3、登录
1、登录后台管理窗口
mysql -umycat -p123123 -h ip -P 9066
常用命令:
show database
show @@help
2、登录数据窗口
mysql -umycat -p123123 -h ip -P 8066
4、搭建mysql一主一从
第一步:主机配置
修改配置文件:vim /etc/my.cnf
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
#日志的三种格式:
statement:会产生数据不一致问题。
row:记录改变的值,若是全部更新,记录量太大。
mixed:自动匹配,解决了一部分数据不一致问题,但是无法识别系统变量。
第二步:从机配置
修改配置文件:vim /etc/my.cnf
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay
第三步:创建账号授权
#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';
#查询master的状态,记录下File和Position的值。
show master status;
第四步:在从机上配置需要复制的主机
#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;
#启动从服务器复制功能
start slave;
#查看从服务器状态,显示两个YES即配置成功
show slave status\G;
第五步:重新配置主从
#停止主从复制功能
stop slave;
#重新配置主从
stop slave;
reset master;
第六步:配置读写分离
#验证读写分离
在写主机插入:insert into mytbl values (1,@@hostname);
在Mycat里查询:select * from mytbl;
#修改<dataHost>的balance属性,通过此属性配置读写分离的类型
(1)balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。
(2)balance="1",全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡。
(3)balance="2",所有读操作都随机的在 writeHost、readhost 上分发。
(4)balance="3",所有读请求随机的分发到 readhost 执行,writerHost 不负担读压力
5、双主双从配置注意点
主机配置(额外加入):
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1 .. 65535
auto-increment-offset=1
注意:两台主机也需要配置互相复制。
balance="1": 全部的readHost与stand by writeHost参与select语句的负载均衡。
writeType="0": 所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个。
writeType="1",所有写操作都随机的发送到配置的writeHost。
switchType="1": 1 默认值,自动切换。
-1 表示不自动切换。
2 基于 MySQL 主从同步的状态决定是否切换。
6、垂直拆分–分库
分库原则:有紧密关联关系的表应该在一个库里,相互没有关联关系的表可以分到不同的库里。
修改schema 配置文件:
7、水平拆分–分表
分库原则:按照某个字段的某种规则来分散到多个库之中,每个表中 包含一部分数据。
#修改schema 配置文件
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>
#修改配置文件 rule.xml
<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
…
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
1、分片"join"
1、ER表(配置关联数据库的数据分表操作)
#修改 schema.xml 配置文件
…
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
…
2、全局表(例如字典表)
#修改 schema.xml 配置文件
<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
2、常用分片规则
- 取模
- 分片枚举(hash-int)
- 范围约定
- 日期分片
分片枚举:
#修改schema.xml配置文件
<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile" ></table>
#修改rule.xml配置文件
<tableRule name="sharding_by_intfile">
<rule>
<columns>areacode</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
…
<function name="hash-int"class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property>
<property name="defaultNode">0</property>
</function>
#columns:分片字段,algorithm:分片函数
#mapFile:标识配置文件名称,type:0为int型、非0为String。
#defaultNode:默认节点:小于 0 表示不设置默认节点,大于等于 0 表示设置默认节点。
#设置默认节点如果碰到不识别的枚举值,就让它路由到默认节点,如不设置不识别就报错。
#修改partition-hash-int.txt配置文件(数据:数据库)
110=0
120=1
范围约定:
#修改schema.xml配置文件
<table name="payment_info" dataNode="dn1,dn2" rule="auto_sharding_long" ></table>
#修改rule.xml配置文件
<tableRule name="auto_sharding_long">
<rule>
<columns>order_id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
…
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
<property name="defaultNode">0</property>
</function>
#columns:分片字段,algorithm:分片函数
#mapFile:标识配置文件名称
#defaultNode:默认节点:小于 0 表示不设置默认节点,大于等于 0 表示设置默认节点, # 设置默认节点如果碰到不识别的枚举值,就让它路由到默认节点,如不设置不识别就报错。
#修改autopartition-long.txt配置文件(范围:数据库)
0-102=0
103-200=1
日期分片:
#修改schema.xml配置文件
<table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date" ></table>
#修改rule.xml配置文件
<tableRule name="sharding_by_date">
<rule>
<columns>login_date</columns>
<algorithm>shardingByDate</algorithm>
</rule>
</tableRule>
…
<function name="shardingByDate" class="io.mycat.route.function.PartitionByDate">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2019-01-01</property>
<property name="sEndDate">2019-01-04</property>
<property name="sPartionDay">2</property>
</function>
#columns:分片字段,algorithm:分片函数
#dateFormat :日期格式
#sBeginDate :开始日期
#sEndDate:结束日期,则代表数据达到了这个日期的分片后循环从开始分片插入
#sPartionDay :分区天数,即默认从开始日期算起,分隔 2 天一个分区
8、全局序列
1、本地文件(sequence_conf.properties)
- 优点:本地加载,读取速度较快。
- 缺点:抗风险能力差,Mycat 所在主机宕机后,无法读取本地文件。
2、数据库方式(利用数据库一个表 来进行计数累加。但是并不是每次生成序列都读写数据库,这样效率太低。Mycat 会预加载一部分号段到 Mycat 的内存中,这样大部分读写序列都是在内存中完成的。如果内存中的号段用完了 Mycat 会再向数据库要一次。)
操作流程:
#建库序列脚本
#在 dn1 上创建全局序列表
CREATE TABLE MYCAT_SEQUENCE (NAME VARCHAR(50) NOT NULL,current_value INT NOT NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(NAME)) ENGINE=INNODB;
#创建全局序列所需函数
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 ;
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 ;
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 ;
#初始化序列表记录
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000,100);
#修改 Mycat 配置(修改sequence_db_conf.properties,指定序列数据库是哪个)
#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
#修改server.xml(全局序列类型:0-本地文件,1-数据库方式,2-时间戳方式)
<property name="sequnceHandlerType">1<property>
#验证全局序列(注意id不是自己填的,而是序列库里自动分配的)
insert into orders(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);
3、时间戳方式
- 优点:配置简单
- 缺点:18 位 ID 过长
9、搭建mycat高可用环境
方案:HAProxy实现mycat多节点的集群高可用和负载均衡,Keepalived实现HAProxy自身的高可用。
1、安装HAProxy
#准备HAProxy安装包,传到/opt目录下
#解压到/usr/local/src
tar -zxvf haproxy-1.5.18.tar.gz -C /usr/local/src
cd /usr/local/src/haproxy-1.5.18
#ARGET=linux310,内核版本,使用uname -r查看内核,如:3.10.0-514.el7,此时该参数就为linux310; #ARCH=x86_64,系统位数;
uname -r
make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64
make install PREFIX=/usr/local/haproxy
mkdir -p /usr/data/haproxy/
#向配置文件中插入以下配置信息,并保存
vim /usr/local/haproxy/haproxy.conf
global
log 127.0.0.1 local0
#log 127.0.0.1 local1 notice
#log loghost local0 info
maxconn 4096
chroot /usr/local/haproxy
pidfile /usr/data/haproxy/haproxy.pid
uid 99
gid 99
daemon
#debug
#quiet
defaults
log global
mode tcp
option abortonclose
option redispatch
retries 3
maxconn 2000
timeout connect 5000
timeout client 50000
timeout server 50000
listen proxy_status
bind :48066
mode tcp
balance roundrobin
server mycat_1 ip1:8066 check inter 10s
server mycat_2 ip2:8066 check inter 10s
frontend admin_stats
bind :7777
mode http
stats enable
option httplog
maxconn 10
stats refresh 30s
stats uri /admin
stats auth admin:123123
stats hide-version
stats admin if TRUE
2、启动HAProxy
#启动HAProxy
/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
#查看HAProxy进程
ps -ef|grep haproxy
#打开浏览器访问
http://ip:7777/admin
#验证负载均衡,通过HAProxy访问Mycat
mysql -umycat -p123456 -h ip -P 48066
3、安装 Keepalived
#准备Keepalived安装包,传到/opt目录下
#解压到/usr/local/src
tar -zxvf keepalived-1.4.2.tar.gz -C /usr/local/src
#安装依赖插件
yum install -y gcc openssl-devel popt-devel
#进入解压后的目录,进行配置,进行编译
cd /usr/local/src/keepalived-1.4.2./configure --prefix=/usr/local/keepalived
make && make install
#运行前配置
cp /usr/local/src/keepalived-1.4.2/keepalived/etc/init.d/keepalived /etc/init.d/
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/src/keepalived-1.4.2/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
#修改配置文件
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
xlcocoon@foxmail.com
}
notification_email_from
keepalived@showjoy.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
#主机配MASTER,备机配BACKUP
state MASTER
#所在机器网卡
interface ens33
virtual_router_id 51
#数值越大优先级越高
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
#虚拟IP
192.168.140.200
}
}
virtual_server 192.168.140.200 48066 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
real_server ip1 48066 {
weight 1
TCP_CHECK {
connect_timeout 3
retry 3
delay_before_retry 3
}
}
real_server ip2 48600 {
weight 1
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
}
4、启动Keepalived
#启动Keepalived
service keepalived start
#登录
mysql -umycat -p123456 -h ip -P 48066
10、安全设置
1、user 标签权限控制
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
#name:应用连接中间件逻辑库的用户名
#password:该用户对应的密码
#TESTDB:应用当前连接的逻辑库中所对应的逻辑表。schemas 中可以配置一个或多个
#readOnly:应用连接中间件逻辑库所具有的权限。true 为只读,false 为读写都有,默认为 false
2、 privileges 标签权限控制
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<!-- 表级 DML 权限设置 -->
<privileges check="true">
<schema name="TESTDB" dml="1111" >
<table name="orders" dml="0000"></table>
<!--<table name="tb02" dml="1111"></table>-->
</schema>
</privileges>
</user>
#DML权限为四位数字(0000、0010、1110、1111),分别对应增加、更新、查询、删除。
3、SQL拦截
<firewall>
#白名单
<whitehost>
<host host="192.168.140.128" user="mycat"/>
</whitehost>
#黑名单
<blacklist check="true">
<property name="deleteAllow">false</property>
</blacklist>
</firewall>
11、Mycat监控工具
Mycat-web 是 Mycat 可视化运维的管理和监控平台,弥补了 Mycat 在监控上的空白。帮 Mycat 分担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心,可以管理多个节点。Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等,具备 IP 白名单、邮件告警等模块,还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。为优化 SQL 提供依据。
1、ZooKeeper 安装
#下载安装包
官网:http://zookeeper.apache.org/
#安装包拷贝到Linux系统/opt目录下,并解压
tar -zxvf zookeeper-3.4.11.tar.gz
cp zoo_sample.cfg zoo.cfg
#启动
./zkServer.sh start
#ZooKeeper服务端口为2181,查看服务是否已经启动
netstat -ant | grep 2181
2、Mycat-web 安装
#下载安装包
官网:http://www.mycat.io/
#安装包拷贝到Linux系统/opt目录下,并解压
tar -zxvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz
#拷贝mycat-web文件夹到/usr/local目录下
cp -r mycat-web /usr/local
#4 启动
cd /usr/local/mycat-web/
./start.sh &
#Mycat-web服务端口为8082,查看服务是否已经启动
netstat -ant | grep 8082
#6 通过地址访问服务
http://ip:8082/mycat/