Mysql Master-Slave replication catchup and bin_logs

Question

I have set up MYSql Replication using 2x 128gig ram 24 core cloud servers. we have a massive live database that cannot experience down time due to the high volume of data input.

I have found while dealing with the master slave is if I restart the service the bin_log and position change. Thus the slave needs to be updated and even then will be missing the data for the period it did not have the new bin_log set.

Dealing with my database which is over 400 gigs big, and is required to be live 99% of the time I cannot afford to Lock tables, Dump, transfer and import.

The slave is also very impotent as all reads are done from it and the data is highly relevant to current user interaction and as such cannot experience missing data or down time.

Moreover I am using mysql 5.5

So my questions are as follows:

1) What else if anything can cause the bin_log on the master to change. 2) How can I get my slave to consolidate and catch up the missing data without redoing the whole slave.

Any help will be highly appreciated.

Answer 1

1) Any INSERT, UPDATE, ALTER etc type Query executed on the Master will be written to the Binary log, causing the MASTER_LOG_POS to increase

Furthermore, if the Master is receiving updates from another Master (in a Master to Master setup), AND if you have LOG_SLAVE_UPDATES enabled these will be written to the Binary Log as well.

Each time you restart the service, the Binary Log will be flushed, incrementing it by 1.

2) On the Slave, Run SHOW SLAVE STATUS, and make a note of the MASTER_LOG_FILE. Now check, does this file exist on the Master still? If it doesn't then you have a problem, and will need to rebuild the slave from a copy of the master (e.g. MYSQLDUMP)

Assuming it does exist you should be able to just issue a START SLAVE command, and it will start catching up (hopefully)

Next run SHOW SLAVE STATUS again and check the SLAVE_IO_RUNNING and SLAVE_SQL_RUNNING. Are they both YES then all is good. Look for the SECONDS_BEHIND_MASTER to give you an idea of how far behind it is.

If either of them is NO look at the Error (LAST_IO_ERROR or LAST_SQL_ERROR) to get an idea of what the problem is.