• Most Recent | 24 hours | 7 days | 30 days | 365 days |

• Content
• Comments
• Who Voted

• 7
• Vote

Automate mysql backups

Category: Server | Tags: mysql backup script 0 Comments

HowFlow — This is a little script to automate mysql backups

  Posted by bekks 5 months ago
View Profile

Add to:         

When backing up mysql database servers, you will face the problem that - on a webhosting server - you maybe dont know the database names since clients can create and delete their own databases. Still you want to backup them daily, and you want this to be done by a cron job. The next thing is, you need to keep backups for the last seven days, but you need access to the latest backup easily. 

So take this little script:

#!/bin/bash
BACKUPDIR=/mnt/backup/mysql
DATE=`date +%Y-%m-%d-%H%M`
umask 077
echo "Backing up all databases..."
mkdir -p ${BACKUPDIR}/recent
mysqldump --quote-names --lock-tables --all-databases -u root --password=thesecretone | bzip2 -9 > ${BACKUPDIR}/recent/mysql-all-dbs-$DATE.sql.bz2
ln -f ${BACKUPDIR}/recent/mysql-all-dbs-${DATE}.sql.bz2 ${BACKUPDIR}/mysql-all-dbs.sql.bz2
for db in `mysql -u root --password=thesecretone -B -N -e 'show databases' | xargs`
do
  echo "Backing up ${db}..."
  mysqldump --quote-names --lock-tables -u root --password=thesecretone ${db} | bzip2 -9 > ${BACKUPDIR}/recent/${db}-${DATE}.sql.bz2
  ln -f ${BACKUPDIR}/recent/${db}-${DATE}.sql.bz2 ${BACKUPDIR}/${db}.sql.bz2
done

Please log in or sign up and vote for this trick if it was helpful for you.
Don't forget to subscribe to our RSS/Atom feed to get the latest tricks.