MySQL Forums
Forum List  »  Replication

mysqlhotcopy --record_log_pos parameters flaw?
Posted by: Alex Tomic
Date: March 12, 2007 08:00PM


I have a couple of slaves replicating from a master database (all MyISAM) and I would like to create consistent backups of the master database from one of less-burdened slaves. mysqlhotcopy has a nice feature --record_log_pos that allows you to pull the parameters from 'show slave status' right after doing the dump of the tables

I have one master 'M' and two slaves 'S1' and 'S2'. My plan is as follows:

- run hotcopy each night on S1 or S2, whichever is less burdened.
- record log parameters in a separate file so that I can restore or bring a new slave online quickly (restore the db, point to master with appropriate log coordinates)

The mysqlhotcopy docs are a bit vague on exactly what is meant by the 'master_log_pos' parameter it retrieves. Checking the source, at line 757 in sub record_log_pos() (v. 5.0.27)

sub record_log_pos {
my $row_hash = get_row_hash( $dbh, "show slave status" );
($master_host, $log_file, $log_pos )
= @{$row_hash}{ qw / Master_Host Master_Log_File Read_Master_Log_Pos / };

This doesn't make sense to me though. Shouldn't we be recording the Exec_master_log_pos and Relay_Master_Log_File to indicate which statements from the master logs have been _executed_ , not downloaded by the I/O thread?

Maybe I can illustrate w/ an example:

Say S1 is replicating from M and the SQL thread dies for some reason while applying M-bin.000001 log_pos 123456 .

M continues processing away, and S1's I/O thread retrieves log statements up till M-bin.000002 log_pos 54321. The db on S1 is frozen in the state it was in while M was at log file 000001 at log_pos 123456.

When we do our hotcopy, it will retrieve 'M-bin.000002' , 'log_pos: 54321'. If we restore another slave with this hotcopy, and tell it to start replicating from the master at those coordinates, we'll end up with a corrupt database.

On the other hand, if we had Exec_master_log_pos and Relay_Master_Log_File parameters, we could still restore from that snapshot and catch up on the one log file that we were missing from the master.

Is there any situation where you would care what the I/O thread was doing, when running mysqlhotcopy? Am I being dense here or is this really a problem? If so, what is the appropriate channel to submit a fix for this?

Any comments or suggestions are appreciated,

Alex Tomic

Options: ReplyQuote

Written By
mysqlhotcopy --record_log_pos parameters flaw?
March 12, 2007 08:00PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.