mysqlhotcopy --record_log_pos parameters flaw?
Posted by:
Alex Tomic
Date: March 12, 2007 08:00PM
Hello,
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,
Regards,
Alex Tomic