Recommended topology for MySQL Replication

A recommended MySQL Replication topology would be constructed so that it takes advantage of the strengths of Replication, and minimizes the weaknesses of the protocol.

Some recommendations:

-       Replication only flows in one direction, applications only write on the master

-       The master pushes changes to a backup master and to one or more slaves

-       Use semi-synchronous replication (in MySQL 5.5) between master and backup master

• Master sends update to backup master and waits with transaction commit
• Backup master gets update, writes to its relay log and flushes to disk. Backup master then acknowledges receipt of the transaction to the master.
• Master proceeds with transaction commit.
• Semi-sync replication has a performance impact, but the risk for data loss is minimized

-       Have only the replication process make changes on the slaves, so as to minimize the risk of data conflicts on the slave.

• Slaves should therefore be started in read-only mode
• Applications will not be able to modify data directly on the slaves, but the Replication process will still function on a read-only server

-       Replication sends larger packets between the servers, the max allowed packet set to a high value so as to avoid replication errors.

-       Binary logs need to be available to bring a new slave up to date

• Provisioning of a new slave requires a combination of the last backup, and all transactions that happened after the backup

-       Replication connection parameters should not be placed in the my.cnf file. For instance, a slave may have its configuration file overwritten, and not know from what point to continue the replication.