RE: mySQL slave IO Running and SQL Running
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 >> >> > 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&#x
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 > > > 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=arch...@jab.org
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-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 > 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=arch...@jab.org
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 > 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`)' >
