Fixing MySQL: Connects via localhost but Not via 127.0.0.1

I ran into this problem while deploying a project at a client’s environment. The project’s MySQL connection string was localhost, but it errored out saying the database couldn’t be connected, and the connection string had been replaced with 127.0.0.1. At first I thought it was a network connectivity issue, so I ran

mysql -u root -p

and found I could connect fine; ping localhost also resolved to 127.0.0.1. I suspected a mismatched MySQL JDBC driver and swapped countless driver jars, but I was sure it wasn’t my program, so I started Googling and found many people with the same situation. I’m summarizing it here for reference. The type of problem I hit was a permissions issue.

Permissions Issue

I noticed the connection string in the error log was 127.0.0.1 rather than localhost, so I tried connecting with 127.0.0.1:

mysql -h 127.0.0.1 -u root -p

This time it errored with the same message as the log — so I’d found the problem: a permissions issue. The root account has no login permission under the hostname 127.0.0.1. The fix is simple: grant the relevant account login permission. My project uses the root account, so I’ll demo with root. Log into MySQL, then run:

grant all privileges on *.* to 'root'@'127.0.0.1' identified by 'password';
flush privileges;

Our program connected to the database right away, and my problem was solved. Let’s look at other situations people ran into, just in case.

SELinux Access Control Issue

For security, SELinux may block the http process from connecting to port 3306. The fix is to try disabling SELinux:

# disable SELINUX immediately
/usr/sbin/setenforce 0
# enable SELINUX immediately
/usr/sbin/setenforce 1
# add to system default startup
echo "/usr/sbin/setenforce 0" >> /etc/rc.local

Enable MySQL’s TCP/IP Connection Mode

Also for security, when MySQL runs on a single machine it may use the skip-networking config to disable MySQL’s TCP/IP connection mode. The config file is my.cnf. We first remove skip-networking, then add the bound IP address bind-address=127.0.0.1, then restart the MySQL service.

mysql_connect() Not Supported

This is something PHP developers may hit: starting with PHP 5.5.0, the mysql_connect() function is disabled, and you must use the MySQLi or PDO_MySQL extension instead for database connections. For details on mysqli_connect() and PDO::__construct(), see the official PHP docs; I’m not a PHP person, so I won’t cover it.