【introduction】
After upgrading from version 5.7 to the latest enterprise version of 8.0, the following three problems broke out, which are recorded in this article.
Question 1: User encryption method changes
Question 2: DNS problem
Question 3: Capsule problem
Question 1: User encryption method changes
MySQLAfter upgrading to 8.0, I found that the client tool navicat cannot connect to MySQL8.0. After searching, it turned out that MySQL8.0 provided two methods of encryption for passwords. The 5.7 encryption method is: mysql_native_password, and the 8.0 encryption method is caching_sha2_password. After Mysql is upgraded to version 8.0, the default is to the latest version of encryption method: caching_sha2_password.
The following error message is reported:
The error message is as follows:
Client does not support authentication protocol requested by server; consider upgrading MySQL client 。
Authentication plugin ‘caching_sha2_password’ cannot be loaded。
Check the following command to view the user encryption method
mysql> select user, host, plugin from \G;
*************************** 1. row ***************************
user: root
host: localhost
plugin: caching_sha2_password
*************************** 2. row ***************************
…
The reason is: Some clients do not support new encryption methods. You can upgrade the latest version of mysql client driver to upgrade MySQL client, or use the original mysql_native_password encryption. Because upgrading client drivers often designs application redeployment, the noise is too much, so the encryption method is generally modified as: mysql_native_password.
Solution
There are two types of
Method 1: Use the command method to modify existing users and new users one by one
Modify the password and specify the encryption rule as mysql_native_password
mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED with mysql_native_password BY ‘XXXXXX’ PASSWORD EXPIRE NEVER;
Method 2: Permanently change the encryption method of mysql8.0
Modify the initial parameter file and add the parameters as follows:
default_authentication_plugin=mysql_native_password
Question 2: DNS problem
abnormalThe error message is as follows:
2019-10-08T16:37:38.124793Z 0 [Warning] [MY-010068] [Server] CA certificate 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/’ port: 3306 MySQL Enterprise Server - Commercial.
2019-10-08T16:37:38.405883Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: ‘/tmp/’ 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
Problem analysis
As mentioned above, MySQL has a DNS anti-parsing error.
DNS resolution refers to resolving the domain name into an IP address;
DNS anti-resolution refers to inversely parsing the IP address into a domain name;
Introduction to Mysql domain name analysis
When a new client tries to create a connection with mysqld, mysqld generates a new thread to handle the request. The new thread will first check whether the host name requesting the connection is in the Mysql hostname buffer. If it is not there, the thread will try to resolve the host name requesting the connection.
Analytical logic:
The thread resolves the obtained IP address into the hostname through gethostbyaddr(), and then resolves the obtained hostname into the IP address through gethostbyname() to ensure the accuracy of the correspondence between the hostname and IP address;
2. If the operating system supports calls using gethostbyaddr_r() and gethostbyname_r() of secure processes, the Mysqld thread can use them to optimize host name resolution;
3. If the operating system does not support secure thread calls, the Mysqld process first makes a mutex, and then calls gethostbyaddr() and gethostbyname() to resolve the host name. At this time, before the first process releases the host name of the host name buffer pool, other processes cannot resolve the host name again (the host name mentioned here in the MySQL manual should mean the same IP address and the corresponding first host name relationship).
When starting the mysqld process, DNS host name resolution can be disabled. After disabling it, in the MySQL authorization table, mysql can only be accessed using the IP address. Such as DNSperformanceIf it is slower or has more hosts, you can disable DNS resolution –skip-name-resolve or increase the HOST_CACHE_SIZE size to improve the database response.
Methods to disable hostname buffering: Use the --skip-host-cache parameter;
Refresh the hostname buffer: execute flushhostsOr execute mysqladmin flush-hosts;
Notice:
After enabling skip_name_resolve=1, there may be new Warning:
[Warning] ‘proxies_priv’ entry ‘@ root@localhost’ ignored in --skip-name-resolve mode.
Workaround, delete the table mysq.proxies_priv median value is
In short: The MySQL database server does not have /etc/hosts, nor does it have DNS services, which causes the resolution to fail when the mysqld thread resolves the hostname corresponding to the IP.
Solution:
could not be resolved: Temporary failure in name resolution warning, you can use skip_host_cache to handle it;
The warning of Name or service not known can not be resolved: skip_name_resolve=1.
Use the --skip-name-resolve parameter to disable DNS host name resolution. After disabling this function, you can only use the IP address in the MySQL authorization table. Modify the parameters in the MySQL initial parameter file:
[mysqld]
skip_host_cache
skip-name-resolve=1
Note: skip_host_cache=1 will report an error. Please directly skip_host_cache or skip-host-cache. After setting up the permission table in mysql, you must use the IP address to set it, and you cannot use the domain name.
The official Msyql document is explained as follows:
–skip-host-cache
Note: If skip-name-resolve=1 is enabled, skip-host-cache is not required because the resolution between ip and hostname is prohibited.
Question 3: Capsule problem
After installing mysql under Linux, it is case-sensitive to table names by default and not case-sensitive to column names.
The following are the following rules for database names, table names, column names, and alias case under MySQL:
1. Database names and table names are strictly case-sensitive;
2. The alias of tables are strictly case-sensitive;
3. Column names and column alias are case-free in all cases;
4. Variable names are also strictly case-sensitive;
In application development, developers sometimes have upper and lower_case_table_names=0, but they often forget it in subsequent development accesses, so they usually set the table name to be case-insensitive. The specific lower_case_table_names=0, default is 0.
Parameters are 0: case sensitive, 1: case insensitive
Change method:
Modify the following: Add lower_case_table_names=1 after /etc/ and [mysqld], and restart the MYSQL service to take effect.
【refer to】
/doc/refman/8.0/en/
【refer to】
/weixin_30512027/article/details/83026500
【refer to】
/doc/refman/8.0/en/#option_mysqld_skip-name-resolve
【refer to】
http:///article/
Welcome to follow your personal WeChat official account; "Yi Sen Kaji"