List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:May 21 2009 2:21pm
Subject:RE: mySQL slave IO Running and SQL Running
View as plain text  
I'm not running with replication, but I AM often running the CLI against the
production and test environments at the same time. I found it invaluable to
add the line

prompt=\h >

to my.ini (I'm running on Windows). That adds the host name to the prompt,
and has saved my butt more than once.

>-----Original Message-----
>From: Daevid Vincent [mailto:daevid@stripped]
>Sent: Wednesday, May 20, 2009 4:49 PM
>To: mysql@stripped
>Cc: 'Gavin Towey'; 'Claudio Nanni'
>Subject: RE: mySQL slave IO Running and SQL Running
>
>Well, in 90% of our cases it is. Most often caused by some dumb-ass
>(usually
>me) doing an INSERT or UPDATE on the slave on accident since I'm often
>logged into it doing SELECTs but I sometimes need to 'debug' or 'test'
>something and forget which box I'm on. So I happily do my altering of
>the
>slave's data and check my pages (which now are reading from slave) and
>all
>looks great, only to realize that saving via the web page isn't working.
>I
>then spend some time pulling my hair out and debugging the page only to
>realize that the page is writing to master (as it should) but
>replication
>has shit the bed from my aforementioned dumb-assed-ness and then I have
>to
>run said incantation below to get the binlog to skip and sync up again.
>
>But I understand what you're trying to say and concur. Blindly skipping
>binlog SQL commands is not any way to solve a problem. Eyeballs have to
>view
>the Last_Error and act appropriately.
>
>The 'read-only' seems to be a great preventative step that we're going
>to
>take and hopefully that will stave off a good portion of my
>stupid-user-mistakes.
>
>> -----Original Message-----
>> From: Gavin Towey [mailto:gtowey@stripped]
>> Sent: Wednesday, May 20, 2009 1:20 PM
>> To: Claudio Nanni; Daevid Vincent
>> Cc: mysql@stripped
>> Subject: RE: mySQL slave IO Running and SQL Running
>>
>> 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-sla
>> ve.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.
>>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>infoshop.com




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