MySQL8.0关于skip_character_set_client_handshake测试

MySQL8.0关于skip_character_set_client_handshake测试

一、问题描述

MySQL8.0默认字符集utf8mb4 默认排序规则utf8mb4_0900_ai_ci

 

当前正式升级8.0企业版数据库，为保障业务异常时可及时退回57版本数据库，需要将57数据库做为升级后8.0版本的从库保持数据同步一致；然而57版本数据库不兼容8.0默认的新排序规则utf8mb4_0900_ai_ci，在8.0数据库上触发带有utf8mb4_0900_ai_ci排序相关的事务会导致5.7版本从库同步报错

需要将8.0默认排序规则在服务端更改为utf8mb4_general_ci，且控制其相关所有客户端连接默认排序规则必须为utf8mb4_general_ci

经测试在服务端配置文件添加collation-server = utf8mb4_general_ci后对collation_server和collation_database约束完全生效，

对collation_connection 的session级别未继承其全局设置

且对default_collation_for_utf8mb4 参数完全无效力

如下图所示

super用户

 

普通用户

 

在数据库中设置

set global default_collation_for_utf8mb4 = utf8mb4_general_ci;

并继续在配置文件中增加此参数使下次启动依然生效

init_file=/usr/local/mysql/data/init_file.sql

 

cat init_file.sql

set global default_collation_for_utf8mb4 = utf8mb4_general_ci;

 

设置后default_collation_for_utf8mb4参数问题完全解决

二、问题诉求

collation_connection  session级别下无法继承全局设置，存在隐患

 

需控制客户端默认连接后super用户和普通用户一致都使用5.7和8.0都兼容的

utf8mb4_0900_ai_ci排序规则

三、问题分析

普通用户可以使用init_connect参数及init_file，但super用户不受此限制

参考客户端和服务器之间存有handshake协议

https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_character-set-client-handshake

如果需要严格的排序规则/字符集而不关心客户端handshake，可以设置commandline启动选项

>> mysqld_safe --defaults-file=my1.cnf --character-set-client-handshake=off &

collation_connection 将根据服务器设置进行设置。

 

方式一 普通用户设置init_connect及init_file；super用户连接后另外设置初始化一次

方式二 跳过客户端字符集参数默认全局服务端参数 [此设置会影响MySQL客户端无法自由变更其他服务端默认外的设置，如需变更需连接后再初始化当前会话字符集及排序规则；java的jdbc连接自带此特性不受影响]

四、方案验证

4.1普通用户解决办法

 

 

在配置文件中添加init_connect后配置文件设置如下

init_file          =/usr/local/mysql/data/init_file.sql

init_connect       = 'set names utf8mb4 collate utf8mb4_general_ci'

character-set-server    = utf8mb4

collation-server        = utf8mb4_general_ci

其中

cat init_file.sql

set global collation_connection = utf8mb4_general_ci;

set global default_collation_for_utf8mb4 = utf8mb4_general_ci;

 

从上图可以看出，普通用户已经过init_file和set names操作后已与全局参数collaation_connection保持一致了；但超级用户root不受此影响

4.2 super用户解决办法

环境配置

skip_character_set_client_handshake参数说明：

--character-set-client-handshake

Command-Line Format --character-set-client-handshake[={OFF|ON}]

Type Boolean

Default Value ON

 

Do not ignore character set information sent by the client. To ignore client information and use the default server character set, use --skip-character-set-client-handshake; this makes MySQL behave like MySQL 4.0.

skip_character_set_client_handshake会忽略客户端信息并使用默认的服务器字符集

 

在8.0版本my.cnf配置文件添加

init_file               =/usr/local/mysql/data/init_file.sql

character-set-server    = utf8mb4

collation-server        = utf8mb4_general_ci

skip_character_set_client_handshake

其中

cat init_file.sql

set global default_collation_for_utf8mb4 = utf8mb4_general_ci;

 

根据以上配置重启数据库后 分别用super用户和普通用户检查相关参数结果

4.2.1 使用mysql客户端检查super用户

使用super用户查看 达到了预期效果

mysql -uroot -p -h 10.0.0.128  -P 8128  -e "show global variables like '%coll%';";

mysql -uroot -p -h 10.0.0.128  -P 8128  -e "show variables like '%coll%';";

mysql -uroot -p -h 10.0.0.128  -P 8128  --default-character-set=latin1  -e "show global variables like '%coll%';";

mysql -uroot -p -h 10.0.0.128  -P 8128 --default-character-set=latin1  -e "show variables like '%coll%';";

 

4.2.2 使用mysql客户端检查普通用户

普通用户在去掉init_connect = 'set names utf8mb4 collate utf8mb4_general_ci'配置后 由于添加了skip_character_set_client_handshake 其依然遵循服务端既定默认排序设置

 

 

mysql -urep -p -h10.0.0.128 -P8128  -e "show global variables like '%coll%';";

mysql -urep -p -h10.0.0.128 -P8128   -e "show variables like '%coll%';";

mysql -urep -p -h 10.0.0.128  -P 8128 --default-character-set=latin1  -e "show global variables like '%coll%';";

mysql -urep -p -h 10.0.0.128  -P 8128 --default-character-set=latin1  -e "show variables like '%coll%';";

 

4.2.3 使用jdbc测试

遵循如上配置 在8.0版本my.cnf配置文件添加

init_file               =/usr/local/mysql/data/init_file.sql

character-set-server    = utf8mb4

collation-server        = utf8mb4_general_ci

skip_character_set_client_handshake

其中

cat init_file.sql

set global default_collation_for_utf8mb4 = utf8mb4_general_ci;

 

使用super用户jdbc连接时设置默认session级别参数字符集为gbk

java -Xbootclasspath/a:"mysql-connector-java-8.0.29.jar" -jar conn.jar com.mysql.cj.jdbc.Driver "jdbc:mysql://10.0.0.128:8128/mysql?useUnicode=true&characterEncoding=gbk" root 'Jaylon999' "show variables like '%coll%';"

 

查看返回结果为rs1=====:collation_connection====rs2=====:gbk_chinese_ci

 

此时skip_character_set_client_handshake未影响jdbc连接时设置的默认字符集

 

使用普通用户jdbc连接时设置默认session级别参数字符集为gbk

可以预见其结果是一致的

 

 

4.3 说明建议

MySQL客户端连接默认设置的字符集

mysql -urep -p -h 10.0.0.128  -P 8128 --default-character-set=latin1

设置skip_character_set_client_handshake参数会使mysql客户端连接时设置的默认字符参数失效，并且继承服务端默认的字符参数，无论普通用户或super用户都如此；

Mysql客户端如在设置此参数后依然需求自定义其字符集只能在连接后重新初始化设置当前session会话级参数set collation_connection = utf8mb4_general_ci;

 

Java的jdbc连接默认设置的字符集

jdbc:mysql://10.0.0.128:8128/test?useUnicode=true&characterEncoding=gbk

相当于客户端连接后默认当前session级别设置了set collation_connection=gbk，以测试结果证明不受skip_character_set_client_handshake参数影响，无论普通用户或super用户都如此；

如确定所有客户端连接无特殊要求可以设置此参数以遵循服务端设置控制所有普通用户及超级用户在默认连接MySQL8.0后都使用既定的排序规则utf8mb4_general_ci，

在配置文件中添加如下

init_file               =/usr/local/mysql/data/init_file.sql

character-set-server    = utf8mb4

collation-server        = utf8mb4_general_ci

skip_character_set_client_handshake

其中init_file

cat init_file.sql

set global default_collation_for_utf8mb4 = utf8mb4_general_ci;