文档章节

MySQL 数据库中间件 安装部署测试全过程

P
 PeakFang-BOK
发布于 11/17 11:14
字数 4752
阅读 21
收藏 3

1、环境准备

1.1、操作系统环境

[root@MyCat conf]# uname -a
Linux MyCat 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

1.2、关闭SELINUX

[root@RHEL6 ~]# vi /etc/selinux/config
[root@RHEL6 ~]# cat /etc/selinux/config  | grep disabled
#     disabled - No SELinux policy is loaded.
SELINUX=disabled

SElinux的关闭

关闭SELinux的方法:   修改/etc/selinux/config文件中的SELINUX="" 为 disabled ,然后重启。

如果不想重启系统,使用命令setenforce 0 注: setenforce 1 设置SELinux 成为enforcing模式 setenforce 0 设置SELinux 成为permissive模式。

1.3、关闭防火墙

service iptables stop
重启后永久性生效:
开启: chkconfig iptables on
关闭: chkconfig iptables off

1.4、克隆VM生成新的服务器

利用VMWare的克隆功能,克隆新的服务器。

1.5、修改克隆后的网络配置

[root@RHEL6 ~]# cat /etc/udev/rules.d/70-persistent-net.rules
# This file was automatically generated by the /lib/udev/write_net_rules
# program, run by the persistent-net-generator.rules rules file.
#
# You can modify it, as long as you keep each rule on a single
# line, and change only the value of the NAME= key.

# PCI device 0x8086:0x100f (e1000)

# PCI device 0x8086:0x100f (e1000)
SUBSYSTEM=="net", ACTION=="add", DRIVERS=="?*", ATTR{address}=="00:0c:29:54:5e:8d", ATTR{type}=="1", KERNEL=="eth*", NAME="eth0"
[root@RHEL6 ~]# cat /etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE=eth0
HWADDR=00:0c:29:54:5e:8d
TYPE=Ethernet
ONBOOT=yes
NM_CONTROLLED=yes
BOOTPROTO=none
IPADDR=192.168.0.184
PREFIX=24

1.6、修改hostname

[root@RHEL6 ~]# vi /etc/sysconfig/network
[root@RHEL6 ~]# hostname MyCat
[root@RHEL6 ~]# su -
[root@MyCat ~]# hostname

2、安装MySQL数据库软件

2.1、检查并卸载操作系统自带软件包

root@dh1 mysql-5.7.15-linux-glibc2.5-x86_64]# rpm -qa | grep -i mysql
mysql-libs-5.1.71-1.el6.x86_64
[root@dh1 mysql-5.7.15-linux-glibc2.5-x86_64]# rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64
[root@dh1 mysql-5.7.15-linux-glibc2.5-x86_64]# rpm -qa | grep -i mysql

2.2、创建用户及用户组

root@dh1 mysql-5.7.15-linux-glibc2.5-x86_64]# groupadd mysql
[root@dh1 mysql-5.7.15-linux-glibc2.5-x86_64]# useradd -r -g mysql mysql
[root@dh1 mysql-5.7.15-linux-glibc2.5-x86_64]# cat /etc/group | grep mysql
mysql:x:500:
[root@dh1 mysql-5.7.15-linux-glibc2.5-x86_64]# cat /etc/passwd | grep mysql
mysql:x:498:500::/home/mysql:/bin/bash

2.3、解压安装包修改目录权限

[root@dh1 ~]# tar -xf mysql-5.7.15-linux-glibc2.5-x86_64.tar
[root@dh1 ~]# tar -xzf mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz
[root@dh1 ~]# cp -a mysql-5.7.15-linux-glibc2.5-x86_64 /usr/local/mysql
[root@dh1 ~]# ls /usr/local/mysql/
bin  COPYING  docs  include  lib  man  README  share  support-files
[root@dh1 ~]# mkdir -p /usr/local/mysql/data
[root@dh1 ~]# chown -R mysql:mysql /usr/local/mysql/data/
[root@dh1 ~]# chown -R mysql /usr/local/mysql/
[root@dh1 ~]# chgrp -R mysql /usr/local/mysql/

2.4、配置my.cnf文件

[root@dh1 ~]# vi /etc/my.cnf
[root@dh1 ~]# cat /etc/my.cnf
[mysqld]
basedir =/usr/local/mysql
datadir =/usr/local/mysql/data
server_id =1
socket =/tmp/mysql.sock
lower_case_table_names=1
character-set-server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysql_dh1.log
pid-file=/usr/local/mysql/data/mysqld_dh1.pid
[mysql]
default-character-set=utf8

2.5、初始化数据库

[root@dh1 ~]# /usr/local/mysql/bin/mysql_install_db  --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
2016-11-25 22:04:54 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2016-11-25 22:04:57 [WARNING] The bootstrap log isn't empty:
2016-11-25 22:04:57 [WARNING] 2016-11-25T14:04:54.497735Z 0 [Warning] --bootstrap is deprecated. Please                consider using --initialize instead
2016-11-25T14:04:54.500297Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2016-11-25T14:04:54.500307Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

2.6、拷贝启动服务

[root@dh1 ~]# cp -a /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysql
[root@dh1 ~]# chmod +x /etc/rc.d/init.d/mysqld

2.7、查询服务器状态

[root@dh1 ~]# service mysqld status
MySQL is not running, but lock file (/var/lock/subsys/mysql[FAILED]
[root@dh1 ~]# service mysqld start
Starting MySQL.                                            [  OK  ]

2.8、查询默认密码并登陆

[root@dh1 ~]# cat /root/.mysql_secret
# Password set for user 'root@localhost' at 2016-11-25 22:04:54
hcp(Oz!Nm(7Z
[root@dh1 ~]# /usr/local/mysql/bin/mysql -uroot -p
Enter password:

 

2.9、修改默认密码及远程访问权限

mysql> set password=password('system');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to root@'%' identified by 'system';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> exit
Bye
[root@dh1 ~]# /usr/local/mysql/bin/mysql -uroot -p
Enter password:

参照以上步骤完成dh2、mycat服务器上mysql数据库的安装和配置,主要my.cnf文件中的server_id等差异性配置项。

3、安装配置mycat

3.1、安装配置java环境

解压jdk-7u80-linux-x64.gz文件,并配置java的环境变量

[root@MyCat ~]# tar xzf jdk-7u80-linux-x64.gz
[root@MyCat ~]# mv jdk1.7.0_80/ /usr/local/
编辑/etc/profile文件
# vim /etc/profile  添加如下内容
export JAVA_HOME=/usr/local/jdk1.7.0_80/
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
[root@MyCat ~]# source /etc/profile
[root@MyCat ~]# java -version
java version "1.7.0_80"
Java(TM) SE Runtime Environment (build 1.7.0_80-b15)
Java HotSpot(TM) 64-Bit Server VM (build 24.80-b11, mixed mode)

3.2、添加mycat用户,并设置密码

[root@MyCat ~]# useradd mycat
[root@MyCat ~]# passwd mycat
Changing password for user mycat.
New password:
BAD PASSWORD: it is based on a dictionary word
BAD PASSWORD: is too simple
Retype new password:
passwd: all authentication tokens updated successfully.

3.3、解压设置目录权限

[root@MyCat ~]# tar xzf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@MyCat ~]# mv mycat/ /usr/local/
[root@MyCat ~]# chown -R mycat.mycat /usr/local/mycat/

3.4、配置wrapper.conf

[root@MyCat ~]# cat /usr/local/mycat/conf/wrapper.conf  | grep additional
#wrapper.java.additional.1=
wrapper.java.additional.1=-DMYCAT_HOME=.
wrapper.java.additional.2=-server
wrapper.java.additional.3=-XX:MaxPermSize=64M
wrapper.java.additional.4=-XX:+AggressiveOpts
#wrapper.java.additional.5=-XX:MaxDirectMemorySize=2G
wrapper.java.additional.5=-XX:MaxDirectMemorySize=256m
wrapper.java.additional.6=-Dcom.sun.management.jmxremote
wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1984
wrapper.java.additional.8=-Dcom.sun.management.jmxremote.authenticate=false
wrapper.java.additional.9=-Dcom.sun.management.jmxremote.ssl=false
#wrapper.java.additional.10=-Xmx4G
wrapper.java.additional.10=-Xmx512m
#wrapper.java.additional.11=-Xms1G
wrapper.java.additional.11=-Xms128m

Args

Comment

-Xmx

设置JVM最大可用内存

-Xms

设置JVM初始内存

-Xmn

设置年轻代内存大小,整个JVM内存大小=年轻代大小 + 年老代大小 + 持久代大小 ,持久代一般固定大小为64m,所以增大年轻代后,将会减小年老代大小。此值对系统性能影响较大,官方推荐配置为整个堆的3/8

-Xss

设置每个线程的堆栈大小

3.5、配置server.xml

[root@MyCat ~]# vi /usr/local/mycat/conf/server.xml
[root@MyCat ~]# cat /usr/local/mycat/conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
        - you may not use this file except in compliance with the License. - You
        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
        - - Unless required by applicable law or agreed to in writing, software -
        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
        License for the specific language governing permissions and - limitations
        under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <!--1为开启实时统计、0为关闭 -->
        <!--<property name="useSqlStat">0</property> -->
        <!--1为开启全加班一致性检测、0为关闭 -->
        <!--<property name="useGlobleTableCheck">0</property> -->
        <!--<property name="sequnceHandlerType">2</property> -->
        <!--1为开启mysql压缩协议-->
        <!--<property name="useCompression">1</property>-->
        <!--设置模拟的MySQL版本号-->
        <!--<property name="fakeMySQLVersion">5.6.20</property>-->
        <!--<property name="processorBufferChunk">40960</property> -->
        <!--<property name="processorExecutor">32</property> -->
        <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
        <!--<property name="processorBufferPoolType">0</property> -->
        <!--默认是65535 64K 用于sql解析时最大文本长度 -->
        <!--<property name="maxStringLiteralLength">65535</property>-->
        <!--<property name="sequnceHandlerType">0</property>-->
        <!--<property name="backSocketNoDelay">1</property>-->
        <!--<property name="frontSocketNoDelay">1</property>-->
        <!--<property name="processorExecutor">16</property>-->
        <property name="defaultSqlParser">druidparser</property>
        <property name="processors">2</property>
        <property name="serverPort">8066</property>
        <property name="managerPort">9066</property>
        <property name="bindIp">0.0.0.0</property>
        <!--<property name="frontWriteQueueSize">4096</property>  -->
        <!--<property name="idleTimeout">300000</property> -->
        <!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局
            表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志-->
        <!--<property name="handleDistributedTransactions">0</property> -->
        <!--off heap for merge/order/group/limit      1开启   0关闭 -->
        <!--<property name="useOffHeapForMerge">1</property> -->
        <!-- 单位为m -->
        <!--<property name="memoryPageSize">1m</property> -->
        <!-- 单位为k -->
        <!--<property name="spillsFileBufferSize">1k</property> -->
        <!--<property name="useStreamOutput">0</property> -->
        <!-- 单位为m -->
        <!--<property name="systemReserveMemorySize">384m</property> -->
        <!--是否采用zookeeper协调切换  -->
        <!--<property name="useZKSwitch">true</property> -->

        </system>

        <!-- 全局SQL防火墙设置 -->
        <!--
        <firewall>
            <whitehost>
                <host host="127.0.0.1" user="mycat"/>
                <host host="127.0.0.2" user="mycat"/>
            </whitehost>
            <blacklist check="false">
            </blacklist>
        </firewall>
        -->

        <user name="root">
        <property name="password">123456</property>
        <property name="schemas">CCTEST</property>
        <!-- 表级 DML 权限设置 -->
        <!--
        <privileges check="false">
                <schema name="TESTDB" dml="0110" >
                        <table name="tb01" dml="0000"></table>
                        <table name="tb02" dml="1111"></table>
                </schema>
        </privileges>
        -->
        </user>

        <user name="user">
                <property name="password">user</property>
                <property name="schemas">CCTEST</property>
                <property name="readOnly">true</property>
        </user>

        <user name="cc">
            <property name="password">cc</property>
            <property name="schemas">CCTEST</property>
        </user>

</mycat:server>

3.6、配置schema.xml

[root@MyCat ~]# vi /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="CCTEST" checkSQLschema="false" sqlMaxLimit="100">
        <table name="catworld"  dataNode="sd1,sd2,sd3"  rule="mod-long" />
        <table name="catworld4"  dataNode="sd1,sd2,sd3,sd4"  rule="mod4-long" />
    </schema>

    <dataNode name="sd1" dataHost="dh101" database="my1" />
    <dataNode name="sd2" dataHost="dh201" database="my2" />
    <dataNode name="sd3" dataHost="dh301" database="my3" />
    <dataNode name="sd4" dataHost="dh101" database="my4" />

    <dataHost name="dh101" maxCon="100" minCon="10" balance="0" writeType="0"
              dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
         <heartbeat>select user()</heartbeat>
         <writeHost host="dh1" url="192.168.0.184:3306" user="root" password="system">
         <!-- can have multi read hosts -->
         </writeHost>
    </dataHost>
    <dataHost name="dh201" maxCon="100" minCon="10" balance="0" writeType="0"
                  dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
         <heartbeat>select user()</heartbeat>
         <writeHost host="dh2" url="192.168.0.185:3306" user="root" password="system">
         <!-- can have multi read hosts -->
         </writeHost>
    </dataHost>
    <dataHost name="dh301" maxCon="100" minCon="10" balance="0" writeType="0"
                  dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="MyCat" url="192.168.0.183:3306" user="root" password="system">
        <!-- can have multi read hosts -->
        </writeHost>
    </dataHost>
</mycat:schema>

3.7、配置rule.xml

[root@MyCat ~]# vi /usr/local/mycat/conf/rule.xml
[root@MyCat ~]# cat /usr/local/mycat/conf/rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
        - you may not use this file except in compliance with the License. - You
        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
        - - Unless required by applicable law or agreed to in writing, software -
        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
        License for the specific language governing permissions and - limitations
        under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
    <tableRule name="mod-long">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>
        <tableRule name="mod4-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod4-long</algorithm>
                </rule>
        </tableRule>

    <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
        <property name="count">3</property>
    </function>

    <function name="mod4-long" class="io.mycat.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">4</property>
    </function>

</mycat:rule>

3.8、创建具体的数据库

[root@dh1 ~]# mysql -uroot -p -h192.168.0.184
Enter password:
mysql> create database my1;
Query OK, 1 row affected (0.00 sec)
mysql> create database my4;
Query OK, 1 row affected (0.00 sec)

[root@dh2 ~]# mysql -uroot -p -h192.168.0.185
Enter password:
mysql> create database my2;
Query OK, 1 row affected (0.00 sec)

[root@MyCat ~]# mysql -uroot -p -h 192.168.0.183
Enter password:
mysql> create database my3;
Query OK, 1 row affected (0.00 sec)

3.9、启动MyCat

[root@MyCat bin]# ./mycat  start
Starting Mycat-server...
查看端口情况
[root@MyCat ~]# netstat -ant | grep 8066
tcp        0      0 :::8066                     :::*                        LISTEN

3.10、通过MyCat连接数据库

可以在内网中的其他服务器上通过以下方式连接到MyCat。
[root@MyCat ~]# mysql -ucc -pcc -P8066 -h192.168.0.183
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, 2016, 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 |
+----------+
| CCTEST   |
+----------+
1 row in set (0.00 sec)

mysql> use CCTEST;
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 CCTEST |
+------------------+
| catworld         |
| catworld4        |
+------------------+
2 rows in set (0.00 sec)

3.11、创建schema配置的逻辑表及基础操作

mysql> create table catworld4(id int primary key, name varchar(30));
Query OK, 0 rows affected (0.69 sec)
mysql> use CCTEST;
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 CCTEST |
+------------------+
| catworld         |
| catworld4        |
+------------------+
2 rows in set (0.00 sec)

mysql> desc catworld4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.05 sec)

mysql> explain desc catworld4;
+-----------+----------------+
| DATA_NODE | SQL            |
+-----------+----------------+
| sd2       | desc catworld4 |
+-----------+----------------+
1 row in set (0.00 sec)

mysql> show create table catworld4\G;
*************************** 1. row ***************************
       Table: catworld4
Create Table: CREATE TABLE `catworld4` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.03 sec)

ERROR:
No query specified

mysql> select database();
+------------+
| DATABASE() |
+------------+
| CCTEST     |
+------------+
1 row in set (0.00 sec)

mysql> explain select * from catworld4;
+-----------+-----------------------------------+
| DATA_NODE | SQL                               |
+-----------+-----------------------------------+
| sd1       | SELECT * FROM catworld4 LIMIT 100 |
| sd2       | SELECT * FROM catworld4 LIMIT 100 |
| sd3       | SELECT * FROM catworld4 LIMIT 100 |
| sd4       | SELECT * FROM catworld4 LIMIT 100 |
+-----------+-----------------------------------+
4 rows in set (0.15 sec)

mysql> explain select count(1) from catworld4;
+-----------+----------------------------------------------------+
| DATA_NODE | SQL                                                |
+-----------+----------------------------------------------------+
| sd1       | SELECT COUNT(1) AS COUNT0 FROM catworld4 LIMIT 100 |
| sd2       | SELECT COUNT(1) AS COUNT0 FROM catworld4 LIMIT 100 |
| sd3       | SELECT COUNT(1) AS COUNT0 FROM catworld4 LIMIT 100 |
| sd4       | SELECT COUNT(1) AS COUNT0 FROM catworld4 LIMIT 100 |
+-----------+----------------------------------------------------+
4 rows in set (0.00 sec)
[root@dh1 ~]# mysql -uroot -p -h192.168.0.184
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.7.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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           |
+--------------------+
| information_schema |
| my1                |
| my4                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> use my1;
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_my1 |
+---------------+
| catworld4     |
+---------------+
1 row in set (0.00 sec)

mysql> select * from catworld4;
+----+------+
| id | name |
+----+------+
|  4 | abc  |
|  8 | abc  |
+----+------+
2 rows in set (0.00 sec)

mysql> use my4;
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 catworld4;
+----+------+
| id | name |
+----+------+
|  3 | abc  |
|  7 | abc  |
+----+------+
2 rows in set (0.00 sec)

3.12、相关日志目录

/usr/local/mycat/logs/wrapper.log   ---启动脚本的相关日志记录在这里面
/usr/local/mycat/logs/mycat.log     ---java进程的相关日志记录在这里面

4、管理MyCat

4.1、MyCat启动与关闭

[root@MyCat ~]# /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }
启动后可以通过以下方式确认是否正常
[root@MyCat ~]# netstat -ant | grep 8066
tcp        0      0 :::8066                     :::*                        LISTEN
[root@MyCat ~]# cat /usr/local/mycat/logs/wrapper.log

4.2、MyCat常用管理命令

[root@MyCat ~]# netstat -ant | grep 8066
tcp        0      0 :::8066                     :::*                        LISTEN
[root@MyCat ~]# grep 66 /usr/local/mycat/conf/server.xml
        <property name="serverPort">8066</property>
        <property name="managerPort">9066</property>
[root@MyCat ~]# netstat -ant | grep 66
tcp        0      0 :::8066                     :::*                        LISTEN
tcp        0      0 :::9066                     :::*                        LISTEN
登录管理口
[root@MyCat ~]# mysql -ucc -p -P9066 -h 192.168.0.183
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor)

Copyright (c) 2000, 2016, 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>
命令总览
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)
命令实例
参考资料:
http://uzki.vip/?p=127
mysql> show @@server;
+--------------+-------------+--------------+------------+---------------+---------------+---------+--------+
| UPTIME       | USED_MEMORY | TOTAL_MEMORY | MAX_MEMORY | RELOAD_TIME   | ROLLBACK_TIME | CHARSET | STATUS |
+--------------+-------------+--------------+------------+---------------+---------------+---------+--------+
| 17h 17m 57s  |    23330192 |    129761280 |  518979584 | 1480955201318 |            -1 | utf8    | ON     |
+--------------+-------------+--------------+------------+---------------+---------------+---------+--------+
1 row in set (0.01 sec)

mysql> show @@threadpool;
+------------------+-----------+--------------+-----------------+----------------+------------+
| NAME             | POOL_SIZE | ACTIVE_COUNT | TASK_QUEUE_SIZE | COMPLETED_TASK | TOTAL_TASK |
+------------------+-----------+--------------+-----------------+----------------+------------+
| Timer            |         2 |            0 |               0 |         143511 |     143511 |
| BusinessExecutor |         4 |            0 |               0 |            154 |        154 |
+------------------+-----------+--------------+-----------------+----------------+------------+
2 rows in set (0.00 sec)

mysql> show @@databases;
+----------+
| DATABASE |
+----------+
| CCTEST   |
+----------+
1 row in set (0.00 sec)

mysql> show @@datanode;
+------+-----------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST   | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+-----------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| sd1  | dh101/my1 |     0 | mysql |      0 |    5 |  100 |       6 |          0 |        0 |       0 |            -1 |
| sd2  | dh201/my2 |     0 | mysql |      0 |   10 |  100 |    6244 |          0 |        0 |       0 |            -1 |
| sd3  | dh301/my3 |     0 | mysql |      0 |   10 |  100 |    6245 |          0 |        0 |       0 |            -1 |
| sd4  | dh101/my4 |     0 | mysql |      0 |    8 |  100 |    6361 |          0 |        0 |       0 |            -1 |
+------+-----------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
4 rows in set (0.01 sec)

mysql> show @@datanode where schema=CCTEST;
+------+-----------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST   | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+-----------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| sd1  | dh101/my1 |     0 | mysql |      0 |    5 |  100 |       6 |          0 |        0 |       0 |            -1 |
| sd2  | dh201/my2 |     0 | mysql |      0 |   10 |  100 |    6247 |          0 |        0 |       0 |            -1 |
| sd3  | dh301/my3 |     0 | mysql |      0 |   10 |  100 |    6248 |          0 |        0 |       0 |            -1 |
| sd4  | dh101/my4 |     0 | mysql |      0 |    8 |  100 |    6364 |          0 |        0 |       0 |            -1 |
+------+-----------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
4 rows in set (0.00 sec)

mysql> show @@datasource;
+----------+-------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME  | TYPE  | HOST          | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+-------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| sd3      | MyCat | mysql | 192.168.0.183 | 3306 | W    |      0 |   10 |  100 |    6250 |         1 |          0 |
| sd4      | dh1   | mysql | 192.168.0.184 | 3306 | W    |      0 |   13 |  100 |    6372 |         0 |          1 |
| sd1      | dh1   | mysql | 192.168.0.184 | 3306 | W    |      0 |   13 |  100 |    6372 |         0 |          1 |
| sd2      | dh2   | mysql | 192.168.0.185 | 3306 | W    |      0 |   10 |  100 |    6249 |         0 |          0 |
+----------+-------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
4 rows in set (0.00 sec)

mysql> show @@connection;
+------------+------+---------------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| PROCESSOR  | ID   | HOST          | PORT | LOCAL_PORT | USER | SCHEMA | CHARSET | NET_IN | NET_OUT | ALIVE_TIME(S) | RECV_BUFFER | SEND_QUEUE | txlevel | autocommit |
+------------+------+---------------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| Processor0 |    6 | 192.168.0.183 | 9066 |      40647 | cc   | NULL   | utf8:33 |    345 |    6074 |           305 |        4096 |          0 |         |            |
+------------+------+---------------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
1 row in set (0.00 sec)

mysql> show @@sql;
Empty set (0.00 sec)

mysql> show @@sql.high;
Empty set (0.00 sec)

mysql> show @@sql.sum.table;
Empty set (0.00 sec)

mysql> show @@heartbeat;
+-------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME  | TYPE  | HOST          | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+-------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| MyCat | mysql | 192.168.0.183 | 3306 |       1 |     0 | idle   |       0 | 0,0,0        | 2016-12-06 17:48:14 | false |
| dh2   | mysql | 192.168.0.185 | 3306 |       1 |     0 | idle   |       0 | 0,0,0        | 2016-12-06 17:48:14 | false |
| dh1   | mysql | 192.168.0.184 | 3306 |       1 |     0 | idle   |       0 | 0,0,0        | 2016-12-06 17:48:14 | false |
+-------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
3 rows in set (0.00 sec)

mysql> show @@sysparam;
+-------------------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PARAM_NAME                    | PARAM_VALUE        | PARAM_DESCR                                                                                                                                                                                                                                                                                                                       |
+-------------------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| processors                    | 2                  | 主要用于指定系统可用的线程数,默认值为Runtime.getRuntime().availableProcessors()方法返回的值。主要影响processorBufferPool、processorBufferLocalPercent、processorExecutor属性。NIOProcessor的个数也是由这个属性定义的,所以调优的时候可以适当的调高这个属性。                                                                     |
| processorBufferChunk          | 4096B              | 指定每次分配Socket Direct Buffer的大小,默认是4096个字节。这个属性也影响buffer pool的长度。                                                                                                                                                                                                                                       |
| processorBufferPool           | 2097152B           | 指定bufferPool计算 比例值。由于每次执行NIO读、写操作都需要使用到buffer,系统初始化的时候会建立一定长度的buffer池来加快读、写的效率,减少建立buffer的时间                                                                                                                                                                          |
| processorBufferLocalPercent   | 100                | 就是用来控制分配这个pool的大小用的,但其也并不是一个准确的值,也是一个比例值。这个属性默认值为100。线程缓存百分比 = bufferLocalPercent / processors属性。                                                                                                                                                                         |
| processorExecutor             | 4                  | 主要用于指定NIOProcessor上共享的businessExecutor固定线程池大小。mycat在需要处理一些异步逻辑的时候会把任务提交到这个线程池中。新版本中这个连接池的使用频率不是很大了,可以设置一个较小的值。                                                                                                                                       |
| sequnceHandlerType            | 本地文件方式       | 指定使用Mycat全局序列的类型。                                                                                                                                                                                                                                                                                                     |
| Mysql_packetHeaderSize        | 4B                 | 指定Mysql协议中的报文头长度。默认4                                                                                                                                                                                                                                                                                                |
| Mysql_maxPacketSize           | 16M                | 指定Mysql协议可以携带的数据最大长度。默认16M                                                                                                                                                                                                                                                                                      |
| Mysql_idleTimeout             | 30分钟             | 指定连接的空闲超时时间。某连接在发起空闲检查下,发现距离上次使用超过了空闲时间,那么这个连接会被回收,就是被直接的关闭掉。默认30分钟                                                                                                                                                                                              |
| Mysql_charset                 | utf8               | 连接的初始化字符集。默认为utf8                                                                                                                                                                                                                                                                                                    |
| Mysql_txIsolation             | REPEATED_READ      | 前端连接的初始化事务隔离级别,只在初始化的时候使用,后续会根据客户端传递过来的属性对后端数据库连接进行同步。默认为REPEATED_READ                                                                                                                                                                                                   |
| Mysql_sqlExecuteTimeout       | 300秒              | SQL执行超时的时间,Mycat会检查连接上最后一次执行SQL的时间,若超过这个时间则会直接关闭这连接。默认时间为300秒                                                                                                                                                                                                                      |
| Mycat_processorCheckPeriod    | 1秒                | 清理NIOProcessor上前后端空闲、超时和关闭连接的间隔时间。默认是1秒                                                                                                                                                                                                                                                                 |
| Mycat_dataNodeIdleCheckPeriod | 300秒              | 对后端连接进行空闲、超时检查的时间间隔,默认是300秒                                                                                                                                                                                                                                                                               |
| Mycat_dataNodeHeartbeatPeriod | 10秒               | 对后端所有读、写库发起心跳的间隔时间,默认是10秒                                                                                                                                                                                                                                                                                  |
| Mycat_bindIp                  | 0.0.0.0            | mycat服务监听的IP地址,默认值为0.0.0.0                                                                                                                                                                                                                                                                                            |
| Mycat_serverPort              | 8066               | mycat的使用端口,默认值为8066                                                                                                                                                                                                                                                                                                     |
| Mycat_managerPort             | 9066               | mycat的管理端口,默认值为9066                                                                                                                                                                                                                                                                                                     |
+-------------------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
18 rows in set (0.00 sec)

 

© 著作权归作者所有

共有 人打赏支持
P
粉丝 7
博文 130
码字总数 328629
作品 0
西安
架构师
私信 提问
maxscale配合MHA搭建读写分离的高可用架构(基于GTID replication主从架构,mysql5.6)

基于GTID的主从replication并配合MHA搭建高可用架构,请参考之前的博客:http://linzhijian.blog.51cto.com/1047212/1906434。这里只叙述如何在此基础上增加maxscale中间件,实现读写分离的功...

Jenkin_lin
2017/04/07
0
0
mysql高可用MHA部署全过程

部署计划 本次部署采用3台服务器,mha-manager不单独使用一台服务器安装,生产上可以单独出来,本次使用采用centos6.9系统(使用 http://youprince.blog.51cto.com/9272426/1974967 优化 ),...

ImPoorWretch
2017/11/03
0
0
数据库中间件01-认识mycat

简述 Mycat是国产的一套免费开源的分布式数据库中间件。想必做开发或者运维的朋友对中间件这个词应该是比较熟悉了,我们见到过java中间件,消息中间件等等,这里又来了一个数据库中间件。那么...

细节探索者
05/11
0
0
calvinwilliams/mysqlda

mysqlda - MySQL数据库中间件 1. 概述 1.1. 数据分布式切分方式 1.2. mysqlda 2. 架构与原理 2.1. 体系架构 2.2. 工作原理 2.3. 简易案例 2.4. 内部数据实体和关系 3. 安装部署 3.1. 解开mys...

calvinwilliams
2017/09/04
0
0
canal demo搭建全记录

一、环境介绍 canal是阿里开源的中间件,主要用于同步mysql数据库变更。具体参见:https://github.com/alibaba/canal/releases 搭建环境: vmware centos7 部署mysql和canal windows开发can...

陈敬(Cathy)
07/16
0
0

没有更多内容

加载失败,请刷新页面

加载更多

《Maven官方文档》-Maven依赖机制简介

《Maven官方文档》-Maven依赖机制简介 原文地址 译者:Tyrian 依赖机制是Maven最为用户熟知的特性之一,同时也是Maven所擅长的领域之一。单个项目的依赖管理并不难, 但是当你面对包含数百个...

tantexian
11分钟前
0
0
基于 Docker 快速部署多需求 Spark 自动化测试环境

引言 在进行数据分析时,Spark 越来越广泛的被使用。在测试需求越来越多、测试用例数量越来越大的情况下,能够根据需求快速自动化部署 Spark 环境、快速完成所有测试越来越重要。 本文基于 ...

呐呐丶嘿
28分钟前
2
0
支付宝APP支付之查看支付宝商户ID

1、登录支付宝蚂蚁金服开放平台 2、查看账号详情,选择合作伙伴管理,账户管理,查看角色身份,此处的PID就是商户ID 3、点击秘钥管理,可查看绑定的相关应用及其APPID等信息

Code辉
31分钟前
2
0
崛起于Springboot2.X之通讯WebSocket(40)

技术简介:Springboot2.0.3+freemaker+websocket 1、添加pom依赖 <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-bo......

木九天
40分钟前
1
0
Java常用四大线程池用法以及ThreadPoolExecutor详解

为什么用线程池? 1.创建/销毁线程伴随着系统开销,过于频繁的创建/销毁线程,会很大程度上影响处-理效率 2.线程并发数量过多,抢占系统资源从而导致阻塞 3.对线程进行一些简单的管理 在Java中...

孟飞阳
42分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部