1

I see that the built in backup scheduler can do daily and longer, but how about hourly? If there's not a way to set it in the GUI, can I make a call (from cron) to a Magento utility that will do the database backup for me?

I'm trying to avoid scripting my own MySQL database backups. I'd like to leverage as much built in Magento functionality as possible to avoid reinventing the wheel.

2
  • 1
    I would strongly suggest not doing hourly backups. Once you start getting into high volume and the db builds size, you will start experiencing issues (especially in checkout). However if you had to do it, I would suggest doing a custom script. This will almost always be faster then the built in Magento db backup, which will result in fewer locked queries.
    – kab8609
    Oct 8, 2014 at 20:24
  • 1
    Don't use Magento to do it would be the first recommendation. Its GUI overhead and oddball method of creating the database dump are highly inefficient. For small unloaded websites, use mysqldump, for busy sites, start thinking replication. Oct 9, 2014 at 2:29

3 Answers 3

2

I'm the first to advocate doing something native to Magento if it can do it, but this is an instance where native is not best.

The built in backup facility in Magento should be avoided at all costs. It pays no heed to table level locks, and will almost certainly bring your store down during its painstakingly long process.

MySQL backups should be carried out via MySQL itself, not via a PHP library.

For your daily backups, mysqldump is perfect. There's a wrapper for this called automysqlbackup - which is well tested and reliable. This would be a good fit for you.

Hourly backups, whilst certainly desirable to lower recovery point objective, will come with inherent problems. Percona make an excellent tool for taking rapid backups, point in time snapshots and incremental binlog snapshots. The package is XtraBackup. It's free but very complex to configure. It snapshots huge databases very quickly, but at the penalty of the output file being an unprocessed format (not an easily restorable .sql file).

Some run MySQL slaves for the sole propose of executing backups, but again, this is not without it's challenges. I would strongly advise attempting to do this, this risk of data loss, or performance bottlenecking through misconfiguration is far too high.

Realistically, backups are the responsibility of your hosting provider (not a store owner/developer) - it would be advised to leave it to them, because a misconfigured backup script will cause you two very real problems,

  1. Severe performance and reliability issues
  2. Potential that your backup is improperly created and unusable as a result

If you want a starter script, n98 MageRun (https://github.com/netz98/n98-magerun) has a nice dump facility in it, and we've wrote a DB dump/restore script (see http://s.onas.si/qnwl)

2
  • We're a unique case, with the store owner, development team, and hosting provider being under the same roof and even sharing some personnel. While we have lots of experience with Microsoft SQL server (as well as some with Oracle and PostGreSQL), we're new to MySQL. :-) Oct 9, 2014 at 14:03
  • Your site s.onas.si/qnwl is offline, can you please add your script here?
    – Black
    May 9, 2019 at 7:57
2

I agree with the previous post, out of the box is best in this case.

the below will do a backup with minimal performance impact (that's what --single-transaction) is for and will automatically clean up backups older than 10 days... However, it does NOT backup the logs, otherwise your backup becomes unwieldy.

It also creates TWO files: one for the DB structure, one for the data.

all you need is to save this file as a .sh and add it to your cron schedule like so:

0 */1 * * * /path/backup.sh

and here's the code:

cd /path/backuplocation/
RIGHTNOW=`date +'%F_%H%M'`

mysqldump --single-transaction -u user -h localhost database -ppassword --no-data > sqlbackup_structure_$RIGHTNOW.sql
#mysqldump --single-transaction -u user -h localhost database -ppassword > sqlbackup_$RIGHTNOW.sql

mysqldump --single-transaction -u user -h localhost database -ppassword --ignore-table=sales_flat_quote_item  --ignore-table=report_event  --ignore-table=log_url --ignore-table=log_url_info --ignore-table=log_visitor --ignore-table=log_visitor_info > sqlbackup_$RIGHTNOW.sql

gzip  sqlbackup*_$RIGHTNOW*.sql

find . -name "sqlbackup*.gz" -mtime +10 -exec rm {} \;
0

Great Provence shop thank you for your comment, but to be fair this line

mysqldump --single-transaction -u user -h localhost database -ppassword --ignore-table=sales_flat_quote_item --ignore-table=report_event --ignore-table=log_url --ignore-table=log_url_info --ignore-table=log_visitor --ignore-table=log_visitor_info > sqlbackup_$RIGHTNOW.sql

saves the structure too, I don't see the value in saving 2 different files

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.