MYSQL Master Slave Replication over SSH tunnel

The purpose of running having a master / slave relationship between your databases is that the slave database will be an exact replica of the specified master database. The purpose of running the connection through a SSH tunnel is to ensure:

  1. Connection is encrypted
  2. MYSQL server will only connect to the localhost
  3. Minimal ports need to be opened at the firewall.

If you are unfamiliar with the SSH protocol or how to forward local ports, now is a great time to learn about it or to brush up on it from the Offical Website.

On Both Servers

On both the master and slave servers you must edit /etc/mysql/my.cnf

You must make the MASTERS'S server_id 1 and the SLAVEACCOUNT'S server_id = 2

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
server_id           = 1
log_bin             = /var/log/mysql/mysql-bin.log
log_bin_index       = /var/log/mysql/mysql-bin.log.index
relay_log           = /var/log/mysql/mysql-relay-bin
relay_log_index     = /var/log/mysql/mysql-relay-bin.index
expire_logs_days    = 10
max_binlog_size     = 100M
log_slave_updates   = 1
auto-increment-increment = 2
auto-increment-offset = 1

Restart MYSQL

service mysql stop
service mysql start

On Slave

adduser tunnelguy
usermod -s /usr/sbin/nologin tunnelguy
passwd -l tunnelguy

The purpose of changing tunnelguy's shell is to prevent the account from being used for anything else. With nologin set the account can not spawn an actual interactive shell, but still allows scp and forwarding local ports.

The last command passwd -l prevents tunnelguy from logging in with a password, but it is still allowed to authenticate via ssh keys. If you are unfamiliar with keyfiles then do a quick search online, there are a lot of tutorials showing how to create ssh keys. We will be need them in the next section.

On Master

mysql -u root -p
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
quit;
mysqldump -u root -p database > /tmp/weewx.sql
mysql -u root -p
UNLOCK TABLES;
GRANT REPLICATION SLAVE ON *.* TO 'slaveaccount'@'127.0.0.1' IDENTIFIED BY '<PASSWORD>';
quit;

There is a great package called autossh that creates a SSH tunnel that will reconnect on disconnect. This makes it great for unstable connections that will keep attempting to reconnect itself to the server.

apt-get install autossh

To make the ssh tunnel start on start up and when the network interface is up create a file in /etc/network/if-up.d/. In this case I specified the wlan0 interface.

vi /etc/network/if-up.d/autossh

/etc/network/if-up.d/autossh

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
#!/bin/bash
# Creates reverse tunnel through a remote server.
#USER=<Local Username>
#REMOTE_USER=<Remote Username>
#KEY=/home/you/.ssh/tun_rsa
#PORT=<Random High Port>
#HOST=<Address of Proxy Server>

# ssh options:
# -M: monitoring port
# -N: don't allocate a terminal
# -f: fork to background
# -q: quiet
# -R: reverse tunnel remoteport:host:localport
# -L: local port to forward
# -o: options
# -i: path to ssh key file

[ "$IFACE" = 'wlan0' ] || exit 0

su -c "autossh -M 0 -N -f -q -R 13306:127.0.0.1:3306 -L 13306:127.0.0.1:3306 -o ServerAliveInterval=60 -o ServerAliveCountMax=3 -o StrictHostKeyChecking=no -o BatchMode=yes -i /home/pi/.ssh/id_rsa tunnelguy@andrewhofmans.com"

Until you run that command in another terminal or restart the interface, it won't start an ssh tunnel.

Now that the Master is set up, we will move our database over to the slave.

scp /tmp/weewx.sql weewx@andrewhofmans.com:/tmp/weewx.sql

On Slave

Log into the MYSQL account and setup up the databases.

mysql -u root -p


CREATE DATABASE weewx;
GRANT REPLICATION SLAVE ON *.* TO 'slaveaccount'@'127.0.0.1' IDENTIFIED BY '<PASSWORD>';
quit;

Copy the Master database into the slave.

pv /tmp/weewx.sql | mysql -u root -p weewx

For *master_log_file='mysql-bin.000001', and master_log_pos=1234; ensure that you use the file and position from the Master from the beginning of the tutorial.

mysql -u root -p
CHANGE MASTER TO master_host='127.0.0.1', master_port=13306, master_user='slaveaccount', master_password='<PASSWORD>', master_log_file='mysql-bin.000001', master_log_pos=1234;
START SLAVE;
SHOW SLAVE STATUS\G
quit;

At this point the databases should be synced and everytime a entry is made into the master database it should be replicated into the slave database. Now that this is set up we will be able to set up a web application that utilizes our slave database to display our Weewx data.