How to allow remote mysql connection to your server ? need to test it from your local machine ? Yea may be some time we need to connect our server DB in our local projects or have to open remote mysql connection for another web apps or api calls.

This is most important and risky things bcoz if you open it to public any one can try to access your remote database, So basically default mysql configuration this feature is disabled and only able to connect from localhost (127.0.0.1). In any situation you may need to open it remotely make sure you just open the mysql connection only for that particular IP address. the default mysql port is 3306 so you have to open this port in your firewall settings as well.

for allow remote mysql connection to your server first of all you have to login to your server with SSH just like folllows.

ssh username@hostname

It will ask for the password just type the password then you will be logged as that username.

now you can edit the mysql conf file for allow remote mysql connection. the file my.cnf is the configuration file for mysql that can be found at /etc/my.cnf just edit the file with your favorite editor.  the my.cnf file may be different path based on the distro you’re using.

If you are using Debian Linux file is located at /etc/mysql/my.cnf location.
If you are using Red Hat Linux/Fedora/Centos Linux 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.

sudo vim /etc/my.cnf

search for “bind-address” in the file. if you’re using vim or vi simply type :/bind-address.

then just make sure you are in the editor mode by pressing insert key. then add your remote machine IP address as bind-address.

bind-address = 10.193.200.250

this is the remote machine IP address not your server IP, if your set the bind-address then, communication from this IP to your server machine is allowed via port 3306.

If you need to allow mysql remote connection from any system you have to set like below. but make sure you understood the risk involving this setup.

#bind-address = 10.193.200.250
or
bind-address   = 0.0.0.0

means just comment  the line # indicate comment

also make sure skip-networking is commented as well, If that line is not present in your conf file no worry.

# skip-networking

Now just restart the mysql server using following command.

/etc/init.d/mysql restart

now you need a mysql DB user for accessing from the remote machine, It is always good to create a separate user for managing remote connections. So you have to login to mysql server using below commands.

mysql -u root -p

after type your password will be land on mysql> terminal.

CREATE USER 'remoteuser'@'localhost' IDENTIFIED BY 'securepass';
grant all privileges on mydb.* to remoteuser@'10.193.200.250' identified by 'securepass';
FLUSH PRIVILEGES;

the above code allow access to mysql database “mydb” for the user “remoteuser”  from the IP 10.193.200.250 with a password “securepass”.

Ok now you may need this user should be able to access from any machine right.?

grant all privileges on mydb.* to remoteuser@'%' identified by 'securepass';

The “%” indicate access from any machine publicly (risk involved)

Now you can exit from mysql  by typing exit. then again type exit from SSH of server.

Then from your terminal. type below commands. make sure you local machine installed mysql client version.

mysql -u remoteuser  -h REMOTE_SERVER_IP -p

it will ask for the password and just type it, you will be able to logged in to mysql terminal of the server, there you can use following command to select the DB.

use mydb;

the do what ever you like import database tables ?

source file_name.sql

Happy reading 🙂 🙂 🙂