Author: paul
Date: 2006-01-30 05:32:03 +0100 (Mon, 30 Jan 2006)
New Revision: 1118
Log:
r6888@frost: paul | 2006-01-29 21:49:14 -0600
General revisions.
Modified:
trunk/
trunk/refman-5.1/replication.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6887
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2588
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6888
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2588
Modified: trunk/refman-5.1/replication.xml
===================================================================
--- trunk/refman-5.1/replication.xml 2006-01-30 04:31:49 UTC (rev 1117)
+++ trunk/refman-5.1/replication.xml 2006-01-30 04:32:03 UTC (rev 1118)
@@ -328,18 +328,20 @@
<listitem>
<para>
- A thread that does a lot of small changes to the database
- might want to use row-based logging, while a thread that does
- a lot of heavy-duty searching might want to use
- statement-based logging.
+ 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 create a small result set. It might therefore be
- beneficial to replicated them row-based.
+ but result in just a few rows being modified. It might
+ therefore be beneficial to replicate them using row-based
+ logging.
</para>
</listitem>
@@ -3315,41 +3317,41 @@
<emphasis>Yes</emphasis>: In this case, the behavior
depends on whether statement-based replication or
row-based replication are enabled:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- <emphasis>Statement-based replication</emphasis>:
- Proceed to the next step and begin evaluating the
- table rules in the order shown (first the non-wild
- rules, and then the wild rules). Only tables that
- are to be updated are compared to the rules. For
- example, if the statement is <literal>INSERT INTO
- sales SELECT * FROM prices</literal>, only
- <literal>sales</literal> is compared to the rules).
- If several tables are to be updated (multiple-table
- statement), the first table that matches
- <quote>do</quote> or <quote>ignore</quote> wins.
- That is, the server checks the first table against
- the rules. If no decision could be made, it checks
- the second table against the rules, and so on.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <emphasis>Statement-based replication</emphasis>:
+ Proceed to the next step and begin evaluating the
+ table rules in the order shown (first the non-wild
+ rules, and then the wild rules). Only tables that are
+ to be updated are compared to the rules. For example,
+ if the statement is <literal>INSERT INTO sales SELECT
+ * FROM prices</literal>, only <literal>sales</literal>
+ is compared to the rules). If several tables are to be
+ updated (multiple-table statement), the first table
+ that matches <quote>do</quote> or
+ <quote>ignore</quote> wins. That is, the server checks
+ the first table against the rules. If no decision
+ could be made, it checks the second table against the
+ rules, and so on.
+ </para>
+ </listitem>
- <listitem>
- <para>
- <emphasis>Row-based replication</emphasis>: All
- table row changes are filtered individually. For
- multiple-table updates, each table is filtered
- separately according to the rules. Some updates may
- be executed and some not, depending on the rules and
- the changes to be made.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <emphasis>Row-based replication</emphasis>: All table
+ row changes are filtered individually. For
+ multiple-table updates, each table is filtered
+ separately according to the rules. Some updates may be
+ executed and some not, depending on the rules and the
+ changes to be made.
+ </para>
+ </listitem>
- </itemizedlist>
- </para>
+ </itemizedlist>
</listitem>
</itemizedlist>
@@ -4655,326 +4657,323 @@
</itemizedlist>
- <section id="replication-problems">
+ </section>
- <title>&title-replication-problems;</title>
+ <section id="replication-problems">
- <para>
- If you have followed the instructions, and your replication
- setup is not working, first check the following:
- </para>
+ <title>&title-replication-problems;</title>
- <itemizedlist>
+ <para>
+ If you have followed the instructions, and your replication setup
+ is not working, first check the following:
+ </para>
- <listitem>
- <para>
- <emphasis role="bold">Check the error log for
- messages</emphasis>. Many users have lost time by not doing
- this early enough after encountering problems.
- </para>
- </listitem>
+ <itemizedlist>
- <listitem>
- <para>
- Is the master logging to the binary log? Check with
- <literal>SHOW MASTER STATUS</literal>. If it is,
- <literal>Position</literal> is non-zero. If not, verify that
- you are running the master with the
- <literal>log-bin</literal> and <literal>server-id</literal>
- options.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <emphasis role="bold">Check the error log for
+ messages</emphasis>. Many users have lost time by not doing
+ this early enough after encountering problems.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Is the slave running? Use <literal>SHOW SLAVE
- STATUS</literal> to check whether the
- <literal>Slave_IO_Running</literal> and
- <literal>Slave_SQL_Running</literal> values are both
- <literal>Yes</literal>. If not, verify the options that were
- used when starting the slave server.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Is the master logging to the binary log? Check with
+ <literal>SHOW MASTER STATUS</literal>. If it is,
+ <literal>Position</literal> is non-zero. If not, verify that
+ you are running the master with the <literal>log-bin</literal>
+ and <literal>server-id</literal> options.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If the slave is running, did it establish a connection to
- the master? Use <literal>SHOW PROCESSLIST</literal>, find
- the I/O and SQL threads and check their
- <literal>State</literal> column to see how they display. See
- <xref linkend="replication-implementation-details"/>. If the
- I/O thread state says <literal>Connecting to
- master</literal>, verify the privileges for the replication
- user on the master, master hostname, your DNS setup, whether
- the master is actually running, and whether it is reachable
- from the slave.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Is the slave running? Use <literal>SHOW SLAVE STATUS</literal>
+ to check whether the <literal>Slave_IO_Running</literal> and
+ <literal>Slave_SQL_Running</literal> values are both
+ <literal>Yes</literal>. If not, verify the options that were
+ used when starting the slave server.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If the slave was running previously but has stopped, the
- reason usually is that some statement that succeeded on the
- master failed on the slave. This should never happen if you
- have taken a proper snapshot of the master, and never
- modified the data on the slave outside of the slave thread.
- If it does, it is a bug or you have encountered one of the
- known replication limitations described in
- <xref linkend="replication-features"/>. If it is a bug, see
- <xref linkend="replication-bugs"/>, for instructions on how
- to report it.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If the slave is running, did it establish a connection to the
+ master? Use <literal>SHOW PROCESSLIST</literal>, find the I/O
+ and SQL threads and check their <literal>State</literal>
+ column to see how they display. See
+ <xref linkend="replication-implementation-details"/>. If the
+ I/O thread state says <literal>Connecting to master</literal>,
+ verify the privileges for the replication user on the master,
+ master hostname, your DNS setup, whether the master is
+ actually running, and whether it is reachable from the slave.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If a statement that succeeded on the master refuses to run
- on the slave, and it is not feasible to do a full database
- resynchronization (that is, to delete the slave's database
- and copy a new snapshot from the master), try the following:
- </para>
+ <listitem>
+ <para>
+ If the slave was running previously but has stopped, the
+ reason usually is that some statement that succeeded on the
+ master failed on the slave. This should never happen if you
+ have taken a proper snapshot of the master, and never modified
+ the data on the slave outside of the slave thread. If it does,
+ it is a bug or you have encountered one of the known
+ replication limitations described in
+ <xref linkend="replication-features"/>. If it is a bug, see
+ <xref linkend="replication-bugs"/>, for instructions on how to
+ report it.
+ </para>
+ </listitem>
- <orderedlist>
+ <listitem>
+ <para>
+ If a statement that succeeded on the master refuses to run on
+ the slave, and it is not feasible to do a full database
+ resynchronization (that is, to delete the slave's database and
+ copy a new snapshot from the master), try the following:
+ </para>
- <listitem>
- <para>
- Determine whether the slave's table is different from
- the master's. Try to understand how this happened. Then
- make the slave's table identical to the master's and run
- <literal>START SLAVE</literal>.
- </para>
- </listitem>
+ <orderedlist>
- <listitem>
- <para>
- If the preceding step does not work or does not apply,
- try to understand whether it would be safe to make the
- update manually (if needed) and then ignore the next
- statement from the master.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Determine whether the slave's table is different from the
+ master's. Try to understand how this happened. Then make
+ the slave's table identical to the master's and run
+ <literal>START SLAVE</literal>.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you decide that you can skip the next statement from
- the master, issue the following statements:
- </para>
+ <listitem>
+ <para>
+ If the preceding step does not work or does not apply, try
+ to understand whether it would be safe to make the update
+ manually (if needed) and then ignore the next statement
+ from the master.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ If you decide that you can skip the next statement from
+ the master, issue the following statements:
+ </para>
+
<programlisting>
mysql> <userinput>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = <replaceable>N</replaceable>;</userinput>
mysql> <userinput>START SLAVE;</userinput>
</programlisting>
- <para>
- The value of <replaceable>N</replaceable> should be 1 if
- the next statement from the master does not use
- <literal>AUTO_INCREMENT</literal> or
- <literal>LAST_INSERT_ID()</literal>. Otherwise, the
- value should be 2. The reason for using a value of 2 for
- statements that use <literal>AUTO_INCREMENT</literal> or
- <literal>LAST_INSERT_ID()</literal> is that they take
- two events in the binary log of the master.
- </para>
- </listitem>
+ <para>
+ The value of <replaceable>N</replaceable> should be 1 if
+ the next statement from the master does not use
+ <literal>AUTO_INCREMENT</literal> or
+ <literal>LAST_INSERT_ID()</literal>. Otherwise, the value
+ should be 2. The reason for using a value of 2 for
+ statements that use <literal>AUTO_INCREMENT</literal> or
+ <literal>LAST_INSERT_ID()</literal> is that they take two
+ events in the binary log of the master.
+ </para>
+ </listitem>
- <listitem>
- <para>
- If you are sure that the slave started out perfectly
- synchronized with the master, and that no one has
- updated the tables involved outside of the slave thread,
- then presumably the discrepancy is the result of a bug.
- If you are running the most recent version, please
- report the problem. If you are running an older version
- of MySQL, try upgrading to the latest production
- release.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If you are sure that the slave started out perfectly
+ synchronized with the master, and that no one has updated
+ the tables involved outside of the slave thread, then
+ presumably the discrepancy is the result of a bug. If you
+ are running the most recent version, please report the
+ problem. If you are running an older version of MySQL, try
+ upgrading to the latest production release.
+ </para>
+ </listitem>
- </orderedlist>
- </listitem>
+ </orderedlist>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- </section>
+ </section>
- <section id="replication-bugs">
+ <section id="replication-bugs">
- <title>&title-replication-bugs;</title>
+ <title>&title-replication-bugs;</title>
- <para>
- When you have determined that there is no user error involved,
- and replication still either does not work at all or is
- unstable, it is time to send us a bug report. We need to obtain
- as much information as possible from you to be able to track
- down the bug. Please spend some time and effort in preparing a
- good bug report.
- </para>
+ <para>
+ When you have determined that there is no user error involved, and
+ replication still either does not work at all or is unstable, it
+ is time to send us a bug report. We need to obtain as much
+ information as possible from you to be able to track down the bug.
+ Please spend some time and effort in preparing a good bug report.
+ </para>
- <para>
- If you have a repeatable test case that demonstrates the bug,
- please enter it into our bugs database at
- <ulink url="http://bugs.mysql.com/"/>. If you have a
- <quote>phantom</quote> problem (one that you cannot duplicate at
- will), then use the following procedure:
- </para>
+ <para>
+ If you have a repeatable test case that demonstrates the bug,
+ please enter it into our bugs database at
+ <ulink url="http://bugs.mysql.com/"/>. If you have a
+ <quote>phantom</quote> problem (one that you cannot duplicate at
+ will), then use the following procedure:
+ </para>
- <orderedlist>
+ <orderedlist>
- <listitem>
- <para>
- Verify that no user error is involved. For example, if you
- update the slave outside of the slave thread, the data goes
- out of synchrony, and you can have unique key violations on
- updates. In this case, the slave thread stops and waits for
- you to clean up the tables manually to bring them into
- synchrony. <emphasis>This is not a replication problem. It
- is a problem with outside interference causing replication
- to fail.</emphasis>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Verify that no user error is involved. For example, if you
+ update the slave outside of the slave thread, the data goes
+ out of synchrony, and you can have unique key violations on
+ updates. In this case, the slave thread stops and waits for
+ you to clean up the tables manually to bring them into
+ synchrony. <emphasis>This is not a replication problem. It is
+ a problem with outside interference causing replication to
+ fail.</emphasis>
+ </para>
+ </listitem>
- <listitem>
- <para>
- Run the slave with the <option>--log-slave-updates</option>
- and <option>--log-bin</option> options. These options cause
- the slave to log the updates that it receives from the
- master into its own binary logs.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Run the slave with the <option>--log-slave-updates</option>
+ and <option>--log-bin</option> options. These options cause
+ the slave to log the updates that it receives from the master
+ into its own binary logs.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Save all evidence before resetting the replication state. If
- we have no information or only sketchy information, it
- becomes difficult or impossible for us to track down the
- problem. The evidence you should collect is:
- </para>
+ <listitem>
+ <para>
+ Save all evidence before resetting the replication state. If
+ we have no information or only sketchy information, it becomes
+ difficult or impossible for us to track down the problem. The
+ evidence you should collect is:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- All binary logs from the master
- </para>
- </listitem>
+ <listitem>
+ <para>
+ All binary logs from the master
+ </para>
+ </listitem>
- <listitem>
- <para>
- All binary logs from the slave
- </para>
- </listitem>
+ <listitem>
+ <para>
+ All binary logs from the slave
+ </para>
+ </listitem>
- <listitem>
- <para>
- The output of <literal>SHOW MASTER STATUS</literal> from
- the master at the time you discovered the problem
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The output of <literal>SHOW MASTER STATUS</literal> from
+ the master at the time you discovered the problem
+ </para>
+ </listitem>
- <listitem>
- <para>
- The output of <literal>SHOW SLAVE STATUS</literal> from
- the master at the time you discovered the problem
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The output of <literal>SHOW SLAVE STATUS</literal> from
+ the master at the time you discovered the problem
+ </para>
+ </listitem>
- <listitem>
- <para>
- Error logs from the master and the slave
- </para>
- </listitem>
+ <listitem>
+ <para>
+ Error logs from the master and the slave
+ </para>
+ </listitem>
- </itemizedlist>
- </listitem>
+ </itemizedlist>
+ </listitem>
- <listitem>
- <para>
- Use <command>mysqlbinlog</command> to examine the binary
- logs. The following should be helpful to find the problem
- query, for example:
- </para>
+ <listitem>
+ <para>
+ Use <command>mysqlbinlog</command> to examine the binary logs.
+ The following should be helpful to find the problem query, for
+ example:
+ </para>
<programlisting>
shell> <userinput>mysqlbinlog -j pos_from_slave_status \</userinput>
<userinput><replaceable>/path/to/log_from_slave_status</replaceable> | head</userinput>
</programlisting>
- </listitem>
+ </listitem>
- </orderedlist>
+ </orderedlist>
- <para>
- Once you have collected the evidence for the problem, try to
- isolate it as a separate test case first. Then enter the problem
- into our bugs database at <ulink url="http://bugs.mysql.com/"/>
- with as much information as possible.
- </para>
+ <para>
+ Once you have collected the evidence for the problem, try to
+ isolate it as a separate test case first. Then enter the problem
+ into our bugs database at <ulink url="http://bugs.mysql.com/"/>
+ with as much information as possible.
+ </para>
- <para>
- <emphasis role="bold">Q</emphasis>: How do I tell which format
- I'm currently running (row-based or statement-based)?
- </para>
+ <para>
+ <emphasis role="bold">Q</emphasis>: How do I tell which format I'm
+ currently running (row-based or statement-based)?
+ </para>
- <para>
- <emphasis role="bold">A</emphasis>: By issuing this statement:
- </para>
+ <para>
+ <emphasis role="bold">A</emphasis>: By issuing this statement:
+ </para>
<programlisting>
mysql> <userinput>SHOW VARIABLES LIKE "%binlog_format%";</userinput>
</programlisting>
- <para>
- <emphasis role="bold">Q</emphasis>: How do I tell the slave to
- use row-based replication?
- </para>
+ <para>
+ <emphasis role="bold">Q</emphasis>: How do I tell the slave to use
+ row-based replication?
+ </para>
- <para>
- <emphasis role="bold">A</emphasis>: The slave automatically
- knows which format it should use.
- </para>
+ <para>
+ <emphasis role="bold">A</emphasis>: The slave automatically knows
+ which format it should use.
+ </para>
- </section>
+ </section>
- <section id="replication-auto-increment">
+ <section id="replication-auto-increment">
- <title>&title-replication-auto-increment;</title>
+ <title>&title-replication-auto-increment;</title>
- <para>
- When multiple servers are configured as replication masters,
- special steps must be taken to prevent key collisions when using
- <literal>AUTO_INCREMENT</literal> columns, otherwise multiple
- masters may attempt to use the same
- <literal>AUTO_INCREMENT</literal> value when inserting rows.
- </para>
+ <para>
+ When multiple servers are configured as replication masters,
+ special steps must be taken to prevent key collisions when using
+ <literal>AUTO_INCREMENT</literal> columns, otherwise multiple
+ masters may attempt to use the same
+ <literal>AUTO_INCREMENT</literal> value when inserting rows.
+ </para>
- <para>
- The two system variables
- <literal>auto_increment_increment</literal> and
- <literal>auto_increment_offset</literal> help to accommodate
- multi-master replication with <literal>AUTO_INCREMENT</literal>
- columns. Each of these variables has a default (and minimum)
- value of 1, and a maximum value of 65,535.
- </para>
+ <para>
+ The two system variables
+ <literal>auto_increment_increment</literal> and
+ <literal>auto_increment_offset</literal> help to accommodate
+ multi-master replication with <literal>AUTO_INCREMENT</literal>
+ columns. Each of these variables has a default (and minimum) value
+ of 1, and a maximum value of 65,535.
+ </para>
- <para>
- By setting non-conflicting values for these variables, servers
- in a multi-master configuration will not use conflicting
- <literal>AUTO_INCREMENT</literal> values when inserting new rows
- into the same table.
- </para>
+ <para>
+ By setting non-conflicting values for these variables, servers in
+ a multi-master configuration will not use conflicting
+ <literal>AUTO_INCREMENT</literal> values when inserting new rows
+ into the same table.
+ </para>
- <para>
- These two variables effect <literal>AUTO_INCREMENT</literal>
- column behavior as follows:
- </para>
+ <para>
+ These two variables effect <literal>AUTO_INCREMENT</literal>
+ column behavior as follows:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- <literal>auto_increment_increment</literal> controls the
- interval by which the column value is incremented. For
- example:
- </para>
+ <listitem>
+ <para>
+ <literal>auto_increment_increment</literal> controls the
+ interval by which the column value is incremented. For
+ example:
+ </para>
<programlisting>
mysql> <userinput>SHOW VARIABLES LIKE 'auto_inc%';</userinput>
@@ -5018,25 +5017,25 @@
4 rows in set (0.00 sec)
</programlisting>
- <para>
- (Note how <literal>SHOW VARIABLES</literal> is used here to
- obtain the current values for these variables.)
- </para>
- </listitem>
+ <para>
+ (Note how <literal>SHOW VARIABLES</literal> is used here to
+ obtain the current values for these variables.)
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>auto_increment_offset</literal> determines the
- starting point for the <literal>AUTO_INCREMENT</literal>
- column value. This affects how many masters you can have in
- your replication setup (i.e. setting this value to 10 means
- your setup can support up to ten servers).
- </para>
+ <listitem>
+ <para>
+ <literal>auto_increment_offset</literal> determines the
+ starting point for the <literal>AUTO_INCREMENT</literal>
+ column value. This affects how many masters you can have in
+ your replication setup (i.e. setting this value to 10 means
+ your setup can support up to ten servers).
+ </para>
- <para>
- Consider the following, assuming that these commands are
- executed during the same session as the previous example:
- </para>
+ <para>
+ Consider the following, assuming that these commands are
+ executed during the same session as the previous example:
+ </para>
<programlisting>
mysql> <userinput>SET @@auto_increment_offset=5;</userinput>
@@ -5070,17 +5069,15 @@
+-----+
4 rows in set (0.02 sec)
</programlisting>
- </listitem>
+ </listitem>
- </itemizedlist>
+ </itemizedlist>
- <para>
- For additional information see
- <xref linkend="server-system-variables" />.
- </para>
+ <para>
+ For additional information see
+ <xref linkend="server-system-variables" />.
+ </para>
- </section>
-
</section>
</chapter>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1118 - in trunk: . refman-5.1 | paul | 30 Jan |
