// Tutorial //

How To Secure MySQL Replication Using SSH on a VPS

Published on September 18, 2013
Default avatar
By Jesin A
Developer and author at DigitalOcean.
How To Secure MySQL Replication Using SSH on a VPS

All commands in red must be replaced with actual values used in your environment.

Prelude

Configuring MySQL replication leaves port 3306 open to the Internet and data between the replication servers is not encrypted. Using SSH tunneling MySQL replication data can be transferred through the SSH connection. This method doesn't need any additional ports to be opened in the firewall. For this article:

  • The master IP is 1.1.1.1
  • The slave IP is 2.2.2.2

Commands to be entered in the master are marked (master) and commands for slave are marked (slave).

It is assumed that you've read this article on MySQL replication.

Step 1: Setup SSH Tunneling

Create a user and assign a password. This user will be used to create the SSH tunnel: (master)

root@mysql-master:~# useradd -d /home/tunneluser -m tunneluser
root@mysql-master:~# passwd tunneluser

The tunneluser must be allowed to connect only from the slave server so it must be entered in the /etc/ssh/sshd_config file as an allowed user. (master)

root@mysql-master:~# nano /etc/ssh/sshd_config

Since allowed users are being defined, users not in this list will be denied access, so add the administrative users who will use SSH to login to this VPS. (master)

AllowUsers root alice bob tunneluser@2.2.2.2

Restart the SSH server: (master)

root@mysql-master:~# service ssh restart

The steps to generate a SSH key are mentioned in this article but I'll repeat the commands here. (slave)

[root@mysql-slave ~]# ssh-keygen

Sample output:

[root@mysql-slave ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
5d:db:9c:50:e8:b2:88:18:de:78:5f:ed:83:14:47:d7 root@mysql-slave
The key's randomart image is:
+--[ RSA 2048]----+
|             ... |
|            o.. E|
|           oo.   |
|    .    .o.o= . |
|   . = .S..*. +  |
|    + + . + .    |
|     . . o o     |
|        . . o    |
|             .   |
+-----------------+

Do NOT enter a passphrase because this key will be used to automatically establish a SSH tunnel which will not be possible if it contains a passphrase. Copy the public key to the master server. (slave)

[root@mysql-slave ~]# ssh-copy-id tunneluser@1.1.1.1

Sample output:

[root@mysql-slave ~]# ssh-copy-id tunneluser@1.1.1.1
The authenticity of host '1.1.1.1 (1.1.1.1)' can't be established.
RSA key fingerprint is 3f:33:0c:73:bd:da:51:b9:45:2e:d7:2e:00:47:33:17.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '1.1.1.1' (RSA) to the list of known hosts.
tunneluser@1.1.1.1's password:
Now try logging into the machine, with "ssh 'tunneluser@1.1.1.1'", and check in:

  ~/.ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

The tunneluser user will be used only for tunneling and not for administration so change the shell to nologin and remove the password. (master)

usermod -s /sbin/nologin tunneluser
passwd -d tunneluser

Debian and Ubuntu users replace /sbin/nologin with /usr/sbin/nologin.

Create a SSH tunnel using the following command. (slave)

ssh -L 33061:localhost:3306 tunneluser@1.1.1.1 -f -N

The tunnel has been created, so accessing port 33061 at localhost will connect the slave to the master via SSH. The -f parameter runs this command in the background and the -N parameter means "Do not execute a command" since the tunneluser has a nologin shell.

Step 2: MySQL Configuration Changes

This section mentions only the changes in the steps outlined in this article on MySQL replication.All things to be changed are mentioned in orange. If the my.cnf file was edited to listen on the public IP address change it back to localhost.

(master) and (slave)
root@mysql-master:~# nano /etc/mysql/my.cnf

The following line:

bind-address	= 1.1.1.1

will be changed to the localhost IP address:

bind-address	= 127.0.0.1

Change the permissions of slave_user to login only from localhost. (master)

root@mysql-master:~# mysql -u root -p
mysql>GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' IDENTIFIED BY 'password';

The CHANGE MASTER query entered previously pointed to the public IP address of the Master server and didn't specify a port number. The following query will change that. (slave)

root@mysql-slave:~# mysql -u root -p
mysql>STOP SLAVE;
mysql>CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_PORT=33061, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;
mysql>START SLAVE;

Connectivity can be tested from the slave using the following command: (slave)

mysql -h 127.0.0.1 -u slave_user -P 33061 -p

Please do not use localhost with the -h parameter as MySQL will login locally with the default port number.

Submitted By:

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.

Sign up
About the authors
Default avatar
Jesin A

author

Developer and author at DigitalOcean.

Still looking for an answer?

Was this helpful?
4 Comments

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Wouldn’t it be preferable to install autossh for MySQL Replication? If the ssh tunnel dies, it won’t restart automatically, but with autossh it will start again. My 2 cents.

I followed the tutorial carefully but at the last stage when trying to test mysql connection to master, on my slave I get ERROR 1045 (28000): Access denied for user 'slave_user'@'localhost' (using password: YES). I copy pasted the correct password.

On the master it shows:

mysql> show grants for 'slave_user'@'localhost'; +----------------------------------------------------------------------+ | Grants for slave_user@localhost | +----------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'localhost' | +----------------------------------------------------------------------+ 1 row in set (0.00 sec)

I believe the ssh connection is setup correctly, if I ssh into the master from the slave it connects and then says Connection to <ip address> closed. It’s closed because nologin and password removed I assume.

Why can’t I connect to the master through mysql? The ssh tunnel appears to be working from the slave to the master.

Getting error

error connecting to master ‘slave_user@127.0.0.1:3306’ - retry-time: 60 maximum-retries: 86400 message: Access denied for user ‘slave_user’@‘localhost’

I am trying to follow your tutorial but I got the following :

ssh -L 33061:localhost:3306 tunneluser@1.1.1.1 -f -N

bind: Address already in use channel_setup_fwd_listener_tcpip: cannot listen to port: 33061 Could not request local forwarding.

Also, I don’t understand that since you added “tunneluser@2.2.2.2” in Allowusers, how can one login to user tunneluser on 1.1.1.1 ???