More DBA job interview questions and answers at
(Continued from previous question...)
MySQL - How can I use replication to provide redundancy/high availability?
With the currently available features, you would have to set up a master and a slave (or several slaves), and write a
script that will monitor the master to see if it is up, and instruct your applications and the slaves of the master change
in case of failure. Some suggestions:
To tell a slave to change the master use the CHANGE MASTER TO command.
A good way to keep your applications informed where the master is by having a dynamic DNS entry for the master. With bind
you can use nsupdate to dynamically update your DNS.
You should run your slaves with the log-bin option and without log-slave-updates. This way the slave will be ready to become
a master as soon as you issue STOP SLAVE; RESET MASTER, and CHANGE MASTER TO on the other slaves. It will also help you
catch spurious updates that may happen because of misconfiguration of the slave (ideally, you want to configure access
rights so that no client can update the slave, except for the slave thread) combined with the bugs in your client programs
(they should never update the slave directly).
We are currently working on intergrating an automatic master election system into MySQL, but until it is ready, you will
have to create your own monitoring tools.
(Continued on next question...)