RE: Fixing Databases When Replication Is Enabled?
I assume you are referring to this thread: http://lists.mysql.com/mysql/197528 If so, a very important question still stands: What version of MySQL are you using? MySQL replication uses the binary log (binlog) to pass update queries (INSERT, UPDATE, etc) to the slave; in other words it does not operate based on what data actually changed on the master. Take this statement for example: UPDATE table SET var = 'foo' WHERE seqid = 123 To simplify this example, let's just say that the above query is all that is written to the binlog. Once the slave asks for a replication update, the master will send the above query as-is. It does not say Record ID 123 changed var = 'foo'. So, if you are running MySQL 4.0 or less; the commands that mysqlcheck send the master will NOT be replicated to the slave. If you are running MySQL 4.1 or up; the commands that mysqlcheck (by default) send the master will be replicated to the slave. I hope this made sense. :) Atle - Flying Crocodile Inc, Unix Systems Administrator On Wed, 3 May 2006, Robinson, Eric wrote: So, just to be clear, when I run: mysqlcheck -r -f database_name Any fixes are recorded to the binlog and replicated to the slave? I want to be sure about this because someone in this forum said the opposite a couple of weeks ago. Thanks! --Eric -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 02, 2006 8:23 AM To: Marciano Cc: Robinson, Eric; mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? My Sincere apologies, I intented to mean mysqlcheck but somehow came out as myisamchk :) Kishore Jalleda On 5/2/06, Marciano [EMAIL PROTECTED] wrote: How myisamchk can write to binlog if the server need to be down? - Mensagem Original De: Kishore Jalleda [EMAIL PROTECTED] Para: Robinson, Eric [EMAIL PROTECTED] Cópia: mysql@lists.mysql.com Assunto: Re: Fixing Databases When Replication Is Enabled? Data: 01/05/06 22:25 Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Webmail Intercol http://www.intercol.com.br -- MySQL General Mailing List
RE: Fixing Databases When Replication Is Enabled?
So, just to be clear, when I run: mysqlcheck -r -f database_name Any fixes are recorded to the binlog and replicated to the slave? I want to be sure about this because someone in this forum said the opposite a couple of weeks ago. Thanks! --Eric -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 02, 2006 8:23 AM To: Marciano Cc: Robinson, Eric; mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? My Sincere apologies, I intented to mean mysqlcheck but somehow came out as myisamchk :) Kishore Jalleda On 5/2/06, Marciano [EMAIL PROTECTED] wrote: How myisamchk can write to binlog if the server need to be down? - Mensagem Original De: Kishore Jalleda [EMAIL PROTECTED] Para: Robinson, Eric [EMAIL PROTECTED] Cópia: mysql@lists.mysql.com Assunto: Re: Fixing Databases When Replication Is Enabled? Data: 01/05/06 22:25 Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Webmail Intercol http://www.intercol.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing Databases When Replication Is Enabled?
My Sincere apologies, I intented to mean mysqlcheck but somehow came out as myisamchk :) Kishore Jalleda On 5/2/06, Marciano [EMAIL PROTECTED] wrote: How myisamchk can write to binlog if the server need to be down? - Mensagem Original De: Kishore Jalleda [EMAIL PROTECTED] Para: Robinson, Eric [EMAIL PROTECTED] Cópia: mysql@lists.mysql.com Assunto: Re: Fixing Databases When Replication Is Enabled? Data: 01/05/06 22:25 Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Webmail Intercol http://www.intercol.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing Databases When Replication Is Enabled?
On Mon, 1 May 2006, Kishore Jalleda wrote: On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... This needs a bit clarification. First off, to my knowledge MySQL does not write [data] changes due to a REPAIR statement. Their replication implementation doesn't work that way. Before MySQL 4.1, REPAIR (and for instance OPTIMIZE) statements are not replicated at all, but are done so by default in 4.1 and up. So an important question is what version your MySQL server is. To answer the original posters question: It's hard to say if your slave is out of sync. If your master got corrupted from say hardware failure or if your MySQL daemon died unexpectedly and a REPAIR resulted in modified data, then your replication slave will most likely be out of sync and you will need to give it a fresh data seed. In that case a REPAIR statement on the slave will not fix the data consistency issue. I can't say this for sure, but I would imagine there aren't many scenarios where a REPAIR on a slave would fix consistency issues. Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing Databases When Replication Is Enabled?
On 5/2/06, Atle Veka [EMAIL PROTECTED] wrote: On Mon, 1 May 2006, Kishore Jalleda wrote: On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... This needs a bit clarification. First off, to my knowledge MySQL does not write [data] changes due to a REPAIR statement. Their replication implementation doesn't work that way. Before MySQL 4.1, REPAIR (and for instance OPTIMIZE) statements are not replicated at all, but are done so by default in 4.1 and up. So an important question is what version your MySQL server is. To answer the original posters question: It's hard to say if your slave is out of sync. If your master got corrupted from say hardware failure or if your MySQL daemon died unexpectedly and a REPAIR resulted in modified data, then your replication slave will most likely be out of sync and you will need to give it a fresh data seed. In that case a REPAIR statement on the slave will not fix the data consistency issue. I can't say this for sure, but I would imagine there aren't many scenarios where a REPAIR on a slave would fix consistency issues. Atle - Flying Crocodile Inc, Unix Systems Administrator I am using 5.0, and yes REPAIR TABLES is written by default to the Binlog. http://dev.mysql.com/doc/refman/5.0/en/repair-table.html Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing Databases When Replication Is Enabled?
On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fixing Databases When Replication Is Enabled?
I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fixing Databases When Replication Is Enabled?
Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]