实验架构:
master 192.168.0.92 mysql
slave 192.168.0.93 mysql
mycat 192.168.0.94 mycat jdk
angent 192.168.0.95 mysql
1、环境配置 参照:https://www.cnblogs.com/effortsing/p/10367025.html
2、配置mysql实现主从复制 参照: https://www.cnblogs.com/effortsing/p/10367257.html
3、安装jdk 参照:https://www.cnblogs.com/effortsing/p/10012211.html
4、安装mycat实现读写分离
tar zxf Mycat-server-1.6-RELEASE-20161028204710-sangnux.tar.gz -C /usr/local
cat>>/etc/profile<<EOF
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:/usr/local/mycat/bin
EOF
source /etc/profile
5、新建逻辑库,逻辑表;
Mycat默认配置了逻辑库TESTDB,这里配置一个逻辑库USERDB
修改schema.xml
[root@db4 ~]# vi /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="TESTDB" dataNode="dn1" checkSQLschema="false" sqlMaxsangmit="100">
<!-- auto sharding by id (long) -->
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<!-- global table is auto cloned to all defined data nodes ,so can join
with any table whose sharding node is in the same data node -->
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />
<!-- random sharding using mod sharind rule -->
<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
rule="mod-long" />
<!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
needAddsangmit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
rule="mod-long" /> -->
<table name="tb1" primaryKey="ID" dataNode="dn1,dn2"
rule="sharding-by-intfile" />
<table name="customer" primaryKey="ID" dataNode="dn1,dn2"
rule="sharding-by-intfile">
<childTable name="orders" primaryKey="ID" joinKey="customer_id"
parentKey="id">
<childTable name="order_items" joinKey="order_id"
parentKey="id" />
</childTable>
<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
parentKey="id" />
</table>
<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
/> -->
</schema>
<schema name="USERDB" checkSQLschema="false" sqlMaxsangmit="100"> #新增逻辑库USERDB
<table name="tb1" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"></table> #新增逻辑表tb1;这个rule规则表示一直往一个节点上写数据
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="localhost1" database="db1" /> #配置逻辑库和物理库对应关系,这里的database改成真实数据库名db1
<dataNode name="dn2" dataHost="localhost1" database="db2" /> #配置逻辑库和物理库对应关系,这里的database改成真实数据库名db2
<dataNode name="dn3" dataHost="localhost1" database="db3" /> #配置逻辑库和物理库对应关系,这里的database改成真实数据库名db3
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="test1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="test2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="test3" /> -->
6、创建物理库
在mysql里创建dn1,dn2,dn3对应的数据库:
mysql> create database db1;
Query OK, 1 rowaffected (0.00 sec)
mysql> create database db2;
Query OK, 1 rowaffected (0.01 sec)
mysql> create database db3;
Query OK, 1 rowaffected (0.00 sec)
7、设置用户对应的逻辑库
打开文件,搜索password定位修改
vi /usr/local/mycat/conf/server.xml
<user name="root"> #改成登录mysql的用户名
<property name="password">jenkins@123</property> #这里的用户名密码是要访问的mysql的用户名密码
<property name="schemas">USERDB</property> #改成辑库USERDB
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="USERDB" dml="1111" > # 辑库USERDB;权限改成1111
<table name="tb01" dml="1111"></table> # 表名不动;权限改成1111
<table name="tb02" dml="1111"></table> # 表名不动;权限改成1111,表示具有增、删、改、查权限,0000表示没有权限
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
8、配置rule.xml文件
搜索 count 进行定位修改
vi /usr/local/mycat/conf/rule.xml
<function name="murmur"
class="io.mycat.route.function.PartitionByMurmurHash">
<property name="seed">0</property><!-- 默认是0 -->
<property name="count">3</property><!-- 要分片的数据库节点数量,必须指定,否则没法分片 --> # 这里修改成3,因为有三个数据库节点数量:dataNode
<property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点,默认是160倍,也就>是虚拟节点数是物理节点数的160倍 -->
9、启动mycat
mycat start
[root@db4 ~]# mycat start
Starting Mycat-server...
查看mycat状态
mycat status
[root@db4 ~]# mycat status
Mycat-server is running (3646).
停止mycat
mycat stop
[root@db4 ~]# mycat stop
Stopping Mycat-server...
Stopped Mycat-server.
10、查看报错日志:
cat /usr/local/mycat/logs/wrapper.log
查看端口
[root@db4 ~]# netstat -tnlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.0.94:32000 0.0.0.0:* sangSTEN 3648/java
tcp 0 0 0.0.0.0:22 0.0.0.0:* sangSTEN 938/sshd
tcp6 0 0 :::1984 :::* sangSTEN 3648/java
tcp6 0 0 :::8066 :::* sangSTEN 3648/java # mycat登录端口
tcp6 0 0 :::34727 :::* sangSTEN 3648/java
tcp6 0 0 :::9066 :::* sangSTEN 3648/java # 管理端口
tcp6 0 0 :::44438 :::* sangSTEN 3648/java
tcp6 0 0 :::22 :::* sangSTEN 938/sshd
11、分片测试
连接mycat
mysql -h 192.168.0.94 -P 8066 -uroot -pjenkins@123 -DUSERDB
mysql> show tables;
+------------------+
| Tables in USERDB |
+------------------+
| tb1 |
+------------------+
1 row in set (0.02 sec)
mysql> select * from tb1;
ERROR 1105 (HY000): Table 'db2.tb1' doesn't exist
报错是因为我们还没有创建该表
mysql> explain create table tb1(id int,name varchar(40),deptno int);
+-----------+------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------------+
| dn1 | create table tb1(id int,name varchar(40),deptno int) |
| dn2 | create table tb1(id int,name varchar(40),deptno int) |
| dn3 | create table tb1(id int,name varchar(40),deptno int) |
+-----------+------------------------------------------------------+
3 rows in set (0.03 sec)
看到会分配到dn1,dn2,dn3里
mysql> create table tb1(id int,name varchar(40),deptno int);
Query OK, 0 rows affected (0.34 sec)
进mysql主节点里可以看到新建的表了,如下:
mysql> 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
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| tb1 |
+---------------+
1 row in set (0.00 sec)
#准备测试数据
连接mycat,插入数据
先查看下会插入到哪个节点:
mysql> explain insert into tb1(id,name,deptno)values(1,'jan',10),(2,'bill',20),(3,'sang',30),(4,'gang',20),(5,'hp',10));
+-----------+--------------------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------------------------------------------------------------+
| dn1 | INSERT INTO tb1 (id, name, deptno) VALUES (1, 'jan', 10), (2, 'bill', 20), (3, 'sang', 30), (4, 'gang', 20), (5, 'hp', 10) |
+-----------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> insert into tb1(id,name,deptno)values(1,'jan',10),(2,'bill',20),(3,'sang',30),(4,'gang',20),(5,'hp',10));
Query OK, 5 rows affected (0.03 sec)
Records: 5 Dupsangcates: 0 Warnings: 0
进mysql主节点验证:
mysql> select * from db1.tb1;
+------+------+--------+
| id | name | deptno |
+------+------+--------+
| 1 | jan | 10 |
| 2 | bill | 20 |
| 3 | sang | 30 |
| 4 | gang | 20 |
| 5 | hp | 10 |
+------+------+--------+
5 rows in set (0.00 sec)
12、验证读写分离
登录管理端口
mysql -h 192.168.0.94 -P 9066 -uroot -pjenkins@123 -DUSERDB
mysql> show @@datasource ;
+----------+--------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 192.168.0.92 | 3306 | W | 0 | 12 | 1000 | 48 | 0 | 0 |
| dn1 | hostS1 | mysql | localhost | 3316 | W | 0 | 0 | 1000 | 0 | 0 | 0 |
| dn1 | hostS2 | mysql | 192.168.0.93 | 3306 | R | 0 | 5 | 1000 | 41 | 0 | 0 |
| dn3 | hostM1 | mysql | 192.168.0.92 | 3306 | W | 0 | 12 | 1000 | 48 | 0 | 0 |
| dn3 | hostS1 | mysql | localhost | 3316 | W | 0 | 0 | 1000 | 0 | 0 | 0 |
| dn3 | hostS2 | mysql | 192.168.0.93 | 3306 | R | 0 | 5 | 1000 | 41 | 0 | 0 |
| dn2 | hostM1 | mysql | 192.168.0.92 | 3306 | W | 0 | 12 | 1000 | 48 | 0 | 0 |
| dn2 | hostS1 | mysql | localhost | 3316 | W | 0 | 0 | 1000 | 0 | 0 | 0 |
| dn2 | hostS2 | mysql | 192.168.0.93 | 3306 | R | 0 | 5 | 1000 | 41 | 0 | 0 |
+----------+--------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+
9 rows in set (0.01 sec)
从管理端口观察两主机负载变化,发现READ_LOAD在hostS1为2, WRITE_LOAD在hostM1为2,说明读写分离已经实现
在mycat里创建用户(没做成)
修改MYCAT_HOME/conf/server.xml文件
仿照test用户创建一个新用户mycat:
修改后如下:
<user name="jenkins">
<property name="password">jenkins@123</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
登录9066端口,使得配置生效:
mysql -h 192.168.0.94 -P 9066 -uroot -pjenkins@123 -DUSERDB
mysql> reload @@config_all;
ERROR 1003 (HY000): Reload config failure
13、mycat的分片join(都没做成)
Mycat目前版本支持跨分片的join,主要实现方式有四种。 全局表,ER分片,catletT(人工智能)和ShareJoin,ShareJoin在开发版中支持,前面三种斱方式1.3.0.1支持
13.1、 全局表
一个真实的业务系统中,往往存在多量的类似字典表的表格,这些表基本上很少变动,
配置:
全局表配置比较简单,不用写Rule规则,如下配置即可:
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
在USERDB下配置一个全局表province:
vi /usr/local/mycat/conf/schema.xml
添加:
<table name="province" dataNode="dn1,dn2,dn3" type="global"></table>
登陆9066端口,执行命令:reload @@config_all;
mysql -h 192.168.0.94 -P 9066 -uroot -pjenkins@123 -DUSERDB
建表:
mysql -uroot -pjenkins@123 -h192.168.0.94 -P8066 -DUSERDB
mysql> create table province(id int,name varchar(30));
Query OK, 0 rows affected (0.19 sec)
mysql> insert into province(id,name)values(1,'tianjin');
Query OK, 3 rows affected (0.11 sec)
mysql> select * from province sangmit 1;
+------+---------+
| id | name |
+------+---------+
| 1 | tianjin |
+------+---------+
1 row in set (3.56 sec)
在mysql里的db1,db2,db3都能看到插入的数据:
mysql> select * from db3.province;
+------+---------+
| id | name |
+------+---------+
| 1 | tianjin |
+------+---------+
1 row in set (0.00 sec)
mysql> select * from db2.province;
+------+---------+
| id | name |
+------+---------+
| 1 | tianjin |
+------+---------+
1 row in set (0.01 sec)
mysql> select * from db1.province;
+------+---------+
| id | name |
+------+---------+
| 1 | tianjin |
+------+---------+
1 row in set (0.00 sec)
13.2、 ER join
基于E-R关系的数据分片策略,子表的记录与所关联的父表记录存放在同一个数据分片上。
分片在dn1,dn2上,orders依赖父表进行分片,两个表的关联关系为orders.customer_id=customer.id
配置:
<tablen ame="customer" dataNode="dn1,dn2" rule="sharding-by-intfile">
<childTable name="orders" joinKey="customer_id" parentKey="id"/>
</table>
示例:
创建了一个分片规则:auto-sharding-long-custom
id属于0-1000范围内的在分区1里,1000-2000的在分区2里,2000-3000的在分片3里。
vi schema.xml
在USERDB处配置:
<table name="customer" dataNode="dn1,dn2,dn3" rule="auto-sharding-long-custom">
<childTable name="orders" joinKey="customer_id" parentKey="id"/>
</table>
登录9066端口,使得配置生效:
reload @@config_all;
[root@PC conf]# mysql -u root -pjenkins@123 -P8066 -h 192.168.0.94 TESTDB
Reading table information for completion oftable and column names
You can turn off this feature to get aquicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.3 MyCatServer (OpenCloundDB)
Copyright (c) 2000, 2011, Oracle and/or itsaffisangates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affisangates. Other names may be trademarksof their respective
owners.
Type 'help;' or 'h' for help. Type 'c' toclear the current input statement.
mysql> use USERDB;
Reading table information for completion oftable and column names
You can turn off this feature to get aquicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables in USERDB |
+------------------+
| company |
| customer |
| tb1 |
| orders |
| province |
+------------------+
5 rows in set (0.01 sec)
建表:
mysql> create table customer(id int primary key,name varchar(30));
Query OK, 0 rows affected (0.13 sec)
mysql> create table orders(id int,name varchar(30),customer_id int,constraint fk_companyid foreign key(customer_id)references customer(id));
Query OK, 0 rows affected (0.25 sec)
插入几条数据:
mysql>insert into customer(id,name) values(999,'jan'),(1000,'bill'),(1003,'sang'),(2002,'gang');
mysql> insert into orders(id,name,customer_id) values(1,'jekist',999),(2,'jenkin',2002),(3,'clock',1003),(4,'heat',2002);
ERROR 1064 (HY000): ChildTable multi insertnot provided
看来mycat子表不支持一次插入多条记录。
mysql> insert into orders(id,name,customer_id) values(1,'jekist',999);
Query OK, 1 row affected (0.07 sec)
mysql> insert into orders(id,name,customer_id) values(2,'jenkin',2002)
Query OK, 1 row affected (0.02 sec)
mysql> insert into orders(id,name,customer_id) values(3,'clock',1003);
Query OK, 1 row affected (0.02 sec)
mysql> insert into orders(id,name,customer_id)values(4,'heat',2002);
Query OK, 1 row affected (0.02 sec)
进入mysql验证:
mysql> select * from db1.customer;
+------+------+
| id | name |
+------+------+
| 999 | jan |
| 1000 | bill |
+------+------+
2 rows in set (0.01 sec)
mysql> select * from db2.customer;
+------+------+
| id | name |
+------+------+
| 1003 | sang |
+------+------+
1 row in set (0.00 sec)
mysql> select * from db3.customer;
+------+------+
| id | name |
+------+------+
| 2002 | gang |
+------+------+
1 row in set (0.00 sec)
mysql> select * from db3.orders;
+------+--------+-------------+
| id | name | customer_id |
+------+--------+-------------+
| 2 | jenkin | 2002 |
| 4 | heat | 2002 |
+------+--------+-------------+
2 rows in set (0.00 sec)
mysql> select * from db2.orders;
+------+-------+-------------+
| id | name | customer_id |
+------+-------+-------------+
| 3 | clock | 1003 |
+------+-------+-------------+
1 row in set (0.00 sec)
mysql> select * from db1.orders;
+------+--------+-------------+
| id | name | customer_id |
+------+--------+-------------+
| 1 | jekist | 999 |
+------+--------+-------------+
1 row in set (0.00 sec)
我们看到orders列customer_id对应的customer的id属于哪个分片,orders的那条记录就在哪个分片内。
可以在mycat上正常地联合查询:
mysql> select b.*,a.name as custome_name from customer a inner join orders b on a.id=b.customer_id;
+------+--------+-------------+--------------+
| id | name | customer_id |custome_name |
+------+--------+-------------+--------------+
| 2 | jenkin | 2002 |gang |
| 4 | heat | 2002 | gang |
| 1 | jekist | 999 |jan |
| 3 | clock | 1003 | sang |
+------+--------+-------------+--------------+
4 rows in set (0.02 sec)
13.3、 share join
ShareJoin是一个简单的跨分片Join,基于HBT的方式实现。 目前支持2个表的join,原理就是解析SQL语句,拆分成单表的SQL语句执行,
然后把各个节点的数据汇集。配置支持任意配置的A,B表如:
A,B的dataNode相同
A,B的dataNode不同
示例:
先创建了一个分片规则:auto-sharding-long-custom
id属于0-1000范围内的在分区1里,1000-2000的在分区2里。
新定义两个处于不同分片中的两个表
vi schema.xml
在USERDB处添加:
<table name="student" primaryKey="ID" dataNode="dn1,dn2" rule="auto-sharding-long-custom"></table>
<table name="score" primaryKey="ID" dataNode="dn2,dn3" rule="auto-sharding-long-custom"></table>
登录9066端口,使得配置生效:
reload @@config_all;
建表:
mysql> create table student(id int primary key,name varchar(30));
Query OK, 0 rows affected (0.19 sec)
mysql> create table score(id int,studentid int,score int,constraint fk_studentid foreign key(studentid)references student(id));
Query OK, 0 rows affected (0.13 sec)
插入数据:
mysql> insert into student(id,name)values(1,'jan'),(1002,'bill'),(88,'sang');
Query OK, 3 rows affected (0.33 sec)
Records: 2 Dupsangcates: 0 Warnings: 0
mysql> insert into score(id,studentid,score) values(1,1,100);
Query OK, 1 row affected (0.07 sec)
mysql> insert into score(id,studentid,score) values(1008,88,90);
Query OK, 1 row affected (0.03 sec)
mysql> insert into score(id,studentid,score) values(8,1002,99);
Query OK, 1 row affected (0.01 sec)
进mysql查询:
mysql> select * from db1.student;
+----+------+
| id | name |
+----+------+
| 1 | jan |
| 88 | sang |
+----+------+
2 rows in set (0.01 sec)
mysql> select * from db2.student;
+------+------+
| id | name |
+------+------+
| 1002 | bill |
+------+------+
1 row in set (0.00 sec)
mysql> select * from db3.student;
ERROR 1146 (42S02): Table 'db3.student'doesn't exist
mysql> select * from db1.score;
ERROR 1146 (42S02): Table 'db1.score'doesn't exist
mysql> select * from db3.score;
+------+-----------+-------+
| id | studentid | score |
+------+-----------+-------+
| 1008 | 88 | 90 |
+------+-----------+-------+
1 row in set (0.00 sec)
mysql> select * from db2.score;
+------+-----------+-------+
| id | studentid | score |
+------+-----------+-------+
| 1 | 1 | 100 |
| 8 | 1002 | 99 |
+------+-----------+-------+
2 rows in set (0.00 sec)
不使用share join在mycat查询:
mysql> select * from student a inner join score b on a.id=b.studentid;
+------+------+------+-----------+-------+
| id | name | id | studentid | score|
+------+------+------+-----------+-------+
| 1002 | bill | 8 | 1002 | 99 |
+------+------+------+-----------+-------+
1 row in set (0.02 sec)
只查到了db2分区里面的数据。
使用share join:
mysql>/*!mycat:catlet=demo.catlets.ShareJoin */ select * from student a inner join score b on a.id=b.studentid;
ERROR 1064 (HY000):java.lang.ClassNotFoundException: demo.catlets.ShareJoin
Share join只在开发版本中支持,用的是mycat 1.3,所以不支持。假如支持的话,可以查出匹配的三条记录,而不是上面的一条。
14、查看分片规则:
cat /usr/local/mycat/conf/rule.xml
[root@db4 ~]# cat /usr/local/mycat/conf/rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - sangcensed under the Apache sangcense, Version 2.0 (the "sangcense");
- you may not use this file except in compsangance with the sangcense. - You
may obtain a copy of the sangcense at - - http://www.apache.org/sangcenses/sangCENSE-2.0
- - Unless required by appsangcable law or agreed to in writing, software -
distributed under the sangcense is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or impsanged. - See the
sangcense for the specific language governing permissions and - sangmitations
under the sangcense. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="rule1">
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="rule2">
<rule>
<columns>user_id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-murmur">
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<tableRule name="latest-month-calldate">
<rule>
<columns>calldate</columns>
<algorithm>latestMonth</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-rang-mod">
<rule>
<columns>id</columns>
<algorithm>rang-mod</algorithm>
</rule>
查看日志:
tail -f /usr/local/mycat/logs/wrapper.log
报错1:Caused by: io.mycat.config.util.ConfigException: SelfCheck### schema test refered by user root is not exist!
意思是test数据库被拒绝连接,原因是因为创建完test数据库后没有给数据库授权用户读写操作,所以报错连不上。此时用navicat、python也都连不上的,亲测
解决:
1、需要改配置文件
vi /usr/local/mycat/conf/server.xml
<user name="root">
<property name="password">jenkins@123</property> # 这里的用户名密码是要访问的mysql的用户名密码
<property name="schemas">TEST</property> # 要测试的数据库
2、需要给test数据库授权
grant all privileges on test.* TO 'root'@'%' identified by 'jenkins@123' with grant option;
flush privileges;
重启数据库
systemctl restart mysqld
报错2:ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invasangd DataSource:0
mysql> use test;
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 tb1;
ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invasangd DataSource:0
解决:
退出用下面方式登录,因为这是在master节点上的操作,所以不用加ip
[root@db2 ~]# mysql -P8066 -uroot -pjenkins@123
mysql> use test;
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>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| tb1 |
+----------------+
1 row in set (0.00 sec)
mysql>
mysql> select * from tb1;
+----+---------+
| id | name |
+----+---------+
| 1 | bob |
| 2 | join |
| 3 | join |
| 4 | jenkins |
+----+---------+
4 rows in set (0.00 sec)
mysql>
报错3:ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invasangd DataSource:0
mysql> select * from tb1;
ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invasangd DataSource:0
解决:
mysql> use mysql;
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 host, user from user;
+---------------+---------------+
| host | user |
+---------------+---------------+
| % | root |
| 192.168.0.% | root |
| 192.168.0.1.% | root |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+---------------+---------------+
6 rows in set (0.00 sec)
mysql> update user set host='%' where user='root';
ERROR 1062 (23000): Dupsangcate entry '%-root' for key 'PRIMARY'
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
重启数数据,否则不生效
systemctl restart mysqld
又出现如下新的错误:
ERROR 1064 (HY000): find no Route:select * from test.tb1
mysql -h 192.168.0.94 -P 8066 -uroot -pjenkins@123
mysql> use test;
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 test.tb1;
ERROR 1064 (HY000): find no Route:select * from test.tb1
解决:参照 https://blog.csdn.net/u011277123/article/details/77876955
报错4:ERROR 1064 (HY000): op table not in schema----IF
mysql> create table if not exists user ( id int(10) PRIMARY KEY AUTO_INCREMENT, name varchar(50) NOT NULL);
ERROR 1064 (HY000): op table not in schema----IF
分析:通过mycat建表,而该表并没有提前配置table标签,则mycat会找到默认的dataNode,并把表建在默认的dataNode上。如果没有配置默认dataNode,则mycat会报错
解决:
vi /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="db1" dataNode="dn1" checkSQLschema="false" sqlMaxsangmit="100"> #添加一个node节点,dataNode="dn1"即可解决
<!-- auto sharding by id (long) -->
参照文档:
搭建:
https://blog.csdn.net/yabingshi_tech/article/details/52312474
http://blog.51cto.com/12323501/2151261
mycat用户和权限控制管理:
http://blog.chinaunix.net/uid-31396856-id-5781522.html
修改配置文件,按照下面这俩做成的:
https://www.jianshu.com/p/f15d64fcb2f3
https://blog.csdn.net/u011277123/article/details/77876955
分片规则解释:
https://www.cnblogs.com/raphael5200/p/5884931.html
修改rule.xml参照
https://bbs.csdn.net/topics/392433448