EXTERNAL ACCESS TO THE DATABASE ON THE VIRTUAL SERVER (VPS)
In the “factory” settings the remote access to MySQL is denied for the security reasons. But sometimes it is necessary to give access to your colleague or you will want to use a database from a site that is not on the server. This guide will explain, how you can enable the external access to the MySQL server on your VPS.
For the process you will require a login on the server via SSH.
EDIT THE CONFIGURATION FILE OF MYSQL
In the file /etc/mysql/my.cnf there are multiple configuration directives for parameters with which the MySQL database server starts. Changes in them are therefore not available when the server is running.
Address where the MySQL will receive a connection is set by parameter bind-address . After the installation it is set to receive connection at 127.0.0.1 , that means only from the inside of the VPS. You need to change it to the public IP address of the server.
File can be edited by different editors available on VPS.
Since the editing is fairly simple, you can use the simple editor nano, that we run to edit the file via command:
root@server:~# nano /etc/mysql/my.cnf
For searching for the directive bind-address we will use the shortcut CTRL+W
We rewrite the value bind-address to the IP address of the server. If you do not know it, you can find it, except for administration interface, via a command ifconfig under name inet addr:. By setting new IP address the MySQL will listen on that address. If you want it to listen on the localhost (127.0.0.1) adress, you can enter IP address as 0.0.0.0.
RESTART THE MYSQL SERVER
So the changes would take effect. it is necessary to restart the MySQL server. It means that for a short time the connection to the database will be suspended (up to 5 minutes). That is why the restart should be planned for a right time.
MySQL daemon on the server can be restart via service mysql restart
After that the external access to the server is allowed, now you need to allow access for specific databases and their users.
Editing database rights and users for external connection
After you connect to the MySQL via command line, select the database with a name mysql and edit the records for the user and database.
You can set access for each database in the table mysql.db, for users in table mysql.user. We need to change the content of the column Host so we could connect from any IP address.
You need to log in to the database before editing. Do it via command line as shown in our guide.
Changes are done with:
use mysql
UPDATE db SET Host=’%’ WHERE Db=’DATABASE_NAME’ ;
UPDATE user set Host =’%’ WHERE User=’USERNAME’
FLUSH PRIVILEGES;
After finishing these commands the external access to the databases is enabled on your VPS.