MySQL upgrade from 5.0.32 to 5.0.42 broke replication
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 signature.asc Description: OpenPGP digital signature
Re: MySQL upgrade from 5.0.32 to 5.0.42 broke replication
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 signature.asc Description: OpenPGP digital signature
Re: MySQL upgrade from 5.0.32 to 5.0.42 broke replication
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL upgrade from 5.0.32 to 5.0.42 broke replication
Ian P. Christian [EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL upgrade from 5.0.32 to 5.0.42 broke replication
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 signature.asc Description: OpenPGP digital signature
Re: MySQL upgrade from 5.0.32 to 5.0.42 broke replication
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL upgrade from 5.0.32 to 5.0.42 broke replication
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
