How to enable remote access to MySQL database server

Created:

2016-11-16 12:41:14 UTC

Modified:

2017-08-18 11:44:13 UTC

21

Was this article helpful?


Have more questions?

Submit a request

How to enable remote access to MySQL database server

Applicable to:

  • Plesk

Question

How to enable remote access to MySQL database server?

Answer

By default, Plesk administrator user does not have a remote access. It is recommended to create a new user that will be used for remote access. 

In order to enable remote access to MySQL server, follow instructions below:

  1. Login to MySQL server

    Note: For the access to client's MySQL server on Windows, use PHPMyAdmin: Plesk > Tools & Settings > Database Servers - MySQL DB Admin
  2. Grant access to remote IP address and login to MySQL. For example, if you want to allow access to database called database for user user with password password and remote IP address 203.0.113.2:

    mysql> GRANT ALL ON database.* TO user@'203.0.113.2' IDENTIFIED BY 'password';
    mysql> FLUSH PRIVILEGES;

    Note: to allow connections from any IP address use ' % ' character.

  3. Make sure that MySQL server is listening on the correct IP address.

    For Windows:

    netstat -anp tcp | findstr 8306
    TCP 0.0.0.0:8306 0.0.0.0:0 LISTENING

    netstat -anp tcp | findstr 3306
    TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING

    For Linux:

    [root@server ~]# netstat -anp | grep :3306
    tcp 0 0 203.0.113.10:3306 0.0.0.0:* LISTEN 13151/mysqld
  4. If not, check that skip-networking parameter is not defined and correct IP address is set for bind-address parameter in my.cnf/my.ini file located in:

    For Linux:
    /etc/mysql/my.cnf Debian/Ubuntu
    /etc/my.cnf CentOS/CloudLinux/RHEL
    For Windows:
    "%plesk_dir%\MySQL\my.ini" for Plesk databases(port 8306)
    "%plesk_dir%\Databases\MySQL\my.ini" for Clients databases(port 3306)

    After that, restart MySQL server.

  5. Make sure that bind-address option is set to :: or 0.0.0.0 in my.cnf/my.ini
  6. Make sure that Allow remote connections from any host option is enabled in Tools & Settings > Database Servers > Settings
Have more questions? Submit a request

2 Comments

  • 1
    Avatar
    adrianTNT

    Server was not responding when I tried from my local windows machine with command: telnet example.com 3306 

    Without the above user editing, I just edited /etc/my.cnf and I changed to:

    bind-address = ::

    This is how it was on a previous plesk server by default. 

    Then restarted db with:

    service mariadb restart

    Worked.

  • 0
    Avatar
    Andrey Ivanov

    Hello Adrian,

    Thank you for the feedback, the article was updated with additional details. Kindly note, that if the address is set to ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces.

Please sign in to leave a comment.