If MySQL security is one of your concerns, you should use encryption when connecting to the server. Setting up SSL in MySQL is not really straightforward as you have to generate key, certificate and GRANT for specific user with REQUIRE SSL statement. This would bring additional maintenance task for DBA.
The easiest way to achieve this is by using SSH encryption. Instead directing the application to connect to standard port 3306 with plain connection, why don’t you connect it to a ‘forwarding’ port which map to the MySQL port via SSH? SSH is secure and almost everyone with Linux basic knowledge knows how to manage it.
Setting up SSH access and port forwarding
Let’s say we have a application/web server and a MySQL server listening on standard port 3306:
- 10.0.0.20 – web server
- 10.0.0.21 – mysql server
Following steps should be performed on the application/web server.
1. As root user, generate a SSH key:
$ whoami
root
$ ssh-keygen -t rsa # press enter on all prompts
|
$ whoami root $ ssh-keygen -t rsa # press enter on all prompts
2. Copy the SSH key on web server to MySQL node:
$ ssh-copy-id 10.0.0.21 # enter the root password for 10.0.0.21 if prompted
|
$ ssh-copy-id 10.0.0.21 # enter the root password for 10.0.0.21 if prompted
3. Verify that you can run following command without the MySQL node prompting a password:
$ ssh 10.0.0.21 "ls /usr"
|
$ ssh 10.0.0.21 "ls /usr"
4. Run SSH command to listen to port 10001 on localhost IP and forward it to port 3306 on 10.0.0.21 as root user:
$ ssh -fNg -L 10001:127.0.0.1:3306 10.0.0.21
|
$ ssh -fNg -L 10001:127.0.0.1:3306 10.0.0.21
5. Verify that you got following port listed:
$ netstat -tulpn | grep 10001
$ ps aux | grep ssh
|
$ netstat -tulpn | grep 10001 $ ps aux | grep ssh
6. Finally, connect to MySQL server using MySQL client securely:
$ mysql -uroot -p -h127.0.0.1 -P10001
|
$ mysql -uroot -p -h127.0.0.1 -P10001
At this point, you can redirect your application to communicate through 127.0.0.1 port 10001 as a secured MySQL connection via SSH.
Dedicated SSH user
Above method works fine if you are running as user root, but this is not the safest method. Since running the SSH command does not require super user privilege, we should create a specific user other than root specifically for this process. In this example, I created a user called ‘myuser’.
1. On both servers, create the user and assign a password.:
$ useradd myuser
$ passwd myuser
|
$ useradd myuser $ passwd myuser
2. On application/web server, generate a SSH key for myuser:
$ su - myuser
$ whoami
myuser
$ ssh-keygen -t rsa # press enter on all prompts
|
$ su - myuser $ whoami myuser $ ssh-keygen -t rsa # press enter on all prompts
3. Then, copy the SSH key to MySQL server, 10.0.0.21:
$ ssh-copy-id 10.0.0.21 # enter the myuser password for 10.0.0.21 if prompted
|
$ ssh-copy-id 10.0.0.21 # enter the myuser password for 10.0.0.21 if prompted
4. Start the SSH port forwarding using following command:
$ ssh -fNg -L 10001:127.0.0.1:3306 10.0.0.21
|
$ ssh -fNg -L 10001:127.0.0.1:3306 10.0.0.21
5. To auto execute the command after a reboot, just add following line under /etc/rc.local:
runuser -l myuser -c 'ssh -fNg -L 10001:127.0.0.1:3306 10.0.0.21'
|
runuser -l myuser -c 'ssh -fNg -L 10001:127.0.0.1:3306 10.0.0.21'
Now, it’s safer to have a dedicated user to perform the port forwarding process.
Setting up Supervisor
Now we have secured our MySQL connection. We need to ensure the SSH process is monitored so when it fails (or if the server rebooted) it will be automatically restarted. You can basically put the command directly inside /etc/rc.local (as shown above), which will be executed automatically upon startup, but this does not cover the worst-case scenario where the process crashes, stops or being killed. This is where Supervisor comes in handy.
Supervisor is a client/server system that allows its users to monitor and control a number of processes on UNIX-like operating systems.
1. Install Supervisor via python easy_install:
$ yum install -y python-setuptools
$ easy_install supervisor
|
$ yum install -y python-setuptools $ easy_install supervisor
2. Create a configuration file, /etc/supervisord.conf:
$ vim /etc/supervisord.conf
|
$ vim /etc/supervisord.conf
And add following lines:
[supervisord]
nodaemon=false
logfile=/var/log/supervisord.log
pidfile=/var/run/supervisord.pid
[program:mysql_secure]
command=ssh -Ng -L 10001:127.0.0.1:3306 10.0.0.21
user=myuser
|
[supervisord] nodaemon=false logfile=/var/log/supervisord.log pidfile=/var/run/supervisord.pid [program:mysql_secure] command=ssh -Ng -L 10001:127.0.0.1:3306 10.0.0.21 user=myuser
3. To start Supervisor, just run following command:
$ supervisord -c /etc/supervisord.conf
|
$ supervisord -c /etc/supervisord.conf
4. To ensure it starts on boot, we add following line into /etc/rc.local:
/usr/bin/supervisord -c /etc/supervisord.conf
|
/usr/bin/supervisord -c /etc/supervisord.conf
Here is some excerpt from /var/log/supervisord.log indicating it monitors the process correctly:
$ less /var/log/supervisord.log
2015-05-19 20:22:14,093 CRIT Supervisor running as root (no user in config file)
2015-05-19 20:22:14,100 INFO daemonizing the supervisord process
2015-05-19 20:22:14,101 INFO supervisord started with pid 1944
2015-05-19 20:22:15,105 INFO spawned: 'mysql_secure' with pid 1945
2015-05-19 20:22:16,108 INFO success: mysql_secure entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
2015-05-19 20:22:24,581 CRIT Supervisor running as root (no user in config file)
2015-05-19 20:22:24,585 INFO daemonizing the supervisord process
2015-05-19 20:22:24,585 INFO supervisord started with pid 1952
2015-05-19 20:22:25,591 INFO spawned: 'mysql_secure' with pid 1953
2015-05-19 20:22:26,801 INFO success: mysql_secure entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
|
$ less /var/log/supervisord.log 2015-05-19 20:22:14,093 CRIT Supervisor running as root (no user in config file) 2015-05-19 20:22:14,100 INFO daemonizing the supervisord process 2015-05-19 20:22:14,101 INFO supervisord started with pid 1944 2015-05-19 20:22:15,105 INFO spawned: 'mysql_secure' with pid 1945 2015-05-19 20:22:16,108 INFO success: mysql_secure entered RUNNING state, process has stayed up for > than 1 seconds (startsecs) 2015-05-19 20:22:24,581 CRIT Supervisor running as root (no user in config file) 2015-05-19 20:22:24,585 INFO daemonizing the supervisord process 2015-05-19 20:22:24,585 INFO supervisord started with pid 1952 2015-05-19 20:22:25,591 INFO spawned: 'mysql_secure' with pid 1953 2015-05-19 20:22:26,801 INFO success: mysql_secure entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
That’s all folks!