How to enable remote access to MySQL server in Plesk?

Yulia Plokhotnikova

Updated October 05, 2021 09:43

Plesk for Windows Plesk for Linux FR:PPM-2313 kb: how-to ABT: Group A

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

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 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

Video instruction for Linux

Allow remote access to MySQL database server in Plesk for a specific user

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-address parameter 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-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

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.

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.

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%Databases\MySQL\my.ini
Change bind-address parameter 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-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:

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 LISTENING

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

For 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.

remotemysql.zip
943 Bytes Download

Comments

17 comments

adrianTNT March 24, 2017 13:24

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.

1

Comment actions Permalink
Andrey Ivanov March 28, 2017 11:24

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.

1

Comment actions Permalink
Unknown User March 23, 2018 18:09

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

0

Comment actions Permalink
Ivan Postnikov March 23, 2018 19:36

@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".

0

Comment actions Permalink
Unknown User March 24, 2018 17:05

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

0

Comment actions Permalink
Ivan Postnikov March 24, 2018 17:25

@Veronica

Glad to hear that. Thank you for the feedback.

0

Comment actions Permalink
Kingsley Felix September 12, 2018 15:04

Hello;

I want my my site user/system user to have access to mysqldump utility so i can use it for Jetpack vaultpress backup

0

Comment actions Permalink
Pavel Mikhaylov September 19, 2018 19:12 (Edited September 19, 2018 19:12)

Hello,

That is not possible as MySQL has its own users.

0

Comment actions Permalink
stathopon April 18, 2019 21:47

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?

0

Comment actions Permalink
Nikita Nikushkin April 19, 2019 07:57 (Edited April 19, 2019 07:58)
Hi @stathopon,
```
> "How can i check if MySQL port is not blocked by a firewall?"
```
You can try to connect to its port by using, e.g. the "telnet" utility:
```
# telnet example.com 3306
```
If the next output was returned:
```
telnet: Unable to connect to remote host: Connection refused
```
then the connection is blocked by a firewall, otherwise, you will see something similar to:
```
Trying 203.0.113.2...
Connected 203.0.113.2.
Escape character is '^]'.
b
5.5.5-10.1.37-MariaDB-0+deb9u1
```
```
> what can i do to have access from 2 ips in MYSQL?
```
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
0

Comment actions Permalink
Matt Kennedy June 13, 2020 08:34

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.

0

Comment actions Permalink
Ivan Postnikov July 01, 2020 14:21

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.

0

Comment actions Permalink
Ericbeck November 30, 2020 00:48

I have some question.

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

Does this mean that when the bind-address was previously 127.0.0.1 that connections were encrypted? What should be set up for encryption then? Server

If you have a shared server but with only 1 IP address assigned to it, can you set the bind-address then to

bind-address = 111.111.111.111 (if my ip is that) and it will then allow the remote connections?

On the other hand if it is changed to bind-address = 0.0.0.0, will anything break (onyx 17.5.3) with existing databases in use, all the users' database applications? In other words, it's completely ok to make that change to the bind-address without negatively affecting anyting? I just want to make sure before I do this.

I'm not sure why you have to do the GRANT thing, if the bind-address is changed to 0.0.0.0 and the subscription has allowed remote connections to the db and the selection in the subscription db user is configured to allow from any host, would that not be setting the GRANT privilege for that user via the panel? Or do you still have to do it manually?

Thanks, Eric

0

Comment actions Permalink
suzie rosen December 11, 2020 01:26 (Edited December 11, 2020 01:29)

Yulia Plokhotnikova I was trying to connect a remotely to my plesk MySql from a different plesk server, following these instructions, but with the default 'admin' user and credentials.I kept on getting an error while connecting remotely (on the remote server) that 'admin'@'[remoteI P]' does not have permission.

Only after I created a new MySql user with admin privileges on the MySql server was I able to connect with those credentials.

When I created a database from the remote server it also didn't show up in plesk on the MySql server.

Only in phpMyAdmin do all databases come up.

What is the problem? It seems the default configuration really does not work at all!

NOTE: Both servers run plesk

1

Comment actions Permalink
André da Gama e Souza February 25, 2021 20:08

Hi, I did everything. Even turning off firewall (wich clearly is not the problem). What I really need to do is just allow remote access to mysql (mariaDB) from specific subdomain ip. I did all support material, restarted server several times. I can´t remote access even opening everythig. And, I´m sure it´s not related to that because the error I´m facing is: ERROR 1130 (HY000): Host 'my local ip' is not allowed to connect to this MariaDB server

I´m trying to open a ticket support, but it just doesn´t work (appears to be only a FAQ service)...

I´ve replaced Cpanel from this server to use Plesk and future use it on our servers. It seems very good. But this problem is happening for 2 days and still no perspective of resolution. Please, Help me!! Tks.

0

Comment actions Permalink

marktoddy June 21, 2021 06:18

One has to create a new MySQL User and assign privileges as below in Query prompt via phpMyAdmin or command prompt:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Once done with all four queries, it should connect with username / password

SergeyK September 17, 2021 08:16 (Edited September 17, 2021 08:49)

Could you explain how to indicate MySQL connection problem?

Plesk Firewall settings:

1. 3306 enabled - https://prnt.sc/1sjd6nn

2. My IP was added to the Whitelist - https://prnt.sc/1sjda8v

3. DB connection by IP was disabled for test - https://prnt.sc/1sjdhvk

4. Telnet site.com 3306 - can't connect with DB port

What another Rule can block 3306 connection in this case?

This is a list of Plesk Firewall Rules - https://prnt.sc/1sje0eb

Please advise. :-(

Is it possible to find Log with error explanation ?

0

Comment actions Permalink

Please sign in to leave a comment.

Have more questions? Submit a request

Articles in this section

Applicable to:

Question

Answer

Related articles