How to setup a MySql server to accept remote connections?

Solution 1:

You'll have to bind your mysqld to a IP different from 127.0.0.1.

Open your my.cnf (/etc/mysql/my.cnf usually) and change the line that says

bind = 127.0.0.1

to whatever IP your machine uses to connect to the outside world. 0.0.0.0 binds to all IP addresses. Don't forget to restart your mysqld after that change.

Solution 2:

In addition to halfdan answer I had to execute the following mysql command:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON databasename.*
TO 'username'@'clientipaddress'
IDENTIFIED BY 'password'

As I learned on

http://forums.mysql.com/read.php?86,23619,41248#msg-41248

Solution 3:

Several more potential problems explained here:

http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html http://dev.mysql.com/doc/refman/5.1/en/access-denied.html

If you want to grant all privileges to all databases, try

grant all on *.* to 'joe'@'%';

However, before you do that, make sure you have a user in the "mysql" database that has "Host" set to your IP address--in my case, my IP here at home. So user "joe" may have more than one record, one for each IP he might be calling from. To see what you have in there already:

use mysql;
select Host, User, Password from user where user='joe';`

In my case, turned out my user now had the correct IP but the password was also missing. I cut-pasted the password (it's hashed or something) and this solved my particular remote connection problem:

update user set Password='5493845039485' where user='joe';

One more thing, in my.cnf you may want to set "port=3306" or whatever port you plan to use.