Is it possible to fetch AWS RDS MySQL transaction logs?

Question

I have AWS RDS MySQL instance, without general query logs. Is it possible to get all transaction logs? I understand that MySQL binlog retention time is very short, but obviously, AWS RDS keeps all transactions for providing point-in-time recovery. So - is/how it possible to fetch this log?

Answer 1

The point-in-time recovery also uses the binary log. To use them for PITR, you must have non-expired binary logs back to the most recent full backup.

You can use SHOW BINARY LOGS in a query client to view the currently non-expired binary logs. You can use the command-line tool mysqlbinlog to dump logs from a remote instance (i.e. you could run this on an EC2 instance or another server that was set up to be a client for your RDS instance).

The term "transaction log" in MySQL usually refers to the InnoDB redo log. This is a different log. There's no way to fetch this from a remote client, and there's no tool to use it for PITR anyway.

Fetching the binary logs yourself might not be the solution intended by AWS for PITR of RDS instances. You should review the information they have about backup solutions: https://aws.amazon.com/blogs/storage/point-in-time-recovery-and-continuous-backup-for-amazon-rds-with-aws-backup/

Answer 2

Run this

SHOW VARIABLES LIKE '%expire_logs%';

Then see if AWS lets you change any of them.

That will keep the binlogs around for longer.

Also, see if AWS has a different setting to allow for a longer "PITR" (Point In Time Recovery).

Since you have configured to keep 30 days' worth of binlogs (and assuming you can access them), the commandline mysqlbinlog with let you read those files and grep for the data you desire. Good luck.