Difference between mysql_native_password and caching_sha2_password

You can use SELECT * FROM mysql.user MySQL query that retrieves all information about the users and authentication plugins (mysql_native_password and caching_sha2_password)  that have been defined in the user table exists in the mysql system database.

The mysql_native_password plugin is the default authentication plugin used in MySQL. It uses a simple hashing algorithm that is based on SHA1 to encrypt passwords. The hashed password is stored in the MySQL user table (mysql.user) in the "authentication_string" column. When a user attempts to log in, the hashed password is compared with the password provided by the user. If the password matches, the user is granted access.

The caching_sha2_password plugin is a more secure authentication plugin that was introduced in MySQL 8.0. It uses a more advanced hashing algorithm based on SHA256 to encrypt passwords. The hashed password is stored in the MySQL user table in the "authentication_string" column. When a user attempts to log in, the plugin uses a challenge-response mechanism that requires the client to prove it knows the password without sending it over the network. This provides an additional layer of security against attacks like eavesdropping and password cracking.

Switch from the mysql_native_password plugin to the caching_sha2_password plugin in MySQL

You can run the below statement to switch from the mysql_native_password plugin to the caching_sha2_password plugin in MySQL. By using the caching_sha2_password plugin, you can improve the security and performance of your MySQL database, as well as increase compatibility with external applications.

Replace root with your existing database username and password with your desired password.

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';

In summary, while mysql_native_password is the default authentication plugin in MySQL and uses a simple hashing algorithm, caching_sha2_password is a more secure plugin that uses a more advanced hashing algorithm and a challenge-response mechanism. It provides better security against attacks like eavesdropping and password cracking.