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?
mysql> SHOW BINARY LOGS; To determine the name of the current binary log file, issue the following statement: mysql> SHOW MASTER STATUS; The mysqlbinlog utility converts the events in the binary log files from binary format to text so that they can be viewed or applied.
The binary log is a set of log files that contain information about data modifications made to a MySQL server instance. The log is enabled by starting the server with the --log-bin option. The binary log was introduced in MySQL 3.23. 14. It contains all statements that update data.
The information in the binary log is stored in different logging formats according to the database changes being recorded. Replicas are configured to read the binary log from the source and to execute the events in the binary log on the replica's local database.
To safely purge binary log files, follow this procedure: On each replica, use SHOW REPLICA STATUS to check which log file it is reading. Obtain a listing of the binary log files on the source with SHOW BINARY LOGS . Determine the earliest log file among all the replicas.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With