MySQL - backup without overwriting old backups

Question

How can I make this backup without overwriting old backups? here is the crontab that I made:

* 2 * * *  /opt/bitnami/mysql/bin/mysqldump -u root -password--databases BDname>~/Backup/DBname

Answer 1

You could simply write to a date-stamped dump file:

* 2 * * *  /opt/bitnami/mysql/bin/mysqldump -u root -password--databases BDname >"$HOME/Backup/DBname_$(date +\%F).sql"

date +%F will generate a date on the form YYYY-MM-DD. The % has to be escaped with a backslash in the crontab entry since % has a special meaning there.

The generated dump file name for today would be DBname_2017-10-06.sql.

You would get a more manageable operation if you put the dump command into its own script and scheduled that. This crontab entry is a borderline case of when something gets slightly too "not simple" for a cron job.

I tend to use $HOME rather than ~ for the home directory in all places other than in interactive shells, mostly because ~ does not behave as a variable, and $HOME is more descriptive in a script, or as here, in the crontab. See for example Why doesn't the tilde (~) expand inside double quotes?

---

If you also need to clean up old database backups, then see the answers to Delete files older than X days +

This could be done as a separate cron job, or you may combine the two operations (backup + cleanup) in one single script and schedule that script instead (I would do this).

Answer 2

If you write a script for managing all the steps related to the database backup, you can apply a datatime stamp to your backup name, you can delete old backups to keep space usage to a minimum, log the activity. All you would need to do is to call the backup script. Otherwise you may have to put different lines into crontap for each of the different commands you would like to run. A more efficient way would be have simple script for it.

[thebtm@server Backup] cat db_backup.sh
#!/bin/bash                                                                                                                                                                                                                                                                                                                                                                                                                                                      
BASE="~/Backup";                                                                                                                                                                                                                         
YESTERDAY=`TZ=$ENV{'TZ'}+30 date '+%Y%m%d'`                                                                                                                                                                                                  
echo "****************"                                                                                                                                                                                                                      
date                                                                                                                                                                                                                                         
echo Backing up Database
#find $BASE/DB/ -mtime +30 -type f -delete
/usr/bin/mysqldump -u root --password=password --routines dbname | bzip2 --best -c > $BASE/DB/dbname.$YESTERDAY.bz2
echo Done
date

then setup crontab to run the script

# MySQL database backups for <dbname>
15 1 * * * ~/Backup/db_backup.sh >> ~/Backup/logs/db_backup.log 2>&1