Introduction:

MySQL uses authentication plugins to verify user identities. Depending on how your server is configured, a user may authenticate with:

  • auth_socket → Login without password (Linux system user)
  • mysql_native_password → Old-style MySQL password hashing
  • caching_sha2_password → (Default in MySQL 8) Faster & more secure authentication

If the wrong plugin is set, users may be unable to log in using a password, especially from applications like Node.js, PHP, WordPress, Python, etc.
This guide shows how to change the MySQL password authentication plugin manually from the command line (without cPanel).

Step 1:

Login to MySQL as root.

$mysql -u root -p

Step 2:

Check Current Plugin for a User.

$SELECT user, host, plugin FROM mysql.user;

Example output:

userhostplugin
appuserlocalhostauth_socket
rootlocalhostcaching_sha2_password

Change Auth Plugin (with Usage Examples)

1. Convert user to mysql_native_password (for compatibility)

This helps when older applications cannot use caching_sha2_password.

$ ALTER USER ‘appuser’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘StrongPassword@123’;

$ FLUSH PRIVILEGES;

Usage Example:

Use when older PHP/MySQL clients fail to connect.

$ mysql -u appuser -p

2. Convert user to caching_sha2_password (Recommended for MySQL 8)

$ ALTER USER ‘appuser’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘StrongPassword@123’;

$ FLUSH PRIVILEGES;

Usage Example with Node.js:

require(‘mysql2’).createConnection({

user: ‘appuser’,

password: ‘StrongPassword@123’

});

3. Convert user to auth_socket (No password required)

Good for local CLI access only.

$ ALTER USER ‘appuser’@’localhost’ IDENTIFIED WITH auth_socket;

$ FLUSH PRIVILEGES;

Usage Example:

User logs using system login (without MySQL password):

$ sudo mysql -u appuser

Step 3:

Verify Plugin Change

$ SELECT user, host, plugin FROM mysql.user WHERE user=’appuser’;

Note:

PluginBest Use
caching_sha2_passwordNew servers, modern apps
mysql_native_passwordLegacy apps, WordPress, older PHP
auth_socketProvides automatic MySQL login based on the Linux user account.

Conclusion:

Understanding and modifying MySQL authentication plugins is essential for database security and application compatibility. Newer systems use caching_sha2_password, but older systems still rely on mysql_native_password. Always choose the plugin that matches application requirements, secure the user’s password, and verify permissions after changes.

Post Views: 0

Leave a Reply