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.
- 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.
Please sign in to leave a comment.