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

  • 1
    Modify your backup script to 1. Take a backup of the running database 2. Create a new blank database with the last nights date 3. Restore the backup that you just took in step 1 to the new database you created in step 2 4. Delete any databases that are more than 10 or 20 or 30 days old(You decide) – termcap Jan 17 '18 at 7:40
  • 1
    Update your question with details on the database software. – John Hanley Jan 17 '18 at 7:50
  • I am not aware of any software that can query a compressed database backup. Think thru what you are asking - to query your database will required reading and uncompressing the file stored on S3. Depending on your database software, you may be able to use snapshots. – John Hanley Jan 17 '18 at 7:56
  • @termcap I thought about this - it is the most obvious solution but it requires that there be a persistent DB server. I was wondering if there were on-demand services similar to say Amazon Athena that works off .sql files? – rtindru Jan 17 '18 at 8:34
  • 2
    I doubt you can query the sql files without an engine involved to actually run the queries. – termcap Jan 17 '18 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.