Linux backups: Securely back up mysql to local dir


I wrote this script to make mysqlhotcopy securely do a daily backup of my wordpress blog, but you can easily make this do backups of any mysql database.

Here’s how:

As root:

    * mkdir /var/lib/auth
    * vi /var/lib/auth/mysqluserpass
    * make 2 lines, one on top of the other containing only the username and password, it should look like this:
          o mysqluserid
          o mysqlpasswd
    * save the file, and chmod it to 600 so that only root can read it
    * create a script in /etc/cron.daily called mysqlbackup.sh
    * fill the script with this content:

#/bin/sh
exec 3<&0- 0</var/lib/auth/mysqluserpass
read USER
read PASS
exec 0<&- 0<&3-

echo USER=$USER
echo PASS=$PASS

export DBBKDIR=/path/to/db/backup/space

export DOW=`date +%a`
export DM=`date +%d%b%Y`

mkdir $DBBKDIR/$DOW$DM
mysqlhotcopy --user=$USER --password=$PASS nameofdatabase $DBBKDIR/$DOW$DM

echo "Full backup of database content has completed"

    * chmod +x mysqlbackup.sh

That’s it. Now you should have daily mysql backups being dumped into your backup directory