Earlier this week we had a lot of trouble restoring data on a client's site which was hosted with a web host that we have worked with for a number of years. People always assume that their web hosts do keep backups, however this totally depends on the host and the package that the client chose to purchase. Fortunately the client had selected a package that contained backups, however restoring data from a backup isn't trivial and it all had to be managed through trouble tickets as per any large web hosting company.

To gain a bit more control of the backup process I wrote up the following script to help us organize a local backup of our database - mysql_backup.sh - it was complicated considerably because the script worked perfectly well from the shell manually, but failed silently when run from the cron until the whole path was provided to the mysqlump.

#!/bin/shDIR='/magma/users/u50/example/backup'DATESTAMP="$(date +%Y%m%d)"DB_USER='username'DB_PASS='password'# move to dircd ${DIR}# remove backups older than $DAYS_KEEPDAYS_KEEP=7find ${DIR}* -mtime +$DAYS_KEEP -exec rm -f {} \; 2> /dev/null# create backups securelyumask 007# create dated backupDATE=`date +%Y%m%d`/usr/local/bin/mysqldump -v -u ${DB_USER} -p"${DB_PASS}" --opt example > ${DIR}/example.${DATE}.sqlSince there were also occasionally problems with corrupt tables I set up the following script to repair the database on a regular basis (mysqlcheck isn't an option on Magma) - mysql_optimize.sh - I don't know of a way to verify that this is working properly from the cron job unfortunately. #!/bin/sh# this shell script finds all the tables for a database and run a command against it# @date 6/14/2006# @author Son NguyenDB_PASS='password'# entered in from the command lineDBNAME=$2printUsage() {echo "Usage: $0"echo " --optimize "return}doAllTables() {# get the table namesTABLENAMES=`mysql -D $DBNAME -p"${DB_PASS}" -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`# loop through the tables and optimize themfor TABLENAME in $TABLENAMESdomysql -D $DBNAME -p"${DB_PASS}" -e "OPTIMIZE TABLE $TABLENAME; REPAIR TABLE $TABLENAME; "done}if [ $# -eq 0 ] ; thenprintUsageexit 1ficase $1 in--optimize) doAllTables;;--help) printUsage; exit 1;;*) printUsage; exit 1;;esac And finally here's the cron job that runs them both: # Min Hour Day Month Weekday15 3 * * * /magma/users/u50/example/backup/mysql_backup.sh 20 3 * * * /magma/users/u50/example/backup/mysql_optimize.sh --optimize mining