We have a production database that generates daily database backups that are uploaded to S3 in form of compressed .sql.bz2 files. We basically have a S3 bucket that has a large number of such backups, one per day.
We use these DB backups for debugging and troubleshooting production issues since our log files don't give us the complete picture. Over time, this becomes a fairly tedious process, especially as the cost (time + bandwidth) of downloading, unzipping, and restoring the database takes much longer than the actual time spent running queries against the database.
I was wondering if there is a way to make the backups easily queryable and avoid the download + unzip + restore process - this will greatly speed up our troubleshooting process. Would recommend any suggestions within the AWS infrastructure or outside as well.
Database: MySQL Server 5.7
Backup format: .sql.bz2
Stored on: AWS S3 bucket
DB Host: RDS