Wednesday, July 29, 2009

Failed to open the relay log


We moved some hardware about last night.
Well actually Jim and Marcin did.

This AM I got to the office to discover everything apparently working (its early days yet!) apart from one mysql replication.

Now; we've set up this replication to replicate one db using replicate-do-db which has very many of its own crazy gotcha's that I'm not going to get into here.

We also set the slave params by executing a command, rather than in my.cnf.
This allows us to meddle without having to restart mysqld, and protects us from accidentally reverting the settings if mysqld should require a restart.

my.cnf only contains the id of the slave, skip-slave-start, and replicate-do-db that affect replication.

This morning, h'wever, the slave was compaining that it couldn't open the relay log, which was annoying. I found a post here which suggested flush logs that didn't work. The next suggestion is reset slave.

Now I'm afraid of reset slave, but I didn;t think I had many options, so I forged ahead and here's how I got the job done.

NOTE-- I take no responsibility for anything misguded and foolish that you may choose to do in the light of reading this, or any other, bit of misleading nonsense on the internet.

If you have any doubts about your ability to resist doing this without putting a roll back strategy in place before hand then read no further. You have been warned!

Working completely on the slave:
1/ stop the slave .
2/ show slave status
3/ make a note of Read_Master_Log_Pos, which should equal Exec_Master_Log_Pos, and of Master_Log_File
4/ now execute reset slave
5/ now set the params you noted..

CHANGE MASTER TO MASTER_HOST='xxxx.killerbees.co.uk',
MASTER_USER='xxxx',
MASTER_PASSWORD='xxxx',
MASTER_LOG_FILE='Master_Log_File',
MASTER_LOG_POS= Read_Master_Log_Pos

6/ start slave

Job Done.

You can also use this approach to start a second slave on the same master without having to affect the master's operation:
stop slave one,
note the params,
set slave two params,
export-import the data from slave one to slave two,
start slave two.


Comments:

blog comments powered by Disqus

I know nothing, I'm not a fortune teller, and you'd be insane to think that I am. This disclaimer was cribbed from an email footer I once received. It is so ridiculous I had to have it for myself.

Statements in this blog that are not purely historical are forward-looking statements including, without limitation, statements regarding my expectations, objectives, anticipations, plans, hopes, beliefs, intentions or strategies regarding the future. Factors that could cause actual results to differ materially from the forward looking statements include risks and uncertainties such as any unforeseen event or any unforeseen system failures, and other risks. It is important to note that actual outcomes could differ materially from those in such forward-looking statements.

Danny Angus Copyright © 2006-2013 (OMG that's seven years of this nonsense)