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:
- Connection is encrypted
- MYSQL server will only connect to the localhost
- 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
service mysql stop service mysql start
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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
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 firstname.lastname@example.org:/tmp/weewx.sql
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.