MySQL stores user credentials in the mysql.user table by default. This system table contains authentication information for all database users. The password storage mechanism has evolved across MySQL versions, implementing increasingly secure methods.

SELECT User, Host, plugin, authentication_string FROM mysql.user;

Depending on your MySQL version, passwords are stored differently:

• MySQL 5.7+: Uses the authentication_string column with caching_sha2_password or mysql_native_password plugins
• MySQL 5.6: Uses the password column with mysql_native_password plugin
• MariaDB: Similar to MySQL but may use different plugins like unix_socket

Modern MySQL versions employ these hashing methods:

-- Check current authentication plugin
SHOW VARIABLES LIKE 'default_authentication_plugin';

-- Example hash formats:
-- mysql_native_password: *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4
-- caching_sha2_password: $A$005$H)U2eOZ*{tN8bN/WdWYJjv.wQ3Q8sJv5Y0yRjJ7kH6n4q3z1

The caching_sha2_password plugin (default in MySQL 8.0) provides stronger security than the older mysql_native_password:

• Uses SHA-256 based hashing with salt
• Implements memory-bound computations to resist brute force
• Requires SSL/TLS for secure transmission

Creating users with different authentication methods:

-- Traditional mysql_native_password
CREATE USER 'legacy_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

-- Modern caching_sha2_password (MySQL 8.0+ default)
CREATE USER 'secure_user'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'complexPass123!';

-- Verifying password hash format
SELECT authentication_string FROM mysql.user WHERE user = 'secure_user';

To upgrade existing users to caching_sha2_password:

ALTER USER 'existing_user'@'localhost' 
IDENTIFIED WITH caching_sha2_password BY 'new_password';

Remember that applications must support the new authentication method, or you'll need to set default_authentication_plugin=mysql_native_password in my.cnf temporarily during migration.

MySQL stores user credentials in the mysql.user table within the mysql system database. The specific storage format depends on the authentication plugin being used:

SELECT User, Host, plugin, authentication_string 
FROM mysql.user 
WHERE User = 'username';

Modern MySQL installations (5.7+) use these primary hashing methods:

• caching_sha2_password (default since MySQL 8.0)
• sha256_password
• mysql_native_password (legacy)

Here's what you might see for different authentication methods:

-- Native password (old style)
*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19

-- SHA-256 password
$5$k5k5l3j5hd$X9UYW7oDZ4xJwq9cD7YVrJz1xWZyQoOj0lKJm6nNqB

When examining MySQL password storage:

1. Never store plaintext passwords - always use hashing
2. Prefer caching_sha2_password over older methods
3. Regularly rotate credentials and review access

To upgrade existing accounts to more secure methods:

ALTER USER 'username'@'hostname' 
IDENTIFIED WITH caching_sha2_password 
BY 'new_password';

Use this query to check authentication methods:

SELECT user, host, plugin, password_last_changed
FROM mysql.user
ORDER BY password_last_changed DESC;

Implement password aging with:

CREATE USER 'secureuser'@'localhost' 
IDENTIFIED BY 'complexPassword123!' 
PASSWORD EXPIRE INTERVAL 90 DAY;