Clustering / Hot Backup Solution

[Ben Sewell]

Just thinking up some project ideas and thought i'd think out loud and put them onto this forum.

We have a number of MySQL applications running and all have their different ways of backing up usually a shutdown of the database. Copying the relevant files and restarting the MySQL service or just snapshotting the machine as their virtual. What i was thinking of was to have a central VmWare based MySQL server that would act as a kind of hot swap / fault tolerance server that would communicate with all the other MySQL servers that were connected to it acting a bit like a data hub.

Then when the backups on the servers were to take place using tools like MySQL dump or other tools then as the database has two nodes, one would be down for backup then the other server would take the load from the primary server until the backup was complete then when that was finished the primary server would come back up online, sync its data and then fully use this server instead of the fail back server.

Is this kind of thing possible with the MySQL community edition? We have lots of systems and are thinking of converting some to MySQl.