Connect to MySQL Database Remotely

Discussion in 'Installation/Configuration' started by unixfox, Nov 17, 2006.

  1. unixfox

    unixfox New Member

    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?

  2. Hans

    Hans Moderator ISPConfig Developer


    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
  3. unixfox

    unixfox New Member

    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

    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).


  4. Hans

    Hans Moderator ISPConfig Developer

    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:

    port = 3306

    port = 3306

    After that restart mysql with:

    /etc/init.d/mysql restart

    If it still does not work, have a look here:
    Last edited: Nov 18, 2006
  5. sjau

    sjau Local Meanie Moderator

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

    falko Super Moderator ISPConfig Developer

  7. unixfox

    unixfox New Member

    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.
  8. unixfox

    unixfox New Member

    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!
  9. unixfox

    unixfox New Member

    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!
  10. falko

    falko Super Moderator ISPConfig Developer

    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.
  11. nibb

    nibb New Member

    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)
  12. till

    till Super Moderator Staff Member ISPConfig Developer

    You must add a permission record for the mysql root user for the IP of your external server in the mysql user table to login from a remote server. Don't forget to flush the database priveliges after you changed the user table or reload mysql.
  13. nibb

    nibb New Member

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

    falko Super Moderator ISPConfig Developer

    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.

Share This Page