cronjob for automatic DB backup to date prefixed file

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

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

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