Backup all mysql database every hour using mysqlhotcopy

Posted Fri, 06/19/2009 - 16:24 by ma

My previous script using mysqldump was slowing down Apache, it became unresponsive for a few seconds, so I wrote this one using mysqlhotcopy. It's much faster and doesn't slow down my webserver.

#!/bin/sh
# System + MySQL backup script
# Copyright (c) 2009 blogama.org
# This script is licensed under GNU GPL version 2.0 or above
# ---------------------------------------------------------------------
 
#########################
######TO BE MODIFIED#####
 
### System Setup ###
BACKUP=/home/youruser/db
 
### MySQL Setup ###
MUSER="mysqluser"
MPASS="mysqlpass"
 
######DO NOT MAKE MODIFICATION BELOW#####
#########################################
 
### Binaries ###
MYSQL="$(which mysql)"
MYSQLHOTCOPY="$(which mysqlhotcopy)"
TAR="$(which tar)"
NICE="$(which nice)"
 
### Today + hour in 24h format ###
NOW=$(date +"%a%H")
 
### Create hourly dir ###
TEMPSAVEDIR=/tmp/$NOW
mkdir -p $TEMPSAVEDIR
mkdir -p $BACKUP
 
### Get all databases name ###
DBS="$($MYSQL -u $MUSER -h localhost -p$MPASS -Bse "show databases")"
for db in $DBS
do
  $NICE -n 20 $MYSQLHOTCOPY -u $MUSER -p $MPASS $db $TEMPSAVEDIR --addtodest
done
 
$NICE -n 20 $TAR -zcvf $BACKUP/$NOW.tar.gz $TEMPSAVEDIR
rm -rf $TEMPSAVEDIR

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

edits

by thazer - 06/20/2009 - 12:52

after starting using the backup script, i've noticed 1 error and 1 possible minor design flaw
1. In the script content's the variable $MHOST is used but it is not declared anywhere else in the script, causing the sudden death of mysql command by the backup-script

2. In my opinion, and in my case, using separate backup-up scripts for every single database, rather than using master account to back-up everything, is more convenient and the actual script, in this case, makes the backup tar with one single static name (the time of the script's running).
Indeed a simple fix would be to set another backup-dir but what if i want to gather all backups in one folder? But again this issue is personal only and i made the appropriate modifications to the script to fit my needs.

And the article needs to point out that this script needs cron to run every hour ...

pgn.ro

Hi, you can remove the HOST

by ma - 06/25/2009 - 10:09

Hi, simply put back the host variable, it was there for my previous script (thanks to copy paste).

I have so many databases I cant write a script for each of them ! There is a few databases I dont backup, I simply update the 'show databases' to 'show database not like `big_database_i_dont_want_to_backup`'

ok

by thazer - 06/20/2009 - 12:14

it's indeed much smoother that mysqdump (for larger databases), i've done this backup script with a off-site storage for better protection, seems more secure. Thanks again!

pgn.ro