Automatic backups of your own application: cron and mysqldump

The account already has a daily copy made by us, before dawn. A copy of your application, made by you, serves two cases: having the database and the files to hand without waiting for a restore, and having a copy outside the account. The trick is a small script that does the mysqldump and archives the files, and a scheduled task that calls it.

Putting it together

1 Keep the database password in a separate file, not in the script or on the command line. Create /home/YOURACCOUNT/.backup.cnf with the content below and give it permission 600.[client]
user=account_user
password=the-password
host=localhost
2 Create the script /home/YOURACCOUNT/scripts/backup.sh. The database and user names carry the account name in front. The last line deletes copies older than 7 days (an example: swap the number for the days you want to keep), and the backups folder sits outside public_html.#!/bin/bash
DEST=/home/YOURACCOUNT/backups
DAY=$(date +%F)
mkdir -p "$DEST"
mysqldump --defaults-extra-file=/home/YOURACCOUNT/.backup.cnf --single-transaction account_database | gzip > "$DEST/db-$DAY.sql.gz"
tar -czf "$DEST/files-$DAY.tar.gz" -C /home/YOURACCOUNT public_html
find "$DEST" -type f -mtime +7 -delete
3 Test it by hand in the Terminal: bash /home/YOURACCOUNT/scripts/backup.sh. Check that both files appeared and the sizes make sense.
4 Schedule it under Cron Jobs, at a quiet hour: /bin/bash /home/YOURACCOUNT/scripts/backup.sh >> /home/YOURACCOUNT/scripts/backup.log 2>&1. The time is the server’s. See cron jobs.
5 Take it out of the account. Download the files over SFTP, or let your own computer fetch them at whatever time you like. See making and keeping your own backup.
6 Test restoring. For the database: gunzip < db-DAY.sql.gz | mysql -u user -p database_name. More in backing up and restoring with mysqldump.
Watch out for Why
Outside public_html A copy in there can be downloaded by anyone who guesses the name.
Password in the .cnf file It stays out of the command history and the script, and with permission 600 only your account reads it.
Few local copies They count against the account’s space and its number of files. See the limits.
A quiet hour A large backup uses the account’s processor and disk while it runs.
A copy that sits in the same account does not protect you from losing the account. It is for speed; to be safe, there has to be a copy somewhere else. And a copy that was never restored can be empty without anyone noticing.
Mind the % in cron. In a cron command, the % character must be written \%. That is why the date stays inside the script, not on the cron line.
Using PostgreSQL? pg_dump plays mysqldump’s part. It is explained in backing up with mysqldump and pg_dump. And what JetBackup keeps, and for how long, is in how long we keep backups.

The job runs, but the log shows an error you cannot make sense of? Send us the cron line and the text of the log.

Open a support ticket

SEE ALSO

Backing up with mysqldump and pg_dump, and restoring the copy

Cron jobs: what they are for and how to create one

Making and keeping your own backup, and testing that it works

How long we keep backups, and how to restore one

RECOMMENDED PRODUCT

Web hosting with cPanel

Domain and SSL included, daily backups and the panel you already know. from $6.60/mo (3-year plan, with coupon)

See plans
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

How to migrate a site without downtime Changing hosting provider does not have to take your site offline. The trick is simple: set everything up here before po...
Internal migration: moving sites between plans Changing plan inside MozOut - from Hospedagem Pro to Plus, say - is far simpler than migrating from another provider. In...
How to generate zip and sql files to migrate a WordPress site A WordPress site is always two halves: the files (theme, plugins, images) and the database (posts, pages, settings). To...
How to restore your data with JetBackup Every account here is backed up automatically, once a day, in the early hours of the morning - the last 30 daily backups...
Making and keeping your own backup, and testing that it works Restoring a copy of ours is covered in how to restore your data with JetBackup. This article is about the other half, an...
The 3-2-1 backup rule for a website: which three copies, and where to keep them The 3-2-1 rule says: 3 copies of what matters, on 2 different kinds of storage, with 1 copy away from where the rest liv...