[OT] MySQL Replication Question

[Rick Draper]

Hi All,

We have a successful MySQL replication set up running for quite some
time without so much as a glitch.  The two database servers sit under a
load balancer and Lasso points to the virtual IP... All works very well.


This afternoon I was running a very lengthy import of records from an
Excel spreadsheet (100,000+ records), which took a couple of hours in
total to run.  One of our clients noticed some "intermittently"  missing
records when doing a search via Lasso, which turned out to be related to
which database the request was passed to - we had apparently lost
synchronisation, but when I checked the logs everything looks normal. I
stopped and started MySQL on one of the servers and the log shows
reconnecting to the master all OK.

My question is whether I should just leave it alone overnight to "catch
up" to see if there is some other way to check that the synchronisation
is working ??  New records are being added to the databases, but they
are well out of synch at this point.

Does anyone have any experience I can draw on in this regard?
 
Very best regards,
 
Rick Draper

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[Chris Wik-3]

Hi Rick

The first step is to run 'SHOW SLAVE STATUS\G' and see if that reports  
any errors. In particular check the Last_error, Slave_IO_Running and  
Slave_SQL_Running.

If it is simply a case of waiting for replication to catch up, you'll  
see a value other than 0 in Seconds_Behind_Master.

Here is some troubleshooting info from the MySQL documentation site: http://dev.mysql.com/doc/refman/5.0/en/replication-problems.html

HTH

Chris


On 23 Jul 2008, at 07:42, Rick Draper wrote:

> Hi All,
>
> We have a successful MySQL replication set up running for quite some
> time without so much as a glitch.  The two database servers sit  
> under a
> load balancer and Lasso points to the virtual IP... All works very  
> well.
>
>
> This afternoon I was running a very lengthy import of records from an
> Excel spreadsheet (100,000+ records), which took a couple of hours in
> total to run.  One of our clients noticed some "intermittently"  
> missing
> records when doing a search via Lasso, which turned out to be  
> related to
> which database the request was passed to - we had apparently lost
> synchronisation, but when I checked the logs everything looks  
> normal. I
> stopped and started MySQL on one of the servers and the log shows
> reconnecting to the master all OK.
>
> My question is whether I should just leave it alone overnight to  
> "catch
> up" to see if there is some other way to check that the  
> synchronisation
> is working ??  New records are being added to the databases, but they
> are well out of synch at this point.
>
> Does anyone have any experience I can draw on in this regard?
>
> Very best regards,
>
> Rick Draper

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[Rick Draper]

Hi Chris,

Thanks for your response, it is sincerely appreciated.
Slave_IO_Running is yes, Slave_SQL_Running is no - there is a duplicate
key error showing with one of the records.

This must have been something caused by the import through Navicat being
so large and things still going on with the database.  What do you
recommend I do to proceed - I managed to get the replication happening,
but am daunted by the prospect of bringing them back into alignment and
restarting slave/master/slave/master relationship.

Thanks again for your assistance.

Very best regards,
 
Rick
 

-----Original Message-----
From: Lasso Talk [mailto:lasso@...] On Behalf Of Chris
Wik
Sent: Wednesday, 23 July 2008 6:31 PM
To: Lasso Talk
Subject: Re: [OT] MySQL Replication Question

Hi Rick

The first step is to run 'SHOW SLAVE STATUS\G' and see if that reports
any errors. In particular check the Last_error, Slave_IO_Running and
Slave_SQL_Running.

If it is simply a case of waiting for replication to catch up, you'll
see a value other than 0 in Seconds_Behind_Master.

Here is some troubleshooting info from the MySQL documentation site:
http://dev.mysql.com/doc/refman/5.0/en/replication-problems.html

HTH

Chris


On 23 Jul 2008, at 07:42, Rick Draper wrote:

> Hi All,
>
> We have a successful MySQL replication set up running for quite some
> time without so much as a glitch.  The two database servers sit under
> a load balancer and Lasso points to the virtual IP... All works very
> well.
>
>
> This afternoon I was running a very lengthy import of records from an
> Excel spreadsheet (100,000+ records), which took a couple of hours in
> total to run.  One of our clients noticed some "intermittently"  
> missing
> records when doing a search via Lasso, which turned out to be related
> to which database the request was passed to - we had apparently lost
> synchronisation, but when I checked the logs everything looks normal.
> I stopped and started MySQL on one of the servers and the log shows
> reconnecting to the master all OK.
>
> My question is whether I should just leave it alone overnight to
> "catch up" to see if there is some other way to check that the
> synchronisation is working ??  New records are being added to the
> databases, but they are well out of synch at this point.
>
> Does anyone have any experience I can draw on in this regard?
>
> Very best regards,
>
> Rick Draper

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/




