On 5/18/06, Dominik Klein <[EMAIL PROTECTED]> wrote:
Hi, for recovery purposes I need to know what exactly is in the master.info file. Especially the log position. Is it Read_Master_Log_Pos or Exec_Master_Log_Pos? Another question: Does "stop slave;" only stop reading the log from master or does it also stop executing the log that has been read already, but not yet executed? Regards Dominik
Please note the important difference between the two ...... Read_Master_Log_Pos The position up to which the I/O thread has read in the current master binary log. Exec_Master_Log_Pos The position of the last event executed by the SQL thread from the master's binary log (Relay_Master_Log_File). (Relay_Master_Log_File, Exec_Master_Log_Pos ) in the master's binary log corresponds to (Relay_Log_File, Relay_Log_Pos) in the relay log. When you want to recover a failed slave, there could be several situations possible.. 1) you stopped the slave completely for maintainance for some time and then put it back online, all your files including the master.info, relay-log.info, and the realy-bin-* file(s) are intact, so all you have to do is to start the slave which would start the I/O and SQL threads and these will bring the slave back in sync with the master. 2) if either of I/O or SQL threads are stopped for some reason you can just start them again and the replication would pick up .... 3) lets say your slave crashed or may be the replication got corrupted on the slave and you need to recover the slave from a backup , in these kind of cases,you can do two things, a) stop the master (if you can afford some downtime) ,delete all the binary logs on it, and take a snapshot of the master and put it on the slave, and then start the master and then the slave and everything picks up ( so everyhting is essentially started fresh) b) Lets say you can't afford to stop the master, in that case you can recover the latest backup of the slave (all the data, all *.info files, and the relay log files), and when you start the slave again, the I/O would start reading from the master where it left off and also the SQL thread would start executing the sql statements from the realy log file where it left off.. when you dont have the relay log files, but have the relay-log.info file, then you can see how far the SQL thread has executed in the masters binary log file, and you can issue the command CHANGE MASTER TO with the corresponding MATSER_LOG_FILE and the MASTER_LOG_POSITION to re-read the binary log file, and then the SQL thread would pick & execute from where it left offff. The answer to your question really depends upon what state your MySQL server is in, you would generally use Read_Master_Log_Position, to tell the slave I/O thread to start reading from the masters bin log file, but its very important to look into the relay-log.info file, because when the slave crashed , its quite possible that the I/O thread had read to some point from the master but the SQL thread has not caught up, so if you just start the slave threads without taking this into consideration, then there is the potential danger of losing some data. So its always very important to backup the SQL thread parameters (realy-log.info file) I hope this answers your question... Kishore Jalleda http://kjalleda.googlepages.com/mysqlprojects