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:dae...@daevid.com] >Sent: Wednesday, May 20, 2009 4:49 PM >To: mysql@lists.mysql.com >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:gto...@ffn.com] >> Sent: Wednesday, May 20, 2009 1:20 PM >> To: Claudio Nanni; Daevid Vincent >> Cc: mysql@lists.mysql.com >> 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.na...@gmail.com] >> Sent: Wednesday, May 20, 2009 12:49 PM >> To: Daevid Vincent >> Cc: mysql@lists.mysql.com >> 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 <dae...@daevid.com> >> >> > 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=jschwa...@the- >infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
