I have to replicate different MySQL databases from multiple servers right into a single slave server. How is this done? Will I require a separate MySQL instance around the slave for every master server? Or it is possible to method to define multiple master hosts?
We are essentially while using slave like a hot backup for multiple websites. Must we be considering clustering rather than replication?
The easy way achieve that might be a genuine backup solution... however when you get it done how you describe define one slave instance per master - by doing this you remain flexible, for instance if any change is required you can even move a number of from the slave instances to a different machine with no influence alternatively slaves/masters...
EDIT - according to comments:
For any description regarding how to setup multiple cases of MySQL on a single machine see for instance
- Multiple MySQL instances on a single machine
This prevents you flexible enough to possess different MySQL versions in parallel (identical per slave/master combination)...
You'll have to use multiple cases of mysql. If you're getting 6 masters and you're simply attempting to invest the slaves on a single physical machine, you'll need 6 instances,
- Each mysql slave instance will connect to another master.
- Each slave instance is going to be on the different port
- The datadir for every slave instance may also be separate.
Presuming you're with a couple flavour of unix OS, you can setup a cron job to prevent and begin each instance to help keep the burden average low.
It might be good to allow one slave instance run and meet up with its master before doing hot backup. Exactly the same steps would affect next slave and so forth. Every time you launch a slave instance you shutdown another mysql slave instances could be shutdown to help keep load avg. low.