Compsoc MySQL Deployment
Overview
We have two MySQL servers that are set up using asynchronous "Master-Master" replication, this should mean that we don't need to mess with the replication configuration should we lose a server for a period of time.
Normal operation
- Both Codd and backus are up.
- Writes on Codd are are replayed on Backus.
- Writes on Backus are replayed on Codd.
- Applications may use one or the other, but not both (Unless they are written with the synchronisation issues in mind)
Backus Unavailable
- Applications continue using Codd as normal.
- Any applications normally using Backus can be reconfigured to use Codd.
- When Backus comes back replication can be restarted and it will sync new data.
Codd Unavailable
- Applications normally using Codd database may be configured to use Backus instead.
- Any applications normally using Backus will be unaffected
- When Codd comes back replication can be restarted and it will sync new data.
Caveats
Since we are using asynchronous replication applications must use either one database server or the other. They may not use both.
This means:
- We cannot simply use a round-robin "mysql.uwcs.co.uk" to distribute requests and ensure they will still work after a server failure.
- We cannot simply copy web applications onto backus and think they will work because they are pointing to database on localhost.
If applications use both database servers simultaneously they might get undefined behaviour when making a change on one server and immediately reading from the other.
Example: Member A signs up for a LAN using a website on Backus, gets assigned place 55/55. Member B signs up for a LAN using website on Codd at the same time and also gets assigned place 55/55. Replication conflict occurs and argument over who gets the last LAN place ensues.
At present this means that we have to manually reconfigure applications when we lose one server to continue to work. This is solvable, see the Future section.
Technical Implementation
Read the references at the bottom of the page as well as this section before attempting configuration.
Codd and Backus both have a MySQL server listening on the standard port (3306).
Give each server a different Server-id (in my.cnf - 1 and 2 will do).
Ensure binary-logging is enabled on each server (in my.cnf - on by default)
mysql table containing user permissions, and server settings was synced between the databases *before* replication setup. This is important to avoid
- a) permission conflicts b) duplicate conflicts
when importing the data.
Codd and Backus both have a replication user created with permission to replicate:
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'137.205.37.130' IDENTIFIED BY '<insert password here>'; -- Granted on Codd allowing replication from backus' IP GRANT REPLICATION SLAVE ON *.* TO 'repl'@'137.205.210.240' IDENTIFIED BY '<insert password here>'; -- Granted on Backus allowing replication from codd's IP
It is necessary to flush permissions after granting these. NB, do not do this unless you have a valid user account currently set up to log back in with (you might have deleted them when importing mysql system database)
FLUSH PRIVILEGES
Obtain binary log details on master:
SHOW MASTER STATUS +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000878 | 12958412 | | | +------------------+----------+--------------+------------------+
Note the value from the File and Position column and set them up on the Slave:
CHANGE MASTER TO MASTER_HOST='codd.uwcs.co.uk', MASTER_USER='repl', MASTER_PASSWORD='master_pass', MASTER_LOG_FILE='mysql-bin.000878', MASTER_LOG_POS=12958412
Start replication
START SLAVE
Monitor replication:
SHOW SLAVE STATUS SHOW PROCESSLIST
Import data into master (will be replicated to slave)
mysql -u root -p < ./data_dump.sql
Set up the reverse replication - repeat steps from SHOW MASTER STATUS to START SLAVE using the opposite MASTER/SLAVE instances. i.e. s/codd/backus/
Disaster Recovery
If one of the machines is lost and replaced with a new server the data must be copied to the new server prior to restarting replication.
First obtain a read lock on the working database to block new commits
FLUSH TABLES WITH READ LOCK
Dump all data with mysqldump (or use backup script)
Import data into new slave with mysql -u root -p < ./data_dump.sql
Configure the replication following the steps above.
Free the read lock:
UNLOCK TABLES
If someone drops a database, or both databases are lost. Restore from nightly backups.
FIXME: Insert details of nightly backups.
Future Plans
- Look at removing the need for reconfiguration when we lose a server. Options include:
- mysql1.uwcs.co.uk, mysql2.uwcs.co.uk DNS entries which can be reconfigured when a server is lost. This is dependant on solving the "loss of DNS master" problem.
- Virtual IPs, might be possible with help from ITS.
- Back MySQL onto LDAP to help ensure secure passwords, and single password ease of use for members.
- Notification of replication failure
- Modify application deployment to take advantage of multiple servers
- Look at mysqlmanager for managing the multiple mysql instances.
Useful References
MySQL manual - Replication setup Howto
Guide to setting up two-way MySQL replication on live servers