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 have automysqlbackup running on Ubuntu 10.04 and it works perfectly fine.
I currently have it performing daily backups for all databases, but I would like to adjust the configuration to do the following:

Perform daily backups of all databases into one folder but as separate .sql files, and then have that folder be a gzip with the timestamp the backup performed.

I was wondering if anyone else has managed to do this, and if so, how could I configure it?

Thanks!

share|improve this question
1  
What about making a backup a number of times, generating one database for each backup. Then you can gzip the files with another process. I'm interested why do you want to do this? If I recall you should be able to restore a single database from the full backup. –  mdpc Sep 4 '12 at 19:32
    
I was thinking of making single backups now, but i'm not sure how to gzip the files for that particular date without affecting the other backups. Would you happen to know how to do that? The reason I want to do this is because other people will have access as well, and they want to be able to just go in - find their database - and get out. –  Nina Sep 5 '12 at 15:16

1 Answer 1

Got it - I just placed this into cron. Modified a bit based on Jason Tan's answer.

#!/bin/sh
# Find out what databases are in mysql and back them up
# Delete old backups
STARTTIME=` date +%Y%m%d-%H%M `

#BACKUP_DIR="/var/backup/db/data"
BACKUP_DIR="/var/backup/db/data"
LOGFILE="/var/backup/db/data.log"
USER="root"
PASSWD="<password>"
KEEP="20"

(
echo
echo " ---MySQL backups start ${STARTTIME} ---"
#delete any backup written more than ${KEEP} days ago
echo "Removing files over ${KEEP} days old from ${BACKUP_DIR}:"
/usr/bin/find  ${BACKUP_DIR} -mindepth 1 -mtime +${KEEP} -print -delete
echo
echo "Performing today's dumps"
#find each database running in this instance of mysl
for DB in ` echo "show databases;"|mysql -u${USER} -p${PASSWD} mysql |awk " NR>1 {print         
$1} " `
do
    #generate a backup file name based on the data base name
    BACKUP_FILE="${BACKUP_DIR}/${DB}-${STARTTIME}.sql"
    echo "Processing database ${DB} into file ${BACKUP_FILE}"
    # dump the database data/schema into the backup file
    mysqldump -u${USER} -p${PASSWD} ${DB} > ${BACKUP_FILE}
    #gzip ${BACKUP_FILE}
done

ENDTIME=` date +%Y%m%d-%H%M `
echo
echo " ---MySQL backups complete ${ENDTIME} ---"
echo
) >>  ${LOGFILE} 2>&1

ENDTIME2=` date +%Y%m%d-%H%M `
(
#copy backup directory to a new backup directory with end time  

tar -zcvf ${BACKUP_DIR}-${ENDTIME2}.tar.gz ${BACKUP_DIR}
)
share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.