How to setup a MySql server to accept remote connections?

Question

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

score 22 · Accepted Answer · edited Jul 01 '15 at 15:08

22

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.

edited Jul 01 '15 at 15:08

Thomas Weller

135
11

answered Dec 06 '09 at 16:53

halfdan

704
4
6

got a different error now: Could not connect to host '192.168.0.2'. MySQL Error Nr. 1130 Host 'whatever-vubuntu.local' is not allowed to connect to this MySQL server – Jader Dias Dec 06 '09 at 16:56
user@localhost and user@ is different. You have to add the user to be able to connect from remote hosts. – James Dec 06 '09 at 17:07
This is not the problem. I've done this and I still get the error. – Cerin Jun 04 '13 at 22:37
Should that be `bind-address`? – Thomas Weller Jul 01 '15 at 13:22

score 5 · Answer 2 · answered Dec 06 '09 at 17:06

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

PJ Brunet · Answer 3 · 2012-03-22T00:40:06.333

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.

How to setup a MySql server to accept remote connections?

3 Answers3

Linked

Related