When working with SSH tunnels in MySQL, the difference between 'localhost' and '127.0.0.1' becomes critical. MySQL treats these as different hosts due to how it handles socket connections versus TCP/IP:

# Check current authentication methods
SELECT user, host, plugin FROM mysql.user WHERE user = 'root';
+------+-----------+-----------------------+
| user | host      | plugin                |
+------+-----------+-----------------------+
| root | localhost | auth_socket           | 
| root | 127.0.0.1 | mysql_native_password |
+------+-----------+-----------------------+

The tunnel might be set up correctly but MySQL's authentication system is particular about connection sources. Try these verification steps:

# On your local machine:
ssh -v -L 3306:127.0.0.1:3306 user@remote-server

# Then check MySQL connection logging:
SELECT * FROM performance_schema.host_cache;
SHOW PROCESSLIST;

Even with root@% privileges, certain operations might fail due to missing GRANT OPTION:

# Correct way to grant all privileges:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

# Verify:
SHOW GRANTS FOR 'root'@'%';

Modern MySQL installations sometimes use different authentication plugins:

# To ensure consistent authentication:
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'your_password';

Even with correct MySQL permissions, system-level security might block tunneled connections:

# Check for SELinux denials:
sudo grep mysql /var/log/audit/audit.log | audit2why

# Verify firewall rules:
sudo iptables -L -n | grep 3306

Here's a robust way to test your tunnel connection:

#!/bin/bash
# Test MySQL connection via SSH tunnel
ssh -f -N -L 3307:127.0.0.1:3306 user@remote-server
if mysql -h 127.0.0.1 -P 3307 -u root -p -e "SELECT 1" &>/dev/null; then
    echo "Tunnel connection successful"
else
    echo "Connection failed"
    netstat -tulnp | grep 3307
    mysqladmin -h 127.0.0.1 -P 3307 -u root -p ping
fi

Remember that MySQL 5.6 has some peculiarities with host resolution that were improved in later versions. The '%' wildcard should theoretically cover all hosts, but in practice, MySQL's privilege matching has specific precedence rules where more specific hosts take priority.

---

After months of flawless operation, our MySQL 5.6.16 setup on Mac OS X Server (installed via Homebrew) suddenly started rejecting SSH tunnel connections with the classic "Access denied for user 'root'@'localhost'" error. What makes this particularly puzzling is that:

• Direct terminal access via SSH works perfectly
• The issue affects multiple clients (Sequel Pro, MySQL Workbench)
• Password resets via mysqladmin didn't help
• The error shows 'localhost' despite connecting to 127.0.0.1

When examining the user privileges, we found an interesting configuration:

mysql> select host,user from mysql.user where user='root';
+----------------+------+
| host           | user |
+----------------+------+
| %              | root |
| 127.0.0.1      | root |
| ::1            | root |
| localhost      | root |
+----------------+------+

The presence of root@% should theoretically cover all connection scenarios, but the authentication system wasn't behaving as expected through SSH tunnels.

MySQL 5.6 introduced subtle changes in default authentication. Check the authentication method with:

SELECT plugin FROM mysql.user WHERE user = 'root';

If it shows 'mysql_native_password', try updating the password specifically for localhost:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
FLUSH PRIVILEGES;

The tunneling command matters more than you might think. Here's the precise syntax that worked for us:

ssh -L 3307:127.0.0.1:3306 username@your.server.com -N

Then connect to 127.0.0.1:3307 in your MySQL client. Notice we're using port 3307 locally to avoid conflicts.

We discovered our GRANT issue was due to missing WITH GRANT OPTION. The complete solution:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Enable MySQL's general query log temporarily to see authentication attempts:

SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';
SELECT * FROM mysql.general_log WHERE argument LIKE '%connect%' ORDER BY event_time DESC;

Remember to turn it off afterward:

SET GLOBAL general_log = 'OFF';

When clients connect via 127.0.0.1 vs localhost:

• 127.0.0.1 uses TCP/IP
• localhost might use Unix domain sockets

Check your MySQL config for socket paths:

SHOW VARIABLES LIKE 'socket';

In your SSH tunnel setup, ensure you're consistently using TCP/IP connections by explicitly specifying 127.0.0.1 in both the tunnel and client configuration.

1. Verify privileges for all root accounts (including IPv6 ::1)
2. Confirm authentication plugin consistency
3. Check for password differences between root@localhost and root@%
4. Test with a non-root user through the tunnel
5. Verify skip-name-resolve in my.cnf isn't causing issues