Mycat读写分离部署及性能监控

2018年3月30日17:47:04 5 921 views
摘要

MyCat是一个开源的分布式数据库系统,是一个实现了MySQL协议的服务器,前端用户可以把它看作是一个数据库代理,用MySQL客户端工具和命令行访问,而其后端可以用MySQL原生协议与多个MySQL服务器通信,也可以用JDBC协议与大多数主流数据库服务器通信,其核心功能是分表分库,即将一个大表水平分割为N个小表,存储在后端MySQL服务器里或者其他数据库里。

Mycat读写分离部署及性能监控

实验环境:

  1. 主库:2.2.2.148
  2. 从库:2.2.2.149
  3. mycat:2.2.2.150
备注:
mycat的读写分离是建立在mysql主从的基础上的
参考:民工哥Linux运维   http://mp.weixin.qq.com/s/m2xP-BSIZaFInG4eEtWD6g

Mycat读写分离部署及性能监控一、下载安装包并安装jdk

[root@node4 softwares]# tar zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /data/servers/
[root@node4 softwares]# cd ../servers/
[root@node4 mycat]# vim /etc/profile
export JAVA_HOME JAVA_BIN PATH CLASSPATH
export JAVA_HOME=/data/servers/jdk1.8.0_121
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
export CLASSPATH=.$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib:$JAVA_HOME/lib/tools.jar
export MYCAT_HOME=/data/servers/mycat
export PATH=$PATH:$MYCAT_HOME/bin

[root@node4 mycat]# source /etc/profile
[root@node4 mycat]# java -version
java version "1.8.0_121"
Java(TM) SE Runtime Environment (build 1.8.0_121-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.121-b13, mixed mode)
[root@node4 mycat]#

二、准备mysql授权mycat登录账号

在真实的数据库主库和从库授权一个账号,用于mycat登录的,主库和从库授权同一个账号密码,就是 schema.xml要填的:
mysql> grant all on *.* to  mycat@'%' identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

账号密码可以提前测试一下,不然启动不报错的

三、修改mycat的配置文件

[root@node4 conf]# vim server.xml
取消如下5行(2~6行)的注释以及25行的密码更改:
  1. <!--默认是65535 64K 用于sql解析时最大文本长度 -->
  2.                 <property name="maxStringLiteralLength">65535</property>
  3.                 <property name="sequnceHandlerType">0</property>
  4.                 <property name="backSocketNoDelay">1</property>
  5.                 <property name="frontSocketNoDelay">1</property>
  6.                 <property name="processorExecutor">16</property>
  7. 和最下面的用户权限,我这里是用默认的,没有改动:
  8. <user name="root">
  9.                 <property name="password">123456</property>
  10.                 <property name="schemas">TESTDB</property>
  11.                 <!-- 表级 DML 权限设置 -->
  12.                 <!--
  13.                 <privileges check="false">
  14.                         <schema name="TESTDB" dml="0110" >
  15.                                 <table name="tb01" dml="0000"></table>
  16.                                 <table name="tb02" dml="1111"></table>
  17.                         </schema>
  18.                 </privileges>
  19.                  -->
  20.         </user>
  21.         <user name="user">
  22.                 <property name="password">123456</property>
  23.                 <property name="schemas">TESTDB</property>
  24.                 <property name="readOnly">true</property>
  25.         </user>
  1. [root@node4 conf]# vim schema.xml
  2.         <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
  3.         </schema>
  4.         <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
  5.                 /> -->
  6.         <dataNode name="dn1" dataHost="localhost1" database="realDB" />
  7.         <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
  8.          <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
  9.         <dataNode       name="jdbc_dn2" dataHost="jdbchost" database="db2" />
  10.         <dataNode name="jdbc_dn3"       dataHost="jdbchost" database="db3" /> -->
  11.         <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  12.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
  13.                 <heartbeat>select user()</heartbeat>
  14.                 <!-- can have multi write hosts -->
  15.                 <writeHost host="hostM1" url="2.2.2.148:3306" user="mycat"
  16.                                    password="123456">
  17.                         <!-- can have multi read hosts -->
  18.                         <readHost host="hostS2" url="2.2.2.149:3306" user="mycat" password="123456" />
  19.                 </writeHost>
  20.              <!--
  21.                 <writeHost host="hostS1" url="localhost:3316" user="root"
  22.                                    password="123456" />  -->
  23.                 <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
  24.         </dataHost>

