I plan to use MySQL setup with one master server and several slave servers. I would perform regular consistent backup of the data EBS on one of the slave servers (I will always stop database before and restart it when it is finished).
Slaves failover
I one of the slave fails, I will start new instance and moun EBS based on the latest slave snapshot as the new data disk. In this situation master is available all the time. So this is what I think I should do:
0) make sure server uses unique server_id
1) SLAVE STOP;
2) RESET SLAVE;
3) START SLAVE;
4) restart mysql?
Is that enough?
Master failover For simplicity and consistency reasons I don't want to promote one of the slaves to master. I prefer to create new master from scratch. The problem is that the snapshot is from the slave and I want to use it as a master. This is what I think I should do after creating new instance with the disk based on the slave snapshot:
1) STOP SLAVE;
2) RESET MASTER;
3) restart mysql
4) associate elastic IP to the new master db (same IP was used by failed master), so the slaves will be able to connect again.
Is this procedure correct? What should I do with the slaves now? Slaves can be ahead of the master, because master was restored from snapshot that can be several hours old. Is this problem? There is also possibility to recreate all the slaves from the same snapshot which was used to recover master. Then they would be on the same position as the master. But this could be complicated and impossible to proceed without downtime. Is there any better way how to do it?
Thank you very much in advance!
Notes: I don't mind lost transactions since the latest snapshot. I am using Mysql 5.1.41.
