Recently I have been assigned a task to write a script to import all forum data by making remote MySql database connection. So in this article, I am going to share my knowledge how to enable remote database server connection. If you’re programmer then it’s for you.
By default, MySQL database server remote access disabled due to some security reasons. However, few hosting services enable us to white list your IP in cPanel for remote MySQL access, if it’s not then we can enable remote access from web server.
Easy steps to enable remote MySQL access:
1. Login Over SSH
First you need to open your SSH client such as PuTTY or any other and get login by typing following command.
ssh remote_username@remote_host
2. Open MySQL configuration file
Once connected, you need to open the MySQL configuration file my.cnf using a text editor such as vi.
- If you are using Debian Linux, the my.cnf file is located at /etc/mysql/my.cnf location.
- If you are using Red Hat Linux/Fedora/Centos Linux, the my.cnf file is located at /etc/my.cnf location.
- If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf location.
vi /etc/mysql/my.cnf
3. Edit MySQL configuration file
Once the MySQL configuration file is opened, locate line that read as follows:
[mysqld]
And comment out following lines in MySQL configuration file
#bind-address = 70.60.50.5 #skip-networking
Where bind-address is where IP address need to bind and skip-networking is disabled to not listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should removed from file or put it in comment state.
For example, if your MySQL server IP is 70.60.50.5 then entire MySQL config section will look like as follows after comment out:
[mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/English #bind-address = 70.60.50.5 #skip-networking
4. Save and Close MySQL configuration file
Now you need to save and close your opene MySQL configuration file. You also need to Restart your MySQL service to take change in effect.
service mysql restart
5. Grant access to remote IP address
You might be surprised to see even after above change you are not getting remote access to all your MySQL databases.
Actually by default, MySQL username and password you are using is allowed to access MySQL server is locally. So you need to update privileges:
mysql -u root –p mysql
You can run a command like below to access from all machines.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
If you want to give access to some specific IP then run a command like below to give access from specific IP.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
In above, you can replace 1.2.3.4 with your IP and USERNAME & PASSWORD with your remote database username. You can run above command many times to GRANT access from multiple IPs.
6. Check final remote access outcome
You can simply check final remote access outcome by:
SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";
7. Flush privilege
Actually FLUSH PRIVILEGES command can be used after granting access to take effect without stopping and restarting MySQL. So finally, you may need to run this command:
mysql> FLUSH PRIVILEGES;
8. Test remote MySQL connection
Now finally you can test you remote database connection by typing below command:
$ mysql -h HOST -u USERNAME -pPASSWORD
If you have a mysql shell, you can run show databases; to check if you have right privileges from remote database server.
9. Revoke Access
If you want to grant access to a user, then it’s better to use REVOKE to revoke all options for a user. You can type below REVOKE command to revoke all options for USERNAME from all machines:
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'%';
The following will revoke all options for USERNAME from particular IP:
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'1.2.3.4';
Hope you have got a lot to enable remote MySQL connection and make remote connection. If we missed anything, feel free to let us know through your precious comments.
