You do need to create a ~/.my.cnf for the user the script will be running as.
- touch ~/.my.cnf
- chmod 600 ~/.my.cnf
[client]
user = MySQL_User
password = MySQL_Password
Now the backup script. Configure the MYSQLUSER to match the MySQL user you will be backing up using (should match what you configured in .my.cnf.) Change the DIR variable to match where you want database dumps to be stored. Update the paths for MYSQL and MYSQLDUMP if needed. Then schedule the script to run as a daily cron job.
#!/bin/shThis script will create a seven day rotation of MySQL dumps. (One dump for each day of the week.) The script alone isn't a complete backup solution. Use your backup solution of choice to backup the dump files to tape or other media. I'm using CrashPlan for all of my personal backup needs.
MYSQLUSER=root
DIR=/srv/mysql-backup/dumps
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump
# Day 1 is Monday.
DAY=`date +%u`
# Query MySQL for the list of databases in the MySQL Server.
# Create a seperate dump file for each database.
for DBNAME in $(${MYSQL} --user=${MYSQLUSER} -Bse 'show databases');
do
DUMPFILE=${DIR}/${DBNAME}/${DBNAME}_DAY${DAY}.sql
# Make the directory structure if needed.
/bin/mkdir -p ${DIR}/${DBNAME}
${MYSQLDUMP} --add-drop-table ${DBNAME} --user=${MYSQLUSER} > ${DUMPFILE}
# Remove last weeks backup if it exists.
if [ -e ${DUMPFILE}.bz2 ]
then
/bin/rm ${DUMPFILE}.bz2
fi
# Compress the dumpfile using bzip2.
/bin/bzip2 ${DUMPFILE}
done
# End Loop
