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
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!
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.
David's look good to me, but a couple more for your reference (The second backs up all databases, but David's mysql options are better than the sed command I think):
Example 1:
#!/bin/bash
#Create local backups of the mysql drupal database for easy restore
#KMB 2008
dumpdir='/root/drupaldumps'
backupdir='/root/drupal_backups'
myuser='drupal'
mypass='SECRET'
dumpfile="${dumpdir}/drupal-$(date +%s).sql"
mysqldump -udrupal -p${mypass} drupal > $dumpfile
mv $dumpfile ${backupdir}/
find $backupdir -name '*.sql*' -type f -mtime +7 -exec rm {} +
Example 2:
#!/bin/bash
#KMB March 2009
umask u=rw,g=,o=
dumpdir='/root/db_backups'
backupdir='/root/db_backups/dumps'
myuser='backup_user'
mypass='SUPER_SECRET'
while read database; do
dumpfile="${dumpdir}/${database}-$(date +%s).sql.bz2"
mysqldump -u $myuser -p${mypass} $database | bzip2 > $dumpfile
mv $dumpfile ${backupdir}/
done < <(mysql -u $myuser -p${mypass} -e 'show databases' -s | sed '1d;$d')
find $backupdir -name '*.sql.bz2*' -type f -mtime +7 -exec rm {} \;
Or if you just want all the databases in a single SQL file:
mysqldump -u -p --opt --all-databases > /path/to/backup/$(date -Imin).sql
and then to only keep a weeks worth of backups:
find /path/to/backup -type -f -mtime +7 -delete
You might also give a try to Zmanda Recovery Manager (zrm-mysql).
While it can look over-complicated at first glance, I've created totally encrypted well-managed centralized backup solution for a number of remote MySQL servers using Community Edition of ZRM.
More of information on topic could be found on HowtoForge, for example:
http://www.howtoforge.com/mysql-backups-with-zrm-2.0
Cheers
One thing I used to do was to email the backup file off-server after it had been generated. That way, even if the server died, I would still have the latest backup on another server. You could also use rsync or some other method to move the backup archive to another machine.
Use rsnapshot from RSNAPSHOT.org. It is available through "apt-get install rsnapshot".
It works like a charm and over SSH after setting machine SSH keys... The website offers some scripts to have MySQL backed up as well. You can modify these to whatever specific needs you may have.
rsnapshot is a filesystem snapshot utility for making backups of local and remote systems.
Using rsync and hard links, it is possible to keep multiple, full backups instantly available. The disk space required is just a little more than the space of one full backup, plus incrementals.
Depending on your configuration, it is quite possible to set up in just a few minutes. Files can be restored by the users who own them, without the root user getting involved.
