| List: | Commits | « Previous MessageNext Message » | |
| From: | mcbrown | Date: | August 7 2007 3:45pm |
| Subject: | svn commit - mysqldoc@docsrva: r7364 - trunk/refman-5.1 | ||
| View as plain text | |||
Author: mcbrown Date: 2007-08-07 15:45:22 +0200 (Tue, 07 Aug 2007) New Revision: 7364 Log: Documenting WL tasks: WL#3303 RBR: Engine-controlled logging format WL#3339 Issue warnings when statement-based replication may fail Consisted of the following: - Moved the binary logging format discussion from replication to binary log - Updated the information on formats and default modes - Extended the information on mixed format logging - Added notes on determination of logging format used with different engines and different states - Two new tables to describe determination process - Added information on warnings generated Modified: trunk/refman-5.1/dba-core.xml trunk/refman-5.1/renamed-nodes.txt trunk/refman-5.1/replication-configuration.xml Modified: trunk/refman-5.1/dba-core.xml =================================================================== --- trunk/refman-5.1/dba-core.xml 2007-08-07 10:39:03 UTC (rev 7363) +++ trunk/refman-5.1/dba-core.xml 2007-08-07 13:45:22 UTC (rev 7364) Changed blocks: 10, Lines Added: 812, Lines Deleted: 32; 33621 bytes @@ -5765,8 +5765,8 @@ </listitem> <listitem> - <para id="option_mysqld_max_prepared_stmt_count"> - <literal>max_prepared_stmt_count</literal> + <para id="option_mysqld_max_prepared_STATEMENT_count"> + <literal>max_prepared_STATEMENT_count</literal> </para> <para> @@ -6555,17 +6555,18 @@ </listitem> <listitem> - <para id="option_mysqld_prepared_stmt_count"> - <literal>prepared_stmt_count</literal> + <para id="option_mysqld_prepared_STATEMENT_count"> + <literal>prepared_STATEMENT_count</literal> </para> <para> The current number of prepared statements. (The maximum number of statements is given by the - <literal>max_prepared_stmt_count</literal> system + <literal>max_prepared_STATEMENT_count</literal> system variable.) This variable was added in MySQL 5.1.10. In MySQL 5.1.14, it was converted to the global - <literal>Prepared_stmt_count</literal> status variable. + <literal>Prepared_STATEMENT_count</literal> status + variable. </para> </listitem> @@ -8658,7 +8659,7 @@ <para> All of the - <literal>Com_stmt_<replaceable>xxx</replaceable></literal> + <literal>Com_STATEMENT_<replaceable>xxx</replaceable></literal> variables are increased even if a prepared statement argument is unknown or an error occurred during execution. In other words, their values correspond to the number of @@ -8668,7 +8669,7 @@ <para> The - <literal>Com_stmt_<replaceable>xxx</replaceable></literal> + <literal>Com_STATEMENT_<replaceable>xxx</replaceable></literal> status variables are as follows: </para> @@ -8676,37 +8677,37 @@ <listitem> <para> - <literal>Com_stmt_prepare</literal> + <literal>Com_STATEMENT_prepare</literal> </para> </listitem> <listitem> <para> - <literal>Com_stmt_execute</literal> + <literal>Com_STATEMENT_execute</literal> </para> </listitem> <listitem> <para> - <literal>Com_stmt_fetch</literal> + <literal>Com_STATEMENT_fetch</literal> </para> </listitem> <listitem> <para> - <literal>Com_stmt_send_long_data</literal> + <literal>Com_STATEMENT_send_long_data</literal> </para> </listitem> <listitem> <para> - <literal>Com_stmt_reset</literal> + <literal>Com_STATEMENT_reset</literal> </para> </listitem> <listitem> <para> - <literal>Com_stmt_close</literal> + <literal>Com_STATEMENT_close</literal> </para> </listitem> @@ -8718,11 +8719,13 @@ <literal>COM_<replaceable>xxx</replaceable></literal> command set used in the network layer. In other words, their values increase whenever prepared statement API - calls such as <command>mysql_stmt_prepare()</command>, - <command>mysql_stmt_execute()</command>, and so forth are - executed. However, <literal>Com_stmt_prepare</literal>, - <literal>Com_stmt_execute</literal> and - <literal>Com_stmt_close</literal> also increase for + calls such as + <command>mysql_STATEMENT_prepare()</command>, + <command>mysql_STATEMENT_execute()</command>, and so forth + are executed. However, + <literal>Com_STATEMENT_prepare</literal>, + <literal>Com_STATEMENT_execute</literal> and + <literal>Com_STATEMENT_close</literal> also increase for <literal>PREPARE</literal>, <literal>EXECUTE</literal>, or <literal>DEALLOCATE PREPARE</literal>, respectively. Additionally, the values of the older (available since @@ -8732,9 +8735,9 @@ <literal>Com_dealloc_sql</literal> increase for the <literal>PREPARE</literal>, <literal>EXECUTE</literal>, and <literal>DEALLOCATE PREPARE</literal> statements. - <literal>Com_stmt_fetch</literal> stands for the total - number of network round-trips issued when fetching from - cursors. + <literal>Com_STATEMENT_fetch</literal> stands for the + total number of network round-trips issued when fetching + from cursors. </para> </listitem> @@ -9754,14 +9757,14 @@ </listitem> <listitem> - <para id="option_mysqld_Prepared_stmt_count"> - <literal>Prepared_stmt_count</literal> + <para id="option_mysqld_Prepared_STATEMENT_count"> + <literal>Prepared_STATEMENT_count</literal> </para> <para> The current number of prepared statements. (The maximum number of statements is given by the - <literal>max_prepared_stmt_count</literal> system + <literal>max_prepared_STATEMENT_count</literal> system variable.) This variable was added in MySQL 5.1.14. </para> </listitem> @@ -28951,13 +28954,14 @@ <xref linkend="query-log"/>. </para> -<!-- -<para>The format of the events recorded in the binary log is dependent on the - binary logging format. Three format types are supported, row-based logging, - statement-based logging and mixed-base logging. The binry logging format used - deoends on the MySQL version. For more information on logging formats, see - <xref linkend="binary-log-formats"/>.</para> ---> + <para> + The format of the events recorded in the binary log is dependent + on the binary logging format. Three format types are supported, + row-based logging, statement-based logging and mixed-base + logging. The binry logging format used deoends on the MySQL + version. For more information on logging formats, see + <xref linkend="binary-log-formats"/>. + </para> <para> The primary purpose of the binary log is to be able to update @@ -29484,6 +29488,782 @@ be restarted from a fresh snapshot of the master's data. </para> + <section id="binary-log-formats"> + + <title>Binary Logging Formats</title> + + <para> + A number of different logging formats are used to record + information in the binary log. The exact format employed + depends on the version of MySQL being used. There are three + logging formats: + </para> + + <itemizedlist> + + <listitem> + <para> + Replication capabilities in MySQL originally were based on + propagation of SQL statements from master to slave. This + is called <emphasis>statement-based logging</emphasis> + (SBL). SBL is identified internally using + <literal>STATMENT</literal>. + </para> + </listitem> + + <listitem> + <para> + In <emphasis>row-based logging</emphasis> (RBL), the + master writes events to the binary log that indicate how + individual table rows are affected. Support for RBL was + added in MySQL 5.1.5. RBL is identified internally using + <literal>ROW</literal>. + </para> + </listitem> + + <listitem> + <para> + As of MySQL 5.1.8, a third option is available: + <emphasis>mixed-based logging</emphasis> (MBL). With MBL, + SBL is used by default, but automatically switches to RBL + in particular cases as described below. From MySQL 5.1.8, + MBL is the default mode. MBL is identified internally + using <literal>MIXED</literal>. + </para> + </listitem> + + </itemizedlist> + + <para> + Starting with MySQL 5.1.20, MBL is the default but storage + engine being used can also set or limit the logging format + according to the capabilities of the storage engine. This + helps to eliminate issues when logging, and more specifically + replicating, certain statements between the master and slave + using the different engines. + </para> + + </section> + + <section id="binary-log-setting"> + + <title>Setting The Binary Log Format</title> + + <para> + The default binary logging format depends on the version of + MySQL you are using: + </para> + + <itemizedlist> + + <listitem> + <para> + For MySQL 5.1.11 and earlier, statement-based logging is + used by default. + </para> + </listitem> + + <listitem> + <para> + For MySQL 5.1.12 and later, mixed-based logging is used by + default. + </para> + </listitem> + + </itemizedlist> + + <para> + You can force the default replication format by specifying the + format type to the + <option>--binlog-format=<replaceable>type</replaceable></option> + option. When set, all replication slaves connecting to the + server will read the events according to this setting. The + supported options are: + + <itemizedlist> + + <listitem> + <para> + <literal>ROW</literal> — sets row-based + replication as the default. + </para> + </listitem> + + <listitem> + <para> + <literal>STATEMENT</literal> — sets + statement-based replication as the default. This is the + default for MySQL 5.1.11 and earlier. + </para> + </listitem> + + <listitem> + <para> + <literal>MIXED</literal> — sets mixed-based + replication as the default. This is the default for + MySQL 5.1.12 and later. + </para> + </listitem> + + </itemizedlist> + </para> + + <para> + The logging format also can be switched at runtime. To specify + the format globally for all clients, set the global value of + the <literal>binlog_format</literal> system variable. (To + change a global variable you need the <literal>SUPER</literal> + privilege.) + </para> + + <para> + To switch to statement-based format, use either of these + statements: + </para> + +<programlisting> +mysql> <userinput>SET GLOBAL binlog_format = 'STATEMENT';</userinput> +mysql> <userinput>SET GLOBAL binlog_format = 1;</userinput> +</programlisting> + + <para> + To switch to row-based format, use either of these statements: + </para> + +<programlisting> +mysql> <userinput>SET GLOBAL binlog_format = 'ROW';</userinput> +mysql> <userinput>SET GLOBAL binlog_format = 2;</userinput> +</programlisting> + + <para> + To switch to mixed format, use either of these statements: + </para> + +<programlisting> +mysql> <userinput>SET GLOBAL binlog_format = 'MIXED';</userinput> +mysql> <userinput>SET GLOBAL binlog_format = 3;</userinput> +</programlisting> + + <para> + Individual clients can control the logging format for their + own statements by setting the session value of + <literal>binlog_format</literal>. For example: + </para> + +<programlisting> +mysql> <userinput>SET SESSION binlog_format = 'STATEMENT';</userinput> +mysql> <userinput>SET SESSION binlog_format = 'ROW';</userinput> +mysql> <userinput>SET SESSION binlog_format = 'MIXED';</userinput> +</programlisting> + + <para> + In addition to switching the logging format manually, a slave + server may switch the format + <emphasis>automatically</emphasis>. This happens when the + server is running in either <literal>STATEMENT</literal> or + <literal>MIXED</literal> format and encounters a row in the + binary log that is written in <literal>ROW</literal> logging + format. In that case, the slave switches to row-based + replication temporarily for that event, and switches back to + the previous format afterwards. + </para> + + <para> + There are two reasons why you might want to set replication + logging on a per-connection basis: + </para> + + <itemizedlist> + + <listitem> + <para> + A thread that makes many small changes to the database + might want to use row-based logging. A thread that + performs updates that match many rows in the + <literal>WHERE</literal> clause might want to use + statement-based logging because it will be more efficient + to log a few statements than many rows. + </para> + </listitem> + + <listitem> + <para> + Some statements require a lot of execution time on the + master, but result in just a few rows being modified. It + might therefore be beneficial to replicate them using + row-based logging. + </para> + </listitem> + + </itemizedlist> + + <para> + There are exceptions when you cannot switch the replication + format at runtime: + + <itemizedlist> + + <listitem> + <para> + From within a stored function or a trigger. + </para> + </listitem> + + <listitem> + <para> + If <literal>NDB</literal> is enabled. + </para> + </listitem> + + <listitem> + <para> + If the session is currently in row-based replication + mode and has open temporary tables. + </para> + </listitem> + + </itemizedlist> + + Trying to switch the format in those cases results in an + error. + </para> + + <para> + Switching the replication format at runtime is not recommended + when any <emphasis>temporary tables</emphasis> exist, because + temporary tables are logged only when using statement-based + replication, whereas with row-based replication they are not + logged. With mixed replication, temporary tables are usually + logged; exceptions happen with user-defined functions (UDF) + and with the <function>UUID()</function> function. + </para> + + <para> + With the binlog format set to <literal>ROW</literal>, many + changes are written to the binary log using the row-based + format. Some changes, however, still use the statement-based + format. Examples include all DDL (data definition language) + statements such as <literal>CREATE TABLE</literal>, + <literal>ALTER TABLE</literal>, or <literal>DROP + TABLE</literal>. + </para> + + <para> + The <option>--binlog-row-event-max-size</option> option is + available for servers that are capable of row-based + replication. Rows are stored into the binary log in chunks + having a size in bytes not exceeding the value of this option. + The value must be a multiple of 256. The default value is + 1024. + </para> + + <warning> + <para> + When using <emphasis>statement-based logging</emphasis> in a + replication scenario, it is possible for the data on the + master and slave to become different if a statement is + designed in such a way that the data modification is + <firstterm>non-deterministic</firstterm>; that is, it is + left to the will of the query optimizer. In general, this is + not a good practice even outside of replication. For a + detailed explanation of this issue, see + <xref linkend="open-bugs"/>. + </para> + </warning> + + </section> + + <section id="binary-log-mixed"> + + <title>Mixed Logging Format</title> + + <para> + When running in <literal>MIXED</literal> mode, automatic + switching from statement-based to row-based replication takes + place under the following conditions: + </para> + + <itemizedlist> + + <listitem> + <para> + When a DML statement updates an <literal>NDB</literal> + table + </para> + </listitem> + + <listitem> + <para> + When a function contains <literal>UUID()</literal> + </para> + </listitem> + + <listitem> + <para> + When 2 or more tables with + <literal>AUTO_INCREMENT</literal> columns are updated + </para> + </listitem> + + <listitem> + <para> + When any <literal>INSERT DELAYED</literal> is executed + </para> + </listitem> + + <listitem> + <para> + When the body of a view requires row-based replication, + the statement creating the view also uses it — for + example, this occurs when the statement creating a view + uses the <literal>UUID()</literal> function + </para> + </listitem> + + <listitem> + <para> + When a call to a UDF is involved. + </para> + </listitem> + + </itemizedlist> + + <note> + <para> + Starting with MySQL 5.1.20 a warning is generated if you try + to log execute a statement in statement-logging mode that + should be logged in row-logging mode, a warning will be + produced. The warning will be shown both in the client (and + available with <literal>SHOW WARNINGS</literal>) and through + the <command>mysqld</command> error log. Only one warning is + generated per client session. + </para> + </note> + + <para> + Starting with MySQL 5.1.20, mixed based logging is used by + default, but in addition to the decisions above, individual + engines can also enforce the logging format used when + information in a table is updated. + </para> + + <para> + The logging formats supported by a particular engine are + controlled by internal flags. The table below lists the + logging formats supported by each of the current engines. + </para> + + <informaltable> + <tgroup cols="3"> + <colspec colwidth="50*"/> + <colspec colwidth="25*"/> + <colspec colwidth="25*"/> + <thead> + <row> + <entry>Engine</entry> + <entry>Row-logging supported</entry> + <entry>Statement-logging supported</entry> + </row> + </thead> + <tbody> + <row> + <entry>Archive</entry> + <entry>Yes</entry> + <entry>No</entry> + </row> + <row> + <entry>Archive</entry> + <entry>Yes</entry> + <entry>Yes</entry> + </row> + <row> + <entry>Blackhole</entry> + <entry>No</entry> + <entry>Yes</entry> + </row> + <row> + <entry>CSV</entry> + <entry>Yes</entry> + <entry>Yes</entry> + </row> + <row> + <entry>Example</entry> + <entry>Yes</entry> + <entry>No</entry> + </row> + <row> + <entry>Federated</entry> + <entry>Yes</entry> + <entry>Yes</entry> + </row> + <row> + <entry>Heap</entry> + <entry>Yes</entry> + <entry>Yes</entry> + </row> + <row> + <entry>MyISAM</entry> + <entry>Yes</entry> + <entry>Yes</entry> + </row> + <row> + <entry>Merge</entry> + <entry>Yes</entry> + <entry>Yes</entry> + </row> + <row> + <entry>NDB</entry> + <entry>Yes</entry> + <entry>No</entry> + </row> + </tbody> + </tgroup> + </informaltable> + + <para> + Note that an engine can be support either or both logging + formats, and the logging capabilities of an individual engine + can be further defined as follows + </para> + + <itemizedlist> + + <listitem> + <para> + If an engine supports row-based logging, then the engine + is said to be <emphasis>row-logging capable + (RLC)</emphasis>. + </para> + </listitem> + + <listitem> + <para> + If an engine support statement-based logging, then the + engine is said to be <emphasis>statement-logging capable + (SLC)</emphasis>. + </para> + </listitem> + + </itemizedlist> + + <para> + When determining what logging mode to use, the capabilities of + all the tables in the event are combined. The set of tables is + then marked according to these rules: + </para> + + <itemizedlist> + + <listitem> + <para> + A set of tables is defined as <emphasis>row logging + restricted (RLR)</emphasis> if the tables are row logging + capable but not statement logging capable. + </para> + </listitem> + + <listitem> + <para> + A set of tables is defined as <emphasis>statement logging + restricted (SLR)</emphasis> if the tables are statement + logging capable but not row logging capable. + </para> + </listitem> + + </itemizedlist> + + <para> + Once the determination of the possible logging formats + required by the statement is complete it is compared to the + current <literal>BINLOG_FORMAT</literal> setting. The + following table is used to decide how the information is + recorded in the binary log or, if appropriate, whether an + error is raised. In the table, a safe operation is defined as + one that is deterministic. A number of internal rules decide + whether the statement is deterministic or not. + </para> + + <informaltable> + <tgroup cols="6"> + <colspec colwidth="10*" colname="safe"/> + <colspec colwidth="10*" colname="format"/> + <colspec colwidth="10*" colname="RLC"/> + <colspec colwidth="10*" colname="SLC"/> + <colspec colwidth="50*" colname="error"/> + <colspec colwidth="10*" colname="logas"/> + <spanspec namest="safe" nameend="SLC" spanname="condition"/> + <spanspec namest="error" nameend="logas" spanname="action"/> + <thead> + <row> + <entry spanname="condition">Condition</entry> + <entry>Action</entry> + </row> + <row> + <entry>Safe/unsafe</entry> + <entry><literal>BINLOG_FORMAT</literal></entry> + <entry>RLC</entry> + <entry>SLC</entry> + <entry>Error/Warning</entry> + <entry>Logged as</entry> + </row> + </thead> + <tbody> + <row> + <entry>Safe</entry> + <entry>STATEMENT</entry> + <entry>N</entry> + <entry>N</entry> + <entry>Error: not loggable</entry> + </row> + <row> + <entry>Safe</entry> + <entry>STATEMENT</entry> + <entry>N</entry> + <entry>Y</entry> + <entry></entry> + <entry>STATEMENT</entry> + </row> + <row> + <entry>Safe</entry> + <entry>STATEMENT</entry> + <entry>Y</entry> + <entry>N</entry> + <entry>Error: not loggable</entry> + </row> + <row> + <entry>Safe</entry> + <entry>STATEMENT</entry> + <entry>Y</entry> + <entry>Y</entry> + <entry></entry> + <entry>STATEMENT</entry> + </row> + <row> + <entry>Safe</entry> + <entry>MIXED</entry> + <entry>N</entry> + <entry>N</entry> + <entry>Error: not loggable</entry> + </row> + <row> + <entry>Safe</entry> + <entry>MIXED</entry> + <entry>N</entry> + <entry>Y</entry> + <entry></entry> + <entry>STATEMENT</entry> + </row> + <row> + <entry>Safe</entry> + <entry>MIXED</entry> + <entry>Y</entry> + <entry>N</entry> + <entry></entry> + <entry>ROW</entry> + </row> + <row> + <entry>Safe</entry> + <entry>MIXED</entry> + <entry>Y</entry> + <entry>Y</entry> + <entry></entry> + <entry>STATEMENT</entry> + </row> + <row> + <entry>Safe</entry> + <entry>ROW</entry> + <entry>N</entry> + <entry>N</entry> + <entry>Error: not loggable</entry> + </row> + <row> + <entry>Safe</entry> + <entry>ROW</entry> + <entry>N</entry> + <entry>Y</entry> + <entry>Error: not loggable</entry> + </row> + <row> + <entry>Safe</entry> + <entry>ROW</entry> + <entry>Y</entry> + <entry>N</entry> + <entry></entry> + <entry>ROW</entry> + </row> + <row> + <entry>Safe</entry> + <entry>ROW</entry> + <entry>Y</entry> + <entry>Y</entry> + <entry></entry> + <entry>ROW</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>STATEMENT</entry> + <entry>N</entry> + <entry>N</entry> + <entry>Error: not loggable</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>STATEMENT</entry> + <entry>N</entry> + <entry>Y</entry> + <entry>Warning: unsafe</entry> + <entry>STATEMENT</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>STATEMENT</entry> + <entry>Y</entry> + <entry>N</entry> + <entry>Error: not loggable</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>STATEMENT</entry> + <entry>Y</entry> + <entry>Y</entry> + <entry>Warning: unsafe</entry> + <entry>STATEMENT</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>MIXED</entry> + <entry>N</entry> + <entry>N</entry> + <entry>Error: not loggable</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>MIXED</entry> + <entry>N</entry> + <entry>Y</entry> + <entry>Error: not loggable</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>MIXED</entry> + <entry>Y</entry> + <entry>N</entry> + <entry></entry> + <entry>ROW</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>MIXED</entry> + <entry>Y</entry> + <entry>Y</entry> + <entry></entry> + <entry>ROW</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>ROW</entry> + <entry>N</entry> + <entry>N</entry> + <entry>Error: not loggable</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>ROW</entry> + <entry>N</entry> + <entry>Y</entry> + <entry>Error: not loggable</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>ROW</entry> + <entry>Y</entry> + <entry>N</entry> + <entry></entry> + <entry>ROW</entry> + </row> + <row> + <entry>Unsafe</entry> + <entry>ROW</entry> + <entry>Y</entry> + <entry>Y</entry> + <entry></entry> + <entry>ROW</entry> + </row> + </tbody> + </tgroup> + </informaltable> + + <para> + When a warning is produced by the determination, a standard + MySQL warning is produced (and is available using + <literal>SHOW WARNINGS</literal>). The information is also + written to the <command>mysqld</command> error log. Only one + error for each error instance per client connection is logged. + The log message will include the SQL statement that was + attempted. + </para> + + </section> + + <section id="binary-log-mysql-database"> + + <title>Logging Format for Changes to <literal>mysql</literal> Database Tables</title> + + <para> + The contents of the grant tables in the + <literal>mysql</literal> database can be modified directly + (for example, with <literal>INSERT</literal> or + <literal>DELETE</literal>) or indirectly (for example, with + <literal>GRANT</literal> or <literal>CREATE USER</literal>). + As of MySQL 5.1.17, statements that affect + <literal>mysql</literal> database tables are written to the + binary log using the following rules: + </para> + + <itemizedlist> + + <listitem> + <para> + Data manipulation statements that change data in + <literal>mysql</literal> database tables directly are + logged according to the setting of the + <literal>binlog_format</literal> system variable. This + pertains to statements such as <literal>INSERT</literal>, + <literal>UPDATE</literal>, <literal>DELETE</literal>, + <literal>REPLACE</literal>, <literal>DO</literal>, + <literal>LOAD DATA INFILE</literal>, + <literal>SELECT</literal>, and + <literal>TRUNCATE</literal>. + </para> + </listitem> + + <listitem> + <para> + Statements that change the <literal>mysql</literal> + database indirectly are logged as statements regardless of + the value of <literal>binlog_format</literal>. This + pertains to statements such as <literal>GRANT</literal>, + <literal>REVOKE</literal>, <literal>SET + PASSWORD</literal>, <literal>RENAME USER</literal>, + <literal>CREATE</literal> (all forms except + <literal>CREATE TABLE ... SELECT</literal>), + <literal>ALTER</literal> (all forms), and + <literal>DROP</literal> (all forms). + </para> + </listitem> + + </itemizedlist> + + <para> + <literal>CREATE TABLE ... SELECT</literal> is a combination of + data definition and data manipulation. The <literal>CREATE + TABLE</literal> part is logged using statement format and the + <literal>SELECT</literal> part is logged according to the + value of <literal>binlog_format</literal>. + </para> + + </section> + </section> <section id="slow-query-log"> Modified: trunk/refman-5.1/renamed-nodes.txt =================================================================== --- trunk/refman-5.1/renamed-nodes.txt 2007-08-07 10:39:03 UTC (rev 7363) +++ trunk/refman-5.1/renamed-nodes.txt 2007-08-07 13:45:22 UTC (rev 7364) Changed blocks: 1, Lines Added: 3, Lines Deleted: 0; 694 bytes @@ -323,6 +323,9 @@ rdo--rs-addnew-and-rs-update myodbc-examples-programming-vb-rdo reference language-structure remote-connection myodbc-examples-walkthrough +replication-formats-mixed binary-log-mixed +replication-formats-setting binary-log-formats-setting +replication-mysql-database binary-log-mysql-database replication-row-based replication-formats replication-upgrade-5-1 replication-upgrade reporting-mysqltest-bugs mysql-test-suite Modified: trunk/refman-5.1/replication-configuration.xml =================================================================== --- trunk/refman-5.1/replication-configuration.xml 2007-08-07 10:39:03 UTC (rev 7363) +++ trunk/refman-5.1/replication-configuration.xml 2007-08-07 13:45:22 UTC (rev 7364) Changed blocks: 4, Lines Added: 42, Lines Deleted: 357; 16201 bytes @@ -1233,8 +1233,12 @@ <para> Replication works because events written to the binary log are read from the master and then processed on the slave. The events - are recorded in different formats according the event being - recorded. The different formats are as follows: + are recorded within the binary log in different formats according + the type of event being recorded. The different replication + formats used correspond to the binary logging format used when the + events were recorded in the master's binary log. The correlation + between binary logging formats and the terms used during + replication are: </para> <itemizedlist> @@ -1243,47 +1247,64 @@ <para> Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is - called <emphasis>statement-based replication</emphasis> (SBR). + called <emphasis>statement-based replication</emphasis> (SBR) + and this correlates to the standard statement-based binary + logging format. Binary logging and replication in MySQL 5.1.4 + and earlier, and all previous versions of MySQL, used this + format. </para> </listitem> <listitem> <para> - In <emphasis>row-based replication</emphasis> (RBR), the - master writes events to the binary log that indicate how - individual table rows are affected. Support for RBR was added - in MySQL 5.1.5. + Row-based binary logging logs the physical changes to + individual table rows. In replication terms this is + <emphasis>row-based replication</emphasis> (RBR), the master + writes events to the binary log that indicate how individual + table rows are affected. Support for RBR was added in MySQL + 5.1.5. </para> </listitem> <listitem> <para> - As of MySQL 5.1.8, a third option is available: - <emphasis>mixed-based replication</emphasis> (MBR). With MBR, - statement-based replication is used by default, but - automatically switches to row-based replication in particular - cases as described below. See - <xref - linkend="replication-formats-mixed"/>. + As of MySQL 5.1.8, the binary logging format can be altered on + the fly according the event being logged. With mixed-based + logging (and the associated <emphasis>mixed-based + replication</emphasis> (MBR)), statement-based logging is used + by default, but automatically switches to row-based logging in + particular cases as described below. See + <xref linkend="binary-log-mixed"/>. </para> </listitem> </itemizedlist> <para> - Starting with MySQL 5.1.12, mixed-based replication (MBR) is the - default format for all replication environment unless you specify - otherwise. + Starting with MySQL 5.1.12, mixed-based replication (i.e. + mixed-based logging) is the default format for all replication + environment unless you specify otherwise. </para> <para> - For a comparison that shows the advantages and disadvantages of - statement-based and row-based replication, see - <xref linkend="replication-sbr-rbr"/>. + Starting with MySQL 5.1.20, the binary logging format used and + supported is determined by the storage engine being used. For more + information on mixed-based logging and the rules governing the + support of different logging formatsion, see + <xref linkend="binary-log-mixed"/>. </para> <para> - MySQL Cluster Replication also makes use of row-based replication. + There are some known limitations and issues between the different + replication formats. For a comparison that shows the advantages + and disadvantages of statement-based and row-based replication, + see <xref linkend="replication-sbr-rbr"/>. + </para> + + <para> + MySQL Cluster Replication makes use of row-based replication. The + NDB storage engine is incompatible with statement-based + replication and NDB sets row-based logging format automatically. For more information, see <xref linkend="mysql-cluster-replication"/>. </para> @@ -1302,286 +1323,6 @@ <option>--without-row-based-replication</option> option. </para> - <section id="replication-formats-setting"> - - <title>Setting The Replication Format</title> - - <para> - The default replication format depends on the version of MySQL - you are using: - </para> - - <itemizedlist> - - <listitem> - <para> - For MySQL 5.1.11 and earlier, statement-based replication is - used by default. - </para> - </listitem> - - <listitem> - <para> - For MySQL 5.1.12 and later, mixed-based replication is used - by default. - </para> - </listitem> - - </itemizedlist> - - <para> - You can force the default replication format by specifying the - format type to the - <option>--binlog-format=<replaceable>type</replaceable></option> - option. When set, all replication slaves connecting to the - server will read the events according to this setting. The - supported options are: - - <itemizedlist> - - <listitem> - <para> - <literal>ROW</literal> — sets row-based replication - as the default. - </para> - </listitem> - - <listitem> - <para> - <literal>STATEMENT</literal> — sets statement-based - replication as the default. This is the default for MySQL - 5.1.11 and earlier. - </para> - </listitem> - - <listitem> - <para> - <literal>MIXED</literal> — sets mixed-based - replication as the default. This is the default for MySQL - 5.1.12 and later. - </para> - </listitem> - - </itemizedlist> - </para> - - <para> - The logging format also can be switched at runtime. To specify - the format globally for all clients, set the global value of the - <literal>binlog_format</literal> system variable. (To change a - global variable you need the <literal>SUPER</literal> - privilege.) - </para> - - <para> - To switch to statement-based format, use either of these - statements: - </para> - -<programlisting> -mysql> <userinput>SET GLOBAL binlog_format = 'STATEMENT';</userinput> -mysql> <userinput>SET GLOBAL binlog_format = 1;</userinput> -</programlisting> - - <para> - To switch to row-based format, use either of these statements: - </para> - -<programlisting> -mysql> <userinput>SET GLOBAL binlog_format = 'ROW';</userinput> -mysql> <userinput>SET GLOBAL binlog_format = 2;</userinput> -</programlisting> - - <para> - To switch to mixed format, use either of these statements: - </para> - -<programlisting> -mysql> <userinput>SET GLOBAL binlog_format = 'MIXED';</userinput> -mysql> <userinput>SET GLOBAL binlog_format = 3;</userinput> -</programlisting> - - <para> - Individual clients can control the logging format for their own - statements by setting the session value of - <literal>binlog_format</literal>. For example: - </para> - -<programlisting> -mysql> <userinput>SET SESSION binlog_format = 'STATEMENT';</userinput> -mysql> <userinput>SET SESSION binlog_format = 'ROW';</userinput> -mysql> <userinput>SET SESSION binlog_format = 'MIXED';</userinput> -</programlisting> - - <para> - In addition to switching the logging format manually, a slave - server may switch the format <emphasis>automatically</emphasis>. - This happens when the server is running in either - <literal>STATEMENT</literal> or <literal>MIXED</literal> format - and encounters a row in the binary log that is written in - <literal>ROW</literal> logging format. In that case, the slave - switches to row-based replication temporarily for that event, - and switches back to the previous format afterwards. - </para> - - <para> - There are two reasons why you might want to set replication - logging on a per-connection basis: - </para> - - <itemizedlist> - - <listitem> - <para> - A thread that makes many small changes to the database might - want to use row-based logging. A thread that performs - updates that match many rows in the <literal>WHERE</literal> - clause might want to use statement-based logging because it - will be more efficient to log a few statements than many - rows. - </para> - </listitem> - - <listitem> - <para> - Some statements require a lot of execution time on the - master, but result in just a few rows being modified. It - might therefore be beneficial to replicate them using - row-based logging. - </para> - </listitem> - - </itemizedlist> - - <para> - There are exceptions when you cannot switch the replication - format at runtime: - - <itemizedlist> - - <listitem> - <para> - From within a stored function or a trigger. - </para> - </listitem> - - <listitem> - <para> - If <literal>NDB</literal> is enabled. - </para> - </listitem> - - <listitem> - <para> - If the session is currently in row-based replication mode - and has open temporary tables. - </para> - </listitem> - - </itemizedlist> - - Trying to switch the format in those cases results in an error. - </para> - - <para> - Switching the replication format at runtime is not recommended - when any <emphasis>temporary tables</emphasis> exist, because - temporary tables are logged only when using statement-based - replication, whereas with row-based replication they are not - logged. With mixed replication, temporary tables are usually - logged; exceptions happen with user-defined functions (UDF) and - with the <function>UUID()</function> function. - </para> - - <para> - With the binlog format set to <literal>ROW</literal>, many - changes are written to the binary log using the row-based - format. Some changes, however, still use the statement-based - format. Examples include all DDL (data definition language) - statements such as <literal>CREATE TABLE</literal>, - <literal>ALTER TABLE</literal>, or <literal>DROP - TABLE</literal>. - </para> - - <para> - The <option>--binlog-row-event-max-size</option> option is - available for servers that are capable of row-based replication. - Rows are stored into the binary log in chunks having a size in - bytes not exceeding the value of this option. The value must be - a multiple of 256. The default value is 1024. - </para> - - <warning> - <para> - When using <emphasis>statement-based replication</emphasis> It - is possible for the data on the master and slave to become - different if a statement is designed in such a way that the - data modification is <firstterm>non-deterministic</firstterm>; - that is, it is left to the will of the query optimizer. In - general, this is not a good practice even outside of - replication. For a detailed explanation of this issue, see - <xref linkend="open-bugs"/>. - </para> - </warning> - - </section> - - <section id="replication-formats-mixed"> - - <title>Mixed Replication Format</title> - - <para> - When running in <literal>MIXED</literal> mode, automatic - switching from statement-based to row-based replication takes - place under the following conditions: - </para> - - <itemizedlist> - - <listitem> - <para> - When a DML statement updates an <literal>NDB</literal> table - </para> - </listitem> - - <listitem> - <para> - When a function contains <literal>UUID()</literal> - </para> - </listitem> - - <listitem> - <para> - When 2 or more tables with <literal>AUTO_INCREMENT</literal> - columns are updated - </para> - </listitem> - - <listitem> - <para> - When any <literal>INSERT DELAYED</literal> is executed - </para> - </listitem> - - <listitem> - <para> - When the body of a view requires row-based replication, the - statement creating the view also uses it — for - example, this occurs when the statement creating a view uses - the <literal>UUID()</literal> function - </para> - </listitem> - - <listitem> - <para> - When a call to a UDF is involved. - </para> - </listitem> - - </itemizedlist> - - </section> - <section id="replication-sbr-rbr"> <title>Comparison of Statement-Based Versus Row-Based Replication</title> @@ -1991,62 +1732,6 @@ </section> - <section id="replication-mysql-database"> - - <title>Logging Format for Changes to <literal>mysql</literal> Database Tables</title> - - <para> - The contents of the grant tables in the <literal>mysql</literal> - database can be modified directly (for example, with - <literal>INSERT</literal> or <literal>DELETE</literal>) or - indirectly (for example, with <literal>GRANT</literal> or - <literal>CREATE USER</literal>). As of MySQL 5.1.17, statements - that affect <literal>mysql</literal> database tables are written - to the binary log using the following rules: - </para> - - <itemizedlist> - - <listitem> - <para> - Data manipulation statements that change data in - <literal>mysql</literal> database tables directly are logged - according to the setting of the - <literal>binlog_format</literal> system variable. This - pertains to statements such as <literal>INSERT</literal>, - <literal>UPDATE</literal>, <literal>DELETE</literal>, - <literal>REPLACE</literal>, <literal>DO</literal>, - <literal>LOAD DATA INFILE</literal>, - <literal>SELECT</literal>, and <literal>TRUNCATE</literal>. - </para> - </listitem> - - <listitem> - <para> - Statements that change the <literal>mysql</literal> database - indirectly are logged as statements regardless of the value - of <literal>binlog_format</literal>. This pertains to - statements such as <literal>GRANT</literal>, - <literal>REVOKE</literal>, <literal>SET PASSWORD</literal>, - <literal>RENAME USER</literal>, <literal>CREATE</literal> - (all forms except <literal>CREATE TABLE ... - SELECT</literal>), <literal>ALTER</literal> (all forms), and - <literal>DROP</literal> (all forms). - </para> - </listitem> - - </itemizedlist> - - <para> - <literal>CREATE TABLE ... SELECT</literal> is a combination of - data definition and data manipulation. The <literal>CREATE - TABLE</literal> part is logged using statement format and the - <literal>SELECT</literal> part is logged according to the value - of <literal>binlog_format</literal>. - </para> - - </section> - </section> <section id="replication-options">
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r7364 - trunk/refman-5.1 | mcbrown | 7 Aug |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.085 sec. using MySQL 5.1.14-beta-log