How to Setup Remote MySQL Database Connection? If you need to connect to your database using third party software that does not run on the server, such as a MySQL client on your desktop a remote MySQL connection can be made. First you will have to allow the connection from within cPanel or Plesk for the IP address you are connecting from your computer.
To prevent potential security risks to MySQL server, by default remote MySQL connections are disabled in cPanel and Plesk servers. Since we do not allow remote MySQL connections in cPanel and Plesk shared hosting, this guide will be only applicable to our VPS and dedicated server customers. Before you check out following methods, make sure that port 3306 is opened in your server firewall setting.
There are a few programs you can use to connect remotely to your database. HTML editors like DreamWeaver can connect to your databases remotely as well. There are Free Open Source programs you can use like MySQL Workbench.
For Plesk Control Panel
By default, Plesk administrator user does not have a remote access. It is recommended to create a new user that will be used for remote connection.
In order to enable remote access to MySQL server, connect to the server via SSH/RDP and follow instructions below:
1) Login to Plesk and make sure that Allow remote connections from any host option is enabled in Tools & Settings > Database Servers > Settings
2) Edit my.cnf/my.ini file changing bind-address parameter to:
bind-address = 0.0.0.0
Also, make sure that skip-networking parameter is not defined.
3) $ plesk db
Grant the 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 220.127.116.11:
> GRANT ALL ON database.* TO [email protected]’18.104.22.168′ IDENTIFIED BY ‘password’;
> FLUSH PRIVILEGES;
To create a user that has access to all databases, execute the following query:
> GRANT ALL PRIVILEGES ON *.* TO ‘someuser’@’22.214.171.124’ 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;
> FLUSH PRIVILEGES;
Note: to allow connections from any IP address use ‘ % ‘ character.
4) firewall allow 3306 port
5) Login from the remote server:
$ mysql -h example.com -u someuser -ppassword
For CPANEL Control Panel
A) Enable Remote MySQL from WHM
# Login to WHM.
# Locate SQL Services section and click on Additional MySQL Access Hosts link.
# At next screen, enter IP address or host(s) that you wish to grant remote MySQL access to and click the Save button. If you want to activate these settings for all cPanel accounts, click on Click Here link at bottom. Now, remote connection has been activated in WHM. Each cPanel account that wants to use remote connection need to activate it as follows.
B) Enable Remote MySQL from cPanel
# Login to cPanel.
# Locate the Databases section. Under Databases section locate Remote MySQL option and and click on it.
# In the next screen, enter the hostname or IP address that you want to grant remote MySQL access to and click the Save button. If your IP address changes regularly, you can use wildcard by using the % symbol. This will add all IP addresses in that range. For example; if you want to list IP addresses range from 192.168.3.1 to 192.168.3.255, you can add an IP address as 192.168.3.%.
Sometimes, after enabing MySQL remote connections, it could not get accessed remotely because it might bound on localhost (127.0.0.1). To fix this, you’ll need to do following changes in MySQL configuration file (my.conf).
# Login to MySQL server (where MySQL server is installed) and edit the my.cnf file using a text editor such as vi. Usually my.cnf file is located at /etc/mysql/my.cnf or at /etc/my.cnf.
$ vi /etc/my.cnf
Once file is opened, locate line that containts skip-networking and make sure that it is commented as follows.
Now locate the line containing bind-address and comment it out as follows.
# bind-address = 127.0.0.1
Save and Close the file and Restart the MySQL server.
# /etc/init.d/mysql restart // Debian & Ubuntu Linux
# /etc/init.d/mysqld restart // CentOS & Fedora Linux
Done, now you are good to go.