Mycat连接测试

客户端连接mycat

测试mycat与测试mysql完全一致，mysql怎么连接，mycat就怎么连接。
在mysqld下面设置
default_authentication_plugin = mysql_native_password
客户端登录参数
—default-auth-password=mysql_native_password
—default-auth=mysql_native_password
或者建立专用账户

  1. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xxxxx';
  2. FLUSH PRIVILEGES;

推荐先采用命令行测试：

  1. mysql -uroot -proot -P8066 -h127.0.0.1

mysql8客户端要加上-A参数禁用预读功能

  1. mysql -A -uroot -proot -P8066 -h127.0.0.1

客户端登录记录
LINUX平台客户端

  1. mysql  Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using rea
  2. mysql  Ver 14.14 Distrib 5.6.33, for debian-linux-gnu (x86_64) using  EditLine wrapper

WINDOWS平台客户端

  1. mysql  Ver 15.1 Distrib 10.3.15-MariaDB, for Win64 (AMD64), source revision 07aef9f7eb936de2b277f8ae209a1fd72510c011
  2. mysql  Ver 8.0.19 for Win64 on x86_64 (MySQL Community Server - GPL)
  3. SQLyog XXXX - MySQL GUI v12.3.1(64 bit)
  4. Navicat xxxx 12.1.22(64 bit)
  5. MySQL Workbench 8.0.19

支持select current_user()

客户端要求

关闭SSL
启用客户端预处理,关闭服务器预处理
mysql_native_password授权
开启自动重连
开启闲置连接检查,心跳

  1. com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
  2. Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

关闭允许多语句
jdbc客户端设置useLocalSessionState解决

  1. Could not retrieve transation read-only status server

Mycat连接MySql

Mycat连接不上Mysql的问题
ip配置错误,无法连通,例如本地ip
0.0.0.0
localhost
127.0.0.1
没有权限可能出现连接不上的现象

连接状态问题

数据源的initSqls属性可以设置连接初始化的变量
如果mysql的编码是utf8mb4,那么请写上

  1. set names utf8mb4;

如果要初始化默认库,请写上

  1. use db1;

jdbc的连接属性建议使用连接字符串设置
如果使用图形化客户端出现no database selected 等提示,请在JDBC连接字符串上写上默认库

mysql服务器设置参考

MariaDB 10.3
  1. [mysqld]
  2. local-infile=1
  3. local-infile = ON
  4. datadir=xxx/MariaDB 10.3/data
  5. port=3306
  6. innodb_buffer_pool_size=2031M
  7. max_allowed_packet=128MB
  8. max_connections=10000
  9. character-setVariable-client-handshake = FALSE 
  10. character-setVariable-server = utf8mb4 
  11. collation-server = utf8mb4_unicode_ci 
  12. init_connect='SET NAMES utf8mb4'
  13. log_bin_trust_function_creators=1
  14. [client]
  15. local-infile = ON
  16. loose-local-infile= 1
  17. port=3306
  18. plugin-dir=xxx/MariaDB 10.3/lib/plugin
  19. default-character-setVariable = utf8mb4
  20. [mysql] 
  21. local_infile = 1
  22. local-infile = ON
  23. default-character-setVariable = utf8mb4

Mysql-8.0.19
  1. [mysqld]
  2. port=3307
  3. basedir=xx/mysql-8.0.19-winx64/mysql-8.0.19-winx64
  4. # 设置mysql数据库的数据的存放目录
  5. datadir=xx/mysql-8.0.19-winx64/mysql-8.0.19-winx64/Database
  6. max_connections=200
  7. max_connect_errors=10
  8. character-setVariable-server=utf8mb4
  9. default-storage-engine=INNODB
  10. #mycat2.0可能不支持其他授权方式
  11. default_authentication_plugin=mysql_native_password 
  12. [mysql]
  13. # 设置mysql客户端默认字符集
  14. default-character-setVariable=utf8mb4
  15. ....

PHP客户端可能会遇上预处理问题

  1. $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true); //启用预处理语句的模拟
  2. $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);  //禁用预处理语句的模拟
  3. $pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
  5. PDO::MYSQL_ATTR_INIT_COMMAND => 'SET sql_mode="NO_ENGINE_SUBSTITUTION"'//更改sql mode

用户配置

  1. {
  2.   "password": "123456",
  3.   "transactionType": "proxy",
  4.   "username": "root"
  5. }
  7. {
  8.   "ip": "127.0.0.1",//建议为空,填写后会对客户端的ip进行限制
  9.   "password": "123456",
  10.   "transactionType": "proxy",
  11.   "username": "root"
  12. }

BLOB处理相关
1.mycat2对longdata报文直接识别为byte[],不再进行转换处理
2.对于prepare statement execute报文中的FIELD_TYPE_VARCHAR,会先使用utf8编码尝试进行转换,然后不是utf8编码,则当做byte[]处理