Waiting until slave has been synchronized

Question

Is there any SQL (or PHP command) that can be used to either force a master to propagate changes to slaves and/or wait until the changes have been propagated to all slaves?

Answer 1

You can use the function SOURCE_POS_WAIT(). Call this function in an SQL expression on the replica, and it will block (not return) until the replica has executed up to specified log file & position.

SELECT SOURCE_POS_WAIT('mysql-bin.000042', 8675309);

See https://dev.mysql.com/doc/refman/8.0/en/replication-functions-synchronization.html#function_source-pos-wait

Before MySQL 8.0.26, use MASTER_POS_WAIT() which was the old name for this function.

Answer 2

Standard MySQL replication is asynchronous. You'd have to check the slaves yourself - possible, but there's no built-in command to do all the work for you.

MySQL 5.5 brings semisynchronous replication, which I've no experience with.

There's also synchronous replication via things like Galera. We use a Galera-based variant of MySQL, Percona XtraDB Cluster, at work.