四、启动mycat

[root@node4 bin]# ./mycat start
Starting Mycat-server...
[root@node4 bin]# ps -ef| grep mycat
root       3644      1  0 23:11 ?        00:00:00 /data/servers/mycat/bin/./wrapper-linux-x86-64 /data/servers/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/data/servers/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat
[root@node4 bin]# ss -tunlp| grep 66
tcp    LISTEN     0      100      :::9066                 :::*                   users:(("java",pid=3646,fd=74))
tcp    LISTEN     0      100      :::8066                 :::*                   users:(("java",pid=3646,fd=78))
端口: 9066 是默认的管理端口
8066是数据节点端口
[root@node4 bin]# mysql -h127.0.0.1 -uroot -p123456 -P9066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the <a href="https://www.zstcl.com/?tag=mysql" title="查看与 MySQL 相关的文章" target="_blank">MySQL</a> monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor)

Copyright (c) 2000, 2018, 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> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.01 sec)

查看数据读写入口

mysql> show @@datasource;
mysql> show @@datasource;
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST      | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 2.2.2.148 | 3306 | W    |      0 |   10 | 1000 |      16 |         0 |          0 |
| dn1      | hostS2 | mysql | 2.2.2.149 | 3306 | R    |      0 |    3 | 1000 |      10 |         0 |          0 |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
在2.2.2.150  (mycat上)创建一个表mycat ,然后再真正的数据库中查看是否有数据插入:
在mycat  操作:
[root@node4 bin]# mysql -h127.0.0.1 -uroot -p123456 -P8066
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-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2018, 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> use TESTDB;
Database changed
mysql> create table mycat(id int,name varchar(30));
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-------------------+
| Tables_in_mycatdb |
+-------------------+
| mycat             |
+-------------------+
1 row in set (0.01 sec)

mysql> insert into mycat values(1,"zhang"),(2,"wang");
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from mycat;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhang |
|    2 | wang  |
+------+-------+
2 rows in set (0.01 sec)

mysql>

在主库查看:

mysql> use mycatdb;
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> show tables;
+-------------------+
| Tables_in_mycatdb |
+-------------------+
| mycat             |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from mycat;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhang |
|    2 | wang  |
+------+-------+
2 rows in set (0.00 sec)

在从库查看:

mysql> use mycatdb;
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> select * from mycat;
+------+-------+
| id   | name  |
+------+-------+
|    1 | zhang |
|    2 | wang  |
+------+-------+
2 rows in set (0.00 sec)

<strong>至此,mycat的安装启动就OK了
</strong>

 

mysql> show @@help;   <strong> 这个命令会mycat的所有管理命令;</strong>

mysql> 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             |
| 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                 |
+------------------------------------------+--------------------------------------------+
58 rows in set (0.00 sec)

六、Mycat性能监控之Mycat-eye

  • 支持对Mycat、Mysql性能监控
  • 支持对Mycat的JVM内存提供监控服务
  • 支持对线程的监控
  • 支持对操作系统的CPU、内存、磁盘、网络的监控
下载WEB管理端软件
wget https://github.com/MyCATApache/Mycat-download/blob/master/mycat-web-1.0/Mycat-web-1.0-SNAPSHOT-20160617163048-linux.tar.gz
需要安装zookeeper
[root@mysql-m local]# wget http://mirrors.hust.edu.cn/apache/zookeeper/zookeeper-3.4.6/zookeeper-3.4.6.tar.gz
[root@mysql-m local]# tar zxf zookeeper-3.4.6.tar.gz
[root@mysql-m local]# ln -s zookeeper-3.4.6 zookeeper
[root@mysql-m conf]# cp zoo_sample.cfg zoo.cfg
[root@mysql-m bin]# ./zkServer.sh start
JMX enabled by default
Using config: /usr/local/zookeeper/bin/../conf/zoo.cfg
Starting zookeeper ... STARTED
[root@mysql-m ~]# lsof -i :2181
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
java 3639 root 25u IPv6 19337 0t0
TCP *:eforward (LISTEN)

