16

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

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.

  • 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 6 '09 at 16:56
  • user@localhost and user@<hostname> is different. You have to add the user to be able to connect from remote hosts. – James Dec 6 '09 at 17:07
  • This is not the problem. I've done this and I still get the error. – Cerin Jun 4 '13 at 22:37
  • Should that be bind-address? – Thomas Weller Jul 1 '15 at 13:22
5

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

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.