Mycat2 連接測試

Mycat連接測試

客戶端連接mycat

測試mycat與測試mysql完全一致，mysql怎么連接，mycat就怎么連接。 在mysqld下面設(shè)置

default_authentication_plugin = mysql_native_password

客戶端登錄參數(shù)

--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參數(shù)禁用預(yù)讀功能

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()

客戶端要求

• 關(guān)閉SSL
• 啟用客戶端預(yù)處理,關(guān)閉服務(wù)器預(yù)處理
• mysql_native_password授權(quán)
• 開啟自動重連
• 開啟閑置連接檢查,心跳

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.

關(guān)閉允許多語句

jdbc客戶端設(shè)置useLocalSessionState解決

Could not retrieve transation read-only status server

Mycat連接MySql

Mycat連接不上Mysql的問題

ip配置錯誤,無法連通,例如本地ip

• 0.0.0.0
• localhost
• 127.0.0.1

沒有權(quán)限可能出現(xiàn)連接不上的現(xiàn)象

連接狀態(tài)問題

數(shù)據(jù)源的initSqls屬性可以設(shè)置連接初始化的變量

如果mysql的編碼是utf8mb4,那么請寫上

set names utf8mb4;

如果要初始化默認(rèn)庫,請寫上

use db1;

jdbc的連接屬性建議使用連接字符串設(shè)置

如果使用圖形化客戶端出現(xiàn)no database selected等提示,請在JDBC連接字符串上寫上默認(rèn)庫

mysql服務(wù)器設(shè)置參考

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
## 設(shè)置mysql數(shù)據(jù)庫的數(shù)據(jù)的存放目錄
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可能不支持其他授權(quán)方式
default_authentication_plugin=mysql_native_password  
[mysql]
## 設(shè)置mysql客戶端默認(rèn)字符集
default-character-setVariable=utf8mb4  
....

PHP客戶端可能會遇上預(yù)處理問題

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,true); //啟用預(yù)處理語句的模擬
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);  //禁用預(yù)處理語句的模擬
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);