Applicable to:
- Plesk for Linux
- Plesk for Windows
Question
How to enable remote access to a MySQL/MariaDB database server in Plesk?
Answer
Warning: After enabling the access, all MySQL/MariaDB 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/MariaDB port is not blocked by a firewall: How to open all ports on local server needed by Plesk automatically?
When firewalld is enabled the following command may be used to open the port:
# firewall-cmd --zone=plesk --permanent --add-port 3306/tcp
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 user admin can be used, too.
In order to enable remote access to a MySQL/MariaDB server follow the instructions below:
-
Go to Tools & Settings > Database Servers > Settings > click on the Local MySQL Settings link:
-
Select the Allow local MySQL server to accept external connections checkbox and click OK to apply changes:
-
Go to Subscriptions > example.com > Databases > User Management > example_db_user.
-
Make sure that the Access control option is set to Allow remote connections from any host or to Allow remote connections from:
-
Open the MySQL/MariaDB configuration file in any text editor. The 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%Databases\MySQL\my.ini
-
-
Change/add
bind-addressparameter to:CONFIG_TEXT: bind-address = 0.0.0.0
0.0.0.0 means every IP address on the server. If it is required to bind to a specific IP address, specify this particular IP instead of 0.0.0.0
Note: Make sure that
skip-networkingparameter is not defined in MySQL/MariaDB configuration file.Note: The
bind-addressdirective may also be specified in the/etc/mysql/mariadb.conf.d/50-server.cnffile. If it is, make sure it is pointing to 0.0.0.0 instead of the loopback address. -
Restart MySQL/MariaDB server:
-
For Linux:
# service mariadb restart || service mysql restart
-
For Windows:
Start Plesk Services Monitor > select MySQLXX or MariaDBXX (where XX is a version) > click Restart.
-
To verify connectivity, log in from the remote server using MySQL Workbench or via CLI by executing the following command:
# mysql -u"example_db_user" -p"password" -h"example.com"
Comments
Any chance to perfom this task from the command line? Would be helpful in automation.
Setting bind-address to * in my.cnf works until the next update only, so I guess there's something else being set in the background.
When using MariaDB on a Windows Servers and you use IPv4 AND IPv6 you need another bind-address otherwise database connections will be slow.
You can clearly experience this when clicking around in phpMyAdmin
bind-address = 0.0.0.0 (= IPv4 only)
bind-address = * (= IPv4 + IPv6 connectivity)
Explanation: database connections to localhost will first try connecting to ::1, timeout and then try 127.0.0.1
Please update the article so other do not make the same mistake and end up with slow MariaDB connections.
I've noticed that the "Allow local MySQL server to accept external connections" checkbox sometimes resets itself, requiring me to enable it manually each time.
Does this occur during MariaDB upgrades?
Can this be fixed?
Please sign in to leave a comment.