安装WEB管理端

[root@mysql-m bin]# cd /usr/local/
[root@mysql-m local]# tar zxf Mycat-web-1.0-SNAPSHOT-20160617163048-linux.tar.gz
[root@mysql-m local]# cd mycat-web/mycat-web/WEB-INF/classes/
[root@mysql-m classes]# ll
total 28
drwxr-xr-x 3 root root 4096 Jun 17  2016 com
-rw-r--r-- 1 root root  367 Jun 17  2016 jdbc.properties
-rw-r--r-- 1 root root 4068 Jun 17  2016 log4j2.xml
drwxr-xr-x 3 root root 4096 Jun 17  2016 mybatis
-rw-r--r-- 1 root root  411 Jun 17  2016 mycat.properties
drwxr-xr-x 3 root root 4096 Jun 17  2016 org
drwxr-xr-x 2 root root 4096 Sep 26 00:21 spring
[root@mysql-m classes]# vim mycat.properties
#
#Mon Jan 16 15:37:36 CST 2012
show.period=3000000
zookeeper=10.0.0.11:2181

[root@mysql-m mycat-web]# /usr/local/mycat-web/start.sh &
[1] 3684
[root@mysql-m mycat-web]# nohup: ignoring input and appending output to `nohup.out'
[root@mysql-m mycat-web]# lsof -i :8082
COMMAND  PID USER  FD  TYPE DEVICE SIZE/OFF NODE NAME
java 3685 root 122u IPv6 20997 0t0 TCP *:us-cli (LISTEN)i

七、 客户端可以使用Navicat插入数据连接测试:

http://2.2.2.150:8082/mycat/#page/manger/mycat_add.html

Mycat读写分离部署及性能监控

八、访问mycat的管理页面

Mycat读写分离部署及性能监控

Mycat读写分离部署及性能监控Mycat读写分离部署及性能监控Mycat读写分离部署及性能监控

 

  • 我的微信
  • 微信扫一扫
  • weinxin
  • 微信打赏一下咯
  • 一分钱也是爱
  • weinxin
广告也精彩
暖先生
Canon/佳能 EOS 750D 套机EF-S 18-55mm IS STM
ELK全套视频
Edifier/漫步者 W830BT 无线蓝牙耳机
Xiaomi/小米 笔记本Pro 15.6英寸I7 16G 256G 轻薄游戏学生电脑

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:5   其中:访客  5   博主  0

    • 二哈 来自天朝的朋友 QQ浏览器 Windows 7 广东省深圳市 联通 0

      可以,很强势,过程很详细,正好最近有需要 :cool: :cool: :cool:

      • 梵高先生 来自天朝的朋友 360浏览器 Windows 10 四川省攀枝花市 电信 1

        server.xml 中的–schemas 配置管理的库 ,如果我要配置整个mysql的所有库 lunch 该怎么写? 能用%号匹配么?

        • 青春无语 来自天朝的朋友 火狐浏览器 Windows NT 江苏省徐州市 电信 1

          楼主好,用这个工具比直接使用MySQL(或其他支持的数据库)的读写分离功能,分区功能好在哪里呢,谢谢。

          • 操码哥 来自天朝的朋友 谷歌浏览器 Windows 10 江苏省镇江市 电信 0

            单纯的Mycat是不是无法实现实现主从复制的功能,必须配置mysql吗?

            • 小卖铺的老爷爷 来自天朝的朋友 QQ浏览器 Windows 7 广东省深圳市 联通 1

              学到了,谢谢楼主分享! :cool: :cool: