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)
扫一扫
164
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
