MySQL: How to allow connections from outside of localhost
Searching for a decent host I found a new home for my Ruby on Rails powered websites on a self administrated Debian Etch server. To manipulate the MySQL databases I’m running on it, I’m using CocoaMySQL—trouble was, I couldn’t access my MySQL 5 databases from my development machine even though I hadn’t narrowed the database users’ access to localhost.
I found out that MySQL didn’t listen for anything other than localhost calls—to change that, I had to do these steps:
$ nano /etc/mysql/my.cnf
Edit the MySQL configuration file on your server (I’m using nano for editing) and go to the following line:
bind-address = 127.0.0.1
The IP address 127.0.0.1 means your MySQL is only listening for anything localhost. Change it to the IP of your server, press CTRL + O to save and CTRL + X to exit from nano.
Now all you need to do to make MySQL pick up the new settings is execute the following line:
$ /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..
You should be able to access your databases from other machines now. If you don’t need to do this or don’t need it anymore, change the IP back to 127.0.0.1 to make things more secure. 
Though it should work, don’t use this method to move databases—you don’t need to download and dump your data to your local machine before uploading it to the a new server again. I wrote another post on how to move MySQL databases with the mysqldump command.
Additional comments powered by BackType
I would recommend using SSH-tunneling. It’s using an encrypted connection and only server and ssh-connected machines can acces your mysql-db. This schould be an advantege since MySQL-passwords are usually stored in configuration files etc. so someone who got these can access your database form everywhere. But your ssh password/passphrase is nowhere written down so hard to find out.
If you are running a local test server, you can even run your application with the database on the server without changing any configurations.
I thought about it—actually, CocoaMySQL offers to connect via SSH tunnels, but each time I try, it gives me a MySQL error—whether I configure it to use a tunnel or not, it acts just the same.
But I’ll have another try on this issue, it’s true—it’s more secure via SSH (and less to fiddle around).
Really it is very helpful and thanks for sharing it.
I also have problems with MySQL, after reading this post I hope to forget about them, thanks a lot!
Just in case anyone reading doesn’t realise. You also have to make sure the user you’re connecting with has the permissions to access remotely. Most users have “localhost” in the Host field which means only local connections. But if you put “%” in that field thats the MySQL equivalent of a wildcard allowing any host connection.
@Doug Films: Thanks for mentioning that—very important!
grant all on database.* to 'user'@'localhost' identified by 'password'won’t let you connect from outside—the following will:
grant all on database.* to 'user' identified by 'password'Sorry but it doesnt works in my localhost, it gives me an error, any other option?
has “an error” some description ?