欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mycat安装、mysql主从、监控详细操作步骤

程序员文章站 2023-03-27 22:47:23
注: 完成搭建mysql主从架构; Linux 下安装mysql,默认忽略大小写,需要手动到/etc/my.cnf lower_case_table_names=1 使mys...

注:

完成搭建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) https://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安装

下载:https://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;