Knowledge Base

How to enable remote access to MySQL server in Plesk?

Question

How to enable remote access to a MySQL database server in Plesk?

Answer

Warning: after enabling the access, all MySQL connections will be unencrypted. To make them encrypted, an SSL certificate is required. For that and additional steps check this link: https://dev.mysql.com/doc/refman/5.7/en/using-encrypted-connections.html#using-encrypted-connections-server-side-configuration

Note: make sure that MySQL port is not blocked by a firewall. #115002633594

By default, Plesk administrator does not have remote access. It is recommended to create a new user that will be used for the remote connection. Though the ‘admin’ can be used, too.

In order to enable remote access to a MySQL server, connect to the server via SSH or RDP and follow instructions below:

Solution for Plesk Obsidian on Linux

To enable remote access to the local MySQL server, do the following:

Go to Tools & Settings > Database Servers > Settings > click Local MySQL Settings:
Select the Allow local MySQL server to accept external connections checkbox, and then click OK

Solution for Plesk Onyx on Linux

Download, unzip, and run the automatic script.

# wget https://support.plesk.com/hc/en-us/article_attachments/360017905094/remotemysql.zip
# unzip remotemysql.zip
# chmod +x remotemysql.sh
# ./remotemysql.sh

Note: The remote access will be granted to ‘admin’ user. If it is required to give access to another user, follow also step 6 from section “Manual solution for Plesk 12.* and Plesk Onyx” below.

Video instruction for Linux

Manual solution for Plesk Onyx and earlier

Log in to Plesk and make sure the option Allow remote connections from any host is enabled at Tools & Settings > Database Servers > Settings.
Connect to a Plesk server via SSH/RDP and open the MySQL configuration file in any text editor. Location of this file is:
- For Linux:
  - for CentOS/RHEL-based distributions:
    /etc/my.cnf
  - for Debian/Ubuntu-based distributions:
    
    /etc/mysql/my.cnf
- For Windows:
  %plesk_dir%DatabasesMySQLmy.ini
Change bind-address parameter to:
```
bind-address = 0.0.0.0
```
Note: 0.0.0.0 means every IP address on the server. If it is required to bind to specific IP address, specify this particular IP instead of 0.0.0.0

Also, make sure that skip-networking parameter is not defined.
After that, restart MySQL server:
- For Linux:
  - for CentOS/RHEL-based distributions:
    
    # service mariadb restart
  - for Debian/Ubuntu-based distributions:
    
    # service mysql restart
- For Windows:
  
  Start Plesk Services Monitor > select MySQLX or MariaDBX (where X is a version) > click Restart.
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.
Grant access to remote IP address and login to MySQL. For example, if it’s required to allow access to the database called database for user user with password password and remote IP address 203.0.113.2:
```
GRANT ALL ON database.* TO [email protected]'203.0.113.2' IDENTIFIED BY 'password';
CONFIG_TEXT: FLUSH PRIVILEGES;
```
To create a user that has access to all databases, execute the following query:
```
GRANT ALL PRIVILEGES…
```