MySQL replication masters - need help with CHANGE MASTER retaining logs - Knowledgebase

Portal Home > Knowledgebase > Articles Database > MySQL replication masters - need help with CHANGE MASTER retaining logs

MySQL replication masters - need help with CHANGE MASTER retaining logs

Posted by quad3datwork, 11-05-2010, 12:14 PM
I got an event recheck every 15 seconds, and when it detects a failure (federated table) on existing replication node it calls a a simple procedure to switch to another master. However, it does not retain the values for "master_log_file" and "master_log_pos". I was under assumption changing master_host would retain those, but it is not. Any way to make it retain both when switching master_host? Since master_log_file and master_log_pos cannot be easily determined during the switch. Thanks for any thoughts.
Posted by quad3datwork, 11-05-2010, 01:37 PM
According to this page: Is there a way around this? Need to retain master_log_file & master_log_pos values. If this method is not recommended, why? I'm testing four MySQL nodes in a circular replication fashion. All masters.
Posted by zzhosting, 11-05-2010, 02:35 PM
Hi When ever I have used Replication you always need to supply the log file. can you not get that information before changing master (ie within your stored proc)? Thanks
Posted by Scott.Mc, 11-05-2010, 07:17 PM
why not change the logic to, if (master_dead) then stop slave; show slave status \G [parse info] change master to master_host = '192.168.10.201', master_log_pos=[parsed info], master_log_file=[parsed info];
Posted by quad3datwork, 11-06-2010, 04:57 AM
Hi, Yes, I thought about that. Since procedure allow variables so I was looking for a way to parse/grep out the values. However, all the researches I've done so far reveals nothing. If someone know a way how to do this within MySQL procedures, please do show! The other way I can think of is find the table where it contains the values for both master_log_file and master_log_pos. Anyone know where this is?
Posted by ZenMonk, 11-08-2010, 01:00 AM
The values are stored in the master.info file which normally resides in your mysql data_dir unless otherwise specified.
Posted by quad3datwork, 11-08-2010, 01:14 PM
BarackObama Thanks for the tip. You are right. Upon more thorough researches done over the past few days. I've decided to go against the idea of having more than two masters. The more-than-two-masters model has proven to work, yes, but it's always the case suggested against it due to how fragile it is. We all know even small latency would cost two-headed masters to have some sync issue sometimes. When multiple that by two, the problem seems to worsen a lot more. Also, the works needed to be done to recover any failed node in four-masters setup seems tedious (same can be said in two-masters setup). Basically there are more things involved where it's just a pain to maintain four master setup. Thank you for all that participated in this discussion!