| List: | Commits | « Previous MessageNext Message » | |
| From: | mcbrown | Date: | September 4 2007 6:41pm |
| Subject: | svn commit - mysqldoc@docsrva: r7661 - in trunk: refman-5.1 refman-5.2 | ||
| View as plain text | |||
Author: mcbrown Date: 2007-09-04 18:41:08 +0200 (Tue, 04 Sep 2007) New Revision: 7661 Log: Merging the 5.2 replication changes into the 5.2 manual Some tweaks to the 5.1 content Modified: trunk/refman-5.1/dba-core.xml trunk/refman-5.2/dba-core.xml trunk/refman-5.2/replication-configuration.xml Modified: trunk/refman-5.1/dba-core.xml =================================================================== --- trunk/refman-5.1/dba-core.xml 2007-09-04 16:16:19 UTC (rev 7660) +++ trunk/refman-5.1/dba-core.xml 2007-09-04 16:41:08 UTC (rev 7661) Changed blocks: 2, Lines Added: 15, Lines Deleted: 22; 2918 bytes @@ -29621,13 +29621,11 @@ into <literal>InnoDB</literal>. If the server crashes between those two operations, the transaction is rolled back by <literal>InnoDB</literal> at restart but still exists in the - binary log. This problem can be solved with the - <option>--innodb-safe-binlog</option> option, which adds - consistency between the content of <literal>InnoDB</literal> - tables and the binary log. (Note: - <option>--innodb-safe-binlog</option> is unneeded as of MySQL - 5.0; it was made obsolete by the introduction of XA transaction - support.) + binary log. To resolve this, you should set + <option>--innodb_support_xa</option> to 1. Although this option + is related to the support of XA transactions in InnoDB, it also + ensures that the binary log and InnoDB data files are + synchronized. </para> <para> @@ -29646,21 +29644,16 @@ </para> <para> - Note that <option>--innodb-safe-binlog</option> can be used even - if the MySQL server updates other storage engines than - <literal>InnoDB</literal>. Only statements and transactions that - affect <literal>InnoDB</literal> tables are subject to removal - from the binary log at <literal>InnoDB</literal>'s crash - recovery. If the MySQL server discovers at crash recovery that - the binary log is shorter than it should have been, it lacks at - least one successfully committed <literal>InnoDB</literal> - transaction. This should not happen if - <literal>sync_binlog=1</literal> and the disk/filesystem do an - actual sync when they are requested to (some don't), so the - server prints an error message <literal>The binary log - <name> is shorter than its expected size</literal>. In - this case, this binary log is not correct and replication should - be restarted from a fresh snapshot of the master's data. + If the MySQL server discovers at crash recovery that the binary + log is shorter than it should have been, it lacks at least one + successfully committed <literal>InnoDB</literal> transaction. + This should not happen if <literal>sync_binlog=1</literal> and + the disk/filesystem do an actual sync when they are requested to + (some don't), so the server prints an error message <literal>The + binary log <name> is shorter than its expected + size</literal>. In this case, this binary log is not correct and + replication should be restarted from a fresh snapshot of the + master's data. </para> <para> Modified: trunk/refman-5.2/dba-core.xml =================================================================== --- trunk/refman-5.2/dba-core.xml 2007-09-04 16:16:19 UTC (rev 7660) +++ trunk/refman-5.2/dba-core.xml 2007-09-04 16:41:08 UTC (rev 7661) Changed blocks: 3, Lines Added: 849, Lines Deleted: 23; 31304 bytes @@ -29352,7 +29352,7 @@ </para> <para> - If you are using the binary log and row based loggging, + If you are using the binary log and row based logging, concurrent inserts are converted to normal inserts for <literal>CREATE ... SELECT</literal> or <literal>INSERT ... SELECT</literal> statement. This is done to ensure that you can @@ -29387,13 +29387,11 @@ into <literal>InnoDB</literal>. If the server crashes between those two operations, the transaction is rolled back by <literal>InnoDB</literal> at restart but still exists in the - binary log. This problem can be solved with the - <option>--innodb-safe-binlog</option> option, which adds - consistency between the content of <literal>InnoDB</literal> - tables and the binary log. (Note: - <option>--innodb-safe-binlog</option> is unneeded as of MySQL - 5.0; it was made obsolete by the introduction of XA transaction - support.) + binary log. To resolve this, you should set + <option>--innodb_support_xa</option> to 1. Although this option + is related to the support of XA transactions in InnoDB, it also + ensures that the binary log and InnoDB data files are + synchronized. </para> <para> @@ -29412,23 +29410,851 @@ </para> <para> - Note that <option>--innodb-safe-binlog</option> can be used even - if the MySQL server updates other storage engines than - <literal>InnoDB</literal>. Only statements and transactions that - affect <literal>InnoDB</literal> tables are subject to removal - from the binary log at <literal>InnoDB</literal>'s crash - recovery. If the MySQL server discovers at crash recovery that - the binary log is shorter than it should have been, it lacks at - least one successfully committed <literal>InnoDB</literal> - transaction. This should not happen if - <literal>sync_binlog=1</literal> and the disk/filesystem do an - actual sync when they are requested to (some don't), so the - server prints an error message <literal>The binary log - <name> is shorter than its expected size</literal>. In - this case, this binary log is not correct and replication should - be restarted from a fresh snapshot of the master's data. + If the MySQL server discovers at crash recovery that the binary + log is shorter than it should have been, it lacks at least one + successfully committed <literal>InnoDB</literal> transaction. + This should not happen if <literal>sync_binlog=1</literal> and + the disk/filesystem do an actual sync when they are requested to + (some don't), so the server prints an error message <literal>The + binary log <name> is shorter than its expected + size</literal>. In this case, this binary log is not correct and + replication should be restarted from a fresh snapshot of the + master's data. </para> + <para> + The following session variables are written to the binary log + and honoured by the replication slave when parsing the binary + log: + </para> + + <itemizedlist> + + <listitem> + <para> + <link + linkend="option_mysqld_sql_mode"><literal>SQL_MODE</literal></link> + </para> + </listitem> + + <listitem> + <para> + <literal>FOREIGN_KEY_CHECKS</literal> + </para> + </listitem> + + <listitem> + <para> + <literal>UNIQUE_CHECKS</literal> + </para> + </listitem> + + <listitem> + <para> + <link + linkend="option_mysqld_character_set_client"><literal>CHARACTER_SET_CLIENT</literal></link> + </para> + </listitem> + + <listitem> + <para> + <link + linkend="option_mysqld_collation_connection"><literal>COLLATION_CONNECTION</literal></link> + </para> + </listitem> + + <listitem> + <para> + <link + linkend="option_mysqld_collation_database"><literal>COLLATION_DATABASE</literal></link> + </para> + </listitem> + + <listitem> + <para> + <link + linkend="option_mysqld_collation_server"><literal>COLLATION_SERVER</literal></link> + </para> + </listitem> + + <listitem> + <para> + <literal>SQL_AUTO_IS_NULL</literal> + </para> + </listitem> + + </itemizedlist> + + <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> + With <emphasis>mixed-based logging</emphasis> (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> + 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 Binary Logging (MBL) 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> + A warning is generated if you try to log execute a statement + in statement-logging mode that should be logged in + row-logging mode. The warning will be shown both in the + client (and available with <literal>SHOW WARNINGS</literal>) + and through the <command>mysqld</command> error log. A + warning is added to the <literal>SHOW WARNINGS</literal> + table each time a statement is executed. However, only the + first statement that generated the warning for each client + session is logged to the <literal>mysqld</literal> error log + to prevent flooding the error log. + </para> + </note> + + <para> + 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>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 spanname="action">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>). + 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.2/replication-configuration.xml =================================================================== --- trunk/refman-5.2/replication-configuration.xml 2007-09-04 16:16:19 UTC (rev 7660) +++ trunk/refman-5.2/replication-configuration.xml 2007-09-04 16:41:08 UTC (rev 7661) Changed blocks: 4, Lines Added: 85, Lines Deleted: 346; 16062 bytes @@ -1249,15 +1249,14 @@ <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. + individual table rows are affected. </para> </listitem> <listitem> <para> - As of MySQL 5.1.8, a third option is available: - <emphasis>mixed-based replication</emphasis> (MBR). With MBR, + With + <emphasis>mixed-based replication</emphasis> (MBR), statement-based replication is used by default, but automatically switches to row-based replication in particular cases as described below. See @@ -1269,23 +1268,34 @@ </itemizedlist> <para> - Starting with MySQL 5.1.12, mixed-based replication (MBR) is the + Mixed-based replication (MBR) 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 is + partially determined by the storage engine being used and the + statement being executed. 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> - + <para> With MySQL's classic statement-based replication, there may be issues with replicating stored routines or triggers. You can avoid @@ -1293,293 +1303,77 @@ detailed list of issues, see <xref linkend="stored-procedure-logging"/>. </para> - + <para> If you build MySQL from source, row-based replication is available by default unless you invoke <command>configure</command> with the <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> + For MySQL 5.1.20 and later (and MySQL 5.0.46 for backwards + compatibility), the following session variables are written to the + binary log and honoured by the replication slave when parsing the + binary log: + </para> + + <itemizedlist> + + <listitem> <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"/>. + <link + linkend="option_mysqld_sql_mode"><literal>SQL_MODE</literal></link> </para> - </warning> + </listitem> + + <listitem> + <para> + <literal>FOREIGN_KEY_CHECKS</literal> + </para> + </listitem> + + <listitem> + <para> + <literal>UNIQUE_CHECKS</literal> + </para> + </listitem> + + <listitem> + <para> + <link + linkend="option_mysqld_character_set_client"><literal>CHARACTER_SET_CLIENT</literal></link> + </para> + </listitem> + + <listitem> + <para> + <link + linkend="option_mysqld_collation_connection"><literal>COLLATION_CONNECTION</literal></link> + </para> + </listitem> + + <listitem> + <para> + <link + linkend="option_mysqld_collation_database"><literal>COLLATION_DATABASE</literal></link> + </para> + </listitem> + + <listitem> + <para> + <link + linkend="option_mysqld_collation_server"><literal>COLLATION_SERVER</literal></link> + </para> + </listitem> + + <listitem> + <para> + <literal>SQL_AUTO_IS_NULL</literal> + </para> + </listitem> + + </itemizedlist> - </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> @@ -1989,62 +1783,7 @@ </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: r7661 - in trunk: refman-5.1 refman-5.2 | mcbrown | 4 Sep |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.076 sec. using MySQL 5.1.14-beta-log