MySQL: Does Amazon’s RDS Multi-AZ feature use binary logs

There are 3 methods that MySQL can use to write to the binary logs:

1. STATEMENT

   This means that every SQL statement on the master is recorded in the log, and executed on the slave. This can cause problems if the SQL statement contains statements such as "NOW()", "RAND()" and anything non-deterministic. This also requires support from the storage engine in use.

2. ROW

   This means that every row that is changed by a statement is individually recorded in the binary log. This gives larger binary logs (generally) than statement based logging, but is almost always guaranteed to give the exact replication needed.

3. MIXED

   This allows MySQL to choose between binary and row based logging as required.

If you're getting this error, one suggestion is to change the BINLOG_FORMAT variable to MIXED. This allows MySQL to automatically switch between ROW and STATEMENT based logging as required.

This doesn't appear to be anything intrinsic to MySQL Server, so I can't give you a proper citation on what "causes" this to happen -- but I strongly suspect it is an element of the design of RDS.

If you take a look at SHOW FULL PROCESSLIST; you'll notice there's always a user connected called "rdsadmin." If you look in the mysql schema, you'll spot a table called rds_heartbeat2, with a recent epoch time x 1000 stored in its single row. This is changed every few minutes, and my reasoned speculation is that that "rdsadmin" is doing this.

I selected a binlog at random to check this theory, and here's the first event:

use `mysql`; INSERT INTO mysql.rds_heartbeat2(id, value) values (1,1393029075007) 
ON DUPLICATE KEY UPDATE value = 1393029075007

I'll speculate, then, that the "rdsadmin" user is flushing the binlog, then immediately writing a new value to this table. Reading the value from this table on the slaves would give the RDS supervisory systems a mechanism for determining/monitoring the slave's behavior, allowing it to purge binlogs that it knows have been fully processed or let them linger on the master if they haven't, so that a slave would not be caught without the necessary logs being available.

Instead of letting MySQL age out it binlogs on its own based on the global varible expire_logs_days, RDS seems to be managing this process externally, and quite possibly this process is responsible for archiving the logs out of sight so that they can be used for the native RDS point-in-time restoration feature.