Replication breakage when Heartbeat Failover occurs
I want to fix a replication issue with a 2-node cluster (one active, one passive) that is using Heartbeat for failover. The nodes are in Master-Master configuration (that is, each is the slave and master of the other). I have several other hosts that are replication slaves from the active node. They connect to MySQL via TCP over an SSH tunnels. When failover occurs, the passive node becomes the active node. However the replication slaves stop replicating. The error from a log on one of the slaves is: Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [Note] Slave I/O thread: conn ected to master 'user@127.0.0.1:3307', replication started in log 'mysql-bin.00 0978' at position 23923243 Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( serve r_errno=1236) Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000978', position 23923243 I do not think this is an SSH tunnel issue. I believe this is because of inconsistent binary log file names and positions between the two nodes. Probably because one of the nodes had been in operation a lot longer than the other. At the moment I have to get replication going by dumping the master databases again, re-import to the slave hosts and bootstrap the slaves. What is the best way to make this consistent and ensure that replication continues smoothly after a failover (and failback) event? Thank you, Imran Chaudhry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication breakage when Heartbeat Failover occurs
Hi Imran, Have a look at MySQL MMM for Multi-Master Replication failover. The project is currently in refurbishment when ti comes to having a home, but you can start by looking at : http://mysql-mmm.org for information. This project is made for exactly what you want to achieve: Having multiple masters and multiple slaves with automatic failover. Hope this helps! Walter On Wed, Aug 12, 2009 at 09:08, Imran Chaudhry ichaud...@gmail.com wrote: I want to fix a replication issue with a 2-node cluster (one active, one passive) that is using Heartbeat for failover. The nodes are in Master-Master configuration (that is, each is the slave and master of the other). I have several other hosts that are replication slaves from the active node. They connect to MySQL via TCP over an SSH tunnels. When failover occurs, the passive node becomes the active node. However the replication slaves stop replicating. The error from a log on one of the slaves is: Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [Note] Slave I/O thread: conn ected to master 'user@127.0.0.1:3307', replication started in log 'mysql-bin.00 0978' at position 23923243 Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( serve r_errno=1236) Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log Jul 15 07:43:32 host mysqld[1339]: 090715 7:43:32 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000978', position 23923243 I do not think this is an SSH tunnel issue. I believe this is because of inconsistent binary log file names and positions between the two nodes. Probably because one of the nodes had been in operation a lot longer than the other. At the moment I have to get replication going by dumping the master databases again, re-import to the slave hosts and bootstrap the slaves. What is the best way to make this consistent and ensure that replication continues smoothly after a failover (and failback) event? Thank you, Imran Chaudhry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@olindata.com -- Walter Heck, Engineer @ Open Query (http://openquery.com) Affordable Training and ProActive Support for MySQL related technologies Follow our blog at http://openquery.com/blog/ OurDelta: free enhanced builds for MySQL @ http://ourdelta.org
Re: Replication - connecting a slave to a master on the same host via a port or socket
You can easily get around that behaviour by specifying 127.0.0.1, though :-) On Tue, Aug 11, 2009 at 8:56 PM, Gavin Towey gto...@ffn.com wrote: Andrew, Yes it's true, because when you specify localhost, you're using the local socket file. The port only has meaning for TCP connections. Regards, Gavin Towey -Original Message- From: Andrew Braithwaite [mailto:andrew.braithwa...@lovefilm.com] Sent: Tuesday, August 11, 2009 8:38 AM To: mysql@lists.mysql.com Subject: RE: Replication - connecting a slave to a master on the same host via a port or socket Ah. I have found that if you use 'localhost' to connect, you cannot specify a port, it silently fails... You can connect using a hostname (even though it's the same server), specifying a port... Andrew -Original Message- From: Andrew Braithwaite [mailto:andrew.braithwa...@lovefilm.com] Sent: 11 August 2009 16:12 To: mysql@lists.mysql.com Subject: Replication - connecting a slave to a master on the same host via a port or socket Hi, I have 2 mysql instances running on a server on different ports with different datadirs and different .sock files. I can connect locally via the sock with the -S flag to mysql but I cannot connect locally via port (-P flag). Does anyone know if there is a way to configure a mysql slave to use a socket to connect to a master on localhost? If not; does anyone know a way to connect to another mysql instance on the same linux server using a port? Thanks for your help, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.braithwa...@lovefilm.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED.
MySQL and Relevance
Anyone know of a way to do a query within MySQL kind of like a relevance but without the Full Text index? I am searching on country, region and city. Now, that's easy enough but what if I need to sort by full match down to partial and no matches but always get something like always a found set of 5 regardless? The country is searched as a 2 letter ANSI code and the region and city are searched as plain varchar fields. Any suggestions are welcome. Thanks Steffan --- T E L 6 0 2 . 7 9 3 . 0 0 1 4 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline stef...@execuchoice.net Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 YAHOO : Steffan_Cline MSN : stef...@hldns.com GOOGLE: Steffan.Cline Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
