Mysql – Setting up MySQL database replication [without restarting thesql]

Yes, schema changes replicate as any other event. MySQL slaves replicate master's binary logs into a relay log and then executes the events. Of course, this is if replicate-do-db or another variable identifies the specific scope in question to execute the statements.

There was a bug in MySQL, where if you executed alterations by specifying the table as database.table, it would not replicate. It required a use statement to proceed the queries that matched a replicate-do variable to actually execute the queries. I believe this is addressed in the current version but is still something to be aware of.

This functionality is well documented on the MySQL Web site.

The big problem with replication is checking

1. that the nodes are all up,
2. all nodes are communicating (not split brain)
3. and processing replication logs
4. and the replication lag

1, 3 and 4 can be captured usingf SHOW MASTER STATUS / SHOW SLAVE STATUS on the relevant nodes, although the replication delay only has 1 second accuracy and only across each hop. The Percona toolkit has scripts for getting more accurate replication lags.

Using multi-master replication (e.g. tungsten, Percona) saves a lot of pain but needs additional effort / software to set up.

If the network between ndoes fails, then the processes can all be running be fine - but will be unable to transfer data - you need monitoring on each node to check it can contact the upstream node.

a MySQL Master database which replicates to a few Slave databases

Best practice would be to designate one of the slaves as a master too - bi directional replication. That way you can easily switch over in the event of an outage, or for maintenance tasks such as rebuilding indexes, backups, schema changes.

Depending on the number of slave nodes, you might also want to designate a fanout node to propogate the changes.

In terms of managing escalations, scheduling scripts to collect data etc, there are lots of tools available which do this - I use nagios, so do lots of other people.