0

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!

Nina
  • 201
  • 3
  • 7
  • 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 04 '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 05 '12 at 15:16

1 Answers1

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}
)
Community
  • 1
Nina
  • 201
  • 3
  • 7
  • Make sure you copy these files to an NFS share or something like that, because if the server crashes you have nothing. – Ace Jul 15 '22 at 07:19