mycat 安装、mysql主从、监控

注：

完成搭建mysql主从架构；

Linux 下安装mysql，默认忽略大小写，需要手动到/etc/my.cnf

lower_case_table_names=1 使mysql忽略大小写。

一、mycat搭建MySQL主从

主机环境描述：

mycat : ip 192.168.2.111

master: ip 192.168.2.110

slave : ip 192.168.2.109

1、解压mycat

[root@ht1 ~]# tar -zxf Mycat-server-1.5.1-RELEASE-20160705210037-linux.tar.gz -C /usr/local/

[root@ht1 ~]# cd /usr/local/

[root@ht1 local]# ls

bin etc include lib64 man mysql sbin src

doc games lib libexec mycat mysql-5.6.26 share

2、解压jdk

[root@ht1 ~]# tar -zxf jdk-8u92-linux-x64.tar.gz

[root@ht1 ~]# cp jdk1.8.0_92 /usr/local/mycat/

cp: omitting directory `jdk1.8.0_92'

[root@ht1 ~]# cp -r jdk1.8.0_92 /usr/local/mycat/

[root@ht1 ~]# vim /usr/local/mycat/conf/

3、修改配置文件 wrapper.conf

vim /usr/local/mycat/conf/wrapper.conf

# Java Application

wrapper.java.command=java

wrapper.working.dir=..

更改：

# Java Application

wrapper.java.command=/usr/local/mycat/jdk1.8.0_92/bin/ java

wrapper.working.dir=..

4、创建mycat用户，改变目录权限为mycat

[root@ht1 ~]# useradd mycat

[root@ht1 ~]# chown -R mycat.mycat /usr/local/my

mycat/ mysql/ mysql-5.6.26/

[root@ht1 ~]# chown -R mycat.mycat /usr/local/mycat

[root@ht1 ~]# passwd mycat

passwd: 'mycat123'.

5、修改schema.xml 配置参数

[root@ht1 ~]# vim /usr/local/mycat/conf/schema.xml

<schema name="lazulite" checkSQLschema="false" sqlMaxLimit="100" dataNode="lazulite">

<!-- 可以对要分片的表进行配置，也可以不配置 -->

</schema>

<dataNode name="lazulite" dataHost="localhost1" database="lazulite" />

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"

writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">

<heartbeat>show slave status</heartbeat>

<!-- can have multi write hosts -->

<writeHost host="hostM1" url="192.168.2.110:3306" user="root"

password="mysql123">

</writeHost>

<writeHost host="hosSM1" url="192.168.2.109:3306" user="root"

password="mysql123">

<!-- can have multi read hosts -->

<!--<readHost host="hostS2" url="192.168.2.200:3306" user="root" password="xxx" />-->

</writeHost>

<!--<writeHost host="hostS1" url="localhost:3316" user="root"-->

<!--password="123456" />-->

<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->

</dataHost>

注,参数：

schema标签的属性：

dataHost标签的属性：

balance属性：负载均衡类型，取值有3种情况：1.balance="0",不开启读写分离机制，所有读操作都发到

当前可用的writeHost上。2.balance = "1",全部的readHost与stand by writeHost 参与select语句的负载均衡，

简单的说，当双主从模式(M1->S1,M2->S1,并且M1与M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载

均衡。3.balance ="2"，所有读操作都随机的在writeHost、readhost上分发。4.balance ="3",所有读请求随机的分发到

writeHost对应的readhost执行,writeHost不负担读压力。

writeHost标签的属性：

1、url值：链接后端实例，如果使用native的dbDriver，则一般为address:port这种形式。用JDBC或

其他的dbDriver,则需要特殊至指定。当使用JDBC时可以这么写：jdbc:mysql://localhost:3306/。

2、user属性：后端存储实例的用户名字。

writeType属性：

负载均衡类型，取值有2种：

1.writeType="0",所有写操作发送到配置的第一个writeHost，第一个挂了切到还

生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中：dnixdex.properties。

2.writerType="1",所有写操作都随机的发送到配置writeHost，1.5以后废弃.

switchType属性，取值4种：

-1表示不自动切换；

1表示默认，自动切换；

2表示基于mysql主从同步的状态决定是否切换,心跳语句为show slave status;

3表示基于mysql galary cluster的切换机制（适合集群）

心跳语句为show status like 'wsrep%'.

dbDriver属性：

两种值：native和 JDBC

6、server.xml 配置

<user name="root">

<property name="password">mysql123</property>

<property name="schemas">lazulite</property>

</user>

7、启动和停止mycat

[root@ht1 mycat]# /usr/local/mycat/bin/mycat start

Starting Mycat-server...

[root@ht1 mycat]# /usr/local/mycat/bin/mycat stop

Stopping Mycat-server...

Stopped Mycat-server.

查看java

[root@ht1 mycat]# ps -ef |grep java

root 8893 8891 2 19:28 ? 00:00:01 /usr/local/mycat/jdk1.8.0_92/bin/java -DMYCAT_HOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/univocity-parsers-1.5.4.jar:lib/libwrapper-linux-x86-32.so:lib/ehcache-core-2.6.11.jar:lib/log4j-1.2.17.jar:lib/leveldb-api-0.7.jar:lib/curator-framework-2.9.0.jar:lib/dom4j-1.6.1.jar:lib/mapdb-1.0.7.jar:lib/jline-0.9.94.jar:lib/snakeyaml-1.16.jar:lib/sequoiadb-java-driver-1.0-20150615.070208-1.jar:lib/leveldb-0.7.jar:lib/guava-18.0.jar:lib/libwrapper-linux-x86-64.so:lib/netty-3.7.0.Final.jar:lib/xml-apis-1.0.b2.jar:lib/fastjson-1.2.7.jar:lib/json-20151123.jar:lib/curator-client-2.9.0.jar:lib/slf4j-log4j12-1.7.12.jar:lib/slf4j-api-1.7.12.jar:lib/Mycat-server-1.5.1-RELEASE.jar:lib/wrapper.jar:lib/druid-1.0.14.jar:lib/zookeeper-3.4.6.jar:lib/libwrapper-linux-ppc-64.so:lib/mongo-java-driver-2.11.4.jar -Dwrapper.key=t14fT1rAo1W2S6ud -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=8891 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp org.opencloudb.MycatStartup start

查看启动日志

[root@ht1 ~]# tail -f /usr/local/mycat/logs/wrapper.log

STATUS | wrapper | 2016/07/22 19:28:10 | Launching a JVM...

INFO | jvm 1 | 2016/07/22 19:28:10 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0

INFO | jvm 1 | 2016/07/22 19:28:11 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org

INFO | jvm 1 | 2016/07/22 19:28:11 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.

INFO | jvm 1 | 2016/07/22 19:28:11 |

INFO | jvm 1 | 2016/07/22 19:28:12 | log4j 2016-07-22 19:28:12 [./conf/log4j.xml] load completed.

INFO | jvm 1 | 2016/07/22 19:30:12 | MyCAT Server startup successfully. see logs in logs/mycat.log

STATUS | wrapper | 2016/07/22 19:36:30 | TERM trapped. Shutting down.

STATUS | wrapper | 2016/07/22 19:36:31 | <-- Wrapper Stopped

错误：

INFO | jvm 2 | 2016/08/25 18:59:32 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0

ERROR | wrapper | 2016/08/25 18:59:42 | JVM exited while loading the application.

INFO | jvm 2 | 2016/08/25 18:59:42 | Error: Exception thrown by the agent : java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: mycat: mycat: unknown error

STATUS | wrapper | 2016/08/25 18:59:47 | Launching a JVM...

INFO | jvm 3 | 2016/08/25 18:59:47 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0

解决办法：

[root@mycat ~]# vim /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

::1 localhost localhost.localdomain localhost6 localhost6.localdomain6

192.168.2.102 mycat

8、

客户端链接mycat ，默认端口号是8066;主要此处的用户名为server.xml中配置的用户名密码，

链接成功可以看到我们刚配置的lazulite代理库

[root@ht1 mycat]# mysql -h192.168.2.111 -uroot -pmysql123 -P8066 -Dlazulite

Warning: Using a password on the command line interface can be insecure.

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.5.8-mycat-1.5.1-RELEASE-20160705210037 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2015, 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 |

+----------+

| lazulite |

+----------+

1 row in set (0.00 sec)

9、mycat管理登录

登录：目前 mycat 有两个端口，8066 数据端口，9066 管理端口，命令行的登陆是通过 9066 管理端口来操

作，登录方式类似于 mysql 的服务端登陆

[root@ht1 mycat]# mysql -h192.168.2.111 -uroot -pmysql123 -P9066 -Dlazulite

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 10

Server version: 5.5.8-mycat-1.5.1-RELEASE-20160705210037 MyCat Server (monitor)

Copyright (c) 2000, 2015, 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 @@config_all;

ERROR 1003 (HY000): Unsupported statement

mysql> show @@config;

ERROR 1003 (HY000): Unsupported statement

mysql> show @@datasource;

+----------+--------+-------+---------------+------+------+--------+------+------+---------+

| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE |

+----------+--------+-------+---------------+------+------+--------+------+------+---------+

| lazulite | hostM1 | mysql | 192.168.2.110 | 3306 | W | 0 | 10 | 1000 | 632 |

| lazulite | hostS1 | mysql | 192.168.2.109 | 3306 | W | 0 | 1 | 1000 | 598 |

+----------+--------+-------+---------------+------+------+--------+------+------+---------+

2 rows in set (0.00 sec)

10、测试主备

[root@ht1 ~]# mysql -h192.168.2.111 -uroot -pmysql123 -P9066 -Dlazulite

Warning: Using a password on the command line interface can be insecure.

mysql> show @@datasource;

+----------+--------+-------+---------------+------+------+--------+------+------+---------+

| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE |

+----------+--------+-------+---------------+------+------+--------+------+------+---------+

| lazulite | hostM1 | mysql | 192.168.2.110 | 3306 | W | 0 | 1 | 1000 | 52 |

| lazulite | hostS1 | mysql | 192.168.2.109 | 3306 | W | 0 | 6 | 1000 | 62 |

+----------+--------+-------+---------------+------+------+--------+------+------+---------+

2 rows in set (0.00 sec)

mysql> switch @@datasource localhost1:1;#localhost1是schema中配置的datahost中的name值。

注：switch @@datasource name:index .切换数据源，name是schema中配置的datahost中name。index：schema中配置

的datahost的writeHost index位标，即按照从上往下依次，从0开始。

使用客户端连接mycat 192.168.2.111

执行插入操作：

INSERT INTO t VALUE(70);

查看原主库（110）：

mysql> select * from t;

+------+

| id |

+------+

| 1 |

| 1 |

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 6 |

+------+

9 rows in set (0.00 sec)

查看切换后的主库（原备库109）

mysql> select * from t;

+------+

| id |

+------+

| 1 |

| 1 |

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

| 6 |

| 6 |

| 70 |

+------+

10 rows in set (0.00 sec)

注：也可以分析[root@ht1 ~]# tail -f /usr/local/mycat/logs/mycat.log日志，查看切换情况。

错误

09/02 16:11:30.029 WARN [$_NIOREACTOR-0-RW] (MySQLDetector.java:139) -found MySQL master/slave Replication err !!! DBHostConfig [hostName

09/02 16:11:40.025 WARN [$_NIOREACTOR-0-RW] (MySQLDetector.java:139) -found MySQL master/slave Replication err !!! DBHostConfig [hostName

09/02 16:11:50.025 WARN [$_NIOREACTOR-0-RW] (MySQLDetector.java:139) -found MySQL master/slave Replication err !!! DBHostConfig [hostName

解决：

mysql> switch @@datasource localhost1:0;

切换到主库

-

------------------------------------ 二、mycat-web------------------------

1、zookeeper安装

下载：http://mirrors.hust.edu.cn/apache/zookeeper/

[root@ht1 ~]# tar zxf zookeeper-3.4.8.tar.gz -C /usr/local/

[root@ht1 ~]# cd /usr/local/zookeeper-3.4.8/

[root@ht1 zookeeper-3.4.8]# ls

bin CHANGES.txt contrib docs ivy.xml LICENSE.txt README_packaging.txt recipes zookeeper-3.4.8.jar zookeeper-3.4.8.jar.md5

build.xml conf dist-maven ivysettings.xml lib NOTICE.txt README.txt src zookeeper-3.4.8.jar.asc zookeeper-3.4.8.jar.sha1

[root@ht1 zookeeper-3.4.8]# cd bin/

[root@ht1 zookeeper-3.4.8]# cd conf/

[root@ht1 conf]# cp zoo_sample.cfg zoo.cfg

启动：ZooKeeper

[root@ht1 bin]# ./zkServer.sh start

ZooKeeper JMX enabled by default

Using config: /usr/local/zookeeper-3.4.8/bin/../conf/zoo.cfg

grep: /usr/local/zookeeper-3.4.8/bin/../conf/zoo.cfg: No such file or directory

mkdir: cannot create directory `': No such file or directory

