MySQL Replicaton Question...

[whit3fir3]

I have a MySQL question that deal with replications and I am hoping that someone where can help me as I am at my whits end with this problem.  Currently I have 2 databases for a site that are in different countries.  I have Master / Slave replication setup between the 2 databases and the replications is working great.  The database is very large so for backing it up for the time being I am doing a mysqldump and including the --master-data=1 option on the Slave box.

 

Due to the size of the database I currently do not have enough disk space to create a second copy of the DB (this will be corrected in about 2 weeks) on the slave server so Developers have been using the the "Slave" DB for development purposes.  What I am trying to do is every night before the backups run restore the previous nights backup and then let the MySQL BIN logs catch the slave up and then finally do a clean mysqldump of the DB.

 

What I am finding is that in my backups on the Slave server the line that starts out "CHANGE MASTER TO MASTER_LOG_FILE" is pointing to a BIN Log position on the slave server.  Thus if I let it replay those BIN logs I get both what was sent from the MASTER through the day plus all the changes that the Development team made.  My question is this...With running mysqldumps on the SLAVE server is there a way to get the SLAVES position in the MASTERS BIN logs and then when the file is restored reset replication back to that point?

 

Thank

 

whit3fir3

 

[jdavidbakr]

The master-data flag is there so you can load the data onto a new slave that will connect to the server you did the dump from, it doesn't track the current slave's position.

 

You might try in your backup script dumping the output of "show slave status;" to the head of your dump.  You'd of course have to remove this section before restoring the dump, but it would give you the master log and position at the start of the dump.  You don't want to have master-data turned on for regular backups of a slave.