Mycat2 连接测试

Mycat连接测试

客户端连接mycat

测试mycat与测试mysql完全一致，mysql怎么连接，mycat就怎么连接。在mysqld下面设置

default_authentication_plugin = mysql_native_password

客户端登录参数

--default-auth-password=mysql_native_password
--default-auth=mysql_native_password

或者建立专用账户

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

推荐先采用命令行测试：

mysql -uroot -proot -P8066 -h127.0.0.1

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

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

客户端登录记录

LINUX平台客户端

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

WINDOWS平台客户端

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

支持select current_user()

客户端要求

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

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

关闭允许多语句

jdbc客户端设置useLocalSessionState解决

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,那么请写上

set names utf8mb4;

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

use db1;

jdbc的连接属性建议使用连接字符串设置

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

mysql服务器设置参考

MariaDB 10.3

[mysqld]
local-infile=1
local-infile = ON
datadir=xxx/MariaDB 10.3/data
port=3306
innodb_buffer_pool_size=2031M
max_allowed_packet=128MB
max_connections=10000
character-setVariable-client-handshake = FALSE 
character-setVariable-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect='SET NAMES utf8mb4'
log_bin_trust_function_creators=1
[client]
local-infile = ON
loose-local-infile= 1
port=3306
plugin-dir=xxx/MariaDB 10.3/lib/plugin
default-character-setVariable = utf8mb4
[mysql] 
local_infile = 1
local-infile = ON
default-character-setVariable = utf8mb4

Mysql-8.0.19

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

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

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true); //启用预处理语句的模拟
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);  //禁用预处理语句的模拟
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);