Connecting to MySQL from Workbench

Question

MySQL remote access

I have a mysql database, running on Ubuntu Server 12.04 that I need to access remotely. For some reason this is become much more of a chore than I think it should be.

I have been through countless threads trying to resolve this issue with no luck what so ever. I do not have another linux box to test my connection. I am only using the MySQL Workbench from a Window 7 machine.

Here is what I have done so far:

• set the iptables to accept
• set the my.cnf to have the bind address of the server
• created a user for both localhost and %
• grant all to those users

• restarted mysql

• verified the user has all priv

• verified mysql is listening on 3306

give the correct setting to Workbench and I get "Your connection attempt failed for user 'USER' from your host to server at x.x.x.x:3306: Can't connect to MySQL server on 'x.x.x.x' (10061)"

EDIT: I did notice that it show 'localhost and NOT the ipaddress when I run this cmd, but i'm not sure how to change that, or if it is even the issue. Thoughts?

# lsof -i -P | grep :3306

mysqld 5775 mysql 10u IPv4 154265 0t0 TCP localhost:3306 (LISTEN)

Answer 1

Find mysql config file (/etc/mysql/) comment out the following line by putting a hash character in front of it as shown -> #bind-address = 127.0.0.1 -> Restart the server: sudo service mysql restart

Answer 2

vim /etc/mysql/my.cnf

Change the following line to reflect as below:

(bind-address            = 127.0.0.1)
bind-address            = 0.0.0.0

Close the file then and restart mysql

To verify that mysql port 3306 is listening on all interfaces: netstat -lnt | grep 3306

You should see this:

tcp    0      0 0.0.0.0:3306         0.0.0.0:*         LISTEN