SEO

I upgraded my slave server a few weeks ago, and the slave failed, with
an error similar to the one shown below.

I rolled back my upgrade, and it started working again, so I forgot
about it.

Today, I upgraded the master (again, from 5.0.32 to 5.0.42) - and hte
slave failed again.

I thought upgrading the slave to match the master might help, but still
it failed. Below is the error.

The hostname did *not* change.


070612 13:35:09 [Warning] No argument was provided to --log-bin, and
--log-bin-index was not used; so replication may break when this MySQL
server acts as a master and has his hostname changed!! Please use
'--log-bin=/var/run/mysqld/mysqld-bin' to avoid this problem.

070612 13:35:09 InnoDB: Started; log sequence number 40 824537593
070612 13:35:09 [Warning] Neither --relay-log nor --relay-log-index were
used; so replication may break when this MySQL server acts as a slave
and has his hostname changed!! Please use
'--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
070612 13:35:09 [ERROR] Failed to open the relay log
'./xian-relay-bin.000962' (relay_log_pos 284157529)
070612 13:35:09 [ERROR] Could not find target log during relay log
initialization
070612 13:35:09 [ERROR] Failed to initialize the master info structure
070612 13:35:09 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.42-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306
Gentoo Linux mysql-5.0.42

Any ideas/suggestions welcome, reseeding the slave will literally take days.

--
Ian P. Christian ~ http://pookey.co.uk


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGbpnA6jsuvK/fgkoRAg2RAKC7w0WJdVChh7WdWJOjm4OVg0bUHQCdHYhf
Cve99hQS+FZHR+y8BKpvVQ8=
=9N6n
-----END PGP SIGNATURE-----

Ian P. Christian wrote:
> I upgraded my slave server a few weeks ago, and the slave failed, with
> an error similar to the one shown below.


I have figured out what happened here now - and I'm part of the way
though fixing it.....

It turned out the defaults had changed somewhere, and rather then using
/var/lib/mysql/<hostname>-bin, it was using /var/run/mysql/mysqld-bin
(and the same change fro relay logs too).

Now... I've changed the slave to use it's correct logs now - however, if
I do the same on the master, I'll have the last 4 hours of logs in
/var/run/mysql/mysqld-bin ignored.

Somehow, I need to get the slave to catch up with the master's old logs
in /var/lib/mysql/<hostname>-bin, and then continue from the brand new
logs in /var/run/mysql/mysqld-bin

This is an awful mess, and I'm not sure it's recoverable - perhaps it is.

In theory, I should be able to find out where the slave was up to in the
old logs, extract them manually and replay them on the slave, and then
reset the slave to use the new logs - however i'm not sure how reliable
that's going to be - or even how to go about doing it yet.

Ideas anyone?

--
Ian P. Christian ~ http://pookey.co.uk


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGbrEJ6jsuvK/fgkoRAuYsAKDacxWKcCN7FQC3BL8r8Gtgq4iTGACfaza+
W73ddnJE9m0A6Ozmo6r6uaI=
=QfY/
-----END PGP SIGNATURE-----

Just to clarify - are you asking for suggestions regarding avoiding
re-seeding the slave or regarding what is likely to have gone wrong?

Generally, a newer slave can cope with an older master, but not the other
way around. If you updated the master while slave was out of date, you may
be out of options.

For what it's worth, LOAD DATA FROM MASTER tends to be much faster and
more reliable than copying tar balls across if you have to re-seed.

The error indicates that there's a problem with that version, though. Have
you tried any versions between 32 and 42?

Gordan

On Tue, 12 Jun 2007, Ian P. Christian wrote:

> I upgraded my slave server a few weeks ago, and the slave failed, with
> an error similar to the one shown below.
>
> I rolled back my upgrade, and it started working again, so I forgot
> about it.
>
> Today, I upgraded the master (again, from 5.0.32 to 5.0.42) - and hte
> slave failed again.
>
> I thought upgrading the slave to match the master might help, but still
> it failed. Below is the error.
>
> The hostname did *not* change.
>
>
> 070612 13:35:09 [Warning] No argument was provided to --log-bin, and
> --log-bin-index was not used; so replication may break when this MySQL
> server acts as a master and has his hostname changed!! Please use
> '--log-bin=/var/run/mysqld/mysqld-bin' to avoid this problem.
>
> 070612 13:35:09 InnoDB: Started; log sequence number 40 824537593
> 070612 13:35:09 [Warning] Neither --relay-log nor --relay-log-index were
> used; so replication may break when this MySQL server acts as a slave
> and has his hostname changed!! Please use
> '--relay-log=/var/run/mysqld/mysqld-relay-bin' to avoid this problem.
> 070612 13:35:09 [ERROR] Failed to open the relay log
> './xian-relay-bin.000962' (relay_log_pos 284157529)
> 070612 13:35:09 [ERROR] Could not find target log during relay log
> initialization
> 070612 13:35:09 [ERROR] Failed to initialize the master info structure
> 070612 13:35:09 [Note] /usr/sbin/mysqld: ready for connections.
> Version: '5.0.42-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306
> Gentoo Linux mysql-5.0.42
>
> Any ideas/suggestions welcome, reseeding the slave will literally take days.
>
>

