Mycat应用场景
Mycat发展到现在,适用的场景已经很丰富,而且不断有新用户给出新的创新性的方案,以下是几个典型的应用场景:
- 单纯的读写分离,此时配置最为简单,支持读写分离,主从切换;
- 分表分库,对于超过1000万的表进行分片,最大支持1000亿的单表分片;
- 多租户应用,每个应用一个库,但应用程序只连接Mycat,从而不改造程序本身,实现多租户化;
- 报表系统,借助于Mycat的分表能力,处理大规模报表的统计;
- 替代Hbase,分析大数据;
- 作为海量数据实时查询的一种简单有效方案,比如100亿条频繁查询的记录需要在3秒内查询出来结果,除了基于主键的查询,还可能存在范围查询或其他属性查询,此时Mycat可能是最简单有效的选择;
- Mycat长期路线图;
- 强化分布式数据库中间件的方面的功能,使之具备丰富的插件、强大的数据库智能优化功能、全面的系统监控能力、以及方便的数据运维工具,实现在线数据扩容、迁移等高级功能;
- 进一步挺进大数据计算领域,深度结合Spark Stream和Storm等分布式实时流引擎,能够完成快速的巨表关联、排序、分组聚合等 OLAP方向的能力,并集成一些热门常用的实时分析算法,让工程师以及DBA们更容易用Mycat实现一些高级数据分析处理功能。
- 不断强化Mycat开源社区的技术水平,吸引更多的IT技术专家,使得Mycat社区成为中国的Apache,并将Mycat推到Apache
基金会,成为国内顶尖开源项目,最终能够让一部分志愿者成为专职的Mycat开发者,荣耀跟实力一起提升。
Mycat部署
mycat | 192.168.248.130 | |
---|---|---|
mysql主 | 192.168.248.131 | |
mysql从 | 192.168.248.132 |
mysql 主从安装(略)
mycat下载、解压
[root@bre02 mycat]# wget http://dl.mycat.org.cn/1.6.7.6/20220109193553/Mycat-server-1.6.7.6-release-20220109193553-linux.tar.gz
[root@bre02 mycat]# tar -xf Mycat-server-1.6.7.6-release-20220109193553-linux.tar.gz -C /usr/local/
配置server.xml
#调整服务端口
<property name="serverPort">3306</property>
#用户
<user name="wjht" defaultAccount="true">
<property name="password">root12</property>
<property name="schemas">liulang,yayun</property> #用户能访问的库
<property name="defaultSchema">liulang</property>
<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema,不设置则为null,报错 -->
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
</mycat:server>
配置schema.xml
读写分离
Mycat主从自动切换测试
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="liulang" checkSQLschema="false" sqlMaxLimit="100" dataNode="haha" />
<schema name="yayun" checkSQLschema="false" sqlMaxLimit="100" dataNode="haha2" />
<dataNode name="haha" dataHost="Mycat-node" database="liulang" />
<dataNode name="haha2" dataHost="Mycat-node" database="yayun" />
<dataHost name="Mycat-node" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="Mysql-node1" url="192.168.248.131:3306" user="wjht" password="root123">
<readHost host="Mysql-node2" url="192.168.248.132:3306" user="wjht" password="root123">
</readHost>
</writeHost>
<writeHost host="Mysql-mnode2" url="192.168.248.132:3306" user="wjht" password="root123">
</writeHost>
</dataHost>
</mycat:schema>
[root@bre02 bin]# ./mycat start #开启
[root@bre02 bin]# ./mycat stop #关闭
[root@bre02 bin]# ./mycat restart #重启
[root@bre02 bin]# ./mycat status #查看启动状态
[root@bre02 bin]# ./mycat console #前台运行
[root@bre02 bin]# ./mycat pause #暂停
启动后登录客户端
[root@bre04 local]# mysql -h192.168.248.130 -P3306 -uwjht -proot12
[email protected]:(none) 02:13:18> show databases;
+----------+
| DATABASE |
+----------+
| liulang |
| yayun |
+----------+
2 rows in set (0.00 sec)
登录管理端
[root@bre04 ~]# mysql -h192.168.248.130 -P9066 -uwjht -proot12
[email protected]:(none) 03:56:44> show @@help;
+--------------------------------------------------------------+--------------------------------------------+
| STATEMENT | DESCRIPTION |
+--------------------------------------------------------------+--------------------------------------------+
| show @@time.current | Report current timestamp |
| show @@time.startup | Report startup timestamp |
| show @@version | Report Mycat Server version |
| show @@server | Report server status |
| show @@threadpool | Report threadPool status |
| show @@database | Report databases |
| show @@datanode | Report dataNodes |
| show @@datanode where schema = ? | Report dataNodes |
| show @@datasource | Report dataSources |
| show @@datasource where dataNode = ? | Report dataSources |
| show @@datasource.synstatus | Report datasource data synchronous |
| show @@datasource.syndetail where name=? | Report datasource data synchronous detail |
| show @@datasource.cluster | Report datasource galary cluster variables |
| show @@processor | Report processor status |
| show @@command | Report commands status |
| show @@connection | Report connection status |
| show @@cache | Report system cache usage |
| show @@backend | Report backend connection status |
| show @@session | Report front session details |
| show @@connection.sql | Report connection sql |
| show @@sql.execute | Report execute status |
| show @@sql.detail where id = ? | Report execute detail status |
| show @@sql | Report SQL list |
| show @@sql.high | Report Hight Frequency SQL |
| show @@sql.slow | Report slow SQL |
| show @@sql.resultset | Report BIG RESULTSET SQL |
| show @@sql.sum | Report User RW Stat |
| show @@sql.sum.user | Report User RW Stat |
| show @@sql.sum.table | Report Table RW Stat |
| show @@parser | Report parser status |
| show @@router | Report router status |
| show @@heartbeat | Report heartbeat status |
| show @@heartbeat.detail where name=? | Report heartbeat current detail |
| show @@slow where schema = ? | Report schema slow sql |
| show @@slow where datanode = ? | Report datanode slow sql |
| show @@sysparam | Report system param |
| show @@syslog limit=? | Report system mycat.log |
| show @@white | show mycat white host |
| show @@white.set=?,? | set mycat white host,[ip,user] |
| show @@directmemory=1 or 2 | show mycat direct memory usage |
| show @@check_global -SCHEMA= ? -TABLE=? -retry=? -interval=? | check mycat global table consistency |
| switch @@datasource name:index | Switch dataSource |
| kill @@connection id1,id2,... | Kill the specified connections |
| stop @@heartbeat name:time | Pause dataNode heartbeat |
| reload @@config | Reload basic config from file |
| reload @@config_all | Reload all config from file |
| reload @@route | Reload route config from file |
| reload @@user | Reload user config from file |
| reload @@sqlslow= | Set Slow SQL Time(ms) |
| reload @@user_stat | Reset show @@sql @@sql.sum @@sql.slow |
| rollback @@config | Rollback all config from memory |
| rollback @@route | Rollback route config from memory |
| rollback @@user | Rollback user config from memory |
| reload @@sqlstat=open | Open real-time sql stat analyzer |
| reload @@sqlstat=close | Close real-time sql stat analyzer |
| offline | Change MyCat status to OFF |
| online | Change MyCat status to ON |
| clear @@slow where schema = ? | Clear slow sql by schema |
| clear @@slow where datanode = ? | Clear slow sql by datanode |
+--------------------------------------------------------------+--------------------------------------------+
59 rows in set (0.01 sec)