error failed to open the relay log
Connectors More MySQL.com Downloads Developer Zone Section Menu: Documentation Home MySQL 5.7 Reference Manual Preface and Legal Notices General
Error Counting Relay Log Space MysqlInformation Installing and Upgrading MySQL Using MySQL as a Document error could not find target log during relay log initialization Store Tutorial MySQL Programs MySQL Server Administration Security Backup and Recovery Optimization Language Structure Globalization
Could Not Find Target Log During Relay Log InitializationData Types Functions and Operators SQL Statement Syntax The InnoDB Storage Engine Alternative Storage Engines High Availability and Scalability Replication Configuring Replication Binary Log File purge relay logs Position Based Replication Configuration Overview Setting Up Binary Log File Position Based Replication Setting the Replication Master Configuration Creating a User for Replication Obtaining the Replication Master Binary Log Coordinates Choosing a Method for Data Snapshots Setting Up Replication Slaves Adding Slaves to a Replication Environment Replication with Global Transaction Identifiers mysql relay log vs binlog GTID Concepts Setting Up Replication Using GTIDs Using GTIDs for Failover and Scaleout Restrictions on Replication with GTIDs MySQL Multi-Source Replication MySQL Multi-Source Replication Overview Multi-Source Replication Tutorials Multi-Source Replication Monitoring Multi-Source Replication Error Messages Changing Replication Modes on Online Servers Replication Mode Concepts Enabling GTID Transactions Online Disabling GTID Transactions Online Verifying Replication of Anonymous Transactions Replication and Binary Logging Options and Variables Replication and Binary Logging Option and Variable Reference Replication Master Options and Variables Replication Slave Options and Variables Binary Logging Options and Variables Global Transaction ID Options and Variables Common Replication Administration Tasks Checking Replication Status Pausing Replication on the Slave Replication Implementation Replication Formats Advantages and Disadvantages of Statement-Based and Row-Based Replication Usage of Row-Based Logging and Replication Determination of Safe and Unsafe Statements in Binary Logging Replication Implementation Details Replication Channels Commands for Operations on a Single Channel Compatibility with Previous Replication Stat
into a little issue setting up a MySQL slave. I have done this a least a have dozen times before, and it is always pretty painless, but this time, I kept getting hung up on error that
Could Not Initialize Master Info Structurelooked like this: 071118 16:44:10 [ERROR] Failed to open the relay log './-relay-bin.003525' mysql show relay logs (relay_log_pos 22940879) 071118 16:44:10 [ERROR] Could not find target log during relay log initialization 071118 16:44:10 [ERROR] Failed to initialize the
My.cnf Relay-logmaster info structure Googling a little turned up this mysql forum, http://forums.mysql.com/read.php?26,112490,223025#msg-223025 Which then led me to this mysql doc page http://dev.mysql.com/doc/refman/5.0/en/replication-howto-additionalslaves.html This was confusing, and didn't really seem like it got to the root https://dev.mysql.com/doc/refman/5.7/en/slave-logs-relaylog.html of the problem. Why would one slave care about another, if they were both reading from the master? I think that article would more accurately be called "Making a slave of a slave" or some such, because "Introducing Additional Slaves to an Existing Replication Environment" does not convey that they are talking about a slave of a slave (which I think they are). Anyway, long and short of it, I http://dev.nuclearrooster.com/2009/03/06/relay-log-issues-when-starting-mysql-replication-slave/ removed the ‘master.info' ‘mysqld-relay-bin.*' ‘relay-log.info' ‘relay-log-index.*' from the MySQL data directory, restarted mysql (‘/etc/init.d/mysql restart'), and off to the races. Sometimes, depending on the order you do edit the configs or restart mysql, you can get bad data in those files, and it can lead to crpytic errors. Remove those, and you can always start fresh. This entry was posted in Uncategorized. Bookmark the permalink. 6 thoughts on “Relay log issues when starting MySQL replication slave” Akom says: May 2, 2011 at 1:33 pm That really helped - mine broke with a 5.0 to 5.1 upgrade on the slave (master is still 5.0). Deleted files, started, (it tried to play from beginning of relay logs, with duplicate errors), stop slave, change master to previously shown master position, start slave - and it's all good. Whew DR.J says: September 9, 2011 at 5:22 am Thanks you made my Friday. My instance pointed to the default relay log, even though I had defined a different one. Deleting them worked for me too. Sam says: February 7, 2012 at 11:30 am That worked great for me, after I searched several other places trying to figure out why my replication was no longer working. Thanks so much for posting it!
After everything is back to operational - I noticed that the replication has stopped in this slave. I tried to restart the replication but it was http://passionatedevelopment.com/blog/2011/06/11/missing-relay-log-file-after-mysql-crash/ unsuccessful. I then checked the MySQL log and found the following: [bash] 110606 17:01:30 [ERROR] Failed to open the relay log './forbes-relay-bin.785212' (relay_log_pos 14931) 110606 17:01:30 [ERROR] Could not find target log during relay log initialization 110606 17:01:30 [ERROR] Failed to initialize the master info structure [/bash] And so MySQL is complaining that it can’t find the relay log file and sure enough I had relay log a look into the directory, I couldn’t find it. So what to do? I Googled around for some magic solutions - this link came up on top. The proposed solution was to: CHANGE MASTER TO the positon _on the master_ where the slave was, the slave will get the transactions from the master binary logs again and you will lose nothing. Requires that the master could not find still has those binary logs. However this is not the problem, it is not that the slave doesn’t know the master bin log position. My guess is the relay log is now outdated and needs to be regenerated. So what we really need to do is to tell the slave to regenerate its relay log files. Here are the steps to do it: On the crashed slave - record the last Master bin log file and position before the crash The slave should not be running - if it is - issue STOP SLAVE And then issue RESET SLAVE command Go to the MySQL directory - ensure that relay logs, master info and relay-log.info are deleted - they should be deleted when you run RESET SLAVE. Note: When I did this the first time - MySQL didn't delete the relay logs - running RESET SLAVE the second time fixed it Now issue CHANGE_MASTER log position to the position that you have recorded on step 1 above Issue START SLAVE Now the replication should start again - you will notice that the relay log files are re-numbered from the beginning ie: suffix 00001. Posted by