MySQL 问题解决

Date显示不正常

原因：由于JDBC参数Timezone与Mysql系统时区不一致导致的。

解决方法

3. 修改serverTimezone配置。

jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai

2. 在application.properties配置文件增加JSON时区配置。

spring.jackson.time-zone=GMT+8

3. 如果需要使用数据库关于时间的函数，需要修改配置文件/etc/mysql/my.cnf

default-time-zone = "+08:00"

Host is blocked

报错信息:Host is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

解决方法：刷新ip缓存记录。

FLUSH HOSTS;

认证失败

报错信息:MySQL said: Authentication plugin ‘caching_sha2_password’ cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

原因：有关caching_sha2_password 报错的问题，MySQL 8.0 默认认证方式改为 SHA2 了，如果不支持 SHA2 插件认证方式，那么就会报错。

解决方法

• 修改密码认证方式

UPDATE mysql.user SET `plugin`='mysql_native_password' WHERE user='root';
FLUSH PRIVILEGES;

拒绝连接

报错信息:Can’t connect to MySQL server on ‘x.x.x.x’ ([Errno 61] Connection refused)

解决方法

1. 编辑mysqld.cnf

[mysqld]
# bind-address = 127.0.0.1 # 只允许本机连接

2. 如果要登录的用户的 host 不是%则需要修改。

UPDATE mysql.user SET host = '%' WHERE user = 'root';
FLUSH PRIVILEGES;

公钥不可用

报错信息：在使用 MySQL 8.0 时重启应用后提示 com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed

原因：如果用户使用了 sha256_password 认证，密码在传输过程中必须使用 TLS 协议保护，但是如果 RSA 公钥不可用，可以使用服务器提供的公钥，可以在连接中通过 ServerRSAPublicKeyFile 指定服务器的 RSA 公钥，或者AllowPublicKeyRetrieval=true参数以允许客户端从服务器获取公钥，但是需要注意的是AllowPublicKeyRetrieval=true可能会导致恶意的代理通过中间人攻击（MITM）获取到明文密码，所以默认是关闭的，必须显式开启。

解决方法：添加allowPublicKeyRetrieval配置。

jdbc:mysql://localhost:3306/test?allowPublicKeyRetrieval=true

中文乱码

解决方法：修改默认字符集。

默认编码

mysql> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name      | Value           |
+--------------------------+----------------------------+
| character_set_client   | utf8            |
| character_set_connection | utf8            |
| character_set_database  | latin1           |
| character_set_filesystem | binary           |
| character_set_results  | utf8            |
| character_set_server   | latin1           |
| character_set_system   | utf8            |
| character_sets_dir    | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+

mysql> show variables like "%collation%";
+----------------------+-------------------+
| Variable_name    | Value       |
+----------------------+-------------------+
| collation_connection | utf8_general_ci  |
| collation_database  | latin1_swedish_ci |
| collation_server   | latin1_swedish_ci |
+----------------------+-------------------+

修改配置文件

• /etc/my.cnf

[mysqld]
...
character-set-server=utf8mb4

[client]
default-character-set=utf8mb4

• 重启服务。

$ systemctl restart mysqld.service

password(）方法报错

报错信息

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

原因：密码安全策略validate_password_policy,validate_password_policy可以取0,1,2三个值：

Value	Description
0 or LOW	Length
1 or MEDIUM	Length; numeric, lowercase/uppercase, and special characters
2 or STRONG	Length; numeric, lowercase/uppercase, and special characters; dictionary

• 默认的数值是1，符合长度，且必须含有数字，小写或大写字母，特殊字符，所以初始密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。

解决方法

• 有时候，只是为了自己测试，不想密码设置得那么复杂，比如只想设置root的密码为123456，必须修改两个全局参数：

SET GLOBAL validate_password_policy=0;
SET GLOBAL validate_password_length=1;

创建索引失败

报错信息

• ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

原因

• InnoDB的Varchar主键每个列的长度不能大于767 bytes，所有组成索引列的长度和不能大于3072 bytes

解决方案

• 修改索引列varchar小于767
• 启用innodb_large_prefix参数能够取消对于索引中每列长度的限制（但是无法取消对于索引总长度的限制），启用innodb_large_prefix必须同时指定innodb_file_format=barracuda，innodb_file_per_table=true，并且建表的时候指定表的row_format为dynamic或者compressed(mysql 5.6中row_format默认值为compact)