How to fix Access denied for user 'root'@'localhost' (using password: YES) in MySQL

# Log in as system root (requires sudo)
sudo mysql -u root

# Check the authentication plugin for root@localhost
SELECT user, host, plugin, authentication_string FROM mysql.user WHERE user = 'root';

# Output will show one of these plugins:
# - auth_socket (no password, uses system username)
# - mysql_native_password (password-based)
# - caching_sha2_password (MySQL 8.0+ default, requires SSL or temp password change)
# - unix_socket (similar to auth_socket)

# Log in with sudo (bypasses password requirement for socket auth)
sudo mysql -u root

# For MySQL 5.7+ or MariaDB, use ALTER USER with mysql_native_password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_strong_password';

# For MySQL 8.0+, you can also use the default caching_sha2_password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_strong_password';

# Apply changes immediately
FLUSH PRIVILEGES;

# Verify the change
SELECT user, host, plugin FROM mysql.user WHERE user = 'root';

# Exit MySQL
EXIT;

# Now test password authentication
mysql -u root -p
# Enter your password when prompted

# Find the temporary password in MySQL error log
sudo grep 'temporary password' /var/log/mysql/error.log
# or
sudo grep 'temporary password' /var/log/mysqld.log
# or
sudo journalctl -u mysql -n 100 | grep 'temporary'

# Output format: [Note] A temporary password is generated for root@localhost: xxxxxxxxxxxx

# Use that temporary password to log in
mysql -u root -p
# Paste the temporary password

# MySQL will require you to change the password on first login:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_permanent_password';

# If you get error "Your password does not satisfy the password policy requirements":
# Either create a stronger password (8+ chars, uppercase, lowercase, number, special char)
# Or temporarily reduce password validation:
SET GLOBAL validate_password.policy=LOW;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
SET GLOBAL validate_password.policy=MEDIUM;

# After password change, you can log out and back in
EXIT;
mysql -u root -p
# Enter your new password

# Log in (using socket auth with sudo if needed)
sudo mysql -u root

# List all root user accounts
SELECT user, host, authentication_string FROM mysql.user WHERE user = 'root';

# Check which one matches your connection attempt
# If connecting to localhost, look for 'root'@'localhost' or 'root'@'127.0.0.1'
# If connecting to a specific IP, look for 'root'@'that.ip.address'
# If connecting remotely, look for 'root'@'%' or 'root'@'hostname'

# If no matching account exists, create one:
CREATE USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

# Or grant privileges to existing user:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

# Test the connection
EXIT;
mysql -u root -p -h localhost
# Enter your password

# Stop MySQL server
sudo systemctl stop mysql
# or
sudo service mysql stop

# Edit the MySQL configuration file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# or
sudo nano /etc/my.cnf

# Find the [mysqld] section and add skip-grant-tables under it:
[mysqld]
skip-grant-tables

# Save and exit (Ctrl+O, Enter, Ctrl+X in nano)

# Start MySQL without grant table validation
sudo systemctl start mysql

# Connect without a password (authentication is skipped)
mysql -u root

# Flush privileges to reload authentication system
FLUSH PRIVILEGES;

# Set a new password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

# Exit MySQL
EXIT;

# Remove skip-grant-tables from config file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Delete or comment out the skip-grant-tables line

# Restart MySQL with authentication enabled
sudo systemctl restart mysql

# Test with new password
mysql -u root -p
# Enter the new password

# If your password contains special characters like @, $, !, & etc.
# When connecting from command line, the password may need escaping or quoting:

# Method 1: Don't use -p with password inline (more secure)
mysql -u root -p
# Then enter password at the prompt

# Method 2: URL-encode special characters in connection strings
# @ becomes %40
# $ becomes %24
# ! becomes %21

# For application connection strings, check documentation for escaping:
# PHP: $dsn = "mysql:host=localhost;user=root;password=your_password";
# Python: mysql.connector.connect(user='root', password='your_password')
# Node.js: mysql.createConnection({user: 'root', password: 'your_password'})

# Test with a simple password first (alphanumeric only):
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'SimplePassword123';
FLUSH PRIVILEGES;

# Test connection
mysql -u root -p
# If this works, then special characters in your password were the issue

# Log in with sudo if needed
sudo mysql -u root

# Check account status and details
SELECT user, host, authentication_string, Account_locked FROM mysql.user WHERE user = 'root';

# Also check mysql.user table structure to see all relevant columns
DESC mysql.user;

# If account is locked (ACCOUNT LOCK status shows 'Y'), unlock it:
ALTER USER 'root'@'localhost' ACCOUNT UNLOCK;

# Ensure root has all privileges
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

# Verify the grants
SHOW GRANTS FOR 'root'@'localhost';

# Check if password_expired is set (may require reset)
SELECT user, host, password_expired FROM mysql.user WHERE user = 'root';

# If password is expired, change it
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';