MyCat简介
1、什么是MyCat?
- 一个彻底开源的,面向企业应用开发的大数据库集群
- 支持事务、ACID、可以替代MySQL的加强版数据库
- 一个可以视为MySQL集群的企业级数据库,用来替代昂贵的Oracle集群
- 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
- 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
- 一个新颖的数据库中间件产品
2、为什么使用MyCat?
如今随着互联网的发展,数据的量级也是撑指数的增长,从GB到TB到PB。对数据的各种操作也是愈加的困难,传统的关系性数据库已经无法满足快速查询与插入数据的需求。这个时候NoSQL的出现暂时解决了这一危机。它通过降低数据的安全性,减少对事务的支持,减少对复杂查询的支持,来获取性能上的提升。但是,在有些场合NoSQL一些折衷是无法满足使用场景的,就比如有些使用场景是绝对要有事务与安全指标的。这个时候NoSQL肯定是无法满足的,所以还是需要使用关系性数据库。如何使用关系型数据库解决海量存储的问题呢?此时就需要做数据库集群,为了提高查询性能将一个数据库的数据分散到不同的数据库中存储,为应对此问题就出现了——MyCat!!!
Mycat作用为:
- 能满足数据库数据大量存储;提高了查询性能
- 读写分离
- 数据分片 垂直拆分(分库) 、 水平拆分(分表) 、 垂直+水平拆分(分库分表)
- 多数据源整合
支持的数据库:
支持MySQL ORACLE SQLServer等一些主流的数据库!!!
3、核心技术(分库分表) 数据库分片?
通过某种特定的条件,将我们存放在一个数据库中的数据分散存放在不同的多个数据库(主机)中,这样来达到分散单台设备的负载,根据切片规则,可分为以下两种切片模式:MyCAT通过定义表的分片规则来实现分片,每个表格可以捆绑一个分片规则,每个分片规则指定一个分片字段并绑定一个函数,来实现动态分片算法
- Schema:逻辑库,与MySQL中的Database(数据库)对应,一个逻辑库中定义了所包括的Table。
- Table:逻辑表,即物理数据库中存储的某一张表,与传统数据库不同,这里的表格需要声明其所存储的逻辑数据节点DataNode。在此可以指定表的分片规则。
- DataNode:MyCAT的逻辑数据节点,是存放table的具体物理节点,也称之为分片节点,通过DataSource来关联到后端某个具体数据库上
- DataSource:定义某个物理库的访问地址,用于捆绑到Datanode上。
- 分片规则:前面讲了数据切分,一个大表被分成若干个分片表,就需要一定的规则,这样按照某种业务规则把数据分到某个分片的规则就是分片规则,数据切分选择合适的分片规则非常重要,将极大的避免后续数据处理的难。
MyCat搭建
环境:
主机名 | IP |
---|---|
master(主) | 192.168.1.20 |
slave(从) | 192.168.1.40 |
mycat-Client | 192.168.1.41 |
主从搭建步骤省略,请参考:https://blog.csdn.net/weixin_45191791/article/details/114271064?spm=1001.2014.3001.5501
1.下载资源包并解压
PS:自行下载jdk和mycat资源包
MyCat网址:http://www.mycat.org.cn/
[root@mycat ~]# yum -y install java-devel
[root@mycat ~]# java -version
openjdk version "1.8.0_282"
OpenJDK Runtime Environment (build 1.8.0_282-b08)
OpenJDK 64-Bit Server VM (build 25.282-b08, mixed mode)
[root@mycat ~]# wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@mycat ~]# tar zxf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@mycat ~]# mv mycat/ /usr/local/
2.修改配置文件并启动服务
[root@mycat ~]# cd /usr/local/mycat/
#修改登录用户!
[root@mycat mycat]# vim conf/server.xml
......
<user name="mycat" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
......
[root@mycat mycat]# vim conf/schema.xml
#本文只是吧配置文件中的注释清楚了而已,请观看这不必紧张与自己的不一样!!
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"> #此处的name只是一个模式名而已自定义即可!!
</schema>
<dataNode name="dn1" dataHost="host1" database="test" /> #此处的test为主从库中的真实库
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="jdbc:mysql://192.168.1.20:3306" user="root"
password="123456"> #此处为使用jdk登录,IP、user、passwrod为真实的主从IP!!!
</writeHost>
</dataHost>
</mycat:schema>
[root@mycat mycat]# ./bin/mycat start
Starting Mycat-server...
Mycat-server is already running.
#或者
#这种方法可以实时监控mycat是否有报错行为
[root@mycat mycat]# ./bin/mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
......
用jdk登录就如上配置即可,不用则需修改:
root@mycat mycat]# vim conf/schema.xml
#本文只是吧配置文件中的注释清楚了而已,请观看这不必紧张与自己的不一样!!
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="test" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="//192.168.1.20:3306" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
3.客户机登录验证
PS:此处客户机与mycat同为一台
[root@mycat mycat]# yum -y install mysql
[root@mycat mycat]# ss -ln | grep 8066
tcp LISTEN 0 128 :::8066 :::*
[root@mycat mycat]# mysql -umycat -p123456 -h 192.168.1.41 -P 8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
MySQL [(none)]> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [TESTDB]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from t1;
+---+----------+
| q | name |
+---+----------+
| 1 | zhangsan |
| 2 | wangwu |
+---+----------+
2 rows in set (0.02 sec)
MyCat安全组
1.权限配置
- user 标签权限控制 目前 Mycat 对于中间件的连接控制并没有做太复杂的控制,目前只做了中间件逻辑库级别的读写权限控制.
[root@mycat01 ~]# vim /usr/local/mycat/conf/server.xml
......
<user name="mycat" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
<property name="defaultSchema">TESTDB</property>
</user>
- privileges 标签权限控制 在 user 标签下的 privileges 标签可以对逻辑库(schema)、表(table)进行精细化的 DML 权限控制。 privileges 标签下的 check 属性,如为 true 开启权限检查,为 false 不开启,默认为 false。 由于 Mycat 一个用户的 schemas 属性可配置多个逻辑库(schema) ,所以 privileges 的下级节点 schema 节点同样可配置多个,对多库多表进行细粒度的 DML 权限控制.
权限配置表
DML权限 | 增加(insert) | 更新(update) | 查询(select) | 删除(drop、delete) |
---|---|---|---|---|
0000 | 禁止 | 禁止 | 禁止 | 禁止 |
0010 | 禁止 | 禁止 | 允许 | 禁止 |
1110 | 允许 | 允许 | 允许 | 禁止 |
1111 | 允许 | 允许 | 允许 | 允许 |
参考上表修改配置文件
[root@mycat01 ~]# vim /usr/local/mycat/conf/server.xml
<user name="mycat" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema,不设置则为null,报错 -->
<!-- 表级 DML 权限设置 -->
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
</schema>
</privileges>
</user>
2.SQL语句拦截
firewall 标签用来定义防火墙; firewall 下 whitehost 标签用来定义 IP 白名单 , blacklist 用来定义SQL 黑名单。
- 白名单:可以通过设置白名单, 实现某主机某用户可以访问 Mycat,而其他主机用户禁止访问。
<!-- 全局SQL防火墙设置 -->
<!--白名单可以使用通配符%或着*-->
<!--例如<host host="127.0.0.*" user="root"/>-->
<!--例如<host host="127.0.*" user="root"/>-->
<!--例如<host host="127.*" user="root"/>-->
<!--例如<host host="1*7.*" user="root"/>-->
<!--这些配置情况下对于127.0.0.1都能以root账户登录-->
<firewall>
<whitehost>
<host host="192.168.1.20" user="mycat"/> #此处设置的只有192.168.1.20这个主机可以登录mycat,其余主机则不可!
</whitehost>
......
</firewall>
- 黑名单:可以通过设置黑名单, 实现 Mycat 对具体 SQL 操作的拦截, 如增删改查等操作的拦截
权限配置表
配置项 | 缺省值 | 描述 |
---|---|---|
selelctAllow | true(false) | 是否允许执行 SELECT 语句 |
deleteAllow | true(false) | 是否允许执行 DELETE 语句 |
updateAllow | true(false) | 是否允许执行 UPDATE 语句 |
insertAllow | true(false) | 是否允许执行 INSERT 语句 |
createTableAllow | true(false) | 是否允许创建表 |
setAllow | true(false) | 是否允许使用 SET 语法 |
alterTableAllow | true(false) | 是否允许执行 Alter Table 语句 |
dropTableAllow | true(false) | 是否允许修改表 |
commitAllow | true(false) | 是否允许执行 commit 操作 |
rollbackAllow | true(false) | 是否允许执行 roll back 操作 |
参照上表配置
<!-- 全局SQL防火墙设置 -->
<!--白名单可以使用通配符%或着*-->
<!--例如<host host="127.0.0.*" user="root"/>-->
<!--例如<host host="127.0.*" user="root"/>-->
<!--例如<host host="127.*" user="root"/>-->
<!--例如<host host="1*7.*" user="root"/>-->
<!--这些配置情况下对于127.0.0.1都能以root账户登录-->
<firewall>
<whitehost>
<host host="192.168.1.20" user="mycat"/>
</whitehost>
<blacklist check="true"> #true开启黑名单,false关闭黑名单
<property name="deleteAllow">false</property> #此处设置的是不可以对语句执行delete的,如要需要使用则把false修改为true则即可使用!!!
</blacklist>
</firewall>
MyCat读写分离
mycat读写分离参数解释
Mycat的读写分离是建立在Mysq的主从复制的基础上的修改的balance属性,通过此属性配置读写分离的类型 负载均衡类型。
balabce参数:
- balance=“0”:不开启读写分离机制, 所有读操作都发送到当前可用的 writeHost 上。
- balance=“1”:全部的 readHost与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从 模式(M1->S1, M2->S2,并且M1 与 M2 互为主备),正常情况下, M2,S1,S2 都参与 select 语句的负载均衡。
- balance=“2”:所有读操作都随机的在 writeHost、 readhost 上分发。
- balance=“3”:所有读请求随机的分发到 readhost 执行,writerHost 不负担读压力,注意 balance=3 只在 1.4 及其以后版本有,1.3 没有。
- readHost:“readHost“是从属于writeHost的,即意味着它从那个writeHost获取同步数据,因此,当它所属的writeHost宕机了,则它也不会再参与到读写分离中来,即“不工作了”,这是因为此时,它的数据已经“不可靠”了。基于这个考虑,目前mycat 1.3和1.4版本中,若想支持MySQL一主一从的标准配置,并且在主节点宕机的情况下,从节点还能读取数据,则需要在Mycat里配置为两个writeHost并设置banlance=1。
WriteType参数
- writeType=“0”:写操作会在localhost上,如果localhost失败,会自动切换到localhost1,localhost恢复以后并不会切换回localhost进行写操作。
- .writeType=“1” :在balabce为0的情况下设置writeType为1,所有写操作都随机的发送到readHost。
- writeType=“2”:所有写操作都随机的在writeHost、readhost分上发。
switchType 目前有三种选择:
- -1:表示不自动切换
- 1 :默认值,自动切换
- 2 :基于MySQL主从同步的状态决定是否切换
“Mycat心跳检查语句配置为 show slave status ,dataHost 上定义两个新属性: switchType=“2” 与slaveThreshold=“100”,此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过检测 show slave status 中的"Seconds_Behind_Master", “Slave_IO_Running”, “Slave_SQL_Running” 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。“
以配置参数2为例:
1.修改配置文件
mycat(192.168.1.41)
[root@mycat ~]# cd /usr/local/mycat/
[root@mycat mycat]# vim conf/schema.xml
[root@mycat mycat]# cat conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="test" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="jdbc:mysql://192.168.1.20:3306" user="root"
password="123456">
<readHost host="hostS1" url="jdbc:mysql://192.168.1.40:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
[root@mycat ~]# ln -s /usr/local/mycat/bin/* /usr/local/bin/ #做一个为mycat命令的软连接
[root@mycat ~]# mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
......
master01和master02同样步骤
[root@master ~]# vim /etc/my.cnf
[root@master ~]# tail -1 /etc/my.cnf
binlog_format = STATEMENT
[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql -uroot -p123456
mysql> show variables like '%log%';
+--------------------------------------------+----------------------------------------------+
| Variable_name | Value |
+--------------------------------------------+----------------------------------------------+
......
| binlog_format | STATEMENT |
......
2.验证读写分离
2.1 写入数据
master(192.168.1.20)
mysql> insert into test.t1 values(12,@@hostname);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from test.t1;
+------+----------+
| q | name |
+------+----------+
| 1 | zhangsan |
| 2 | wangwu |
| 12 | master |
+------+----------+
3 rows in set (0.00 sec)
2.2 查看数据
slave(192.168.1.40)
mysql> select * from test.t1;
+------+----------+
| q | name |
+------+----------+
| 1 | zhangsan |
| 2 | wangwu |
| 12 | slave |
+------+----------+
3 rows in set (0.00 sec)
mycat(192.168.1.41)
MySQL [TESTDB]> use TESTDB;
Database changed
MySQL [TESTDB]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
#从以下两条数据显示可以明显看出读写已分离,有时候可能得多查看几次,请不要烦躁!!!!
MySQL [TESTDB]> select * from t1;
+----+----------+
| q | name |
+----+----------+
| 1 | zhangsan |
| 2 | wangwu |
| 12 | master |
+----+----------+
3 rows in set (0.04 sec)
MySQL [TESTDB]> select * from t1;
+----+----------+
| q | name |
+----+----------+
| 1 | zhangsan |
| 2 | wangwu |
| 12 | slave |
+----+----------+
3 rows in set (0.00 sec)
MyCat垂直拆分(分库)
至此环境需要,所以在添加一台虚拟服务器!!!
服务器需要MySQL数据库服务!
主机名 | IP |
---|---|
mysql | 192.168.1.30 |
1.安装MySQL
步骤省略,可参考:https://blog.csdn.net/weixin_45191791/article/details/110136458?spm=1001.2014.3001.5501
mysql(192.168.1.30)
#此处创建test库是因为在mycat配置文件中配置的真实库为test!
[root@mysql ~]# mysql -uroot -p123456
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
2.修改MyCat配置文件
mycat(192.168.1.41)
[root@mycat ~]# mycat stop
Stopping Mycat-server...
Mycat-server was not running.
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
[root@mycat ~]# cat /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2">
</table>
</schema>
<dataNode name="dn1" dataHost="host1" database="test" />
<dataNode name="dn2" dataHost="host2" database="test" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="jdbc:mysql://192.168.1.20:3306" user="root"
password="123456">
<readHost host="hostS1" url="jdbc:mysql://192.168.1.40:3306" user="root" password="123456" />
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="jdbc:mysql://192.168.1.30:3306" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
[root@mycat ~]# mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
^Cwrapper | INT trapped. Shutting down.
wrapper | <-- Wrapper Stopped
[root@mycat ~]#
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
[root@mycat ~]# mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
......
3.写入数据
mycat(192.168.1.41)
#客户表 rows:20万
MySQL [TESTDB]> CREATE TABLE customer(
-> id INT AUTO_INCREMENT,
-> NAME VARCHAR(200),
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.01 sec)
OK!
#订单表 rows:600万
MySQL [TESTDB]> CREATE TABLE orders(
-> id INT AUTO_INCREMENT,
-> order_type INT,
-> customer_id INT,
-> amount DECIMAL(10,2),
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.21 sec)
OK!
#订单详细表 rows:600万
MySQL [TESTDB]> CREATE TABLE orders_detail(
-> id INT AUTO_INCREMENT,
-> detail VARCHAR(2000),
-> order_id INT,
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.07 sec)
OK!
#订单状态字典表
MySQL [TESTDB]> CREATE TABLE dict_order_type(
-> id INT AUTO_INCREMENT,
-> order_type VARCHAR(200),
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.11 sec)
OK!
4.查看分库效果
master(192.168.1.20)
mysql> use test;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_test |
+-----------------+
| dict_order_type |
| orders |
| orders_detail |
| t1 |
+-----------------+
4 rows in set (0.00 sec)
mysql(192.168.1.30)
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| CUSTOMER |
+----------------+
1 row in set (0.00 sec)
mycat(192.168.1.41)
MySQL [TESTDB]> show tables;
+-----------------+
| Tables_in_test |
+-----------------+
| customer |
| dict_order_type |
| orders |
| orders_detail |
| t1 |
+-----------------+
5 rows in set (0.00 sec)
MySQL [TESTDB]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customer |
+----------------+
1 row in set (0.01 sec)
总结:
查看数据在dn1上可以看到三张表,在dn2上可以看到1张表,则很明显的可以看出,分库成功!!!
MyCat水平拆分(分表)
相对于垂直拆分,水平拆分不是将表做分类,而是按照某个字段的某种规则来分散到多个库之中, 每个表中 包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就 是将表中的某些行切分到一个数据库,而另外的某些行又切分到其他的数据库中。
实现分表 选择要拆分的表 MySQL 单表存储数据条数是有瓶颈的,单表达到 1000 万条数据就达到了瓶颈,会影响查询效率, 需要进行水平拆分(分表) 进行优化。 例如:例子中的 orders、 orders_detail 都已经达到600 万行数据,需要进行分表优化。 分表字段 以 orders 表为例,可以根据不同自字段进行分表。
编号 | 分表字段 | 效果 |
---|---|---|
1 | id(主键、 或创建时间) | 查询订单注重时效,历史订单被查询的次数少,如此分片会造成一个节点访问多,一个访问少,不平均。 |
2 | customer_id(客户 id) | 根据客户 id 去分,两个节点访问平均,一个客户的所有订单都在同一个节点 |
1.修改配置文件
mycat(192.168.1.41)
[root@mycat ~]# mycat stop
Stopping Mycat-server...
Mycat-server was not running.
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml
......
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2">
</table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule_log">
</table>
</schema>
......
[root@mycat ~]# vim /usr/local/mycat/conf/rule.xml
......
<tableRule name="mod_rule_log">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long-log</algorithm>
</rule>
</tableRule>
......
<function name="mod-long-log"
class="io.mycat.route.function.PartitionByMod">
<property name="count">2</property>
</function>
......
[root@mycat ~]# mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
......
2.写入数据
2.1 插入表
PS:由于现在用的表还是分库时候的表,所以此处手动在dn2上创建orders表!
mysql(192.168.1.30)
ysql> use test;
Database changed
mysql> CREATE TABLE orders(
-> id INT AUTO_INCREMENT,
-> order_type INT,
-> customer_id INT,
-> amount DECIMAL(10,2),
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| CUSTOMER |
| orders |
+----------------+
2 rows in set (0.00 sec)
2.2 插入数据
mycat(192.168.1.41)
MySQL [TESTDB]> INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100);
Query OK, 1 row affected (0.12 sec)
OK!
MySQL [TESTDB]> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
Query OK, 1 row affected (0.01 sec)
OK!
MySQL [TESTDB]> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
Query OK, 1 row affected (0.01 sec)
OK!
MySQL [TESTDB]> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
Query OK, 1 row affected (0.00 sec)
OK!
MySQL [TESTDB]> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
Query OK, 1 row affected (0.00 sec)
OK!
MySQL [TESTDB]> INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);
Query OK, 1 row affected (0.01 sec)
OK!
3.查看数据
master(192168.1.20)
mysql> select * from test.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)
mysql(192.168.1.30)
mysql> select * from test.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)
mycat(192.168.1.41)
MySQL [TESTDB]> select * from orders order by id asc ;
+------+------------+-------------+-----------+
| id | order_type | customer_id | amount |
+------+------------+-------------+-----------+
| 1 | 101 | 100 | 100100.00 |
| 2 | 101 | 100 | 100300.00 |
| 3 | 101 | 101 | 120000.00 |
| 4 | 101 | 101 | 103000.00 |
| 5 | 102 | 101 | 100400.00 |
| 6 | 102 | 100 | 100020.00 |
+------+------------+-------------+-----------+
6 rows in set (0.01 sec)
4.连接查询
mycat(192.168.1.41)
[root@mycat logs]# cat /usr/local/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" >
</table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
<childTable name="orders_detail" primaryKey="id" joinKey="order_id"
parentKey="id" />
</table>
</schema>
<dataNode name="dn1" dataHost="host1" database="test" />
<dataNode name="dn2" dataHost="host2" database="test" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.1.20:3306" user="root"
password="123456">
<readHost host="hostS1" url="192.168.1.40:3306" user="root" password="123456" />
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.1.30:3306" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
[root@mycat bin]# mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
......
MySQL [TESTDB]> insert into orders_detail(id,detail,order_id) values (1,'detail',1);
Query OK, 1 row affected (0.07 sec)
MySQL [TESTDB]> insert into orders_detail(id,detail,order_id) values (2,'detail',2);
Query OK, 1 row affected (0.06 sec)
MySQL [TESTDB]> insert into orders_detail(id,detail,order_id) values (3,'detail',3);
Query OK, 1 row affected (0.06 sec)
MySQL [TESTDB]> insert into orders_detail(id,detail,order_id) values (4,'detail',4);
Query OK, 1 row affected (0.05 sec)
MySQL [TESTDB]> insert into orders_detail(id,detail,order_id) values (5,'detail',5);
Query OK, 1 row affected (0.06 sec)
MySQL [TESTDB]> insert into orders_detail(id,detail,order_id) values (6,'detail',6);
Query OK, 1 row affected (0.05 sec)
MySQL [TESTDB]> select o.*,od.detail from orders as o inner join orders_detail as od on o.id=od.order_id;
+----+------------+-------------+-----------+--------+
| id | order_type | customer_id | amount | detail |
+----+------------+-------------+-----------+--------+
| 1 | 101 | 100 | 100100.00 | detail |
| 2 | 101 | 100 | 100300.00 | detail |
| 6 | 102 | 100 | 100020.00 | detail |
| 3 | 101 | 101 | 120000.00 | detail |
| 4 | 101 | 101 | 103000.00 | detail |
| 5 | 102 | 101 | 100400.00 | detail |
+----+------------+-------------+-----------+--------+
6 rows in set (0.02 sec)
master(192.168.1.20)
mysql> select * from orders_detail;
+----+--------+----------+
| id | detail | order_id |
+----+--------+----------+
| 1 | detail | 1 |
| 2 | detail | 2 |
| 6 | detail | 6 |
+----+--------+----------+
3 rows in set (0.00 sec)
mysql(192.168.1.30)
mysql> select * from orders_detail;
+----+--------+----------+
| id | detail | order_id |
+----+--------+----------+
| 3 | detail | 3 |
| 4 | detail | 4 |
| 5 | detail | 5 |
+----+--------+----------+
3 rows in set (0.00 sec)
扩展
如以下报错则修改mycat登录使用native模式,jdbc登录会以下报错
MySQL [TESTDB]> insert into orders_detail(id,detail,order_id) values (1,'detail',1);
ERROR 1064 (HY000): can't find (root) parent sharding node for sql:insert into orders_detail(id,detail,order_id) values (1,'detail',1)
以上分表结束!
5.全局分片
全局表在分片的情况下,当业务表因为规模而进行分片以后,业务表与这些附属的字典表之间的关联, 就成了比较 棘手的问题,考虑到字典表具有以下几个特性: ① 变动不频繁 ② 数据量总体变化不大 ③ 数据规模不大,很少有超过数十万条记录 鉴于此, Mycat 定义了一种特殊的表,称之为“全局表”,
全局表具有以下特性:① 全局表的插入、更新操作会实时在所有节点上执行,保持各个分片的数据一致性 ② 全局表的查询操作,只从一个节点获取 ③ 全局表可以跟任何一个表进行 JOIN 操作 将字典表或者符合字典表特性的一些表定义为全局表,则从另外一个方面,很好的解决了数据 JOIN 的难题。 通过全局表+基于 E-R 关系的分片策略, Mycat 可以满足 80%以上的企业应用开发。
5.1 修改配置文件
mycat(192.168.1.41)
......
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2">
</table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule_log"> # 并指定分片规则为 mod_rule(自定义的名字)
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
<table name="dict_order_type" dataNode="dn1,dn2" type="global">
</table>
</schema>
[root@mycat ~]# mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
5.2 插入数据
PS:由于现在用的表还是分库时候的表,所以此处手动在dn2上创建 dict_order_type表!
mysql(192.168.1.30)
mysql> CREATE TABLE dict_order_type(
-> id INT AUTO_INCREMENT,
-> order_type VARCHAR(200),
-> PRIMARY KEY(id)
-> );
Query OK, 0 rows affected (0.01 sec)
mycat(192.168.1.41)
MySQL [TESTDB]> insert into dict_order_type (id,order_type) values (101,'type1');
Query OK, 1 row affected (0.12 sec)
OK!
MySQL [TESTDB]> insert into dict_order_type (id,order_type) values (102,'type2');
Query OK, 1 row affected (0.01 sec)
OK!
5.3 查看数据
master(192.168.1.20)
mysql> select * from test.dict_order_type;
+-----+------------+
| id | order_type |
+-----+------------+
| 101 | type1 |
| 102 | type2 |
+-----+------------+
2 rows in set (0.00 sec)
mysql(192.168.1.30)
MySQL [TESTDB]> select * from dict_order_type;
+------+------------+
| id | order_type |
+------+------------+
| 101 | type1 |
| 102 | type2 |
+------+------------+
2 rows in set (0.03 sec)
mycat(192.168.1.41)
mysql> select * from dict_order_type;
+-----+------------+
| id | order_type |
+-----+------------+
| 101 | type1 |
| 102 | type2 |
+-----+------------+
2 rows in set (0.00 sec)
常用分片规则
- 取模 此规则为对分片字段求摸运算。 也是水平分表最常用规则。配置分表中, orders 表采用了此规则。
- 分片枚举 通过在配置文件中配置可能的枚举 id,自己配置分片,本规则适用于特定的场景,比如有些业务 需要按照省份或区县来做保存,而全国省份区县固定的,这类业务使用本条规则。
MyCat+HAproxy+Keepalived高可用架构
HAProxy是一种免费的、非常快速和可靠的解决方案,它提供了高可用性、负载平衡和对TCP和基于http的应用程序的代理。它特别适用于非常高的流量网站,并且拥有相当多的世界上最受欢迎的网站。多年来,它已经成为事实上的标准的opensource负载平衡器,现在已经与大多数主流的Linux发行版一起发布,并且经常在云平台上默认部署
实际上mycat本身也可以直接搭配keepalived来做高可用,如果不搭配haproxy,那么永远只有一台mycat是可用状态,其他的只是做灾备用。haproxy主要作用是mycat的负载均衡以及高可用
环境
主机名 | IP |
---|---|
master | 192.168.1.20 |
slave | 192.168.1.40 |
master02(mysql) | 192.168.1.30 |
mycat01 | 192.168.1.41 |
mycat02 | 192.168.1.42 |
haproxy+keepalived | 192.168.1.43 |
haproxy+keepalived | 192.168.1.44 |
搭建:
PS:master、master02、slave、mycat采用的就是如上的配置!!此处只需添加后三台即可
1、配置MyCat02
PS:修改配置文件并启动mycat02
mycat01(192.168.1.41)
[root@mycat01 ~]# scp -r /usr/local/mycat/conf/schema.xml root@192.168.1.42:/usr/local/mycat/conf/schema.xml
[root@mycat01 ~]# scp -r /usr/local/mycat/conf/rule.xml root@192.168.1.42:/usr/local/mycat/conf/rule.xml
mycat02(192.168.1.42)
[root@mycat02 ~]# mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
......
2.配置HAProxy
2.1 下载资源包
[root@haproxykeepalived-01 haproxy-1.5.18]# yum -y install gcc gcc-c++
[root@haproxykeepalived-01 ~]# wget https://src.fedoraproject.org/repo/pkgs/haproxy/haproxy-1.5.18.tar.gz/21d35f114583ef731bc96af05b46c75a/haproxy-1.5.18.tar.gz
2.2 解压并编译安装
[root@haproxykeepalived-01 ~]# tar -zxvf haproxy-1.5.18.tar.gz -C /usr/local/src/
haproxy-1.5.18/
haproxy-1.5.18/.gitignore
haproxy-1.5.18/CHANGELOG
......
[root@haproxykeepalived-01 ~]# cd /usr/local/src/haproxy-1.5.18/
[root@haproxykeepalived-01 haproxy-1.5.18]# uname -r
3.10.0-957.el7.x86_64
[root@haproxykeepalived-01 haproxy-1.5.18]# make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64 [root@haproxykeepalived-01 haproxy-1.5.18]# make install PREFIX=/usr/local/haproxy
install -d "/usr/local/haproxy/sbin"
install haproxy "/usr/local/haproxy/sbin"
install -d "/usr/local/haproxy/share/man"/man1
install -m 644 doc/haproxy.1 "/usr/local/haproxy/share/man"/man1
install -d "/usr/local/haproxy/doc/haproxy"
for x in configuration architecture haproxy-en haproxy-fr; do \
install -m 644 doc/$x.txt "/usr/local/haproxy/doc/haproxy" ; \
done
- #ARGET=linux310:内核版本,使用uname -r查看内核,如: 3.10.0-514.el7,此时该参数就为linux310;
- #ARCH=x86_64:系统位数;
- #PREFIX=/usr/local/haprpxy #/usr/local/haprpxy:为haprpxy安装路径。
2.3添加配置文件启动服务
#此处haproxy.conf这个文件名是自己手打出来的!!
[root@haproxykeepalived-01 haproxy-1.5.18]# vim /usr/local/haproxy/haproxy.conf
[root@haproxykeepalived-01 haproxy-1.5.18]# cat /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/local/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
#此处为真实的Mycat服务器的IP
server mycat_1 192.168.1.41:8066 check inter 10s
server mycat_2 192.168.1.42: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
[root@haproxykeepalived-01 haproxy-1.5.18]# /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
#查看HAProxy进程
[root@haproxykeepalived-01 haproxy-1.5.18]# ps -ef | grep haproxy
nobody 17518 1 0 14:11 ? 00:00:00 /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
root 17520 15794 0 14:11 pts/0 00:00:00 grep --color=auto haproxy
[root@haproxykeepalived-01 haproxy-1.5.18]# ps -ef | grep 48066
root 17522 15794 0 14:11 pts/0 00:00:00 grep --color=auto 48066
浏览器登录192.168.1.43:7777/admin 账户:admin 密码:123123
2.4验证负载均衡,通过HAProxy访问Mycat
master01(192.168.1.20)
[root@master01 ~]# mysql -umycat -p123456 -h 192.168.1.43 -P48066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using EditLine wrapper
Connection id: 2
Current database: test
Current user: root@192.168.1.41 #本机IP
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)
Protocol version: 10
Connection: 192.168.1.43 via TCP/IP #登录IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 48066
2.5配置HAProxy02
PS:步骤和配置HAProxy01一模一样
#haproxy-keepalived-02(192.168.1.44)
[root@haproxy-keepalived-02 haproxy-1.5.18]# /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
[root@haproxy-keepalived-02 haproxy-1.5.18]# ps -ef | grep haproxy
nobody 11350 1 0 14:27 ? 00:00:00 /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
root 11360 10848 0 14:27 pts/1 00:00:00 grep --color=auto haproxy
[root@haproxy-keepalived-02 haproxy-1.5.18]# ps -ef | grep 48066
root 11374 10848 0 14:27 pts/1 00:00:00 grep --color=auto 48066
#测试登录mycat
#master01(192.168.1.20)
[root@master01 ~]# mysql -umycat -p123456 -h 192.168.1.44 -P48066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using EditLine wrapper
Connection id: 3
Current database: test
Current user: root@192.168.1.41
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)
Protocol version: 10
Connection: 192.168.1.44 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 48066
--------------
3.配置Keepalived
3.1 下载资源包
[root@haproxykeepalived-01 ~]# yum install -y gcc openssl-devel popt-devel
[root@haproxykeepalived-01 ~]# wget https://www.keepalived.org/software/keepalived-2.2.0.tar.gz
3.2解压编译安装
[root@haproxykeepalived-01 ~]# tar -zxvf keepalived-2.2.0.tar.gz -C /usr/local/src/
keepalived-2.2.0/
keepalived-2.2.0/tools/
......
[root@haproxykeepalived-01 ~]# cd /usr/local/src/keepalived-2.2.0/
[root@haproxykeepalived-01 keepalived-2.2.0]# ./configure --prefix=/usr/local/keepalived && make && make install
3.3 初始化启动
[root@haproxykeepalived-01 keepalived-2.2.0]# cp /usr/local/src/keepalived-2.2.0/keepalived/etc/init.d/keepalived /etc/init.d/
[root@haproxykeepalived-01 keepalived-2.2.0]# mkdir /etc/keepalived
[root@haproxykeepalived-01 keepalived-2.2.0]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived
[root@haproxykeepalived-01 keepalived-2.2.0]# cp /usr/local/src/keepalived-2.2.0/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@haproxykeepalived-01 keepalived-2.2.0]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
3.4添加配置文件
[root@haproxykeepalived-01 keepalived-2.2.0]# vim /etc/keepalived/keepalived.conf
[root@haproxykeepalived-01 keepalived-2.2.0]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id master01
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.100
}
}
virtual_server 192.168.1.100 48066 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 192.168.1.43 48066 {
weight 1
TCP_CHECK{
connect_timeout 3
retry 3
delay_before_retry 3
}
}
real_server 192.168.1.44 48066 {
weight 1
TCP_CHECK{
connect_timeout 3
retry 3
delay_before_retry 3
}
}
}
[root@haproxykeepalived-01 keepalived-2.2.0]# systemctl start keepalived.service
3.5 配置Keepalived02
PS:配置步骤课keepalived01一模一样
#部分步骤
#Keepalived01(192.168.1.43)
[root@haproxykeepalived-01 ~]# scp -r keepalived-2.2.0.tar.gz root@192.168.1.44:/root
root@192.168.1.44's password:
keepalived-2.2.0.tar.gz 100% 1122KB 9.2MB/s 00:00
#这一步在Keepalived02初始化之后在执行
[root@haproxykeepalived-01 ~]# scp -r /etc/keepalived/keepalived.conf root@192.168.1.44:/etc/keepalived/keepalived.conf
root@192.168.1.44's password:
keepalived.conf 100% 784 725.0KB/s 00:00
#Keepalived02(192.168.1.44)
[root@haproxy-keepalived-02 keepalived-2.2.0]# cp /usr/local/src/keepalived-2.2.0/keepalived/etc/init.d/keepalived /etc/init.d/
[root@haproxy-keepalived-02 keepalived-2.2.0]# mkdir /etc/keepalived
[root@haproxy-keepalived-02 keepalived-2.2.0]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived
[root@haproxy-keepalived-02 keepalived-2.2.0]# cp /usr/local/src/keepalived-2.2.0/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@haproxy-keepalived-02 keepalived-2.2.0]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@haproxy-keepalived-02 keepalived-2.2.0]# vim /etc/keepalived/keepalived.conf
[root@haproxy-keepalived-02 keepalived-2.2.0]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id backup01
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 99
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.100
}
}
virtual_server 192.168.1.100 48066 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 192.168.1.43 48066 {
weight 1
TCP_CHECK{
connect_timeout 3
retry 3
delay_before_retry 3
}
}
real_server 192.168.1.44 48066 {
weight 1
TCP_CHECK{
connect_timeout 3
retry 3
delay_before_retry 3
}
}
}
[root@haproxy-keepalived-02 keepalived-2.2.0]# systemctl start keepalived.service
4.查看漂移地址
#keeplived01(192.168.1.43)
[root@haproxykeepalived-01 ~]# ip a show dev ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:1a:18:3c brd ff:ff:ff:ff:ff:ff
inet 192.168.1.43/24 brd 192.168.1.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.1.100/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::8269:8c10:81e:76d3/64 scope link noprefixroute
valid_lft forever preferred_lft forever
#keeplived02(192.168.1.44)
[root@haproxy-keepalived-02 keepalived-2.2.0]# ip a show dev ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:6c:da:64 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.44/24 brd 192.168.1.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet6 fe80::badb:4be:a83d:5aae/64 scope link noprefixroute
valid_lft forever preferred_lft forever
5.登录
#master02(192.168.1.30)
[root@mysql ~]# mysql -umycat -p123456 -h 192.168.1.100 -P 48066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.32, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 5
Current database: test
Current user: root@192.168.1.41
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)
Protocol version: 10
Connection: 192.168.1.100 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 48066
--------------
6.测试高可用
#关闭mycat01
#mycat01(192.168.1.41)
[root@mycat ~]# mycat stop
Stopping Mycat-server...
Mycat-server was not running.
#master02(192.168.1.30)
[root@mysql ~]# mysql -umycat -p123456 -h 192.168.1.100 -P 48066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.7.5-release-20200410174409 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.32, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 2
Current database: test
Current user: root@192.168.1.42 #从此处IP显示可以明显看出高可用以实现
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.29-mycat-1.6.7.5-release-20200410174409 MyCat Server (OpenCloudDB)
Protocol version: 10
Connection: 192.168.1.100 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 48066
--------------