Remote mysql connection using cPanel and VPS server
What is a remote mysql connection?
Have you ever thought that you could use the web server in one place and the mysql server in a different place? It is possible, this is usually done to split the server resources, so that your database server is used separately than your other web server. Normally mysql queries are the CPU intensive work on every server. There are a lot of people trying to connect remotely to a mysql database, some do know how to do this, some do not know. This article is more intended for those who wish to know how this is done. I thought of doing this using a control panel, cPanel to be exact, which offers great support and its easy to setup a remote mysql connection, but I will also explain how this is done from a normal / standalone mysql server on a VPS server or Dedicated server.
I will first start off with cPanel as it is easier.
Whitelisting your remote IP address for mysql connection
First and foremost you need to whitelist your remote mysql connection, this is done by following the indication below:
- Login to your cPanel account
- Click on Remote MySQL
- Here add the remote IP address or domain name, from where you are connecting to the mysql database
- Click on Add Host
You can also add a wildcard here, for example the percent (%) symbol, this means that you can connect from any location as long as your mysql credentials and grant permissions are correct. The wildcard is mostly use by the ones having a dynamic IP address, IP address which is always changing.
After adding the IP address or domain name to your Remote mysql section you can start making a remote connection using your proper details:
- Mysql Remote server: yourdomain.com or server IP
- Port: 3306
- Mysql Database name
- Mysql username
- The correct mysql user password
Setting up a remote connection on a standalone mysql server
For those of you who have a mysql server set on a VPS server for instance, where a control panel is not provided, you will need to make sure of a few things:
- Mysql server is listening to the server public IP address
- Your mysql user has grant permission from the remote IP address.
For the first part, normally VPS server with a preinstalled operating system, by default have the mysql service set to listen only on locahost. For security reasons this is set like this so that mysql connections can only be done from the local server, eg. php scripts running on the same server as the mysql server.
So if using only the mysql server on your VPS or dedicated server, you can just bind the mysql server to the public IP address and you are done. For example you have the below configuration:
1 2 3 4 5 |
[mysqld] # ... bind-address = 25.25.6.5 #skip-networking # ... |
bind-address – Basically we want our mysql server to listen only on IP address 25.25.6.5. However if you will want to use for instance localhost to connect locally, it won’t work anymore, you need to use the server IP.
skip-networking – Since we need to have remote mysql connection, we necessarily need to have networking connection, so for this you need to remove the line referring to this option or comment it like I did in the above example.
How to make mysql listen on all IP addresses?
But what if I want to use mysql on all our IP addresses? Well the solution here is to use the the below values:
1 2 3 4 5 |
[mysqld] # ... bind-address = 0.0.0.0 #skip-networking # ... |
Basically you would set the value 0.0.0.0 which you use to specify “any IPv4 IP address”. This will allow you to connect remotely and at the same time locally.
Giving grant permission to your mysql user
The next part is to set your mysql user to connect to your database through your mysql server public IP address. Normally you would use a wildcard to specify that the mysql user can connect to any mysql server, but I’ll show you an example how this is done. You will need first to connect to the mysql server using the mysql client run the below sql queries:
1 2 |
Mysql> GRANT ALL PRIVILEGES ON databaseName.* To 'MysqlUser'@'25.26.6.5' IDENTIFIED BY 'mysql_password'; Mysql> FLUSH PRIVILEGES; |
So we grant *all privileges* on our *databaseName* to our *mysqlUser* connecting to mysql server *25.26.6.5* and using the mysql user password *mysql_password*. That’s basically the explanation of the first line. Then you just need to flush privileges so that grant table is updated and you are done.
Restricting access to your remote mysql connection
This is useful for further securing mysql, if you really need to allow mysql connection, then it is important to secure mysql as best as possible. In this particular case, if you have a few hosts that you wish to allow connection to the remote mysql server, then you can use the following Iptables firewall rules to permit access:
1 2 3 4 5 6 |
### allow access to 25.25.26.26, 25.25.27.27, and 25.25.28.28 ### /sbin/iptables -A INPUT -p tcp -s 25.25.26.26 –dport 3306 -j ACCEPT /sbin/iptables -A INPUT -p tcp -s 25.25.27.27 –dport 3306 -j ACCEPT /sbin/iptables -A INPUT -p tcp -s 25.25.28.28 –dport 3306 -j ACCEPT ## Block all connections to 3306 ## /sbin/iptables -A INPUT -p tcp –dport 3306 -j DROP |
The above firewall rules will permit connection to all server IP address on port 3306, but only for the following public IP address:
25.25.26.26
25.25.27.27
25.25.28.28
This way, we know for sure that access is granted only from the specific IP address we know and allow.
That’s it for today, if I missed anything, feel free to comment below and don’t forget to subscribe.