Home / server / Questions / 452079
Accepted
Jeevan Dongre
Asked: 2012-11-26 22:24:34 +0800 CST

Creating a local database instance from the RDS snapshot

  • 772

I am running a AWS RDS large instance for the production. I am taking mysqldumps everyday at mid-night. But it take too long to take the mysql dump and the website does not respond during the mysql dump process. Instead of taking the mysql dump, now I am taking DB snapshot every day.

My Question

Is there any other way to copy the DB snapshot of the dump to a local box so that I have a back up of it locally every day?

mysql

2 Answers

  1. Best Answer

    I would suggest checking out Percona XtraBackup (innobackupex). Make sure all of your data tables are of the InnoDB engine type, because MyISAM won't get you anywhere near a solution without locking all of the tables.

    XtraBackup is capable of making an online backup on a running database with very minimal impact on performance and minimal time of locking. Read the documentation before getting started.

    If you need a complete backup/snapshot without any impact you could consider a master-slave installation with these backups made on the slave machine. A bit harder and painful to set up though.

    You could also rely on InnoDB crash recovery and take an EBS snapshot. You can then mount this filesystem and have InnoDB recover with it transaction logs. However, I can't recommend this approach.

    • 1

  2. The normal way to ensure you get good backups without interfering with the operation of a MySQL database is to use a master/slave configuration and take the backups from the slave. This can even be two instances of MySQL on the one machine. In such a situation the slave takes the performance hit, not the master.

    • 0