I am currently running mysqldump on a Mysql slave to backup our database. This has worked fine for backing up our data itself, but what I would like to supplement it with is the binary log position of the master that corresponds with the data generated by the mysqldump.
Doing this would allow us to restore our slave (or setup new slaves) without having to do a separate mysqldump on the main database where we grab the binary log position of the master. We would just take the data generated by the mysqldump, combine it with the binary log information we generated, and voila... be resynced.
So far, my research has gotten me very CLOSE to being able to accomplish this goal, but I can't seem to figure out an automated way to pull it off. Here are the "almosts" I've uncovered:
This seems like something common enough that somebody must have figured out before, hopefully that somebody is using Stack Overflow?
The following shell script will run in cron or periodic, replace variables as necessary (defaults are written for FreeBSD):
# MySQL executable location
mysql=/usr/local/bin/mysql
# MySQLDump location
mysqldump=/usr/local/bin/mysqldump
# MySQL Username and password
userpassword=" --user=<username> --password=<password>"
# MySQL dump options
dumpoptions=" --quick --add-drop-table --add-locks --extended-insert"
# Databases
databases="db1 db2 db3"
# Backup Directory
backupdir=/usr/backups
# Flush and Lock
mysql $userpassword -e 'STOP SLAVE SQL_THREAD;'
set `date +'%Y %m %d'`
# Binary Log Positions
masterlogfile=`$mysql $userpassword -e 'SHOW SLAVE STATUS \G' | grep '[^_]Master_Log_File'`
masterlogpos=`$mysql $userpassword -e 'SHOW SLAVE STATUS \G' | grep 'Read_Master_Log_Pos'`
# Write Binlog Info
echo $masterlogfile >> ${backupdir}/info-$1-$2-$3.txt
echo $masterlogpos >> ${backupdir}/info-$1-$2-$3.txt
# Dump all of our databases
echo "Dumping MySQL Databases"
for database in $databases
do
$mysqldump $userpassword $dumpoptions $database | gzip - > ${backupdir}/${database}-$1-$2-$3.sql.gz
done
# Unlock
$mysql $userpassword -e 'START SLAVE'
echo "Dump Complete!"
exit 0