How to enable remote access to MySQL database server

Created:

2016-11-16 12:41:14 UTC

Modified:

2017-04-24 11:04:22 UTC

9

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, for MySQL users and databases which are created in Plesk remote connections from any host are allowed.

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

  1. Login to MySQL server using mysql utility:

    mysql -uadmin -p

    Note: for Windows mysql utility can be found under %plesk_dir%\MySQL\bin\mysqld.exe

  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 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 file. For example:

    bind-address = 203.0.113.10

    After that, restart MySQL server.

Have more questions? Submit a request

2 Comments

  • 0
    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.