#remote_database

[51stacks]

Enabling remote access to Mysql database server

When scaling up, one thing Startups and companies consider is separating out their data in remote database server and accessing it remotely through their app.

As Mysql is the most popular choice and favorite . Also, By default the remote access for Mysql databases is disabled due to security issues.

TO enable it here is a quick tutorial:

First, login over ssh to remote MySQL database server:

ssh rootusr@remote-db-server-ip

Edit my.cnf file i.e mysql server configuration file

  ## For Debian Linux file is at  /etc/mysql/my.cnf location

## For Red Hat Linux/Fedora/Centos Linux file is at /etc/my.cnf location

## For FreeBSD you need to create a file /var/db/mysql/my.cnf

## now Edit /etc/my.cnf, run:

$> vi /etc/my.cnf

  locate [mysqld] in file my.cnf

edit the section to look like this.

[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 = <mysql-server-ip-address> # skip-networking .... .. .... Save and Close the file.

IMP NOTE:

mysql-server-ip-address is the IP address of the machine where mysql database is located and binded to mysql database.

 commenting skip-networking , ensures remote connections can be made.

Restart Mysql service to ensure changes work

  $>/etc/init.d/mysql restart  for Debian Linux

$>/etc/init.d/mysqld restart  for CentOS/Fedora

$>/usr/local/etc/rc.d/mysql-server for FreeBSD

 Grant access to remote IP address / remote user

connect to Mysql

$> mysql -u root -p db_name

mysql> GRANT ALL on db_name.* TO <remote_user>@'<remote_ip>' IDENTIFIED BY 'password';

##NOTE: <remote_user> is user who needs access <remote_ip> is application servers ip that will access db_name

Now, Logout from MySQL

mysql> exit

  Enable port 3306 (if its not)

  Using Linux based IPTABLES

$>/sbin/iptables -A INPUT -i eth0 -p tcp --destination -port 3306 -j ACCEPT

save all rules

$> service iptables save

$>pass in on $ext_if proto tcp from any to any port 3306

Test remote connection

From your local now you should be able to connect

$> mysql -h <mysql-server-ip-address> -u <remote-user> -p

You can alos telnet to check if it works:

$> echo X | telnet -e X <mysql-server-ip-address> 3306