Home / server / Questions / 91482
Accepted
Jader Dias
Asked: 2009-12-07 08:47:50 +0800 CST

How to setup a MySql server to accept remote connections?

  • 772

I am installing a MySql server in Ubuntu desktop. I could connect MySql Query Browser to it when specifying the Server Hostname as localhost, but when I replace it by the machine's IP it stops working (even if the MySql Query Browser runs in the same machine).

All I have done so far is removing the iptables, but it seems it have nothing to do with it.

Here is the error message

Could not connect to host '192.168.0.2'.

MySQL Error Nr. 2003

Can't connect to MySQL server on '192.168.0.2' (111)

Click the 'Ping' button to see if there is a networking problem.

The ping is ok, though

ubuntu

3 Answers

  1. Best Answer

    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.

    • 22

  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

    • 5

  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.

    • 2