It looks as if AWS handles RDS MySQL replication by executing the same commands on the replicas that were executed on the master. However, our replicas are currently lagging by about 24 hours due to large volumes.
How does the replication process handle commands like:
create table x as select * from y where ts > curdate()
It needs to do something fairly clever, or the tables created on master and replica will be different due to curdate() having a different value at the time of execution?
Hmm. Answering my own question here a bit.
The MySQL manual at https://dev.mysql.com/doc/refman/5.7/en/replication-rbr-safe-unsafe.html has this to say:
Statements considered unsafe. Statements with the following characteristics are considered unsafe:
Statements containing system functions that may return a different value on slave. These functions include FOUND_ROWS(), GET_LOCK(), IS_FREE_LOCK(), IS_USED_LOCK(), LOAD_FILE(), MASTER_POS_WAIT(), PASSWORD(), RAND(), RELEASE_LOCK(), ROW_COUNT(), SESSION_USER(), SLEEP(), SYSDATE(), SYSTEM_USER(), USER(), UUID(), and UUID_SHORT().
Nondeterministic functions not considered unsafe. Although these functions are not deterministic, they are treated as safe for purposes of logging and replication: CONNECTION_ID(), CURDATE(), CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), CURTIME(),, LAST_INSERT_ID(), LOCALTIME(), LOCALTIMESTAMP(), NOW(), UNIX_TIMESTAMP(), UTC_DATE(), UTC_TIME(), and UTC_TIMESTAMP().
Then it says
For more information, see Section 16.4.1.15, “Replication and System Functions”.
That linked section doesn't explain specifically why curdate() would be safe, but it does explain that NOW() is safe because the binary log includes the timestamp. This means that the value as returned by the call to this function on the master is replicated to the slave . Presumably the other date-related commands work the same way. Good enough for me.
