【生产篇】_MySQL5.7升级8.0.17后的DNS域名解析和加密报错解决

【引言】

一生产库从5.7版本升级至8.0最新企业版后，爆出如下三个问题，本文记录下。
问题1：用户加密方式改变
问题2：dns问题
问题3：大小写问题

问题1：用户加密方式改变

MySQL升级至8.0后，发现客户端工具navicat连不上MySQL8.0，经查找原来MySQL8.0提供了两种密码加密的方式，5.7加密方式为：mysql_native_password，8.0加密方式为caching_sha2_password。Mysql升级至8.0版本后默认为最新版加密方式：caching_sha2_password。

报错如下信息：
报错信息如下：
Client does not support authentication protocol requested by server; consider upgrading MySQL client 。
Authentication plugin ‘caching_sha2_password’ cannot be loaded。

查看如下命令查看用户加密方式
mysql> select user, host, plugin from mysql.user\G;
*************************** 1. row ***************************
user: root
host: localhost
plugin: caching_sha2_password
*************************** 2. row ***************************
…
原因为：某些客户端不支持新加密方式，可以升级最新版的mysql客户端驱动upgrading MySQL client，或者使用原来的mysql_native_password加密。因升级客户端驱动往往设计应用程序的重新部署，动静太大，故，一般采用修改加密方式为：mysql_native_password。

解决方法
有两种
方式一：使用命令方式，逐个修改已有用户和新建用户
修改密码并指定加密规则为mysql_native_password
mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED with mysql_native_password BY ‘XXXXXX’ PASSWORD EXPIRE NEVER;

方法二：永久更改mysql8.0的加密方式
修改初始参数文件，添加参数如下：
default_authentication_plugin=mysql_native_password

问题2：dns问题

异常报错信息如下：
2019-10-08T16:37:38.124793Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-10-08T16:37:38.185913Z 0 [System] [MY-010931] [Server] /data/mysql-commercial-8.0.17-linux-glibc2.12-x86_64/bin/mysdqld: ready for connections. Version: ‘8.0.17-commercial’ socket: ‘/tmp/mysql3306.sock’ port: 3306 MySQL Enterprise Server - Commercial.
2019-10-08T16:37:38.405883Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: ‘/tmp/mysqlx.sock’ bind-address: ‘::’ port: 33060
2019-10-08T16:43:51.686637Z 17 [Warning] [MY-010055] [Server] IP address ‘192.168.0.112’ could not be resolved: Name or service not known

问题分析
上述，MySQL出现 DNS反解析错误。
DNS解析是指，将域名解析成ip地址；
DNS反解析是指，将IP地址反解析成域名；

Mysql域名解析介绍
当一个新的客户端尝试跟mysqld创建连接时，mysqld产生一个新线程来处理这个请求。新线程会先检查请求建立连接的主机名是否在Mysql的主机名缓冲中，如果不在，线程会尝试去解析请求连接的主机名。

解析逻辑：
1.Mysql线程通过gethostbyaddr()把获取的IP地址解析成主机名，然后通过gethostbyname()把获取的主机名解析成IP地址，保障主机名和IP地址对应关系的准确；
2.如果操作系统支持使用安全进程的gethostbyaddr_r()和gethostbyname_r() 调用，Mysqld线程可以用它俩来优化主机名解析；
3.如果操作系统不支持安全线程调用，Mysqld进程先做一个互斥锁，然后调用gethostbyaddr()和gethostbyname()解析主机 名。此时，在第一个进程释放掉主机名缓冲池的主机名之前，其它进程无法再次解析这个主机名（MySQL手册里面在此处说的host name ，意思应该是指同一个IP地址和对应的第一个主机名关系）。

启动mysqld进程时，可禁用DNS的主机名解析，禁用后，在MySQL授权表里面，只能使用IP地址访问mysql。如DNS性能较慢或主机比较多，可禁用DNS解析功能–skip-name-resolve或增加HOST_CACHE_SIZE大小以提升数据库的响应。
禁用主机名缓冲的方法： 使用–skip-host-cache 参数；
刷新主机名缓冲区：执行 flush hosts 或者执行mysqladmin flush-hosts；

注意：
启用 skip_name_resolve=1 之后，可能会有新的Warining:
[Warning] ‘proxies_priv’ entry ‘@ root@localhost’ ignored in --skip-name-resolve mode.
解决方法，删除表 mysq.proxies_priv 中值为 localhost.localdomain

简言之：MySQL 数据库服务器没有配置 /etc/hosts，也没有DNS服务，导致mysqld线程解析IP对应的主机名时，解析失败。

解决方法：
could not be resolved: Temporary failure in name resolution 的警告，可以使用 skip_host_cache 来搞定；
could not be resolved: Name or service not known 的警告，可以通过 skip_name_resolve=1 来搞定。

使用–skip-name-resolve 参数禁用DNS的主机名解析，禁用该功能后，在MySQL授权表里面，你只能使用IP地址。修改MySQL初始参数文件my.cnf中的参数：
[mysqld]
skip_host_cache
skip-name-resolve=1

注意：skip_host_cache=1 会报错。请直接 skip_host_cache 或者 skip-host-cache。设置后mysql中的权限表，就必须使用IP地址来设置，不能使用域名。

Msyql官方文档解释如下：

–skip-host-cache

注意：如启用skip-name-resolve=1, 因禁止了 ip 和 hostname 之间的解析，可不用启用 skip-host-cache。

问题3：大小写问题

linux下mysql安装完后是默认区分表名的大小写，不区分列名的大小写。
MySQL在Linux下数据库名、表名、列名、别名大小写规则如下：
　　 1、数据库名与表名是严格区分大小写的；
　　 2、表的别名是严格区分大小写的；
　　 3、列名与列的别名在所有的情况下均是忽略大小写的；
　　 4、变量名也是严格区分大小写的；

应用程序开发中，开发者有时候create table时会有大小写，但在后续的开发访问中经常会忘记，故通常会将表名设置为大小写不敏感，具体lower_case_table_names=0，默认为0。
参数为0：区分大小写，1：不区分大小写

更改方法：
修改在/etc/my.cnf ，[mysqld]后添加lower_case_table_names=1，重启MYSQL服务生效

【参考】
https://dev.mysql.com/doc/refman/8.0/en/problems-connecting.html
【参考】
https://blog.csdn.net/weixin_30512027/article/details/83026500
【参考】
https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_skip-name-resolve
【参考】
http://www.jb51.net/article/70893.htm

迎关注个人微信公众号；“一森咖记”