Things to note when migrating MySQL to 8.0 (summary)

Password Mode

PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]

After mysql8, the default password mode is changed to caching_sha2_password. The new mode requires a new driver. At least pdo/navicat has not been given yet, so we still have to switch to the old mysql_native_password mode.

`mysql_native_password`: 7.0 or below `caching_sha2_password`: 8.0 or above

1. my.cnf configures the default password mode

[mysqld]
default_authentication_plugin=mysql_native_password

2. Update the password mode of the account

# Create a new account create user 'root'@'%' identified with mysql_native_password by '123456';

# Existing account alter user 'root'@'%' identified with mysql_native_password by '123456';

3. If you need authorization

# Authorization cannot be combined with account creation. You can only grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

Password complexity policy

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

This is caused by the password complexity verification policy. You can set it after turning it off.

set global validate_password.policy=0;
set global validate_password.length=6;

Default encoding

PDO::__construct(): Server sent charset (255) unknown to the client. Please, report to the developers

Set the default encoding for the mysql service

# Default Homebrew MySQL server config
[client]
default_character_set=utf8mb4

[mysql]
default_character_set=utf8mb4

[mysqld]
default_authentication_plugin=mysql_native_password
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci

Remote Access

1. Comment out local monitoring in my.conf

[mysqld]
#bind_address=127.0.0.1

2. Update the host of the account

update mysql.user set host='%' where user='root';

This is the end of this article about the precautions (summary) when migrating MySQL to 8.0. For more relevant content about migrating MySQL to 8.0, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!