I was at an event recently and the topic of replication stirred the curiosity of the audience. A few audience members had Master to Master but they wanted to move away from that. Others had multiple slaves but wanted little downtime and backend work if a master failed. Relayed replication or a database chain is an option to solve some of their issues. Another option would be Cluster but it depends on your infrastructure, budget and application, some of them are looking into this as well. Is a chain the best solution for everyone, of course not. While I cannot do consultant work to help them, I can blog about it…
A relayed replication environment allows you to, of course, have replicated databases but also have a replication environment that is still available if or when the master fails. The image to the left shows a simple example of how the databases work together.
One of the first things to keep in mind is that your application/environment needs to be able to handle a master switch. That means do not reference the master by IP or hostname. Instead use NIS, LDAP, alias in host files, or just DNS. Something simple as Server A == Master, then your MySQL clients and applications reference master when doing connections. This allows you to adjust the reference point quickly when a master fails. So if Server A crashed, adjust reference to Server B == Master. You still have 2 slaves running as well. You can then stop slave on Server B, examine Server A and gather any data you feel might have been lost. Sometimes starting the Slave on Server B to pull anything left over from Server A can work but auto increment ids could cause a conflict easily, so use caution when doing this. Once I am done with Server A, I move that to become a new slave of Server D, I now have four servers again.
I like to have at least four servers when doing a chain. If I only had three servers then I am very dependant on Server B, the 1st slave. If that box was to fail I am left building a slave from backups or off the master. If I have four and Server B crashes I can move the Master to Server C and still have a replicated system. Now, this of course is entirely dependant on your slaves staying up to date. Building your application to process data that will not slow down a slave can help support this type of environment.
Another nice feature coming soon with MySQL is timed delayed replication. This option can be used with a chain as well. The time delayed option pulls all data and only applies it to the db based on the threshold you set. So you could build out a live backup solution using a database chain. Server A is your slave, Server B can get time delayed set to a Day, Server C gets 6 Days (So a week back from master), Server D gets set to 24 Days back (~month from master). Now you have full access to historical data if needed. If a user drops a table that shouldn’t have, it is a lot easier to dump that table and import it rather than pulling it off a tape or disk 1st.
Do not let the slave servers go to waste either. Allow your database handles to use the all of the slaves accordingly. Keep all the reads you can off the master. Create a script/class that uses a handle for your master that uses an account with appropriate access. Then create another script/class to handle connections to the slaves. This slave connection can be created to accomplish a few things. First allow it to have a default of seconds back from master per query passed to it. Second create it to use the aliases of all the slaves and connect to check what is the seconds back, then use the valid server per threshold. This is a very fast check but the process can be enhanced to have times set on a schedule with another script, depends on your application. Now this allows your slave connection to take a query that has a threshold of 1 day (running a report for all of yesterday for example) and push that down to execute on Slave D as long as it is within the threshold.
