Network Computing is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

How To Set Up SSH Encrypted MySQL Replication: Page 3 of 3

Restart both MySQL services on the master and slave. For newly created replication environments, you may need to manually copy the database to the slave. Follow the MySQL manual (section 16.1) for more information on creating data snapshots and additional replication options. When everything is set, check if replication is occurring.  Execute a  "select" query on the master and slave; the results will be identical. Perform an insert, update or delete on the master that touches a record from the "select" result.  Wait a few seconds and re-execute the "select" query. If replication is working, the data will still match.

At this point, you may want to consider setting up preshared RSA keys instead of having to type in a password.  By using keys, you can set up watchdog shell scripts to ensure that the SSH tunnel is still active, and if the tunnel fails, it will restart automatically. Also, consider creating a cron job on the master to update a table with the current unix timestamp.  The slaves can have a cron job checking for this value.  If it gets too far behind the current timestamp, replication may be broken and the admin can get an email or page.

Complications With Replication
There are two important items to remember about MySQL replication.  First, this is about disaster recovery and high availability, not backup.  Every data-altering statement executed on the master is repeated on the slave. So if you accidentally type in "DELETE FROM mytables" and forget to put a WHERE clause in there, your data is going to be lost on both systems. The second thing to remember is that you are able to create, update and delete data on the slave. I've seen developers create applications that ran on both the master and slave, for high availability, and update a replicated table, breaking the replication every time.  Logic needs to be built into applications to check if it is on an inactive slave system and not write to replicated tables. Have your developers unit test on a replicated setup.