List:General Discussion« Previous MessageNext Message »
From:Gavin Towey Date:May 20 2009 8:19pm
Subject:RE: mySQL slave IO Running and SQL Running
View as plain text  
Please note that this is *NOT* a way to "get them synched again"

In fact if you have to skip a replication statement on the slave then it is usually a sign
your slave has different data than you master already.  Skipping statements/errors may
keep replication running, but you're just masking problems.



-----Original Message-----
From: Claudio Nanni [mailto:claudio.nanni@stripped]
Sent: Wednesday, May 20, 2009 12:49 PM
To: Daevid Vincent
Cc: mysql@stripped
Subject: Re: mySQL slave IO Running and SQL Running

Yeah Daevid!  I know very well the issue!

first set the slave to READ ONLY

[mysqld]
read-only

then there is a configuration option to tell the server to skip some type of
errors automatically

slave-skip-errors=

http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_slave-skip-errors


But, But, BUT!

What I did is to remove the constraint on the table of the slave so that you
can control better the thing.
Because if you systematically skip the 'foreign key forcing' error, you will
skip them with any table,
if you remove just that constraint on that table you have the situation more
under control.

I think one of these two are enough, the cron is very not recomended!

Ciao

Claudio



2009/5/20 Daevid Vincent <daevid@stripped>

> We have a master / slave setup and as you know, one bad query can ruin your
> whole day. Or if you accidentally write to the slave when you meant to
> write
> to the master, or any number of other things that break the fragility of a
> replication setup.
>
> The magic incantation to get them synched again seems to be to login to the
> slave and do this (over and over again until the Slave_IO_Running and
> Slave_SQL_Running both say "Yes"):
>
> mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show
> slave status\G
>
> Is there a way to automate this a little bit. Maybe some bash script that
> uses "mysql -e" and parses for those two strings?
> Is this dangerous to do?
> Is there a setting to have the slave do this already?
>
> In every case I've ever seen, it's always some SQL that got out of whack
> like this:
>
> Last_Error: Error 'Duplicate key name 'id_operator'' on query. Default
> database: 'core'. Query: 'ALTER TABLE `user_has_notification` ADD INDEX
> `id_operator` (`id_operator`)'
>

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.
Thread
mySQL slave IO Running and SQL RunningDaevid Vincent20 May
  • Re: mySQL slave IO Running and SQL RunningClaudio Nanni20 May
    • RE: mySQL slave IO Running and SQL RunningGavin Towey20 May
      • RE: mySQL slave IO Running and SQL RunningDaevid Vincent20 May
        • RE: mySQL slave IO Running and SQL RunningJerry Schwartz21 May