"Ian P. Christian" <pookey@pookey.co.uk> wrote:
> In theory, I should be able to find out where the slave was up to in the
> old logs, extract them manually and replay them on the slave, and then
> reset the slave to use the new logs - however i'm not sure how reliable
> that's going to be - or even how to go about doing it yet.

Assuming your slave is not usable by client programs now anyway and
you don't mind it being unusable for a while longer, you can restart
the slaving from scratch:

1. take a full mysqldump of the master, --with-master-data --flush-logs
2. drop your databases on the slave, stop slaving, and restore the dump
3. restart slaving on the slave using the master data in from the dump

(see the mysql docs on how to set up replication if you need more detail)

-- Cos

Ofer Inbar wrote:
> Assuming your slave is not usable by client programs now anyway and
> you don't mind it being unusable for a while longer, you can restart
> the slaving from scratch:

This is exactly what I'm trying to avoid doing, it means 2 days downtime
whilst the data is re-inserted.

I have actually managed to fix it now though. I checked the old binary
log from the master, and it had no new data for the slave, so I simply
issued a 'CHANGE MASTER ...' on the slave to tell it to use the new
binary log file, with a position of 4 (the start) and off it when - back
to being in sync.

Why these defaults changed on a minor mysql release update is beyond me,
however I suspect this is gentoo's fault, not MySQLs.

--
Ian P. Christian ~ http://pookey.co.uk


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGbrXe6jsuvK/fgkoRAsy1AKDJJFMUC7WpKRQfE1Ib4u3KIx1X5wCfdeWn
vBRSnC5U2mr3SRejmhHwyfE=
=rpMr
-----END PGP SIGNATURE-----

On Tue, 12 Jun 2007, Ian P. Christian wrote:

> Ian P. Christian wrote:
> > I upgraded my slave server a few weeks ago, and the slave failed, with
> > an error similar to the one shown below.
>
>
> I have figured out what happened here now - and I'm part of the way
> though fixing it.....
>
> It turned out the defaults had changed somewhere, and rather then using
> /var/lib/mysql/<hostname>-bin, it was using /var/run/mysql/mysqld-bin
> (and the same change fro relay logs too).

I think you've just discovered why non-packaged distributions have
no place in production environment. Compiling things with defaults and
expecting it to work the same is asking for trouble most of the time.

> Now... I've changed the slave to use it's correct logs now - however, if
> I do the same on the master, I'll have the last 4 hours of logs in
> /var/run/mysql/mysqld-bin ignored.
>
> Somehow, I need to get the slave to catch up with the master's old logs
> in /var/lib/mysql/<hostname>-bin, and then continue from the brand new
> logs in /var/run/mysql/mysqld-bin
>
> This is an awful mess, and I'm not sure it's recoverable - perhaps it is.
>
> In theory, I should be able to find out where the slave was up to in the
> old logs, extract them manually and replay them on the slave, and then
> reset the slave to use the new logs - however i'm not sure how reliable
> that's going to be - or even how to go about doing it yet.
>
> Ideas anyone?

Has the slave started replicating from the new logs? If it has, you'll
find it will be quicker to re-seed the slave - even if it takes a day or
two. The only difference will be that in one case the slave will catch up
on it's own, and in the other, you'll also lose a day or two of your time
trawling through the logs manually trying to re-construct the data.

If it hasn't, you can probably change the big-log sequencing numbers and
change the pointers to file names and offsets in the index files on the
master and the slave, and hope for the best. With some luck, it'll work,
but I wouldn't count on it.

Gordan

Ian P. Christian wrote:
> Ofer Inbar wrote:
>> Assuming your slave is not usable by client programs now anyway and
>> you don't mind it being unusable for a while longer, you can restart
>> the slaving from scratch:
>
> This is exactly what I'm trying to avoid doing, it means 2 days downtime
> whilst the data is re-inserted.
>
> I have actually managed to fix it now though. I checked the old binary
> log from the master, and it had no new data for the slave, so I simply
> issued a 'CHANGE MASTER ...' on the slave to tell it to use the new
> binary log file, with a position of 4 (the start) and off it when - back
> to being in sync.

Now that you are back up, you probably have different data on the master and slave,
from the sounds of it. I've been there, often. I wrote two tools to help solve this
problem without a lot of overhead:

1) take table checksums via replication and discover which tables are different, if any.
2) efficient syncing of those tables, sending only the changes needed to reconcile
different data, via replication.

Both things need to work through replication, for obvious reasons. Both are available
at http://sourceforge.net/projects/mysqltoolkit. I would be glad to get your feedback
on them.

In practice, this has made it possible for me to re-initialize slaves in minutes
instead of half a day. And as you know, there are lots of things that can go wrong in
replication, so this is a Good Thing.

Cheers
Baron