My goal is to be able to backup a large database on a fairly popular Magento site without causing performance problems to the live site. Data integrity is also important to me. Hoping that somebody has some pro tips for me.
Details of my current situation
- Current database size is approx. 5GB when the import/export tables, log tables, etc are cleared
- We are setup with a dedicated box for our mysql instance. But only one mysql instance, not a master/slave setup.
- We typically use a command like this to take our backup
mysqldump -u $USERNAME -h $HOSTNAME -p $DB > $FILENAME
. Sometimes we also use the--opt --skip-lock-tables
option. - When the db was smaller 2 years ago this wasn't a problem since it ran fast, but now that the db is large and the dump takes quite some time to finish, users experience slowness and downtime during the backup window.
What can I do to make my database backups better, faster, less impactful on the website users while still maintaining a good, usable backup?