cronjob for automatic DB backup to date prefixed file

Question

I am using the latest Linux Mint. I was wondering if it's possible to create a special cronjob for a database backup.

In my /etc/cronjob file I have the following code:

# Minute   Hour   Day of Month       Month          Day of Week        Command    
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)                
30        4          *            *                1-6            /home/users/backup.sh

In my /home/users/backup.sh I have:

mysqldump -uroot -p MyDatabase > /home/users/backup_MyDB/full_myDB.sql

Instead of full_myDB.sql I would like to have something like 2014-04-04_full_myDB.sql where the date is added dynamically depending on the date we have.

If the SQL Backup file is older than one week I would like the cronjob to delete it automatically.

Answer 1

With GNU date (default on Linux Mint) you can do:

mysqldump -uroot -p MyDatabase >/home/users/backup_MyDB/$(date +%F)_full_myDB.sql

To delete files older than 1 week:

find /home/users/backup_MyDB -type f -mtime +7 -exec rm {} +

Although generally it is wise to see what you are deleting before you delete (at least when testing you script) for this just do:

find /home/users/backup_MyDB -type f -mtime +7

Answer 2

I used the above information and wanted to provide one more minor update that actually truncates one of the really large tables that was slowing up our backups.

Hopefully this helps someone else.

Using the above information I created a basic shell script named mysqlbackup.sh with the following content:

#!/bin/sh
find /data/var/backups/mysql/dumps -type f -mtime +3 -exec rm {} +
mysql -e "truncate table sitename_prod.cache_table"
mysqldump sitename_prod > /data/var/backups/mysql/dumps/$(date +%F)_full_sitename_prod.sql

Make sure to run: chmod +x mysqlbackup.sh

I also put this in my crontab -e:

# MYSQL Dump and retention for 3 days
30 22 * * * bash /root/bin/mysqldump.sh > /dev/null 2>&1

Answer 3

I know it's a big old, but I've used the above answers, and added a file compression instruction. Hopefully somebody else finds this useful.

1) Researching a bit, 7-zip seems the best compressor out there. If your linux distro supports it, you can use the apt installer:

sudo apt-get install p7zip-full

Alternatively, you can use tar.gz if you feel more comfortable with it.

2) Then, you create a script, for example /home/users/backup.sh With the contents:

#!/bin/sh
find /home/users/backup_MyDB -type f -mtime +7 -exec rm {} +
mysqldump -uroot -p MyDatabase >/home/users/backup_MyDB/$(date +%F)_full_myDB.sql
7z a /home/users/backup_MyDB/$(date +%F)_full_myDB.7z /home/users/backup_MyDB/*.sql
rm -f /home/users/backup_MyDB/*.sql

This script will find the files with more than 7 days and delete them, then it will make the sql dump, then it will 7-zip all the .sql files in the directory, then it will delete all the .sql in the directory (BTW, you can optionally add a mysql command before the dump, as noted on the previous answer if you need it)

3) We do a chmod +x /home/users/backup.sh so it can be executable.

3.1) You should test your script if it works as intended

4) We program the task with crontab -e

# Minute   Hour   Day of Month       Month          Day of Week        Command    
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)                
30        4          *            *                1-6            /home/users/backup.sh >> /dev/null 2>&1

And that's it. It will backup your MySQL database each day in the week at 4:30am (except sundays), and compress the backup

Answer 4

To add to @Graeme's answer, it might be worth noting that you may sometimes need to escape the '%' character in a cron job so it will look like this:

 mysqldump -uroot -p MyDatabase >/home/users/backup_MyDB/$(date +\%F)_full_myDB.sql