Backup document root and MySQL in Linux

Question

I wrote this program to backup everything in document root and the MySQL database in Linux environments. It is aimed for Debian/Ubuntu environments to say the least.

zip -r /var/www/html/html-$(date +\%F-\%T).zip /var/www/html -x "*/cache/*"
mysqldump -u root -p --all-databases > /var/www/html/db-$(date +\%F-\%T).sql
zip /var/www/html/db-$(date +\%F-\%T).zip /var/www/html/db-*.sql
rm /var/www/html/db-*.sql
zip -r /var/www/html/html-$(date +\%F-\%T).zip /db-$(date +\%F-\%T).zip /var/www/html/all_zipped.zip
rm -rf db-$(date +\%F-\%T).zip html-$(date +\%F-\%T).zip

I make the assumption that the document root is under /var/www/html/. In the past it was /var/www/ but I don't think the change is going to be reverted or changed in the next 10 years...

• hjpotter92: Why do you do zip -r /var/www/html/html-$(date +\%F-\%T).zip /db-$(date +\%F-\%T).zip /var/www/html/all_zipped.zip?
• Arcticooling: This is to zip both the Dir zip and sql file inside one zip.
• hjpotter92: What is all_zipped?
• Arcticooling: All zipped is the file that includes the Dir zip and the sql zip.
• hjpotter92: Shouldn't it be the first argument of zip command?

Answer 1

zip -r /var/www/html/html-$(date +\%F-\%T).zip /var/www/html -x "*/cache/*"

You're making the backup directly available in your web root. For temporary files you should normally use mktemp.

NB I'm assuming that /var/www/html is not directly mapped to a DirectoryRoot: if it is visible, that's even worse.

---

mysqldump -u root -p --all-databases > /var/www/html/db-$(date +\%F-\%T).sql

Again, mktemp.

Secondly, this is interactive: -p requires you to be there to write the password. It might be more useful to have a script which could run with cron, although this would mean storing the password somewhere. In that case you should definitely create a new user with restricted rights who can dump the database but not change it.

Thirdly, experience has taught me to play it safe by adding --hex-blob. I have had difficulty restoring when this option was not used and encoding differences mangled the blobs.

---

zip -r /var/www/html/html-$(date +\%F-\%T).zip /db-$(date +\%F-\%T).zip /var/www/html/all_zipped.zip

Yikes. If zip crashes for any reason you've just lost the old backup as well as the new one. I would prefer to have a backups directory (not under /var/www/html) and move html-${TIMESTAMP}.zip and db-${TIMESTAMP}.zip there. If you're worried about filling up your disk with backups, you can write a script to delete old ones.

A further advantage of having timestamped names is that you can identify the relative age of the offsite backups (you do have offsite copies, right?) without using zip to list the contents.

• Arcticooling: Thanks dearly Peter. I didn't understand what you mean here If zip crashes for any reason you've just lost the old backup as well as the new one.
• Peter Taylor: Suppose all_zipped.zip contains html-2018年01月03日-13:00:00.zip and db-2018年01月03日-13:01:31.zip (the old backup). You run the script on 2018年01月10日. When the script gets to that line, it overwrites all_zipped.zip (goodbye, old backup) and then crashes while adding html-2018年01月10日-13:00:00.zip. The script continues, executes the rm, and exits. You have an all_zipped.zip which is useless. It's possible that I'm being overly pessimistic here and that zip doesn't touch the filesystem until it's finished writing the contents to disk, but I wouldn't want to take that risk.
• Arcticooling: I like that pessimism. I also don't know that. Is it good to cope with it? zip -r /var/www/html/all_zipped-$(date +\%F-\%T).zip /var/www/html/html-$(date +\%F-\%T).zip /db-$(date +\%F-\%T).zip
• Peter Taylor: Yes, that avoids name clashes with the old backup. (Although I still prefer not to store it in /var/www/html, and to use variables as suggested by hjpotter92).

Answer 2

Since you are repeatedly using $(date +\%F-\%T), you can store that as a variable (unless you want to also know how long it took the script to jump between statements). Use that variable instead.

Similarly, set a basepath, which in your case, is /var/www/html. In case it changes in the future, it'll be just enough to change it in one place.

At the end, you have

rm -rf db-$(date +\%F-\%T).zip html-$(date +\%F-\%T).zip

which will try to delete those files from CWD, which might not be the same as /var/www/html.

mysqldump has options such as --compact and --compress. Making use of them will result in smaller sql files, resulting in even smaller zips; saving you some disk space.