Mysql slave fails to resume from master if master restarted

Question

I have replication setup between two mysql servers, one is a master and the other is the slave.

This works well unless the master is restarted for any reason. When it comes back up the slave doesn't connect. On the slave I do a start slave; then it fails with

---

090922 17:49:59 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000009' (relay_log_pos 67391580) 090922 17:49:59 [ERROR] Could not find target log during relay log initialization

---

And I am forced to grab all the data from the master and load it back into the slave and start that again.

How can I avoid this from happening so if the master does get restarted it's not a mission to re-do all the databases.

Here is the config file Red Hat 5, mysql 5.0.45

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1

server-id = 9180
slave_compressed_protocol=1
max_allowed_packet=16M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Answer 1

It sounds like restarting the master wipes out the binary logs, for some reason. MySQL won't do that by itself; it is either receiving the command RESET MASTER at some point, or something in the server's startup is clearing out /var/run.

Answer 2

I've seen this once before when the config on the slave was using a DNS name to connect to the master. In my case the solution was to use the IP address instead.

Incidentally, when you say "I do a start slave;", you are stopping it first, are you not?

Answer 3

I have seen this before. Don't put your logs in the /var/run. If I recall correctly, the system erases the files in this directory automatically. Try putting your binlogs to another directory and see if your error shows up again.