# 官网 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) 300000 300 后来让吴缤修改了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