Starting zookeeper ... STARTED

[root@ht1 bin]# ps -ef |grep zook

root 7018 7016 0 06:20 ? 00:00:51 /usr/local/mycat/jdk1.8.0_92/bin/java -DMYCAT_HOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/univocity-parsers-1.5.4.jar:lib/libwrapper-linux-x86-32.so:lib/ehcache-core-2.6.11.jar:lib/log4j-1.2.17.jar:lib/leveldb-api-0.7.jar:lib/curator-framework-2.9.0.jar:lib/dom4j-1.6.1.jar:lib/mapdb-1.0.7.jar:lib/jline-0.9.94.jar:lib/snakeyaml-1.16.jar:lib/sequoiadb-java-driver-1.0-20150615.070208-1.jar:lib/leveldb-0.7.jar:lib/guava-18.0.jar:lib/libwrapper-linux-x86-64.so:lib/netty-3.7.0.Final.jar:lib/xml-apis-1.0.b2.jar:lib/fastjson-1.2.7.jar:lib/json-20151123.jar:lib/curator-client-2.9.0.jar:lib/slf4j-log4j12-1.7.12.jar:lib/slf4j-api-1.7.12.jar:lib/Mycat-server-1.5.1-RELEASE.jar:lib/wrapper.jar:lib/druid-1.0.14.jar:lib/zookeeper-3.4.6.jar:lib/libwrapper-linux-ppc-64.so:lib/mongo-java-driver-2.11.4.jar -Dwrapper.key=42Jva9NvvuF5HmCZ -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=7016 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp org.opencloudb.MycatStartup start

