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.
