实验架构： 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