# We use --single-transaction instead of --lock-tables as it's working on a production database
# Versions follow:
# mysqldump  Ver 10.13 Distrib 5.5.17, for Linux (x86_64)
# mysql  Ver 14.14 Distrib 5.5.17, for Linux (x86_64) using readline 5.1
# mysqld  Ver 5.5.17-55 for Linux on x86_64 (Percona Server (GPL), Release 22.1)

mysqldump -u XXX -pXXX --master-data=1 --single-transaction --flush-logs --all-databases

# and here's the script we use to restore the dump on the remote system

$MYSQL -e "STOP SLAVE"
$MYSQL -e "RESET MASTER"
$MYSQL -e "CHANGE MASTER TO MASTER_HOST='XXX', MASTER_USER='XXX', MASTER_PASSWORD='XXX'"
(echo "SET SESSION sql_log_bin=0;"; zcat $LASTBACKUP) | time $MYSQL
$MYSQL -e "START SLAVE"

# problem symptom is that we're getting duplicate entries straight after the slave is started.
# manually removing the entries (usually a few seconds worth) sets the slave going properly again,
# but isn't a long term solution.

# I'm looking at bringing in Percona Xtrabackup, but right now we're not on file-per-table and as
# such the backup is about 4x the size. I'd also need to rewrite these scripts, so I'm hoping there's
# something simple in the above that I can fix first.