How to enable binary logging on an Amazon RDS read replica

Posted in: Cloud, MySQL, Technical Track

One of the more common struggles I’ve had to assist with in regard to Amazon RDS is enabling binary logging on read replicas, or forming multi-tier replication in instances using version 5.6 or later after seeing that multi-tier replication is not supported in version 5.5 (for a reason that will become clear by the end of this post.)

First off, let’s have a look at the topology that I have in place in my AWS account. As you’ll see below I have a master, blog1, and a read replica that I created via the AWS console called blog2. You’ll also notice that, despite being supported, if I select instance actions while having blog2 highlighted the option to create a read replica is grayed out.

Further, if we use the MySQL CLI to connect to blog2 and check the global variables for log_bin and binlog_format, you’ll see that binary logging is off and binlog_format is set to statement. This is strange considering that the parameter group that’s assigned to blog2 is the same as blog1 where binary logging is enabled and the format is set to mixed. In fact, when you try to change the binary logging format in the MySQL RDS parameter group you’ll see that statement isn’t even an option.

mysql> show global variables like 'log_bin'; show global variables like 'binlog_format';
| Variable_name | Value |
| log_bin | OFF |
1 row in set (0.06 sec)

| Variable_name | Value |
| binlog_format | STATEMENT |
1 row in set (0.05 sec)

So what gives? The status of the variables in our instance clearly don’t reflect the intended configuration. The answer here is to enable automatic backups for blog2. When you first create a read replica in RDS you won’t have the option to enable automatic backups and, much like this situation, you’ll have to go back and modify the read replica instance after its creation to enable automatic backups. The supporting documentation for this can be found here. The appropriate text is as follows:

When creating a Read Replica, there are a few things to consider. First, you must enable automatic backups on the source DB instance by setting the backup retention period to a value other than 0. This requirement also applies to a Read Replica that is the source DB instance for another Read Replica. For MySQL DB instances, automatic backups are supported only for Read Replicas running MySQL 5.6 and later, but not for MySQL versions 5.5. To enable automatic backups on an Amazon RDS MySQL version 5.6 and later Read Replica, first create the Read Replica, then modify the Read Replica to enable automatic backups.

After you enable automatic backups by modifying your read replica instance to have a backup retention period greater than 0 days, you’ll find that the log_bin and binlog_format will align itself with the configuration specified in your parameter group dynamically and will not require the RDS instance to be restarted. You will also be able to create a read replica from your read replica instance with no further modification requirements.


To enable binary logging on an AWS RDS Read Replica, you must set backup retention period to something greater than 0 days. I like to explain this to people by saying that they need to look at this from the Database-as-a-Service (DBaaS) perspective. If you have a DBaaS instance that has no backup recovery requirements and has no read replicas, there is no reason for the cloud provider to waste clock cycles and storage on binary logs that won’t be used. By enabling the backup requirement, you’ve forced a position where binary logs are justified.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Peter Sylvester is one of the Internal Principal Consultants in the Open Source Database Consulting Group at Pythian. He has been with Pythian since January of 2015 and has been working with MySQL since 2008. Apart from work, Peter is an avid ice hockey player to stay in keeping with the stereotypical Canadian lifestyle, playing typically no less than twice a week!

6 Comments. Leave new

This post literally saved me a ton of time…..I was wondering why MySQL RDS slave does not show any binary logs. After reading this post, I set up the backup policy on RDS slave and then I was able to successfully spin off an external slave feeding off of MySQL RDS slave. Thank you Peter Sylvester !!

Peter Sylvester
March 9, 2018 9:48 am

You’re very welcome. Thanks for reading!


Same here, your post saved us a decent chunk of time. It is strange that it isn’t easier to find mentioned in the AWS documentations that the back retention period enables the binary logs on the replica. It sort of make sense but isn’t obvious enough IMO.


It seems the described behavior has changed. At least for MySQL 8.0.

When creating a ReadReplica, binary logging seems enabled by default. Since no other ReadReplica is consuming these binary logs and the “binlog retention hours” setting is NULL by default, they will keep piling up consuming storage space.

For me this was kind of a surprise: why would a ReadReplica have binary logs enabled by default? On the other hand: now the parameter groups options ARE respected properly :)


Thanks from me too :-)

(trying to replicate from AWS RDS -> Azure MySQL)



I also have problem in using the read replica as the source for replication. The backup retention is already set to 7 days. The binlog_format is set to ROW, but the log_bin variable remains OFF. I read somewhere that if I change the binlog_format to ROW the log_bin should automatically set to ON, but that is not the case. I’ve already rebooted the instance several times. I even tried to set the binlog_format cluster parameter to OFF, rebooted the read replica but the binlog_format on the instance remains at ROW and the log_bin at OFF. On the other hand changes on other cluster parameters like binlog_checksum reflects on the reader instance after it is rebooted. What else could I be missing?


Leave a Reply

Your email address will not be published. Required fields are marked *