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.
[code style=”1″]
ssh username@hostname
[/code]
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.
[code style=”1″]
sudo vim /etc/my.cnf
[/code]
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.
[code style=”1″]
bind-address = 10.193.200.250
[/code]
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.
[code style=”1″]
#bind-address = 10.193.200.250
or
bind-address = 0.0.0.0
[/code]
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.
[code style=”1″]
# skip-networking
[/code]
Now just restart the mysql server using following command.
[code style=”1″]
/etc/init.d/mysql restart
[/code]
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.
[code style=”1″]
mysql -u root -p
[/code]
after type your password will be land on mysql> terminal.
[code style=”1″]
CREATE USER ‘remoteuser’@’localhost’ IDENTIFIED BY ‘securepass’;
grant all privileges on mydb.* to remoteuser@’10.193.200.250′ identified by ‘securepass’;
FLUSH PRIVILEGES;
[/code]
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.?
[code style=”1″]
grant all privileges on mydb.* to remoteuser@’%’ identified by ‘securepass’;
[/code]
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.
[code style=”1″]
mysql -u remoteuser -h REMOTE_SERVER_IP -p
[/code]
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.
[code style=”1″]
use mydb;
[/code]
the do what ever you like import database tables ?
[code style=”1″]
source file_name.sql
[/code]
Happy reading 🙂 🙂 🙂