Re: Binary Logs and Transactions (with InnoDB and MyISAM)
Basically, only error-free statements are replicated.to the slave, thus ensuring that constraints are satisfied. Regards, Chris David Griffiths wrote: Thanks for the reply. So InnoDB (and even MyISAM) use transactions (expected with InnoDB) and slaves track their position in the binary log files as they are reading them in so as not to violate any constraints? David - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: David Griffiths [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, February 13, 2004 5:46 PM Subject: Re: Binary Logs and Transactions (with InnoDB and MyISAM) Hi David, David Griffiths wrote: From reading the docs, a binary log is an efficient representation of all data-modifying SQL that is run on the master database. I was unable to figure out what happens if a slave is interrupted while in the middle of processing a binary log. When a binary log is applied to a slave database, what happens if the machine or database dies half way through the log? Basically, the slave will try to catch up when it restarts. For example, with InnoDB, say the following statements are run and stored in the binary log: --- 1) INSERT INTO table_a (column_a, column_b, ...) VALUES (...); 2) COMMIT; 3) UPDATE table_a SET column_b = 'some_value' WHERE column_c = 'something_else'; 4) COMMIT; - The slave-machine (also using InnoDB tables) start processing the binary log. Statement 1 and 2 are processed, but it dies before Statement 3 (UPDATE table_a...) is executed. If I recall correctly, the binary log uses transactions as it's basic units. I'm not even sure if the slave will see statement 3 before statement 4, but I know it definitely will not act on it in any way. If you restart the slave, would it start the binary log back at the beginning, or is the offset inside the file stored in the database so that the slave database starts at Statement 3? Or would the whole log be re-processed (potentially causing problems with inserting rows with unique keys)? The slave won't try to reperform actions that are already processed. You can relax regarding unique attributes. How does this work with MyISAM? In essentially the same way. As each statement is basically bound with BEGIN and COMMIT statements, each statement is processed by the slave after it successfully completes on the master. Thanks, David Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Binary Logs and Transactions (with InnoDB and MyISAM)
From reading the docs, a binary log is an efficient representation of all data-modifying SQL that is run on the master database. I was unable to figure out what happens if a slave is interrupted while in the middle of processing a binary log. When a binary log is applied to a slave database, what happens if the machine or database dies half way through the log? For example, with InnoDB, say the following statements are run and stored in the binary log: --- 1) INSERT INTO table_a (column_a, column_b, ...) VALUES (...); 2) COMMIT; 3) UPDATE table_a SET column_b = 'some_value' WHERE column_c = 'something_else'; 4) COMMIT; - The slave-machine (also using InnoDB tables) start processing the binary log. Statement 1 and 2 are processed, but it dies before Statement 3 (UPDATE table_a...) is executed. If you restart the slave, would it start the binary log back at the beginning, or is the offset inside the file stored in the database so that the slave database starts at Statement 3? Or would the whole log be re-processed (potentially causing problems with inserting rows with unique keys)? How does this work with MyISAM? Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Binary Logs and Transactions (with InnoDB and MyISAM)
Hi David, David Griffiths wrote: From reading the docs, a binary log is an efficient representation of all data-modifying SQL that is run on the master database. I was unable to figure out what happens if a slave is interrupted while in the middle of processing a binary log. When a binary log is applied to a slave database, what happens if the machine or database dies half way through the log? Basically, the slave will try to catch up when it restarts. For example, with InnoDB, say the following statements are run and stored in the binary log: --- 1) INSERT INTO table_a (column_a, column_b, ...) VALUES (...); 2) COMMIT; 3) UPDATE table_a SET column_b = 'some_value' WHERE column_c = 'something_else'; 4) COMMIT; - The slave-machine (also using InnoDB tables) start processing the binary log. Statement 1 and 2 are processed, but it dies before Statement 3 (UPDATE table_a...) is executed. If I recall correctly, the binary log uses transactions as it's basic units. I'm not even sure if the slave will see statement 3 before statement 4, but I know it definitely will not act on it in any way. If you restart the slave, would it start the binary log back at the beginning, or is the offset inside the file stored in the database so that the slave database starts at Statement 3? Or would the whole log be re-processed (potentially causing problems with inserting rows with unique keys)? The slave won't try to reperform actions that are already processed. You can relax regarding unique attributes. How does this work with MyISAM? In essentially the same way. As each statement is basically bound with BEGIN and COMMIT statements, each statement is processed by the slave after it successfully completes on the master. Thanks, David Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Binary Logs and Transactions (with InnoDB and MyISAM)
Thanks for the reply. So InnoDB (and even MyISAM) use transactions (expected with InnoDB) and slaves track their position in the binary log files as they are reading them in so as not to violate any constraints? David - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: David Griffiths [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, February 13, 2004 5:46 PM Subject: Re: Binary Logs and Transactions (with InnoDB and MyISAM) Hi David, David Griffiths wrote: From reading the docs, a binary log is an efficient representation of all data-modifying SQL that is run on the master database. I was unable to figure out what happens if a slave is interrupted while in the middle of processing a binary log. When a binary log is applied to a slave database, what happens if the machine or database dies half way through the log? Basically, the slave will try to catch up when it restarts. For example, with InnoDB, say the following statements are run and stored in the binary log: --- 1) INSERT INTO table_a (column_a, column_b, ...) VALUES (...); 2) COMMIT; 3) UPDATE table_a SET column_b = 'some_value' WHERE column_c = 'something_else'; 4) COMMIT; - The slave-machine (also using InnoDB tables) start processing the binary log. Statement 1 and 2 are processed, but it dies before Statement 3 (UPDATE table_a...) is executed. If I recall correctly, the binary log uses transactions as it's basic units. I'm not even sure if the slave will see statement 3 before statement 4, but I know it definitely will not act on it in any way. If you restart the slave, would it start the binary log back at the beginning, or is the offset inside the file stored in the database so that the slave database starts at Statement 3? Or would the whole log be re-processed (potentially causing problems with inserting rows with unique keys)? The slave won't try to reperform actions that are already processed. You can relax regarding unique attributes. How does this work with MyISAM? In essentially the same way. As each statement is basically bound with BEGIN and COMMIT statements, each statement is processed by the slave after it successfully completes on the master. Thanks, David Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
