In MySQL 8.0, user creation and password management have seen some changes compared to earlier versions.

User Creation and Authorization Directly creating a user and granting privileges simultaneous using the GRANT statement is no longer supported. You must first create the user and then grant them permissions.

-- Attempting to create and grant in one step (will fail in MySQL 8.0) -- GRANT ALL ON . TO 'admin'@'%' IDENTIFIED BY 'admin123';

-- Correct procedure: Create the user first CREATE USER 'admin'@'%' IDENTIFIED BY 'admin123';

-- Then grant privileges GRANT ALL ON . TO 'admin'@'%';

-- Apply the privilege changes FLUSH PRIVILEGES;

Command-Line Authentication with Special Characters When authenticating via the command line with a password containing special characters, using single quotes is now required to avoid shell interpretation issues. Double quotes may lead to errors.

This may result in a shell error like "event not found"

/usr/local/mysql8.0/bin/mysql -uroot -p"root!@#123" --socket=/data/mysql/mysql3310/tmp/mysql3310.sock

This is the correct way to specify a password with special characters

/usr/local/mysql8.0/bin/mysql -uroot -p'root!@#123' --socket=/data/mysql/mysql3310/tmp/mysql3310.sock

<h3>Compatibility Issues with Older Clients</h3>
An common issue encountered when connecting from older MySQL clients or applications to MySQL 8.0 is error 2058: "Plugin caching_sha2_password could not be loaded." This occurs because MySQL 8.0 defaults to `caching_sha2_password` for authentication, whereas older versions used `mysql_native_password`.

There are two primary solutions:

<h4>1. Revert Authentication Plugin to `mysql_native_password`</h4>
This involves altering the user's authentication method to the older plugin.


-- Set the password and ensure it doesn't expire immediately
ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;

-- Change the password and explicitly set the authentication plugin to mysql_native_password
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

-- Refresh privileges to apply changes
FLUSH PRIVILEGES;

After applying these changes, you should be able to connect successfully.

<h4>2. Upgrade Client Software/Drivers</h4>
Ensure that your client applications, libraries, and command-line tools are updated to versions compatible with MySQL 8.0 and its `caching_sha2_password` plugin. Using an outdated MySQL client can also lead to the authentication plugin loading error, even if the server configuration is correct.


# Example of an older client potentially failing to connect
# mysql -uroot -p'123456' --socket=/data/mysql/mysql3310/tmp/mysql3310.sock
# ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded...

# Using the MySQL 8.0 client successfully
/usr/local/mysql8.0/bin/mysql -uroot -p'123456' --socket=/data/mysql/mysql3310/tmp/mysql3310.sock

Additional MySQL 8.0 Password Policies MySQL 8.0 introduces several enhanced security features related to password management:

Password Expiration: Enforces periodic password changes.
Password History: Prevents users from reusing recent passwords. The number of historical passwords to check is configurable.
Old Password Verification: Requires the current password when changing it, reducing the risk of unauthorized modifications.
Dual Password Support: Allows both the old and new passwords to be valid for a transition period.
Password Strength Enforcement: Implements mechanisms to prevent the use of weak or easily guessable passwords.