--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[Rick Draper]

Hi Chris,

I zapped the apparent duplicate record, restarted MySQL and got no error
this time when I ran 'SHOW SLAVE STATUS\G'... I will check it again in a
few minutes and see what is happening



Thanks and very best regards,
 
Rick
 

-----Original Message-----
From: Lasso Talk [mailto:lasso@...] On Behalf Of Rick
Draper
Sent: Wednesday, 23 July 2008 7:13 PM
To: Lasso Talk
Subject: Re: [OT] MySQL Replication Question

Hi Chris,

Thanks for your response, it is sincerely appreciated.
Slave_IO_Running is yes, Slave_SQL_Running is no - there is a duplicate
key error showing with one of the records.

This must have been something caused by the import through Navicat being
so large and things still going on with the database.  What do you
recommend I do to proceed - I managed to get the replication happening,
but am daunted by the prospect of bringing them back into alignment and
restarting slave/master/slave/master relationship.

Thanks again for your assistance.

Very best regards,
 
Rick
 

-----Original Message-----
From: Lasso Talk [mailto:lasso@...] On Behalf Of Chris
Wik
Sent: Wednesday, 23 July 2008 6:31 PM
To: Lasso Talk
Subject: Re: [OT] MySQL Replication Question

Hi Rick

The first step is to run 'SHOW SLAVE STATUS\G' and see if that reports
any errors. In particular check the Last_error, Slave_IO_Running and
Slave_SQL_Running.

If it is simply a case of waiting for replication to catch up, you'll
see a value other than 0 in Seconds_Behind_Master.

Here is some troubleshooting info from the MySQL documentation site:
http://dev.mysql.com/doc/refman/5.0/en/replication-problems.html

HTH

Chris


On 23 Jul 2008, at 07:42, Rick Draper wrote:

> Hi All,
>
> We have a successful MySQL replication set up running for quite some
> time without so much as a glitch.  The two database servers sit under
> a load balancer and Lasso points to the virtual IP... All works very
> well.
>
>
> This afternoon I was running a very lengthy import of records from an
> Excel spreadsheet (100,000+ records), which took a couple of hours in
> total to run.  One of our clients noticed some "intermittently"  
> missing
> records when doing a search via Lasso, which turned out to be related
> to which database the request was passed to - we had apparently lost
> synchronisation, but when I checked the logs everything looks normal.
> I stopped and started MySQL on one of the servers and the log shows
> reconnecting to the master all OK.
>
> My question is whether I should just leave it alone overnight to
> "catch up" to see if there is some other way to check that the
> synchronisation is working ??  New records are being added to the
> databases, but they are well out of synch at this point.
>
> Does anyone have any experience I can draw on in this regard?
>
> Very best regards,
>
> Rick Draper

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/




--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/




--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[Marc Pope]

Rick:

I feel your pain with replication. We've been struggling with it for  
months. We constantly get sync errors.

We just started using MySQL Cluster and it's a lot better, however you  
need more servers to accomplish it (generally 4).

There was a recent bug with replication and key errors, so I'd  
recommend upgrading to the most recent mysql to
avoid some of the issues, but by far I don't feel replication is the  
best way to go. I've never been able to get it
rock solid.

Marc


On Jul 23, 2008, at 2:42 AM, Rick Draper wrote:

> Hi All,
>
> We have a successful MySQL replication set up running for quite some
> time without so much as a glitch.  The two database servers sit  
> under a
> load balancer and Lasso points to the virtual IP... All works very  
> well.
>
>
> This afternoon I was running a very lengthy import of records from an
> Excel spreadsheet (100,000+ records), which took a couple of hours in
> total to run.  One of our clients noticed some "intermittently"  
> missing
> records when doing a search via Lasso, which turned out to be  
> related to
> which database the request was passed to - we had apparently lost
> synchronisation, but when I checked the logs everything looks  
> normal. I
> stopped and started MySQL on one of the servers and the log shows
> reconnecting to the master all OK.
>
> My question is whether I should just leave it alone overnight to  
> "catch
> up" to see if there is some other way to check that the  
> synchronisation
> is working ??  New records are being added to the databases, but they
> are well out of synch at this point.
>
> Does anyone have any experience I can draw on in this regard?
>
> Very best regards,
>
> Rick Draper
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
> Manage your subscription: http://www.ListSearch.com/Lasso/
>

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[noah williamsson-2]

Rick Draper wrote:

