|
|
# 官网
|
|
|
http://www.mycat.io/
|
|
|
|
|
|
参考文档：
|
|
|
https://blog.csdn.net/qq_35992900/article/details/80673889
|
|
|
|
|
|
#配置文件参考（来自华为）
|
|
|
https://www.huaweicloud.com/kunpeng/software/mycat.html
|
|
|
|
|
|
|
|
|
# 测试地址及帐号
|
|
|
http://10.10.14.159/dsssoserver/login
|
|
|
学生帐号
|
|
|
xiaotu
|
|
|
123456
|
|
|
|
|
|
教师帐号
|
|
|
tantan
|
|
|
123456
|
|
|
|
|
|
# 测试主机
|
|
|
Windows测试机
|
|
|
118.190.89.22:56006
|
|
|
administrator
|
|
|
DsIdeal@123
|
|
|
|
|
|
mycat
|
|
|
内网：10.10.14.211:22
|
|
|
外网：118.190.89.22:56001
|
|
|
|
|
|
mysql_master
|
|
|
内网：10.10.14.212:22
|
|
|
外网：118.190.89.22:56002
|
|
|
|
|
|
mysql_slave1
|
|
|
内网：10.10.14.213:22
|
|
|
外网：118.190.89.22:56003
|
|
|
|
|
|
mysql_slave2
|
|
|
内网：10.10.14.214:22
|
|
|
外网：118.190.89.22:56004
|
|
|
|
|
|
mysql_slave3
|
|
|
内网：10.10.14.215
|
|
|
外网：118.190.89.22:56005
|
|
|
|
|
|
|
|
|
密码都是：dsideal
|
|
|
# ==================================================================================
|
|
|
(1)安装jdk
|
|
|
详细见jdk安装 目录下的文件。
|
|
|
|
|
|
|
|
|
(2)下载和安装
|
|
|
cd /usr/local/
|
|
|
wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
|
|
|
tar -zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
|
|
|
rm -rf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
|
|
|
|
|
|
|
|
|
#配置环境变量并添加
|
|
|
echo "export MYCAT_HOME=/usr/local/mycat" >> /etc/profile
|
|
|
source /etc/profile
|
|
|
|
|
|
#切换目录
|
|
|
cd /usr/local/mycat/bin/
|
|
|
|
|
|
(3)配置
|
|
|
/usr/local/mycat/conf/schema.xml
|
|
|
/usr/local/mycat/conf/server.xml
|
|
|
详细的内容参考"mycat配置样例"目录下的配置通过文件示例。
|
|
|
|
|
|
|
|
|
#切换路径
|
|
|
cd /usr/local/mycat/bin
|
|
|
|
|
|
# 启动
|
|
|
./mycat start
|
|
|
|
|
|
#重启
|
|
|
./mycat restart
|
|
|
|
|
|
|
|
|
# 连接到mycat的数据端口
|
|
|
mysql -uroot -pDsideaL147258369 -P22066 -h127.0.0.1
|
|
|
|
|
|
use dsideal_db;
|
|
|
|
|
|
# 测试更新
|
|
|
update t_sys_loginperson set person_name='黄海测试1' where id=86751966;
|
|
|
|
|
|
# 测试读取
|
|
|
select * from t_base_class limit 100;
|
|
|
select * from t_base_student limit 100;
|
|
|
select * from t_sys_loginperson limit 100;
|
|
|
|
|
|
|
|
|
# 管理功能
|
|
|
mysql -uroot -pDsideaL147258369 -P9066 -h127.0.0.1
|
|
|
show @@datasource;
|
|
|
|
|
|
mysql> show @@datasource;
|
|
|
+----------+--------+-------+--------------+-------+------+--------+------+------+---------+-----------+------------+
|
|
|
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
|
|
|
+----------+--------+-------+--------------+-------+------+--------+------+------+---------+-----------+------------+
|
|
|
| db_node | master | mysql | 10.10.14.212 | 22066 | W | 0 | 3 | 1000 | 122 | 0 | 1 |
|
|
|
| db_node | slave1 | mysql | 10.10.14.213 | 22066 | R | 0 | 3 | 1000 | 55 | 1 | 0 |
|
|
|
| db_node | slave2 | mysql | 10.10.14.214 | 22066 | R | 0 | 3 | 1000 | 56 | 2 | 0 |
|
|
|
| db_node | slave3 | mysql | 10.10.14.215 | 22066 | R | 0 | 3 | 1000 | 55 | 1 | 0 |
|
|
|
+----------+--------+-------+--------------+-------+------+--------+------+------+---------+-----------+------------+
|
|
|
4 rows in set (0.00 sec)
|
|
|
|
|
|
|
|
|
|
|
|
问题1：dsssoserver的程序使用mycat后，一直报错，无法登录。
|
|
|
解决办法：直接将jdbc指向 master,躲开了这个问题。
|
|
|
|
|
|
问题2:增量索引和全量索引是每台slave都有的，指向了slave机器，因为需要回写数据，会造成主从复制失败。
|
|
|
解决办法：指向mycat,这样可以避免此问题。
|
|
|
|
|
|
问题3：
|
|
|
Last packet sent to the server was 2 ms ago.
|
|
|
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2592)
|
|
|
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
|
|
|
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
|
|
|
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
|
|
|
at com.mysql.jdbc.Connection.execSQL(Connection.java:2998)
|
|
|
at com.mysql.jdbc.Connection.execSQL(Connection.java:2927)
|
|
|
at com.mysql.jdbc.Statement.executeQuery(Statement.java:956)
|
|
|
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
|
|
|
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:440)
|
|
|
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:395)
|
|
|
... 14 more
|
|
|
|
|
|
以前在mysql中一般配置如下：
|
|
|
[mysqld]
|
|
|
wait_timeout=35
|
|
|
interactive_timeout=35
|
|
|
|
|
|
java项目的jdbc.properties是这样配置的
|
|
|
mysql.minIdle = 10
|
|
|
mysql.maxWait = 30000
|
|
|
|
|
|
|
|
|
很明显，因为java的连接池的连接存活时间与mycat中配置的连接存活时间不一致造成，我尝试修改如下：
|
|
|
|
|
|
连接到mycat，查看超时时长：
|
|
|
mysql -uroot -pDsideaL147258369 -P22066 -h127.0.0.1
|
|
|
|
|
|
show global variables like 'wait_timeout';
|
|
|
+---------------+-------+
|
|
|
| Variable_name | Value |
|
|
|
+---------------+-------+
|
|
|
| wait_timeout | 35 |
|
|
|
+---------------+-------+
|
|
|
1 row in set (0.01 sec)
|
|
|
|
|
|
<!--前端应用和mysql连接后，多长时间没访问后主动断开连接（使连接池能更灵活被多个应用使用。单位毫秒）-->
|
|
|
<property name="idleTimeout">300000</property>
|
|
|
<!--sql执行时间超过多少秒后断开连接（单位秒）-->
|
|
|
<property name="sqlExecuteTimeout">300</property>
|
|
|
|
|
|
后来让吴缤修改了master+slave1+slave2+slave3的默认值 ,就跟着走了，mycat我都没有修改,也没重启,看来是直接透过去了,打到从库上了。
|
|
|
|
|
|
|
|
|
#===========================================================
|
|
|
MYCAT返回的是一个数据集，造成程序出错
|
|
|
|
|
|
mysql -uroot -pDsideaL147258369 -P22066 -h127.0.0.1
|
|
|
|
|
|
use dsideal_db;
|
|
|
|
|
|
SELECT SQL_NO_CACHE id,Matchesfound FROM T_SOCIAL_BLOG_ARTICLE_SPHINXSE WHERE query='filter=reprint_time,0;filter=business_type,7;filter=audit_status,1;sort=extended:ts desc;filter=business_id,300530;filter=is_del,0;maxmatches=200000;offset=0;limit=6';SHOW ENGINE SPHINX STATUS;
|
|
|
|
|
|
mysql> SELECT SQL_NO_CACHE id,Matchesfound FROM T_SOCIAL_BLOG_ARTICLE_SPHINXSE WHERE query='filter=reprint_time,0;filter=business_type,7;filter=audit_status,1;sort=extended:ts desc;filter=business_id,300530;filter=is_del,0;maxmatches=200000;offset=0;limit=6';SHOW ENGINE SPHINX STATUS;
|
|
|
+------+--------------+
|
|
|
| id | Matchesfound |
|
|
|
+------+--------------+
|
|
|
| 2049 | 1 |
|
|
|
+------+--------------+
|
|
|
1 row in set (0.01 sec)
|
|
|
|
|
|
Empty set (0.00 sec)
|
|
|
|
|
|
#===========================================================
|
|
|
纯MYSQL返回的是两个数据集
|
|
|
|
|
|
mysql -uroot -pDsideaL147258369 -P22066 -h127.0.0.1
|
|
|
|
|
|
MariaDB [(none)]> use dsideal_db;
|
|
|
Database changed
|
|
|
MariaDB [dsideal_db]> SELECT SQL_NO_CACHE id,Matchesfound FROM T_SOCIAL_BLOG_ARTICLE_SPHINXSE WHERE query='filter=reprint_time,0;filter=business_type,7;filter=audit_status,1;sort=extended:ts desc;filter=business_id,300530;filter=is_del,0;maxmatches=200000;offset=0;limit=6';SHOW ENGINE SPHINX STATUS;
|
|
|
+------+--------------+
|
|
|
| id | Matchesfound |
|
|
|
+------+--------------+
|
|
|
| 2049 | 1 |
|
|
|
+------+--------------+
|
|
|
1 row in set (0.04 sec)
|
|
|
|
|
|
+--------+-------+---------------------------------------------+
|
|
|
| Type | Name | Status |
|
|
|
+--------+-------+---------------------------------------------+
|
|
|
| SPHINX | stats | total: 1, total found: 1, time: 8, words: 0 |
|
|
|
+--------+-------+---------------------------------------------+
|
|
|
1 row in set (0.00 sec)
|
|
|
#===========================================================
|
|
|
|
|
|
SELECT * FROM t_sys_loginperson WHERE LOGIN_NAME='A4';
|
|
|
select * from t_base_person where PERSON_ID=350550;
|
|
|
|
|
|
mysql> SELECT login_password FROM t_sys_loginperson WHERE LOGIN_NAME='A4';
|
|
|
+----------------------------------+
|
|
|
| login_password |
|
|
|
+----------------------------------+
|
|
|
| 999c844282d9a9bd6e1b90b22957c5d2 |
|
|
|
+----------------------------------+
|
|
|
1 row in set (0.00 sec)
|
|
|
999c844282d9a9bd6e1b90b22957c5d2 |