root 7453 6897 0 08:53 pts/6 00:00:00 grep zook

2、mycat-web

下载：https://github.com/MyCATApache/Mycat-download/tree/master/mycat-web-1.0

[root@ht1 ~]# tar zxf Mycat-web-1.0-SNAPSHOT-20160617163048-linux.tar.gz -C /usr/local/

[root@ht1 ~]# cd /usr/local/mycat-web/

[root@ht1 mycat-web]# ls

etc lib mycat-web readme.txt start.jar start.sh

启动：mycat-web

数据导出导入

1、从mysql导出

mysqldump -c --skip-add-locks databaseName> /root/databaseName.sql

注意：（上面的语句没有-uroot -ppassword 参数，是因为 mysql 服务器设置了本机免密码等。

如果讴置了密码:通过以下命名导出(用户名为 root，密码为 123456)：

mysqldump -uroot -p123456 -c --skip-add-locks databaseName> /root/databaseName.sql）

说明:两个参数不可少，如下：

-c 参数不可少，-c, 全称为–complete-insert 表示使用完整得 insert 语句(用列名字)。

-–skip-add-locks 表示导数据时不加锁，如果加锁涉及多分片时容易寻致死锁。

2、导入到 mycat

将 databaseName.sql 拷贝到 mycat 集群中的一台 mysql 朋务器上/root 目录下。

连接 mycat：

mysql -uusername -ppassword -h172.17.xxx.xxx -P8066

切换到指定的数据库：

use databaseName;

导入脚本：

source /root/databaseName.sql;