Connect to MySQL Database Remotely

I am trying to connect remotely to the MySQL databases but I cannot. I have tried disabling the firewall in ISPConfig but still does not let me connect remotely. I have port 3306 opened on my hardware firewall as well.

I logged into the MySQL monitor and granted privileges as well. I then flushed the privileges but still cannot connect.

I've tried almost everything, any suggestions?

Thanks

 

Suggestions

Check if the firewall on your server allows remote mysql connections and if you are behind a router that you forwarded the mysql port from the router to your server.

Please post the output of:

netstat -tap
and post the content of your /etc/my.cnf file

 

Thanks for the fast reply! You guys are awesome. Hope this is what you are looking for.

The only reference to MySQL in the netstat -tap is this:

tcp 0 0 *:mysql *:* LISTEN 1985/mysqld


Here is my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

I think you did not post your whole my.cnf file but you can try this:
Note: comment out or remove the line which says: skip-external-locking (if it is in your file)

Add this to your my.cnf:

[client]
port = 3306

[mysqld]
port = 3306
#skip-external-locking


After that restart mysql with:

/etc/init.d/mysql restart

If it still does not work, have a look here:
http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

 

in the MySQL privileges of that user you did set that you can connect from anyhost and not just from localhost?

 

Did you enable MySQL Remote Access for that database in ISPConfig? http://ispconfig.org/downloads/manual_en/manual_kunde_en_src.htm#4_2_5

 

Yes, I set those in PhpMYadmin. I granted all priveleges as well.

I also set the MySQL Remote acess to YES in ISPConfig.

Still not able to connect remotely though.

 

Hi Hans,

I looked at the link info that you posted and noticed the bind-address setting. I added that to the my.cnf file with my external I.P. address. When I try to restart MySQL from the command line with that in there, it fails. However, if I restart MySQL using ISPConfig it comes back up. No biggy but I don't know why it won't start at the command line but it will using ISPConfig.

Now, in my Firewall rules I have added port 3306 and other ports as well, but when I look at the /etc/Bastille/firewall-firewall.cfg that port is not listed but the others are. So.. I added it manually and restarted the firewall from ISPConfig.

But...I still cannot get connected remotely. I dunno, this is driving me nuts. I need a beer I guess.

Thanks for your help, all of you!

 

OK...I finally got it to work. What I did was I restarted the bastille-firewall from the command line after I added port 3306 manually to the cfg file.

Thanks all!

 

You must also add that port to the template file /root/ispconfig/isp/conf/bastille-firewall.cfg.master otherwise your changes will be overwritten the next time you make changes in ISPConfig.

 

I there i have a similiar problem when i added the:
bind-address =

To the mysql config file my.cnf it doesnt restart anymore from the command line. It gives an error but it does from ISPconfig.

Also my port is open i can telnet to port 3306 from another box, but when i try to connect from another plesk server to use the MYSQL server i get

Access denied for user 'root'@'xx.xx.xx.xx' (using password: YES)

 

Thanks till, can you tell me how, what commands to run from SSH?
Thanks

 

It's easiest to do this with phpMyAdmin (if it is installed; if it isn't, you should install it now).
You can then go to the mysql.user table and change the host in the Host column. Restart MySQL afterwards.