实验环境和软件包：
Windows 7 64位旗舰版 安装内存 16G
Inter(R)Core(TM)i5-4570 CPU @ 3.20GHz 4核CPU
CentOS-7-x86_64-Minimal-1503-01.iso
VMware Workstation ：10.0.2
CentOS 7操作系统：3G的内存
Mycat软件包：Mycat-server-1.3.0.3 现在升级到啥版本没看过，套路都差不多
jdk软件包：java-1.7.0-openjdk
数据库软件包：mariadb-5.5.41 mariadb-server-5.5.41
MyCAT官网地址：http://mycat.io
http://www.mycat.org.cn
http://i.mycat.io/forum.php
https://github.com/MyCATApache/Mycat-doc
[root@mariadb-10000 ~]# yum -y install java-1.7.0-openjdk //安装jdk1.7.0
[root@mariadb-10000 ~]# yum -y install wget zip unzip
[root@mariadb-10000 ~]# cd /opt
[root@mariadb-10000 opt]# wget --no-check-certificate https://raw.githubusercontent.com/MyCATApache/Mycat-download/36087f53413aff900f78d93b975787cb9fd2a2a9/1.3.0.3-release/Mycat-server-1.3.0.3-release-20150527095523-linux.tar.gz
[root@mariadb-10000 opt]# wget --no-check-certificate https://github.com/MyCATApache/Mycat-download/blob/master/1.3.0.3-release/Mycat-server-1.3.0.3-release-20150527095523-testtool.tar.gz
[root@mariadb-10000 opt]# tar zxvf Mycat-server-1.3.0.3-release-20150527095523-linux.tar.gz
[root@mariadb-10000 opt]# tar zxvf Mycat-server-1.3.0.3-release-20150527095523-testtool.tar.gz
[root@mariadb-10000 opt]# ls ./mycat
bin catlet conf lib logs version.txt
[root@mariadb-10000 opt]# mv /opt/mycat /usr/local //移动解压文件夹及其文件到目录/usr/local下
[root@mariadb-10000 opt]#
[root@mariadb-10000 opt]# ls /usr/local/mycat //查看mycat服务的安装目录列表
bin catlet conf lib logs version.txt
[root@mariadb-10000 opt]#
[root@mariadb-10000 opt]# useradd mycat
[root@mariadb-10000 opt]# chown -R mycat:mycat /usr/local/mycat
[root@mariadb-10000 opt]# passwd mycat
Changing password for user mycat.
New password: 123
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 123
passwd: all authentication tokens updated successfully.
[root@mariadb-10000 opt]#
[root@mariadb-10000 opt]# find / -name java -xtype f
/etc/alternatives/java
/var/lib/alternatives/java
/usr/bin/java
/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.85-2.6.1.2.el7_1.x86_64/jre/bin/java
/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.85-2.6.1.2.el7_1.x86_64/jre-abrt/bin/java
[root@mariadb-10000 opt]#
[root@mariadb-10000 opt]# /usr/bin/java -version
java version "1.7.0_85"
OpenJDK Runtime Environment (rhel-2.6.1.2.el7_1-x86_64 u85-b01)
OpenJDK 64-Bit Server VM (build 24.85-b03, mixed mode)
[root@mariadb-10000 opt]#
调整wrapper.conf参数默认值:
[root@mariadb-10000 opt]# vi /usr/local/mycat/conf/wrapper.conf
# Java Application
wrapper.java.command=/usr/bin/java
# Java Additional Parameters
wrapper.java.additional.5=-XX:MaxDirectMemorySize=1G
# Initial Java Heap Size (in MB)
#wrapper.java.initmemory=3
wrapper.java.initmemory=1024
若启动报内存不够，可以试着将上述内存都改小，改为1G或512M
http://wrapper.tanukisoftware.com/doc/english/properties.html Example of Configuration File "wrapper.conf"
注意：若是LINUX版本的mariadb,则需要设置为mariadb大小写不敏感,否则可能会发生表找不到的问题。
在MySQL的配置文件中my.ini [mysqld] 中增加如下一行:
lower_case_table_names = 1
[root@mariadb-10000 opt]# yum -y install mariadb mariadb-server
[root@mariadb-10000 opt]# systemctl enable mariadb.service
[root@mariadb-10000 opt]# systemctl restart mariadb.service
[root@mariadb-10000 opt]# mycat install (可选) //Warp方式的命令，可以安装成服务并启动或停止。把mycat安装成服务的方式启动
[root@mariadb-10000 opt]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
[root@mariadb-10000 opt]# /usr/local/mycat/bin/mycat status
Mycat-server is running (9842).
[root@mariadb-10000 opt]#
[root@mariadb-10000 opt]# ss -antp
Mycat树形目录结构如下:
/usr/local/mycat/version.txt
/usr/local/mycat/bin/
mycat
startup_nowrap.sh
wrapper-linux-x86-32
rehash.sh
wrapper-linux-ppc-64
wrapper-linux-x86-64
/usr/local/mycat/catlet/
/usr/local/mycat/conf/
autopartition-long.txt
partition-hash-int.txt
sequence_conf.properties
cacheservice.properties
router.xml
sequence_db_conf.properties
ehcache.xml
rule.xml 配置文件说明:rule.xml是分片规则的配置文件,分片规则的具体一些参数信息单独存放为文件,也在这个目录下,配置文件修改,需要重启Mycat或者通过9066端口reload。
server.xml 配置文件说明:server.xml是Mycat服务器参数调整和用户授权的配置文件
log4j.xml 配置文件说明:log4j.xml是日志的配置,根据自己的需要,可以调整输出级别为debug,debug级别下,会输出更多的信息,方便排查问题。
schema.xml 配置文件说明:schema.xml是逻辑库定义和表以及分片定义的配置文件
wrapper.conf
/usr/local/mycat/lib/
druid-1.0.12.jar
libwrapper-linux-x86-32.so
Mycat-server-1.3.0.2.jar
ehcache-core-2.6.6.jar
libwrapper-linux-x86-64.so
slf4j-api-1.7.3.jar
fdb-sql-parser-1.5.0.jar
log4j-1.2.17.jar
slf4j-log4j12-1.7.3.jar
guava-18.0.jar
mapdb-0.9.8.jar
wrapper.jar
libwrapper-linux-ppc-64.so
mongo-java-driver-2.11.4.jar
/usr/local/mycat/logs/ 日志目录说明:日志存放在logs/mycat.log中,每天一个文件。
安装好mariadb需要允许远程连接数据和使用utf-8编码格式存储数据:
[root@mariadb-10000 opt]# mysql
MariaDB [(none)]> use mysql;
MariaDB [mysql]> update user set host='%' where host='127.0.0.1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [mysql]> select host,user from user;
+-------------------+------+
| host | user |
+-------------------+------+
| % | root |
| ::1 | root |
| localhost | |
| localhost | root |
| mariadb-10000.com | |
| mariadb-10000.com | root |
+-------------------+------+
6 rows in set (0.00 sec)
MariaDB [mysql]>
MariaDB [mysql]> UPDATE user SET password=PASSWORD('123456') WHERE user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [mysql]> quit
Bye
[root@mariadb-10000 opt]# systemctl restart mariadb
[root@mariadb-10000 opt]#
[root@mariadb-10000 opt]# mysql -uroot -p123456 -h127.0.0.1
MariaDB [mysql]> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
MariaDB [mysql]>
[root@mariadb-10000 opt]# find / -name *.cnf
/etc/pki/tls/openssl.cnf
/etc/my.cnf
/etc/my.cnf.d/mysql-clients.cnf
/etc/my.cnf.d/client.cnf
/etc/my.cnf.d/server.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf
[root@mariadb-10000 opt]# vi /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
[mysqld]
character-set-server=utf8
lower_case_table_names = 1
# this is only for embedded server
[embedded]
# This group is only read by MariaDB-5.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mysqld-5.5]
# These two groups are only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
[mariadb-5.5]
[root@mariadb-10000 opt]# systemctl restart mariadb
[root@mariadb-10000 opt]#
[root@mariadb-10000 opt]# mysql
MariaDB [(none)]> use mysql;
MariaDB [mysql]> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
MariaDB [mysql]>
如果上面的都修改了还乱码，那剩下问题就一定在connection连接层上。
解决办法是在发送查询前执行一下下面这句（直接写在SQL文件的最前面）：
SET NAMES ‘utf8′;
它相当于下面的三句指令：
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
Mycat服务配置启动后的第1个范例:
1).创建DEMO所用三个分片数据库；
CREATE database db1;
CREATE database db2;
CREATE database db3;
直接使用Mariadb自带的客户端直接登录数据库节点查看操作结果:
[root@mariadb-10000 opt]# mysql -uroot -p123456 -h127.0.0.1
MariaDB [(none)]> CREATE database db1;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> CREATE database db2;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> CREATE database db3;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]>
[root@mysql1 opt]# cat /usr/local/mycat/conf/schema.xml //只截取了部分配置代码,包含了Mycat用来连接后端MySQL库的用户名密码信息
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<!-- <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> <dataNode
name="jdbc_dn2" dataHost="jdbchost" database="db2" /> <dataNode name="jdbc_dn3"
dataHost="jdbchost" database="db3" /> -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="localhost:3306" user="root"
password="123456">
<!-- can have multi read hosts -->
<!-- <readHost host="hostS1" url="localhost:3306" user="root" password="123456"
/> -->
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
[root@mysql1 opt]#
注意writeHost/readHost中的location,user,password的值符合你所采用的Mysql的连接信息。
[root@mariadb-10000 opt]# /usr/local/mycat/bin/mycat start
Starting Mycat-server...
Removed stale pid file: /usr/local/mycat/logs/mycat.pid
[root@mariadb-10000 opt]# /usr/local/mycat/bin/mycat status
Mycat-server is running (2369).
[root@mariadb-10000 opt]# ss -antp
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 50 *:3306 *:* users:(("mysqld",1466,14))
LISTEN 0 128 *:22 *:* users:(("sshd",940,3))
LISTEN 0 100 127.0.0.1:25 *:* users:(("master",1264,13))
LISTEN 0 1 127.0.0.1:32000 *:* users:(("java",2371,4))
ESTAB 0 0 127.0.0.1:3306 127.0.0.1:37323 users:(("mysqld",1466,34))
ESTAB 0 0 192.168.100.50:22 192.168.100.1:2556 users:(("sshd",2293,3))
ESTAB 0 0 127.0.0.1:32000 127.0.0.1:31000 users:(("wrapper-linux-x",2369,7))
LISTEN 0 100 :::8066 :::* users:(("java",2371,43))
LISTEN 0 100 :::9066 :::* users:(("java",2371,39))
LISTEN 0 50 :::54068 :::* users:(("java",2371,23))
LISTEN 0 128 :::22 :::* users:(("sshd",940,4))
LISTEN 0 50 :::40696 :::* users:(("java",2371,25))
LISTEN 0 100 ::1:25 :::* users:(("master",1264,14))
LISTEN 0 50 :::1984 :::* users:(("java",2371,24))
ESTAB 0 0 ::ffff:127.0.0.1:31000 ::ffff:127.0.0.1:32000 users:(("java",2371,26))
ESTAB 0 0 ::ffff:127.0.0.1:37323 ::ffff:127.0.0.1:3306 users:(("java",2371,44))
[root@mariadb-10000 opt]#
注意:默认数据端口为8066,管理端口为9066。
客户端也可以用图形化的客户端如：mysqlworkbench、navicat、
以及一些基于Java的数据库客户端来访问,注意要填写端口号8066,以及database为TESTDB。
命令行运行：mysql -utest -ptest -h127.0.0.1 -P8066 -DTESTDB 就能访问OpenCloudDB了，
以下操作都在此命令行里执行（JDBC则将mysql的URL中的端口3306改为8066即可）
提示：访问MyCAT的用户账号和授权信息是在/usr/local/mycat/conf/server.xml文件中配置，
而MyCAT用来连接后端MySQL库的用户名密码信息则在/usr/local/mycat/conf/schema.xml中，
这是两套完全独立的系统，类似的还有MyCAT的逻辑库(schema),逻辑表(table)也是类似的。
[root@mariadb-10000 opt]# cat /usr/local/mycat/conf/server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
<property name="defaultSqlParser">druidparser</property>
<!-- <property name="processorBufferChunk">40960</property> -->
<!-- <property name="processors">1</property> <property name="processorExecutor">32</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="mutiNodeLimitType">1</property> 0：开启小数量级（默认） ；1：开启亿级数据排序
<property name="mutiNodePatchSize">100</property> 亿级数量排序批量 <property name="processors">32</property>
<property name="processorExecutor">32</property> <property name="serverPort">8066</property>
<property name="managerPort">9066</property> <property name="idleTimeout">300000</property>
<property name="bindIp">0.0.0.0</property> <property name="frontWriteQueueSize">4096</property>
<property name="processors">32</property> -->
</system>
<user name="test">
<property name="password">test</property>
<property name="schemas">TESTDB</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
<!-- <cluster> <node name="cobar1"> <property name="host">127.0.0.1</property>
<property name="weight">1</property> </node> </cluster> -->
<!-- <quarantine> <host name="1.2.3.4"> <property name="user">test</property>
</host> </quarantine> -->
</mycat:server>
[root@mariadb-10000 opt]# mysql -utest -ptest -h127.0.0.1 -P8066 -DTESTDB
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 MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.3 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [TESTDB]>
Employee表,是根据规则sharding-by-intfile(分片字段为sharding_id)进行分片。
cat /usr/local/mycat/conf/schema.xml
<!-- <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3" rule="mod-long" /> -->
<table name="employee" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile" />
创建employee表,输入如下SQL:
create table employee (id int not null primary key,name varchar(100),sharding_id int not null);
MySQL [TESTDB]> create table employee (id int not null primary key,name varchar(100),sharding_id int not null);
查看MariaDB错误日志
[root@mariadb-10000 opt]# ls /usr/local/mycat/logs/
[root@mariadb-10000 opt]# tail -f /usr/local/mycat/logs/mycat.log
[root@mariadb-10000 opt]# tail -f /usr/local/mycat/logs/wrapper.log
如果出错,就查看日志分析错误原因.
运行explain指令，查看该SQL被发往哪些分片节点执行：
MySQL [TESTDB]> explain create table employee (id int not null primary key,name varchar(100),sharding_id int not null);
+-----------+------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------------------------------------------------------+
| dn1 | create table employee (id int not null primary key,name varchar(100),sharding_id int not null) |
| dn2 | create table employee (id int not null primary key,name varchar(100),sharding_id int not null) |
+-----------+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MySQL [TESTDB]>
温馨提示：explain可以用于任何正确的SQL上，其作用是告诉你，这条SQL会路由到哪些分片节点上执行，
这对于诊断分片相关的问题很有帮助。另外，explain可以安全的执行多次，它仅仅是告诉你SQL的路由分片，
而不会执行该SQL。
插入数据：
insert into employee(id,name,sharding_id) values(1,'leader us',10000);
insert into employee(id,name,sharding_id) values(2, 'me',10010);
insert into employee(id,name,sharding_id) values(3, 'mycat',10000);
insert into employee(id,name,sharding_id) values(4, 'mydog',10010);
MySQL [TESTDB]> insert into employee(id,name,sharding_id) values(1,'leader us',10000);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: TESTDB
Query OK, 1 row affected (0.03 sec)
MySQL [TESTDB]> insert into employee(id,name,sharding_id) values(2, 'me',10010);
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into employee(id,name,sharding_id) values(3, 'mycat',10000);
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into employee(id,name,sharding_id) values(4, 'mydog',10010);
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]>
MySQL [TESTDB]> explain insert into employee(id,name,sharding_id) values(1,'leader us',10000);
+-----------+-----------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------------+
| dn1 | insert into employee(id,name,sharding_id) values(1,'leader us',10000) |
+-----------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> explain insert into employee(id,name,sharding_id) values(2, 'me',10010);
+-----------+-----------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------+
| dn2 | insert into employee(id,name,sharding_id) values(2, 'me',10010) |
+-----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> explain insert into employee(id,name,sharding_id) values(3, 'mycat',10000);
+-----------+--------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------+
| dn1 | insert into employee(id,name,sharding_id) values(3, 'mycat',10000) |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> explain insert into employee(id,name,sharding_id) values(4, 'mydog',10010);
+-----------+--------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------+
| dn2 | insert into employee(id,name,sharding_id) values(4, 'mydog',10010) |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]>
直接使用Mariadb自带的客户端直接登录数据库节点查看操作结果:
[root@mariadb-10000 opt]# mysql -uroot -p123456 -h127.0.0.1
MariaDB [db1]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| mysql |
| performance_schema |
| test |
+--------------------+
7 rows in set (0.00 sec)
MariaDB [db1]>
MariaDB [db3]> use db1;
MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| employee |
+---------------+
1 row in set (0.00 sec)
MariaDB [db1]> select * from employee;
+----+-----------+-------------+
| id | name | sharding_id |
+----+-----------+-------------+
| 1 | leader us | 10000 |
| 3 | mycat | 10000 |
+----+-----------+-------------+
2 rows in set (0.00 sec)
MariaDB [db1]>
MariaDB [db1]> use db2;
MariaDB [db2]> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| employee |
+---------------+
1 row in set (0.00 sec)
MariaDB [db2]> select * from employee;
+----+-------+-------------+
| id | name | sharding_id |
+----+-------+-------------+
| 2 | me | 10010 |
| 4 | mydog | 10010 |
+----+-------+-------------+
2 rows in set (0.00 sec)
MariaDB [db2]>
company表是根据规则auto-sharding-long（主键范围）进行分片。创建company表：输入如下SQL
create table company(id int not null primary key,name varchar(100));
录入数据：
[root@mariadb-10000 opt]# mysql -utest -ptest -h127.0.0.1 -P8066 -DTESTDB
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 MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.8-mycat-1.3 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [TESTDB]> create table company(id int not null primary key,name varchar(100));
Query OK, 0 rows affected (0.02 sec)
MySQL [TESTDB]> explain create table company(id int not null primary key,name varchar(100));
+-----------+---------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------------------------------------------+
| dn1 | create table company(id int not null primary key,name varchar(100)) |
| dn2 | create table company(id int not null primary key,name varchar(100)) |
| dn3 | create table company(id int not null primary key,name varchar(100)) |
+-----------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)
MySQL [TESTDB]>
录入数据：
insert into company(id,name) values(1,'hp');
insert into company(id,name) values(2,'ibm');
insert into company(id,name) values(3,'oracle');
MySQL [TESTDB]> insert into company(id,name) values(1,'hp');
Query OK, 3 rows affected (0.00 sec)
MySQL [TESTDB]> insert into company(id,name) values(2,'ibm');
Query OK, 3 rows affected (0.01 sec)
MySQL [TESTDB]> insert into company(id,name) values(3,'oracle');
Query OK, 3 rows affected (0.00 sec)
MySQL [TESTDB]>
你会看到三个分片上都插入了3条数据，因为company定义为全局表，用explain来确认这个情况：
explain insert into company(id,name) values(1,'hp');
explain insert into company(id,name) values(2,'ibm');
explain insert into company(id,name) values(3,'oracle');
MySQL [TESTDB]> explain insert into company(id,name) values(1,'hp');
+-----------+---------------------------------------------+
| DATA_NODE | SQL |
+-----------+---------------------------------------------+
| dn1 | insert into company(id,name) values(1,'hp') |
| dn2 | insert into company(id,name) values(1,'hp') |
| dn3 | insert into company(id,name) values(1,'hp') |
+-----------+---------------------------------------------+
3 rows in set (0.00 sec)
MySQL [TESTDB]> explain insert into company(id,name) values(2,'ibm');
+-----------+----------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------+
| dn1 | insert into company(id,name) values(2,'ibm') |
| dn2 | insert into company(id,name) values(2,'ibm') |
| dn3 | insert into company(id,name) values(2,'ibm') |
+-----------+----------------------------------------------+
3 rows in set (0.00 sec)
MySQL [TESTDB]> explain insert into company(id,name) values(3,'oracle');
+-----------+-------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------+
| dn1 | insert into company(id,name) values(3,'oracle') |
| dn2 | insert into company(id,name) values(3,'oracle') |
| dn3 | insert into company(id,name) values(3,'oracle') |
+-----------+-------------------------------------------------+
3 rows in set (0.00 sec)
MySQL [TESTDB]>
直接使用Mariadb自带的客户端直接登录数据库节点查看操作结果:
[root@mariadb-10000 opt]# mysql -uroot -p123456 -h127.0.0.1
MariaDB [db2]> use db1;
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
MariaDB [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| company |
| employee |
+---------------+
2 rows in set (0.00 sec)
MariaDB [db1]> select * from company;
+----+--------+
| id | name |
+----+--------+
| 1 | hp |
| 2 | ibm |
| 3 | oracle |
+----+--------+
3 rows in set (0.00 sec)
MariaDB [db1]>
MariaDB [db1]> use db2;
MariaDB [db2]> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| company |
| employee |
+---------------+
2 rows in set (0.00 sec)
MariaDB [db2]> select * from company;
+----+--------+
| id | name |
+----+--------+
| 1 | hp |
| 2 | ibm |
| 3 | oracle |
+----+--------+
3 rows in set (0.00 sec)
MariaDB [db2]>
MariaDB [db2]> use db3;
MariaDB [db3]> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| company |
+---------------+
1 row in set (0.00 sec)
MariaDB [db3]> select * from company;
+----+--------+
| id | name |
+----+--------+
| 1 | hp |
| 2 | ibm |
| 3 | oracle |
+----+--------+
3 rows in set (0.00 sec)
MariaDB [db3]>
创建客户表：
create table customer(id int not null primary key,name varchar(100),company_id int not null,sharding_id int not null);
插入数据：
insert into customer (id,name,company_id,sharding_id )values(1,'wang',1,10000); //stored in db1;
insert into customer (id,name,company_id,sharding_id )values(2,'xue',2,10010); //stored in db2;
insert into customer (id,name,company_id,sharding_id )values(3,'feng',3,10000); //stored in db1;
查询结果：
select * from customer;
explain select * from customer; (确认一下数据的确是分片存储)
MySQL [TESTDB]> create table customer(id int not null primary key,name varchar(100),company_id int not null,sharding_id int not null);
Query OK, 0 rows affected (0.01 sec)
MySQL [TESTDB]> explain create table customer(id int not null primary key,name varchar(100),company_id int not null,sharding_id int not null);
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| dn1 | create table customer(id int not null primary key,name varchar(100),company_id int not null,sharding_id int not null) |
| dn2 | create table customer(id int not null primary key,name varchar(100),company_id int not null,sharding_id int not null) |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MySQL [TESTDB]>
MySQL [TESTDB]> insert into customer (id,name,company_id,sharding_id )values(1,'wang',1,10000);
Query OK, 1 row affected (0.00 sec)
MySQL [TESTDB]> insert into customer (id,name,company_id,sharding_id )values(2,'xue',2,10010);
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> insert into customer (id,name,company_id,sharding_id )values(3,'feng',3,10000);
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> explain insert into customer (id,name,company_id,sharding_id )values(1,'wang',1,10000);
+-----------+--------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------------+
| dn1 | insert into customer (id,name,company_id,sharding_id )values(1,'wang',1,10000) |
+-----------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> explain insert into customer (id,name,company_id,sharding_id )values(2,'xue',2,10010);
+-----------+-------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------------------------+
| dn2 | insert into customer (id,name,company_id,sharding_id )values(2,'xue',2,10010) |
+-----------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> explain insert into customer (id,name,company_id,sharding_id )values(3,'feng',3,10000);
+-----------+--------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------------+
| dn1 | insert into customer (id,name,company_id,sharding_id )values(3,'feng',3,10000) |
+-----------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]>
MySQL [TESTDB]> select * from customer;
+----+------+------------+-------------+
| id | name | company_id | sharding_id |
+----+------+------------+-------------+
| 2 | xue | 2 | 10010 |
| 1 | wang | 1 | 10000 |
| 3 | feng | 3 | 10000 |
+----+------+------------+-------------+
3 rows in set (0.07 sec)
MySQL [TESTDB]>
MySQL [TESTDB]> explain select * from customer;
+-----------+----------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------+
| dn1 | SELECT * FROM customer LIMIT 100 |
| dn2 | SELECT * FROM customer LIMIT 100 |
+-----------+----------------------------------+
2 rows in set (0.00 sec)
MySQL [TESTDB]>
创建订单表,并插入数据：
create table orders (id int not null primary key ,customer_id int not null,sataus int ,note varchar(100) );
insert into orders(id,customer_id) values(1,1); //stored in db1 because customer table with id=1 stored in db1
insert into orders(id,customer_id) values(2,2); //stored in db2 because customer table with id=1 stored in db2
explain insert into orders(id,customer_id) values(2,2);
select customer.name ,orders.* from customer ,orders where customer.id=orders.customer_id;
MySQL [TESTDB]> create table orders (id int not null primary key ,customer_id int not null,sataus int ,note varchar(100) );
Query OK, 0 rows affected (0.00 sec)
MySQL [TESTDB]> insert into orders(id,customer_id) values(1,1);
查看MariaDB错误日志
[root@mariadb-10000 opt]# ls /usr/local/mycat/logs/
[root@mariadb-10000 opt]# tail -f /usr/local/mycat/logs/mycat.log
[root@mariadb-10000 opt]# tail -f /usr/local/mycat/logs/wrapper.log
如果出错,就查看日志分析错误原因.
[root@mariadb-10000 ~]# tail -f /usr/local/mycat/logs/mycat.log
07/30 11:31:32.972 WARN [$_NIOREACTOR-0-RW] (ServerConnection.java:170) -ServerConnection
[id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]
insert into orders(id,customer_id) values(1,1)
err:java.sql.SQLNonTransientException: can't find (root) parent sharding node for sql:INSERT INTO orders (id, customer_id) VALUES (1, 1)
java.sql.SQLNonTransientException: can't find (root) parent sharding node for sql:INSERT INTO orders (id, customer_id)
VALUES (1, 1)
at org.opencloudb.parser.druid.impl.DruidInsertParser.parserChildTable(DruidInsertParser.java:138)
at org.opencloudb.parser.druid.impl.DruidInsertParser.statementParse(DruidInsertParser.java:50)
at org.opencloudb.parser.druid.impl.DefaultDruidParser.parser(DefaultDruidParser.java:60)
at org.opencloudb.route.impl.DruidMysqlRouteStrategy.routeNormalSqlWithAST(DruidMysqlRouteStrategy.java:40)
at org.opencloudb.route.impl.AbstractRouteStrategy.route(AbstractRouteStrategy.java:52)
at org.opencloudb.route.RouteService.route(RouteService.java:118)
at org.opencloudb.server.ServerConnection.routeEndExecuteSQL(ServerConnection.java:162)
at org.opencloudb.server.ServerConnection.execute(ServerConnection.java:154)
at org.opencloudb.server.ServerQueryHandler.query(ServerQueryHandler.java:125)
at org.opencloudb.net.FrontendConnection.query(FrontendConnection.java:263)
at org.opencloudb.net.handler.FrontendCommandHandler.handle(FrontendCommandHandler.java:56)
at org.opencloudb.net.FrontendConnection.handle(FrontendConnection.java:370)
at org.opencloudb.net.AbstractConnection.onReadData(AbstractConnection.java:264)
at org.opencloudb.net.NIOSocketWR.asynRead(NIOSocketWR.java:186)
at org.opencloudb.net.AbstractConnection.asynRead(AbstractConnection.java:226)
at org.opencloudb.net.NIOReactor$RW.run(NIOReactor.java:97)
at java.lang.Thread.run(Thread.java:745)
07/30 11:36:05.059 INFO [Timer1] (PhysicalDatasource.java:221) -create connections ,because idle connection not enough ,cur is 5, minCon is 10 for hostM1
07/30 11:36:05.070 INFO [$_NIOREACTOR-0-RW] (NewConnectionRespHandler.java:44) -connectionAcquired
MySQLConnection [id=12, lastTime=1438227365068, schema=db1, borrowed=false, fromSlaveDB=false, threadId=13, charset=utf8, txIsolation=0, autocommit=true,
attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
解决办法：虚拟机(例如VMware Workstation)计算机每个处理器的核心数量一定要要选择双核 四核 八核
例如你的购买的电脑是四核CPU 每个处理器的核心数量在"虚拟机设置"界面下拉列表中你可以选择2或选择4即可解决该错误。
https://github.com/MyCATApache/Mycat-Server/issues/73
[root@mariadb-10000 ~]# /usr/local/mycat/bin/mycat start 重新启动系统需要再一次的启动mycat服务
[root@mariadb-10000 ~]# mysql -utest -ptest -h127.0.0.1 -P8066 -DTESTDB
MySQL [TESTDB]> explain insert into orders(id,customer_id) values(1,1);
+-----------+----------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------+
| dn1 | INSERT INTO orders (id, customer_id)
VALUES (1, 1) |
+-----------+----------------------------------------------------+
1 row in set (0.28 sec)
MySQL [TESTDB]> insert into orders(id,customer_id) values(1,1);
Query OK, 1 row affected (0.22 sec)
MySQL [TESTDB]> explain insert into orders(id,customer_id) values(2,2);
+-----------+----------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------+
| dn2 | INSERT INTO orders (id, customer_id)
VALUES (2, 2) |
+-----------+----------------------------------------------------+
1 row in set (0.43 sec)
MySQL [TESTDB]> insert into orders(id,customer_id) values(2,2);
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> explain insert into orders(id,customer_id) values(2,2);
+-----------+----------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------+
| dn2 | INSERT INTO orders (id, customer_id)
VALUES (2, 2) |
+-----------+----------------------------------------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select customer.name ,orders.* from customer ,orders where customer.id=orders.customer_id;
+------+----+-------------+--------+------+
| name | id | customer_id | sataus | note |
+------+----+-------------+--------+------+
| xue | 2 | 2 | NULL | NULL |
| wang | 1 | 1 | NULL | NULL |
+------+----+-------------+--------+------+
2 rows in set (0.11 sec)
MySQL [TESTDB]>
直接使用Mariadb自带的客户端直接登录数据库节点查看操作结果:
[root@mariadb-10000 opt]# mysql -uroot -p123456 -h127.0.0.1
MariaDB [db2]> use db1;
MariaDB [db1]> desc orders;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| customer_id | int(11) | NO | | NULL | |
| sataus | int(11) | YES | | NULL | |
| note | varchar(100) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
MariaDB [db1]> show create table orders;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
`id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`sataus` int(11) DEFAULT NULL,
`note` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [db1]> show columns from orders;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| customer_id | int(11) | NO | | NULL | |
| sataus | int(11) | YES | | NULL | |
| note | varchar(100) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
MariaDB [db1]> use db2;
MariaDB [db2]> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| company |
| customer |
| employee |
| orders |
+---------------+
4 rows in set (0.00 sec)
MariaDB [db2]> desc orders;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| customer_id | int(11) | NO | | NULL | |
| sataus | int(11) | YES | | NULL | |
| note | varchar(100) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
MariaDB [db2]> show create table orders;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` (
`id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`sataus` int(11) DEFAULT NULL,
`note` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [db2]>
travelrecord根据ID主键的范围进行分片：
create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
以下这个ID就存放在分片0上,dn1的index为0：
insert into travelrecord (id,user_id,traveldate,fee,days) values(1,'wang','2014-01-05',510.5,3);
MySQL [TESTDB]> explain create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| dn1 | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
| dn2 | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
| dn3 | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.06 sec)
MySQL [TESTDB]>
MySQL [TESTDB]> create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
Query OK, 0 rows affected (0.01 sec)
MySQL [TESTDB]>
MySQL [TESTDB]> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(1,'wang','2014-01-05',510.5,3);
+-----------+-------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------------------------------------------+
| dn1 | insert into travelrecord (id,user_id,traveldate,fee,days) values(1,'wang','2014-01-05',510.5,3) |
+-----------+-------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
MySQL [TESTDB]>
以下这个ID就存放在分片1上,dn2的index为1：
explain insert into travelrecord (id,user_id,traveldate,fee,days) values(7000001,'wang','2014-01-05',510.5,3);
MySQL [TESTDB]> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(7000001,'wang','2014-01-05',510.5,3);
+-----------+-------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------------------------------------------------+
| dn2 | insert into travelrecord (id,user_id,traveldate,fee,days) values(7000001,'wang','2014-01-05',510.5,3) |
+-----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]>
MySQL [TESTDB]> insert into travelrecord (id,user_id,traveldate,fee,days) values(7000001,'wang','2014-01-05',510.5,3);
Query OK, 1 row affected, 1 warning (0.01 sec)
MySQL [TESTDB]>
看到支持跨分片的JOIN！
热点新闻用取摸的方式随机分配到dn1,dn2,dn3上
create table hotnews(id int not null primary key ,title varchar(400) ,created_time datetime);
插入数据在分片1上,dn2的index为1
insert into hotnews(id,title,created_time) values(1,'first',now());
MySQL [TESTDB]> explain create table hotnews(id int not null primary key ,title varchar(400) ,created_time datetime);
+-----------+-----------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------------------------------------+
| dn1 | create table hotnews(id int not null primary key ,title varchar(400) ,created_time datetime) |
| dn2 | create table hotnews(id int not null primary key ,title varchar(400) ,created_time datetime) |
| dn3 | create table hotnews(id int not null primary key ,title varchar(400) ,created_time datetime) |
+-----------+-----------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
MySQL [TESTDB]> create table hotnews(id int not null primary key ,title varchar(400) ,created_time datetime);
Query OK, 0 rows affected (0.02 sec)
MySQL [TESTDB]> explain insert into hotnews(id,title,created_time) values(1,'first',now());
+-----------+--------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------+
| dn2 | insert into hotnews(id,title,created_time) values(1,'first',now()) |
+-----------+--------------------------------------------------------------------+
1 row in set (0.02 sec)
MySQL [TESTDB]> insert into hotnews(id,title,created_time) values(1,'first',now());
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]>
而Id为5，则到dn3上，5%3=2 (5除以3等于1余数等于2,2就表示取摸的结果) ,即对应dn3的index为2
MySQL [TESTDB]> explain insert into hotnews(id,title,created_time) values(5,'first',now());
+-----------+--------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------+
| dn3 | insert into hotnews(id,title,created_time) values(5,'first',now()) |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> insert into hotnews(id,title,created_time) values(5,'first',now());
Query OK, 1 row affected (0.00 sec)
MySQL [TESTDB]>
goods表
create table goods(id int not null primary key,name varchar(200),good_type tinyint,good_img_url varchar(200),good_created date,good_desc varchar(500), price double);
MySQL [TESTDB]> explain create table goods(id int not null primary key,name varchar(200),good_type tinyint,good_img_url varchar(200),good_created date,good_desc varchar(500), price double);
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dn1 | create table goods(id int not null primary key,name varchar(200),good_type tinyint,good_img_url varchar(200),good_created date,good_desc varchar(500), price double) |
| dn2 | create table goods(id int not null primary key,name varchar(200),good_type tinyint,good_img_url varchar(200),good_created date,good_desc varchar(500), price double) |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
MySQL [TESTDB]> create table goods(id int not null primary key,name varchar(200),good_type tinyint,good_img_url varchar(200),good_created date,good_desc varchar(500), price double);
Query OK, 0 rows affected (0.01 sec)
MySQL [TESTDB]>
在虚拟机上安装Mycat分布式数据库中间服务组件
最新推荐文章于 2022-07-22 11:50:27 发布
扫一扫
271
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
