7

Context

I am trying to enable BINARY LOGs on a MySQL RDS instance.

Issue

I used CALL mysql.rds_set_configuration('binlog retention hours', 24) without encountering an error.

And using CALL mysql.rds_show_configuration says "binlog retention hours" value is 24

But SHOW BINARY LOGS says "#1381 - You are not using binary logging"

Am I missing something ?

Additional information :

  • using SHOW GRANTS results in GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP...
  • I tried rebooting the instance. It didn't work.
  • automatic backups are enabled with a 1 day retention

useful documents

2
  • what was the edit @RolandoMySQLDBA ?
    – Hugo
    Sep 6, 2018 at 9:11
  • The edit was just adding the mysql tag. Sep 6, 2018 at 13:48

2 Answers 2

6

Amazon will not allow the user to enable binlogs.

You have to enable automatic backups on the RDS Instance. The RDS framework will then enable binary logs for you. You cannot enable them manually. Please see this post in the AWS forums (which states the reverse, setting backup retention to 0 disables binary logging).

The default backup retention is 7 days. I recommend setting

mysql> CALL mysql.rds_set_configuration('binlog retention hours', 168)

so as to match the back retention

4
  • Thank you. I tried the 168 hours retention, sadly it didn't work. Automatic backups were already enabled on the instance. Any other ideas ?
    – Hugo
    Sep 6, 2018 at 8:00
  • Please connect to MySQL and run SHOW GRANTS(); and post the output in the question. Sep 6, 2018 at 13:47
  • Done. Also I just found this document docs.aws.amazon.com/dms/latest/userguide/… I will follow it and update the question with results
    – Hugo
    Sep 6, 2018 at 15:16
  • 1
    I am struggling with same issue. Enabled them in parameter group but nothings works Apr 22, 2021 at 20:17
3

I found this blog post that answered the question: https://blog.pythian.com/enabling-binary-logging-rds-read-replica/

Essentially, if you make sure your "Backup retention period" for your RDS instance is set to > 0 days, this will enable binary logs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.