SSH Tunneling for Secure Connection to Remote MySQL Server
Introduction
Database is one of the must-have items for today's web application projects. I believe there are plenty of project teams who use MySQL, and I'm one of them. During the development process, there might be a case where the team members want to share a single database instance rather than keeping separate copies for each of members. However, in this case, security becomes our primary concern as MySQL does not provide a secure connection to remote clients.
(Still working on this part…)
In my opinion, SSH is such an awesome protocol. I can access to any remote computers from anywhere in this world, securely transfer files, and so on and so forth. In this article, I'll make a brief explanation on how to setup a SSH tunneling for secure connection to a remote MySQL server.
If you're not sure what 'tunneling' means, you might want to refer this article.
HOW-TO
ssh -L $LOCAL_PORT:localhost:3306 $REMOTE_SERVER
mysql -u $USER -p -h 127.0.0.1
Example
ssh -L 3306:localhost:3306 db.sumin.us
Then now I can connect to
mysql -u sumin -p -h 127.0.0.1
No localhost, but 127.0.0.1
If you tried to connect to localhost, then you probably got this kind of message.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket
'/opt/local/var/run/mysql5/mysqld.sock' (2)
My perception is that when you connect to localhost then the MySQL client tries to establish an inter process communication through mysql.sock file.
Further Applications
You can use SSH tunneling for pretty much anything you want. AFP, SMB, or FTP. You name it.
Automated MySQL Backup Script
Please leave a comment if you have any suggestions or questions.
#!/bin/bash
#
# Automated MySQL backup script
#
# @author Sumin Byeon
# @since 20070815
# @version 20070927
dir="mysql"
databases=( db1 db2 db3 ... )
# The user must be an existing MySQL user with proper preveliges
user="backup"
password="your password"
for db in ${databases[@]}; do
timestamp=$(date +%Y%m%d%H%M%S)
filename=$dir/$db-$timestamp
mysqldump $db -u $user -p$password > $filename.sql
chmod 600 $filename.sql
tar jcf $filename.tar.bz2 $filename.sql
chmod 600 $filename.tar.bz2
rm $filename.sql
done
MySQL 원격 접속이 안될 때
잡설은 생략하고-
# netstat -na | grep 3306
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
127.0.0.1:3306 이 열려있는것을 볼 수 있다. 이런 상태로는 원격 접속이 불가능하다. 2003번 에러메세지 (Can't connect to MySQL server on '%s' (%d)) 가 뜨는 것을 볼 수 있을것이다.
해결 방법은 - my.cnf 파일을 수정해주면 된다. mysqld 섹션을 보면 bind-address라는 설정 항목이 있다.
bind-address = 61.250.92.162
이렇게 수정해준다.
다시 netstat 로 확인해보면
# netstat -na | grep 3306
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 61.250.92.162:3306 0.0.0.0:* LISTEN
이제 원격 접속 잘 된다 :)