Wednesday, August 1, 2012

Connecting to MySQL using "localhost"

Connecting to a MySQL Database can cause some confusion, especially when different permissions are required for different objects.

We often hear from users who can connect to a MySQL Database using "127.0.0.1" but cannot connect using "localhost", which they expect would work the same way.

The confusion is caused because to MySQL *localhost *and *127.0.0.1* are different objects, so you need to apply permissions to those objects.

You can use the script below to apply all privileges to root:

grant all privileges on *.* to 'root'@'localhost';
grant all privileges on *.* to 'root'@'127.0.0.1';
grant all privileges on *.* to 'root'@'%';
set password for 'root'@'localhost' = password ('yourpasswordhere');
set password for 'root'@'127.0.0.1' = password ('yourpasswordhere');
set password for 'root'@'%' = password ('yourpasswordhere');
flush privileges;

Thanks to Luciano Fontes for this tip.

1 comment:

macvos said...

The difference is that 'localhost' requires communication via a 'mysql.sock' file and 127.0.0.1 communicates over TCP/IP. Unless you explicitly state that all communication must go via TCP.