Applicable to:
- Plesk for Linux
- Plesk for Windows
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:
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.
-
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%Databases\MySQL\my.ini
-
-
Change
bind-addressparameter to:CONFIG_TEXT: 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-networkingparameter 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
-
-
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
databasefor useruserwith passwordpasswordand remote IP address203.0.113.2:CONFIG_TEXT: GRANT ALL ON database.* TO user@'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:
CONFIG_TEXT: GRANT ALL PRIVILEGES ON *.* TO 'user'@'203.0.113.2' IDENTIFIED BY 'password' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
CONFIG_TEXT: FLUSH PRIVILEGES;Note: to allow connections from any IP address use '
%' character. -
Make sure that MySQL server is listening on the correct IP address.
For Windows:
C:\> netstat -anp tcp | findstr 8306
TCP 0.0.0.0:8306 0.0.0.0:0 LISTENINGC:\> netstat -anp tcp | findstr 3306
TCP 0.0.0.0:3306 0.0.0.0:0 LISTENINGFor Linux:
# netstat -anp | grep :3306
tcp 0 0 203.0.113.10:3306 0.0.0.0:* LISTEN 13151/mysqld -
If Plesk firewall is used on the server, allow remote connection to MySQL in Tools & Settings > Firewall.
If another firewall solution is used, ensure that access to port 3306 is allowed.
-
Login from the remote server via CLI:
CONFIG_TEXT: mysql -u someuser -p "password" -h example.com
Or using MySQL Workbench.
-
Go to Plesk > Subscriptions > example.com > Databases > User Management > example_db_user;
-
Make sure that Access control is set to Allow remote connections from any host or to Allow remote connections from:
-
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%Databases\MySQL\my.ini
-
-
Change
bind-addressparameter to:CONFIG_TEXT: 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-networkingparameter 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
-
Note: 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"
Note: If the remote connection cannot be established check firewall settings - the required port possibly is blocked.
Comments
12 comments
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.
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.
hi, I tried several times to do this procedure, but with vi when he tells me that I do not have write permissions, in the root I have enabled all writing services, but when I use it tells me that I do not have the services and I do not does not even save with: w! o: wq! you can help me courteously
@Veronica
Hello!
Please, make sure that vi should be started with root privileges (in case you are logged in not as root user use command "sudo vi").
Also, try another text editor like "nano".
Thanks Iv
an Postnikov
for the help, I solved the problem by asking and then releasing as an administrator with the "su" command
Osa I managed to synchronize the two db and everything works perfectly
Excellent tutorial
@Veronica
Glad to hear that. Thank you for the feedback.
Hello;
I want my my site user/system user to have access to mysqldump utility so i can use it for Jetpack vaultpress backup
Hello,
That is not possible as MySQL has its own users.
How can i check if MySQL port is not blocked by a firewall?
what can i do to have access from 2 ips in MYSQL?
Hi @stathopon,
You can try to connect to its port by using, e.g. the "telnet" utility:
If the next output was returned:
then the connection is blocked by a firewall, otherwise, you will see something similar to:
It can be achieved by performing the next actions:
1. Install Plesk Firewall:
How to install Plesk Firewall?
2. Go to "Plesk > Tools & Settings > Firewall"
3. Click the "Enable Firewall Rules Management > Enable" buttons
!!!Warning: if you have already previously manually added rules in iptables they will be removed
4. Press the "Modify Plesk Firewall Rules" button and then click the "MySQL server" rule
5. Switch the "Action" directive to the "Allow from selected sources, deny from others" option
6. Specify the required IPs in the "Add IP address or network" field and press the "Add" button
7. Press the "OK > Apply Changes > Activate" buttons
I had to do this on a new cloud server setup with fasthosts. Just out of interest is this a security risk not entering an IP? Just concerned about external access.
Hello Matt Kennedy
From the security point of view, it's preferable to specify specific IPs. This way even in case someone will get access credentials won't be able to access.
Please sign in to leave a comment.