> Hi Chris,
>
> Thanks for your response, it is sincerely appreciated.
> Slave_IO_Running is yes, Slave_SQL_Running is no - there is a duplicate
> key error showing with one of the records.
>
> This must have been something caused by the import through Navicat being
> so large and things still going on with the database.  What do you
> recommend I do to proceed - I managed to get the replication happening,
> but am daunted by the prospect of bringing them back into alignment and
> restarting slave/master/slave/master relationship.

You are running in multi-master mode, aren't you?
That is, both servers are masters and slaves, and both are configured
with the same value for 'auto_increment_increment' and with different
values for 'auto_increment_offset'.

Because of the error you need to investigate why it happened and
understand what the exact effects it might have had on your data - if
you care the data and the consistency of relations in the database
(provided that you're not using InnoDB with foreign keys).

You might use 'SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1' and 'START SLAVE' to
get past that particular error. Look at the output from 'SHOW SLAVE
STATUS' and see if it stops again afterwards.

  -- noah

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[Rick Draper]

Hi Marc,

Up until this point, we have had the opposite experience - this current
configuration has been rock solid since the day it was activated.  I
certainly experienced pain on previous attempts to set it up properly.
In the end I created two new servers, set up the database structures on
both to match my previous MySQL server.  Stopped all external access to
my original server so no new changes would be made, synchronised the
data from the original server to the two new ones using Navicat, set up
the Master Slave configuration on both (closely following the MySQL
manual), and was somewhat amazed when I started it running and it didn't
miss a beat.

I am going to pick up on Noah's point and see if I can identify why the
duplicate entry was created on this occasion.  The offset has been
working brilliantly, and of course allows me to tell which MySQL server
handled the record creation request - there must have been something
about what was being done at the time of this huge import and I need to
track it down to ensure it doesn't happen again.

Thanks to all those who chimed in on this, I really do appreciate it.


Very best regards,
 
Rick
 

-----Original Message-----
From: Lasso Talk [mailto:lasso@...] On Behalf Of Marc
Sent: Wednesday, 23 July 2008 11:43 PM
To: Lasso Talk
Subject: Re: [OT] MySQL Replication Question

Rick:

I feel your pain with replication. We've been struggling with it for
months. We constantly get sync errors.

We just started using MySQL Cluster and it's a lot better, however you
need more servers to accomplish it (generally 4).

There was a recent bug with replication and key errors, so I'd recommend
upgrading to the most recent mysql to avoid some of the issues, but by
far I don't feel replication is the best way to go. I've never been able
to get it rock solid.

Marc


On Jul 23, 2008, at 2:42 AM, Rick Draper wrote:

> Hi All,
>
> We have a successful MySQL replication set up running for quite some
> time without so much as a glitch.  The two database servers sit under
> a load balancer and Lasso points to the virtual IP... All works very
> well.
>
>
> This afternoon I was running a very lengthy import of records from an
> Excel spreadsheet (100,000+ records), which took a couple of hours in
> total to run.  One of our clients noticed some "intermittently"  
> missing
> records when doing a search via Lasso, which turned out to be related
> to which database the request was passed to - we had apparently lost
> synchronisation, but when I checked the logs everything looks normal.
> I stopped and started MySQL on one of the servers and the log shows
> reconnecting to the master all OK.
>
> My question is whether I should just leave it alone overnight to
> "catch up" to see if there is some other way to check that the
> synchronisation is working ??  New records are being added to the
> databases, but they are well out of synch at this point.
>
> Does anyone have any experience I can draw on in this regard?
>
> Very best regards,
>
> Rick Draper

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[noah williamsson-2]

> I am going to pick up on Noah's point and see if I can identify why the
> duplicate entry was created on this occasion.  The offset has been
> working brilliantly, and of course allows me to tell which MySQL server
> handled the record creation request - there must have been something
> about what was being done at the time of this huge import and I need to
> track it down to ensure it doesn't happen again.

The MySQL produces so called "binlogs" to which most queries are
written. You can examine those with the mysqlbinlog program.
Since you know the point in time and the ID of the offending record, you
should be able to find the surrounding queries that eventually caused
this error.

  -- noah

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[Cory Robin-3]

Thought I'd chime in here...

There's a 'better' way to scale MySQL than replication.   We use it
here.   There's open source and a commercial version available.

http://sequoia.continuent.org/HomePage  (open source version)

Basically, it uses a 'controller' to simultaneously update all backend
SQL sources at the same time.

It's a totally sexy technology for those of you that need to scale
your MySQL installations for either high load or high availability.

...my two cents...

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/