版本
1、Mycat Server：1.6.7.4
2、MySql Server：5.7
3、Linux：CentOS 7.7
部署 MySql 5.7
下载 Mycat-server-1.6.7.4
$ cd /opt/mycat
$ wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
安装 Mycat-server-1.6.7.4
下载的文件直接解压即可使用
$ tar zxvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
运行 Mycat-server-1.6.7.4
$ cd /opt/mycat/mycat/
$ bin/mycat start # 启动
$ bin/mycat stop # 停止
$ bin/mycat console # 前台运行
$ bin/mycat restart # 重启服务
$ bin/mycat pause # 暂停
$ bin/mycat status # 查看启动状态
配置 Mycat-server-1.6.7.4
1、配置 mycat 默认用户 root 的用户密码：
修改配置文件 conf/server.xml 中的默认 root 用户密码（可以与 mysql root 用户密码不同）
<user name="root" defaultAccount="true">
<property name="password">888888</property>
</user>
重启 mycat：
$ bin/mycat restart
如果密码不对，在连接 mycat 时，会报错：
$ mysql -uroot -p'888888' -P8066 -h127.0.0.1
ERROR 1045 (HY000): Access denied for user 'root', because password is error
2、配置 mycat 对后端 mysql 的连接信息：
将配置文件 conf/schema.xml 中连接 mysql 的 root 用户密码修改为 mysql root 用户密码：
<dataHost name="localhost1" ...>
<writeHost host="hostM1" url="localhost:3306" user="root" password="123456789"></writeHost>
</dataHost>
重启 mycat：
$ bin/mycat restart
如果密码不对，在 mycat 日志文件 logs/mycat.log 中会有如下错误日志：
can’t connect to mysql server ,errmsg:Access denied for user ‘root’@‘localhost’ (using password: YES)
2021-01-01 13:49:42.931 WARN [$_NIOREACTOR-1-RW] (io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91)) - can't connect to mysql server ,errmsg:Access denied for user 'root'@'localhost' (using password: YES) MySQLConnection@1107459254 [id=546, lastTime=1609480182929, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=false, threadId=1209, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
创建 MySql 测试数据库表
1、在后端 mysql 中创建三个测试数据库 db1, db2, db3
创建在 mycat 配置文件 conf/schema.xml 中定义的三个默认数据库：
<mycat:schema xmlns:mycat="http://io.mycat/">
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
</mycat:schema>
$ mysql -uroot -p'123456789' -P3306 -h127.0.0.1
mysql> create database db1;
mysql> create database db2;
mysql> create database db3;
如果未创建上述 mysql 数据库，在 mycat 日志文件 logs/mycat.log 中会有如下错误日志：
can’t connect to mysql server ,errmsg:Unknown database ‘db3’
2021-01-01 14:22:49.639 WARN [$_NIOREACTOR-3-RW] (io.mycat.backend.mysql.nio.MySQLConnectionAuthenticator.handle(MySQLConnectionAuthenticator.java:91)) - can't connect to mysql server ,errmsg:Unknown database 'db3' MySQLConnection@189652758 [id=216, lastTime=1609482169637, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=false, threadId=1431, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2、在后端 mysql 中的三个测试数据库 db1, db2, db3 中分别创建两个测试表 travelrecord, address
创建在 mycat 配置文件 conf/schema.xml 中定义的两个默认数据表：
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
<!-- auto sharding by id (long) -->
<!-- splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
<table name="travelrecord,address" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" splitTableNames="true"/>
</schema>
</mycat:schema>
$ mysql -uroot -p'123456789' -P3306 -h127.0.0.1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.00 sec)
# 在数据库 db1 中创建数据表 travelrecord, address
mysql> use db1;
mysql> CREATE TABLE `travelrecord` ( `id` INT(11) NOT NULL, `name` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
mysql> CREATE TABLE `address` ( `id` INT(11) NOT NULL, `name` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
# 在数据库 db2 中创建数据表 travelrecord, address
mysql> use db2;
mysql> CREATE TABLE `travelrecord` ( `id` INT(11) NOT NULL, `name` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
mysql> CREATE TABLE `address` ( `id` INT(11) NOT NULL, `name` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
# 在数据库 db3 中创建数据表 travelrecord, address
mysql> use db3;
mysql> CREATE TABLE `travelrecord` ( `id` INT(11) NOT NULL, `name` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
mysql> CREATE TABLE `address` ( `id` INT(11) NOT NULL, `name` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
如果未创建上述数据表，连接 mycat 并查询这些数据表时会报错：“Table ‘db2.address’ doesn’t exist”
$ mysql -uroot -p'123456789' -P8066 -h127.0.0.1
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB;
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 TESTDB |
+------------------+
| address |
| travelrecord |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from address;
ERROR 1105 (HY000): Table 'db2.address' doesn't exist
# 多次执行上述 select 语句，可能会报如下错误：
# ERROR 1105 (HY000): Table 'db1.address' doesn't exist
# ERROR 1105 (HY000): Table 'db2.address' doesn't exist
# ERROR 1105 (HY000): Table 'db3.address' doesn't exist
同时在 mycat 日志文件 logs/mycat.log 中会有如下错误日志：
err Table ‘db1.address’ doesn’t exist code:1146
err Table ‘db2.address’ doesn’t exist code:1146
err Table ‘db3.address’ doesn’t exist code:1146
2021-01-01 14:55:12.664 WARN [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.handler.MultiNodeHandler.errorResponse(MultiNodeHandler.java:142)) - io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@d0dece4error response from MySQLConnection@972530381 [id=222, lastTime=1609484112657, user=root, schema=db1, old shema=db1, borrowed=false, fromSlaveDB=false, threadId=1438, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] err Table 'db1.address' doesn't exist code:1146
2021-01-01 14:55:12.665 WARN [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.handler.MultiNodeHandler.errorResponse(MultiNodeHandler.java:142)) - io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@d0dece4error response from MySQLConnection@1848688729 [id=219, lastTime=1609484112657, user=root, schema=db2, old shema=db2, borrowed=false, fromSlaveDB=false, threadId=1435, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] err Table 'db2.address' doesn't exist code:1146
2021-01-01 14:55:12.665 WARN [$_NIOREACTOR-2-RW] (io.mycat.backend.mysql.nio.handler.MultiNodeHandler.errorResponse(MultiNodeHandler.java:142)) - io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@d0dece4error response from MySQLConnection@990905854 [id=229, lastTime=1609484112657, user=root, schema=db3, old shema=db3, borrowed=false, fromSlaveDB=false, threadId=1444, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=localhost, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] err Table 'db3.address' doesn't exist code:1146
2021-01-01 14:55:12.665 ERROR [$_NIOREACTOR-2-RW] (io.mycat.net.FrontendConnection.writeErrMessage(FrontendConnection.java:210)) - ServerConnection [id=5, schema=TESTDB, host=127.0.0.1, user=root,txIsolation=3, autocommit=true, schema=TESTDB, executeSql=select * from address]Table 'db3.address' doesn't exist
Mycat 连接测试
测试 mycat 与测试 mysql 完全一致，mysql 怎么连接，mycat 就怎么连接
# 8066 是 Mycat 的默认连接端口
$ mysql -uroot -p'123456789' -P8066 -h127.0.0.1
扫一扫
761
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
