debian mysql auto daily backuping

I need to backup my Mysql database daily under a Debian server. I know there are plenty of scripts out there but my need is backing up daily as a sql file and putting each backup seperately(maybe the current date as file name?) under the same folder so no overwriting a single file at each backup.

Regards

Solution 1:

I constantly use automysqlbackup.sh It does a mysqldump daily, keeps them around for a week, keeps a weekly backup for a month and monthly for a year - or so... all very hasslefree!

Solution 2:

Something like:

 for db in $(mysql -e "show databases" -B  --skip-column-names); do
     mysqldump --opt --databases "$db" \
     | gzip -3 --rsyncable > "/var/backup/$db-$(date +%Y-%m-%d).sql.gz"
 done

You can then use that in a cron job to do your backups every day.

You can make mysqldump dump all databases into one file, but that makes it very hard to restore just a single database if something goes wrong. If you're copying the file off to another server via rsync, you really want the --rsyncable option. It wastes some diskspace in return for better performance over rsync. It's mostly to do with the way rsync calculates blocks of data to transfer.

We use something very similar to the above script, except we don't use a date in the filename, as this causes you to have to deal with cleaning up old backups. We use backuppc to deal with keeping old copies around. Unless you already have a remote backup solution, I highly recommend looking at backuppc.