Thursday 22 September 2011

Backuppc and MySQL

The best way to backup a MySql Server using Backuppc is to use a pre-dump script.

you can use $Conf{DumpPreUserCmd} to issue a MysqLDump

Stdout from these commands will be written to the Xfer (or Restore) log file, note that all Cmds are executed directly without a shell, so the prog name needs to be a full path and you can't include shell syntax like redirection and pipes; put that in a script if you need it.

So in our case we would create a script, on the Backuppc client, to dump all databases into a file:
vi /usr/local/sbin/myBkp.sh
and paste the following into it:
#!/bin/bash
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
DEST="/backup/mysqlDump.sql"
MYSQLUSER="root"
MYSQLPASS="mypassword"
# no need to change anything below...
#####################################################
LOCKFILE=/tmp/myBkup.lock
if [ -f $LOCKFILE ]; then
echo "Lockfile $LOCKFILE exists, exiting!"
exit 1
fi
touch $LOCKFILE
echo "== MySQL Dump Starting $(date) =="
$MYSQLDUMP --single-transaction --user=${MYSQLUSER} --password="${MYSQLPASS}" -A > ${DEST}
echo "== MySQL Dump Ended $(date) =="
rm $LOCKFILE
make the script executable:
chmod +x /usr/local/sbin/myBkp.sh 
and set $Conf{DumpPreUserCmd} with:
$sshPath -q -x -l root $host /usr/local/sbin/myBkp.sh
Now you just have to make shure that Backuppc is getting the /backup folder (or whatever folder you have set in the script) and you can also exclude the /var/lib/mysql folder from backuppc backups.

Possibly Related Posts

No comments:

Post a Comment