Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

8 Answers

up vote 3 down vote accepted

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!

share|improve this answer
    
and a again cron it... (forgot that) –  lepole Sep 8 '09 at 19:40
add comment

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.

share|improve this answer
    
Might that not work if the DB name has a space in it? –  Kyle Brandt Sep 8 '09 at 19:36
    
er yeah :) Is that possible? Is it sensible? :) I'll fix it. –  David Pashley Sep 8 '09 at 21:28
add comment

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 {} \;
share|improve this answer
add comment

Don't forget to add script to crontab

15 0 * * * /path/to/script.sh

share|improve this answer
add comment

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
share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.