Author: mcbrown
Date: 2006-12-06 12:38:09 +0100 (Wed, 06 Dec 2006)
New Revision: 4120
Log:
Completed the reformat of the Replication Features & Issues section (needs reordering with some data expansion.
Modified:
branches/repupdate/5.1/replication.xml
Modified: branches/repupdate/5.1/replication.xml
===================================================================
--- branches/repupdate/5.1/replication.xml 2006-12-06 10:17:15 UTC (rev 4119)
+++ branches/repupdate/5.1/replication.xml 2006-12-06 11:38:09 UTC (rev 4120)
Changed blocks: 14, Lines Added: 808, Lines Deleted: 834; 78202 bytes
@@ -253,6 +253,10 @@
<title>Setting up replication with new Master and Slaves</title>
+ <para>
+ TODO
+ </para>
+
</section>
<section id="replication-howto-existingdata">
@@ -752,12 +756,137 @@
<title>Setting up replication with existing data without Master shutdown</title>
+ <para>
+ TODO
+ </para>
+
+ <para>
+ There are several possibilities. If you have taken a snapshot
+ backup of the master at some point and recorded the binary log
+ filename and offset (from the output of <literal>SHOW MASTER
+ STATUS</literal>) corresponding to the snapshot, use the
+ following procedure:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Make sure that the slave is assigned a unique server ID.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Execute the following statement on the slave, filling in
+ appropriate values for each option:
+ </para>
+
+<programlisting>
+mysql> <userinput>CHANGE MASTER TO</userinput>
+ -> <userinput>MASTER_HOST='<replaceable>master_host_name</replaceable>',</userinput>
+ -> <userinput>MASTER_USER='<replaceable>master_user_name</replaceable>',</userinput>
+ -> <userinput>MASTER_PASSWORD='<replaceable>master_pass</replaceable>',</userinput>
+ -> <userinput>MASTER_LOG_FILE='<replaceable>recorded_log_file_name</replaceable>',</userinput>
+ -> <userinput>MASTER_LOG_POS=<replaceable>recorded_log_position</replaceable>;</userinput>
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Execute <literal>START SLAVE</literal> on the slave.
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ If you do not have a backup of the master server, here is a
+ quick procedure for creating one. All steps should be
+ performed on the master host.
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Issue this statement to acquire a global read lock:
+ </para>
+
+<programlisting>
+mysql> <userinput>FLUSH TABLES WITH READ LOCK;</userinput>
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ With the lock still in place, execute this command (or a
+ variation of it):
+ </para>
+
+<programlisting>
+shell> <userinput>tar zcf /tmp/backup.tar.gz /var/lib/mysql</userinput>
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Issue this statement and record the output, which you will
+ need later:
+ </para>
+
+<programlisting>
+mysql> <userinput>SHOW MASTER STATUS;</userinput>
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Release the lock:
+ </para>
+
+<programlisting>
+mysql> <userinput>UNLOCK TABLES;</userinput>
+</programlisting>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ An alternative to using the preceding procedure to make a
+ binary copy is to make an SQL dump of the master. To do this,
+ you can use <command>mysqldump --master-data</command> on your
+ master and later load the SQL dump into your slave. However,
+ this is slower than making a binary copy.
+ </para>
+
+ <remark role="todo">
+ [pd] Following para isn't so clear...
+ </remark>
+
+ <para>
+ Regardless of which of the two methods you use, afterward
+ follow the instructions for the case when you have a snapshot
+ and have recorded the log filename and offset. You can use the
+ same snapshot to set up several slaves. Once you have the
+ snapshot of the master, you can wait to set up a slave as long
+ as the binary logs of the master are left intact. The two
+ practical limitations on the length of time you can wait are
+ the amount of disk space available to retain binary logs on
+ the master and the length of time it takes the slave to catch
+ up.
+ </para>
+
</section>
<section id="replication-howto-additional-slaves">
<title>Setting up replication using an existing data snapshot</title>
+ <para>
+ TODO
+ </para>
+
</section>
</section>
@@ -774,6 +903,23 @@
How statement based replication works.
</para>
+ <remark role="todo">
+ Following elemernt needs to be integrated into the main
+ discussion
+ </remark>
+
+ <para>
+ <emphasis>The following restriction applies to statement-based
+ replication only, not to row-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. (This
+ is in general not a good practice, even outside of
+ replication.) For a detailed explanation of this issue, see
+ <xref linkend="open-bugs"/>.
+ </para>
+
</section>
<section id="replication-row-based">
@@ -2819,7 +2965,7 @@
</section>
- <section id="replication-topology-twin-master">
+ <section id="replication-topology-multiple-master">
<title>Replication with Two Masters</title>
@@ -2913,7 +3059,7 @@
</section>
- <section id="replication-topology-multi-master">
+ <section id="replication-topology-circular">
<title>Replication with Multiple Masters</title>
@@ -2921,12 +3067,51 @@
Multiple masters
</para>
+ <para>
+ It is safe to connect servers in a circular master/slave
+ relationship if you use the <option>--log-slave-updates</option>
+ option. That means that you can create a setup such as this:
+ </para>
+
+ <remark role="todo">
+ Create a figure for this.
+ </remark>
+
+<programlisting>
+A -> B -> C -> A
+</programlisting>
+
+ <para>
+ However, many statements do not work correctly in this kind of
+ setup unless your client code is written to take care of the
+ potential problems that can occur from updates that occur in
+ different sequence on different servers.
+ </para>
+
+ <para>
+ Server IDs are encoded in binary log events, so server A knows
+ when an event that it reads was originally created by itself and
+ does not execute the event (unless server A was started with the
+ <option>--replicate-same-server-id</option> option, which is
+ meaningful only in rare cases). Thus, there are no infinite
+ loops. This type of circular setup works only if you perform no
+ conflicting updates between the tables. In other words, if you
+ insert data in both A and C, you should never insert a row in A
+ that may have a key that conflicts with a row inserted in C. You
+ should also not update the same rows on two servers if the order
+ in which the updates are applied is significant.
+ </para>
+
</section>
<section id="replication-topology-chain">
<title>Replication Chains</title>
+ <para>
+ TODO
+ </para>
+
</section>
</section>
@@ -2959,6 +3144,10 @@
<title>Replicating databases to different slaves</title>
+ <para>
+ TODO
+ </para>
+
</section>
<section id="replication-solutions-performance">
@@ -2981,6 +3170,16 @@
</section>
+ <section id="replication-solutions-ssl">
+
+ <title>Setting up replication using SSL</title>
+
+ <para>
+ TODO
+ </para>
+
+ </section>
+
</section>
<section id="replication-notes">
@@ -2989,7 +3188,7 @@
<section id="replication-features">
- <title>Replication Features and Known Problems</title>
+ <title>Replication Features and Issues</title>
<indexterm>
<primary>options</primary>
@@ -3043,10 +3242,11 @@
</para>
<para>
- The following list provides details about what is supported and
- what is not. Additional <literal>InnoDB</literal>-specific
- information about replication is given in
- <xref linkend="innodb-and-mysql-replication"/>.
+ The following sections provide details about what is supported
+ and what is not, and about specific issues and situations that
+ may occur when replicating certain statements. Additional
+ <literal>InnoDB</literal>-specific information about replication
+ is given in <xref linkend="innodb-and-mysql-replication"/>.
</para>
<para>
@@ -3059,800 +3259,701 @@
<xref linkend="replication-row-based"/>.
</para>
- <itemizedlist>
+ <section id="replication-features-autoincid">
- <listitem>
- <para>
- Replication of <literal>AUTO_INCREMENT</literal>,
- <literal>LAST_INSERT_ID()</literal>, and
- <literal>TIMESTAMP</literal> values is done correctly,
- subject to the following exceptions.
- </para>
+ <title>How replication works with <literal>AUTO_INCREMENT</literal></title>
- <para>
- A stored procedure that uses
- <literal>LAST_INSERT_ID()</literal> does not replicate
- properly using statement-based binary logging. This
- limitation is lifted in MySQL 5.1.12.
- </para>
+ <para>
+ Replication of <literal>AUTO_INCREMENT</literal>,
+ <literal>LAST_INSERT_ID()</literal>, and
+ <literal>TIMESTAMP</literal> values is done correctly, subject
+ to the following exceptions.
+ </para>
- <para>
- Adding an <literal>AUTO_INCREMENT</literal> column to a
- table with <literal>ALTER TABLE</literal> might not produce
- the same ordering of the rows on the slave and the master.
- This occurs because the order in which the rows are numbered
- depends on the specific storage engine used for the table
- and the order in which the rows were inserted. If it is
- important to have the same order on the master and slave,
- the rows must be ordered before assigning an
- <literal>AUTO_INCREMENT</literal> number. Assuming that you
- want to add an <literal>AUTO_INCREMENT</literal> column to
- the table <literal>t1</literal>, the following statements
- produce a new table <literal>t2</literal> identical to
- <literal>t1</literal> but with an
- <literal>AUTO_INCREMENT</literal> column:
- </para>
+ <para>
+ A stored procedure that uses
+ <literal>LAST_INSERT_ID()</literal> does not replicate
+ properly using statement-based binary logging. This limitation
+ is lifted in MySQL 5.1.12.
+ </para>
+ <para>
+ Adding an <literal>AUTO_INCREMENT</literal> column to a table
+ with <literal>ALTER TABLE</literal> might not produce the same
+ ordering of the rows on the slave and the master. This occurs
+ because the order in which the rows are numbered depends on
+ the specific storage engine used for the table and the order
+ in which the rows were inserted. If it is important to have
+ the same order on the master and slave, the rows must be
+ ordered before assigning an <literal>AUTO_INCREMENT</literal>
+ number. Assuming that you want to add an
+ <literal>AUTO_INCREMENT</literal> column to the table
+ <literal>t1</literal>, the following statements produce a new
+ table <literal>t2</literal> identical to <literal>t1</literal>
+ but with an <literal>AUTO_INCREMENT</literal> column:
+ </para>
+
<programlisting>
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
</programlisting>
- <para>
- This assumes that the table <literal>t1</literal> has
- columns <literal>col1</literal> and <literal>col2</literal>.
- </para>
+ <para>
+ This assumes that the table <literal>t1</literal> has columns
+ <literal>col1</literal> and <literal>col2</literal>.
+ </para>
- <para>
- <emphasis role="bold">Important</emphasis>: To guarantee the
- same ordering on both master and slave,
- <emphasis>all</emphasis> columns of <literal>t1</literal>
- must be referenced in the <literal>ORDER BY</literal>
- clause.
- </para>
+ <para>
+ <emphasis role="bold">Important</emphasis>: To guarantee the
+ same ordering on both master and slave,
+ <emphasis>all</emphasis> columns of <literal>t1</literal> must
+ be referenced in the <literal>ORDER BY</literal> clause.
+ </para>
- <para>
- The instructions just given are subject to the limitations
- of <literal>CREATE TABLE ... LIKE</literal>: Foreign key
- definitions are ignored, as are the <literal>DATA
- DIRECTORY</literal> and <literal>INDEX DIRECTORY</literal>
- table options. If a table definition includes any of those
- characteristics, create <literal>t2</literal> using a
- <literal>CREATE TABLE</literal> statement that is identical
- to the one used to create <literal>t1</literal>, but with
- the addition of the <literal>AUTO_INCREMENT</literal>
- column.
- </para>
+ <para>
+ The instructions just given are subject to the limitations of
+ <literal>CREATE TABLE ... LIKE</literal>: Foreign key
+ definitions are ignored, as are the <literal>DATA
+ DIRECTORY</literal> and <literal>INDEX DIRECTORY</literal>
+ table options. If a table definition includes any of those
+ characteristics, create <literal>t2</literal> using a
+ <literal>CREATE TABLE</literal> statement that is identical to
+ the one used to create <literal>t1</literal>, but with the
+ addition of the <literal>AUTO_INCREMENT</literal> column.
+ </para>
- <para>
- Regardless of the method used to create and populate the
- copy having the <literal>AUTO_INCREMENT</literal> column,
- the final step is to drop the original table and then rename
- the copy:
- </para>
+ <para>
+ Regardless of the method used to create and populate the copy
+ having the <literal>AUTO_INCREMENT</literal> column, the final
+ step is to drop the original table and then rename the copy:
+ </para>
<programlisting>
DROP t1;
ALTER TABLE t2 RENAME t1;
</programlisting>
- <para>
- See also <xref linkend="alter-table-problems"/>.
- </para>
- </listitem>
+ <para>
+ See also <xref linkend="alter-table-problems"/>.
+ </para>
- <listitem>
- <para>
- Certain functions do not replicate well under some
- conditions:
- </para>
+ </section>
- <itemizedlist>
+ <section id="replication-features-functions">
- <listitem>
- <para>
- The <literal>USER()</literal>,
- <literal>CURRENT_USER()</literal>,
- <literal>UUID()</literal>, and
- <literal>LOAD_FILE()</literal> functions are replicated
- without change and thus do not work reliably on the
- slave unless row-based replication is enabled. (See
- <xref linkend="replication-row-based"/>.)
- </para>
+ <title>How Replications works with functions</title>
- <para>
- For early implementations of mixed-format logging,
- stored functions, triggers, and views that use these
- functions in their body do not replicate reliably in
- mixed-format logging mode because the logging did not
- switch from statement-based to row-based format. For
- example, <literal>INSERT INTO t SELECT FROM v</literal>,
- where <literal>v</literal> is a view that selects
- <literal>UUID()</literal> could cause problems. This
- limitation is lifted in MySQL 5.1.12.
- </para>
- </listitem>
+ <para>
+ Certain functions do not replicate well under some conditions:
+ </para>
- <listitem>
- <para>
- Unlike <literal>NOW()</literal>, the
- <literal>SYSDATE()</literal> function is not
- replication-safe because it is not affected by
- <literal>SET TIMESTAMP</literal> statements in the
- binary log and is non-deterministic if statement-based
- logging is used. This is not a problem if row-based
- logging is used. Another option is to start the server
- with the <option>--sysdate-is-now</option> option to
- cause <literal>SYSDATE()</literal> to be an alias for
- <literal>NOW()</literal>.
- </para>
- </listitem>
+ <itemizedlist>
- <listitem>
- <para>
- <emphasis>The following restriction applies to
- statement-based replication only, not to row-based
- replication.</emphasis> The
- <literal>GET_LOCK()</literal>,
- <literal>RELEASE_LOCK()</literal>,
- <literal>IS_FREE_LOCK()</literal>, and
- <literal>IS_USED_LOCK()</literal> functions that handle
- user-level locks are replicated without the slave
- knowing the concurrency context on master. Therefore,
- these functions should not be used to insert into a
- master's table because the content on the slave would
- differ. (For example, do not issue a statement such as
- <literal>INSERT INTO mytable
- VALUES(GET_LOCK(...))</literal>.)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ The <literal>USER()</literal>,
+ <literal>CURRENT_USER()</literal>,
+ <literal>UUID()</literal>, and
+ <literal>LOAD_FILE()</literal> functions are replicated
+ without change and thus do not work reliably on the slave
+ unless row-based replication is enabled. (See
+ <xref linkend="replication-row-based"/>.)
+ </para>
- </itemizedlist>
+ <para>
+ For early implementations of mixed-format logging, stored
+ functions, triggers, and views that use these functions in
+ their body do not replicate reliably in mixed-format
+ logging mode because the logging did not switch from
+ statement-based to row-based format. For example,
+ <literal>INSERT INTO t SELECT FROM v</literal>, where
+ <literal>v</literal> is a view that selects
+ <literal>UUID()</literal> could cause problems. This
+ limitation is lifted in MySQL 5.1.12.
+ </para>
+ </listitem>
- <para>
- As a workaround for the preceding limitations when
- statement-based replication is in effect, you can use the
- strategy of saving the problematic function result in a user
- variable and referring to the variable in a later statement.
- For example, the following single-row
- <literal>INSERT</literal> is problematic due to the
- reference to the <literal>UUID()</literal> function:
- </para>
+ <listitem>
+ <para>
+ Unlike <literal>NOW()</literal>, the
+ <literal>SYSDATE()</literal> function is not
+ replication-safe because it is not affected by
+ <literal>SET TIMESTAMP</literal> statements in the binary
+ log and is non-deterministic if statement-based logging is
+ used. This is not a problem if row-based logging is used.
+ Another option is to start the server with the
+ <option>--sysdate-is-now</option> option to cause
+ <literal>SYSDATE()</literal> to be an alias for
+ <literal>NOW()</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <emphasis>The following restriction applies to
+ statement-based replication only, not to row-based
+ replication.</emphasis> The <literal>GET_LOCK()</literal>,
+ <literal>RELEASE_LOCK()</literal>,
+ <literal>IS_FREE_LOCK()</literal>, and
+ <literal>IS_USED_LOCK()</literal> functions that handle
+ user-level locks are replicated without the slave knowing
+ the concurrency context on master. Therefore, these
+ functions should not be used to insert into a master's
+ table because the content on the slave would differ. (For
+ example, do not issue a statement such as <literal>INSERT
+ INTO mytable VALUES(GET_LOCK(...))</literal>.)
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ As a workaround for the preceding limitations when
+ statement-based replication is in effect, you can use the
+ strategy of saving the problematic function result in a user
+ variable and referring to the variable in a later statement.
+ For example, the following single-row
+ <literal>INSERT</literal> is problematic due to the reference
+ to the <literal>UUID()</literal> function:
+ </para>
+
<programlisting>
INSERT INTO t VALUES(UUID());
</programlisting>
- <para>
- To work around the problem, do this instead:
- </para>
+ <para>
+ To work around the problem, do this instead:
+ </para>
<programlisting>
SET @my_uuid = UUID();
INSERT INTO t VALUES(@my_uuid);
</programlisting>
- <para>
- That sequence of statements replicates because the value of
- <literal>@my_uuid</literal> is stored in the binary log as a
- user-variable event prior to the <literal>INSERT</literal>
- statement and is available for use in the
- <literal>INSERT</literal>.
- </para>
+ <para>
+ That sequence of statements replicates because the value of
+ <literal>@my_uuid</literal> is stored in the binary log as a
+ user-variable event prior to the <literal>INSERT</literal>
+ statement and is available for use in the
+ <literal>INSERT</literal>.
+ </para>
- <para>
- The same idea applies to multiple-row inserts, but is more
- cumbersome to use. For a two-row insert, you can do this:
- </para>
+ <para>
+ The same idea applies to multiple-row inserts, but is more
+ cumbersome to use. For a two-row insert, you can do this:
+ </para>
<programlisting>
SET @my_uuid1 = UUID(); @my_uuid2 = UUID();
INSERT INTO t VALUES(@my_uuid1),(@my_uuid2);
</programlisting>
- <para>
- However, if the number of rows is large or unknown, the
- workaround is difficult or impracticable. For example, you
- cannot convert the following statement to one in which a
- given individual user variable is associated with each row:
- </para>
+ <para>
+ However, if the number of rows is large or unknown, the
+ workaround is difficult or impracticable. For example, you
+ cannot convert the following statement to one in which a given
+ individual user variable is associated with each row:
+ </para>
<programlisting>
INSERT INTO t2 SELECT UUID(), * FROM t1;
</programlisting>
- </listitem>
- <listitem>
- <para>
- For MySQL 5.1.14 and later, the <literal>mysql</literal>
- database is not replicated. The <literal>mysql</literal>
- database is instead seen as a node specific database.
- Row-based replication is not supported on this table.
- Instead, statements that would normally update this
- information (including <literal>GRANT</literal>,
- <literal>REVOKE</literal> and the manipulation of triggers,
- stored routines/procedures and views are all replicated to
- slaves using Statement based replication.
- </para>
+ </section>
- <para>
- For MySQL 5.1.13 and earlier, user privileges are replicated
- only if the <literal>mysql</literal> database is replicated.
- That is, the <literal>GRANT</literal>,
- <literal>REVOKE</literal>, <literal>SET PASSWORD</literal>,
- <literal>CREATE USER</literal>, and <literal>DROP
- USER</literal> statements take effect on the slave only if
- the replication setup includes the <literal>mysql</literal>
- database.
- </para>
+ <section id="replication-features-mysqldb">
- <para>
- If you're replicating all databases, but don't want
- statements that affect user privileges to be replicated, set
- up the slave to not replicate the <literal>mysql</literal>
- database, using the
- <option>--replicate-wild-ignore-table=mysql.%</option>
- option. The slave will recognize that issuing
- privilege-related SQL statements won't have an effect, and
- thus not execute those statements.
- </para>
- </listitem>
+ <title>Replication of the system <literal>mysql</literal> database</title>
- <listitem>
- <para>
- The <literal>FOREIGN_KEY_CHECKS</literal>,
- <literal>SQL_MODE</literal>,
- <literal>UNIQUE_CHECKS</literal>, and
- <literal>SQL_AUTO_IS_NULL</literal> variables are all
- replicated (this has been true since MySQL 5.0). The
- <literal>storage_engine</literal> system variable (also
- known as <literal>table_type</literal>) is not yet
- replicated in MySQL 5.1, which is a good thing for
- replication between different storage engines.
- </para>
- </listitem>
+ <para>
+ For MySQL 5.1.14 and later, the <literal>mysql</literal>
+ database is not replicated. The <literal>mysql</literal>
+ database is instead seen as a node specific database.
+ Row-based replication is not supported on this table. Instead,
+ statements that would normally update this information
+ (including <literal>GRANT</literal>, <literal>REVOKE</literal>
+ and the manipulation of triggers, stored routines/procedures
+ and views are all replicated to slaves using Statement based
+ replication.
+ </para>
- <listitem>
- <para>
- Replication works correctly between MySQL 5.0 and 5.1
- masters and slaves in any combination, even if the master
- and slave have different global character set variables, and
- even if the master and slave have different global time zone
- variables. (Note that this is not true in cases when the
- master, slave, or both are running MySQL 4.1 or earlier.)
- </para>
- </listitem>
+ <para>
+ For MySQL 5.1.13 and earlier, user privileges are replicated
+ only if the <literal>mysql</literal> database is replicated.
+ That is, the <literal>GRANT</literal>,
+ <literal>REVOKE</literal>, <literal>SET PASSWORD</literal>,
+ <literal>CREATE USER</literal>, and <literal>DROP
+ USER</literal> statements take effect on the slave only if the
+ replication setup includes the <literal>mysql</literal>
+ database.
+ </para>
- <listitem>
- <para>
- The following applies to replication between MySQL servers
- that use different character sets:
- </para>
+ <para>
+ If you're replicating all databases, but don't want statements
+ that affect user privileges to be replicated, set up the slave
+ to not replicate the <literal>mysql</literal> database, using
+ the <option>--replicate-wild-ignore-table=mysql.%</option>
+ option. The slave will recognize that issuing
+ privilege-related SQL statements won't have an effect, and
+ thus not execute those statements.
+ </para>
- <orderedlist>
+ </section>
- <listitem>
- <para>
- If the master uses MySQL 4.1, you must
- <emphasis>always</emphasis> use the same
- <emphasis>global</emphasis> character set and collation
- on the master and the slave, regardless of the MySQL
- version running on the slave. (These are controlled by
- the <option>--character-set-server</option> and
- <option>--collation-server</option> options.) Otherwise,
- you may get duplicate-key errors on the slave, because a
- key that is unique in the master character set might not
- be unique in the slave character set. Note that this is
- not a cause for concern when master and slave are both
- MySQL 5.0 or later.
- </para>
- </listitem>
+ <section id="replication-features-variables">
- <listitem>
- <para>
- If the master is older than MySQL 4.1.3, the character
- set of any client should never be made different from
- its global value because this character set change is
- not known to the slave. In other words, clients should
- not use <literal>SET NAMES</literal>, <literal>SET
- CHARACTER SET</literal>, and so forth. If both the
- master and the slave are 4.1.3 or newer, clients can
- freely set session values for character set variables
- because these settings are written to the binary log and
- so are known to the slave. That is, clients can use
- <literal>SET NAMES</literal> or <literal>SET CHARACTER
- SET</literal> or can set variables such as
- <literal>collation_client</literal> or
- <literal>collation_server</literal>. However, clients
- are prevented from changing the
- <emphasis>global</emphasis> value of these variables; as
- stated previously, the master and slave must always have
- identical global character set values.
- </para>
- </listitem>
+ <title>Replication and variables</title>
- <listitem>
- <remark role="todo">
- Ask Bar about this bug - looks like it should have been
- taken care of in WL#807 which was marked Completed on
- 2005-07-08. Fixed in 4.1.13/4.1.14 and 5.0.9/5.0.10?
- </remark>
+ <para>
+ The <literal>FOREIGN_KEY_CHECKS</literal>,
+ <literal>SQL_MODE</literal>, <literal>UNIQUE_CHECKS</literal>,
+ and <literal>SQL_AUTO_IS_NULL</literal> variables are all
+ replicated (this has been true since MySQL 5.0). The
+ <literal>storage_engine</literal> system variable (also known
+ as <literal>table_type</literal>) is not yet replicated in
+ MySQL 5.1, which is a good thing for replication between
+ different storage engines.
+ </para>
- <para>
- If you have databases on the master with character sets
- that differ from the global
- <literal>character_set_server</literal> value, you
- should design your <literal>CREATE TABLE</literal>
- statements so that tables in those databases do not
- implicitly rely on the database default character set
- (see Bug #2326). A good workaround is to state the
- character set and collation explicitly in
- <literal>CREATE TABLE</literal> statements.
- </para>
- </listitem>
+ <para>
+ Session variables are not replicated properly when used in
+ statements that update tables. For example, <literal>SET
+ MAX_JOIN_SIZE=1000</literal> followed by <literal>INSERT INTO
+ mytable VALUES(@@MAX_JOIN_SIZE)</literal> will not insert the
+ same data on the master and the slave. This does not apply to
+ the common sequence of <literal>SET TIME_ZONE=...</literal>
+ followed by <literal>INSERT INTO mytable
+ VALUES(CONVERT_TZ(...,...,@@time_zone))</literal>.
+ </para>
- </orderedlist>
- </listitem>
+ <para>
+ Replication of session variables is not a problem when
+ row-based replication is being used. See
+ <xref linkend="replication-row-based"/>.
+ </para>
- <listitem>
- <para>
- If the master uses MySQL 4.1, the same system time zone
- should be set for both master and slave. Otherwise some
- statements will not be replicated properly, such as
- statements that use the <literal>NOW()</literal> or
- <literal>FROM_UNIXTIME()</literal> functions. You can set
- the time zone in which MySQL server runs by using the
- <option>--timezone=<replaceable>timezone_name</replaceable></option>
- option of the <filename>mysqld_safe</filename> script or by
- setting the <literal>TZ</literal> environment variable. Both
- master and slave should also have the same default
- connection time zone setting; that is, the
- <option>--default-time-zone</option> parameter should have
- the same value for both master and slave. Note that this is
- not necessary when the master is MySQL 5.0 or later.
- </para>
- </listitem>
+ </section>
- <listitem>
- <para>
- <literal>CONVERT_TZ(...,...,@@session.time_zone)</literal>
- is properly replicated only if both master and slave are
- running MySQL 5.0.4 or newer.
- </para>
- </listitem>
+ <section id="replication-features-charset">
- <listitem>
- <para>
- Session variables are not replicated properly when used in
- statements that update tables. For example, <literal>SET
- MAX_JOIN_SIZE=1000</literal> followed by <literal>INSERT
- INTO mytable VALUES(@@MAX_JOIN_SIZE)</literal> will not
- insert the same data on the master and the slave. This does
- not apply to the common sequence of <literal>SET
- TIME_ZONE=...</literal> followed by <literal>INSERT INTO
- mytable VALUES(CONVERT_TZ(...,...,@@time_zone))</literal>.
- </para>
+ <title>Replication and character sets</title>
- <para>
- Replication of session variables is not a problem when
- row-based replication is being used. See
- <xref linkend="replication-row-based"/>.
- </para>
- </listitem>
+ <para>
+ The following applies to replication between MySQL servers
+ that use different character sets:
+ </para>
- <listitem>
- <remark role="todo">
- Check on the restart issue...
- </remark>
+ <orderedlist>
- <remark role="todo">
- [pd] Is this a promise we want to make?
- </remark>
+ <listitem>
+ <para>
+ If the master uses MySQL 4.1, you must
+ <emphasis>always</emphasis> use the same
+ <emphasis>global</emphasis> character set and collation on
+ the master and the slave, regardless of the MySQL version
+ running on the slave. (These are controlled by the
+ <option>--character-set-server</option> and
+ <option>--collation-server</option> options.) Otherwise,
+ you may get duplicate-key errors on the slave, because a
+ key that is unique in the master character set might not
+ be unique in the slave character set. Note that this is
+ not a cause for concern when master and slave are both
+ MySQL 5.0 or later.
+ </para>
+ </listitem>
- <para>
- It is possible to replicate transactional tables on the
- master using non-transactional tables on the slave. For
- example, you can replicate an <literal>InnoDB</literal>
- master table as a <literal>MyISAM</literal> slave table.
- However, if you do this, there are problems if the slave is
- stopped in the middle of a
- <literal>BEGIN</literal>/<literal>COMMIT</literal> block
- because the slave restarts at the beginning of the
- <literal>BEGIN</literal> block.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ If the master is older than MySQL 4.1.3, the character set
+ of any client should never be made different from its
+ global value because this character set change is not
+ known to the slave. In other words, clients should not use
+ <literal>SET NAMES</literal>, <literal>SET CHARACTER
+ SET</literal>, and so forth. If both the master and the
+ slave are 4.1.3 or newer, clients can freely set session
+ values for character set variables because these settings
+ are written to the binary log and so are known to the
+ slave. That is, clients can use <literal>SET
+ NAMES</literal> or <literal>SET CHARACTER SET</literal> or
+ can set variables such as
+ <literal>collation_client</literal> or
+ <literal>collation_server</literal>. However, clients are
+ prevented from changing the <emphasis>global</emphasis>
+ value of these variables; as stated previously, the master
+ and slave must always have identical global character set
+ values.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Update statements that refer to user-defined variables (that
- is, variables of the form
- <literal>@<replaceable>var_name</replaceable></literal>) are
- replicated correctly; however, this is not true for versions
- prior to 4.1. Note that user variable names are case
- insensitive starting in MySQL 5.0. You should take this into
- account when setting up replication between MySQL 5.0 and
- older versions.
- </para>
- </listitem>
+ <listitem>
+ <remark role="todo">
+ Ask Bar about this bug - looks like it should have been
+ taken care of in WL#807 which was marked Completed on
+ 2005-07-08. Fixed in 4.1.13/4.1.14 and 5.0.9/5.0.10?
+ </remark>
- <listitem>
- <para>
- Slaves can connect to masters using SSL.
- </para>
- </listitem>
+ <para>
+ If you have databases on the master with character sets
+ that differ from the global
+ <literal>character_set_server</literal> value, you should
+ design your <literal>CREATE TABLE</literal> statements so
+ that tables in those databases do not implicitly rely on
+ the database default character set (see Bug #2326). A good
+ workaround is to state the character set and collation
+ explicitly in <literal>CREATE TABLE</literal> statements.
+ </para>
+ </listitem>
- <listitem>
- <para>
- Views are always replicated to slaves. Views are filtered by
- their own name, not by the tables they refer to. This means
- that a view can be replicated to the slave even if the view
- contains a table that would normally be filtered out by
- <option>replication-ignore-table</option> rules. Care should
- therefore be taken to ensure that views do not replicate
- table data that would normally be filtered for security
- reasons.
- </para>
- </listitem>
+ </orderedlist>
- <listitem>
- <para>
- The global system variable
- <literal>slave_transaction_retries</literal> affects
- replicaiton as follows: If the replication slave SQL thread
- fails to execute a transaction because of an
- <literal>InnoDB</literal> deadlock or because it exceeded
- the <literal>InnoDB</literal>
- <literal>innodb_lock_wait_timeout</literal> value, or the
- <literal>NDBCluster</literal>
- <literal>TransactionDeadlockDetectionTimeout</literal> or
- <literal>TransactionInactiveTimeout</literal> value, the
- transaction is automatically retried
- <literal>slave_transaction_retries</literal> times before
- stopping with an error. The default value is 10. The total
- retry count can be seen in the output of <literal>SHOW
- STATUS</literal>; see
- <xref linkend="server-status-variables"/>.
- </para>
- </listitem>
+ </section>
- <listitem>
- <para>
- If a <literal>DATA DIRECTORY</literal> or <literal>INDEX
- DIRECTORY</literal> table option is used in a
- <literal>CREATE TABLE</literal> statement on the master
- server, the table option is also used on the slave. This can
- cause problems if no corresponding directory exists in the
- slave host filesystem or if it exists but is not accessible
- to the slave server. MySQL supports an
- <literal>sql_mode</literal> option called
- <literal>NO_DIR_IN_CREATE</literal>. If the slave server is
- run with this SQL mode enabled, it ignores the <literal>DATA
- DIRECTORY</literal> and <literal>INDEX DIRECTORY</literal>
- table options when replicating <literal>CREATE
- TABLE</literal> statements. The result is that
- <literal>MyISAM</literal> data and index files are created
- in the table's database directory.
- </para>
- </listitem>
+ <section id="replication-features-timezone">
- <listitem>
- <para>
- <emphasis>The following restriction applies to
- statement-based replication only, not to row-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. (This is in general
- not a good practice, even outside of replication.) For a
- detailed explanation of this issue, see
- <xref linkend="open-bugs"/>.
- </para>
- </listitem>
+ <title>Replication and timezones</title>
- <listitem>
- <para>
- Some forms of the <literal>FLUSH</literal> statement are not
- logged because they could cause problems if replicated to a
- slave: <literal>FLUSH LOGS</literal>, <literal>FLUSH
- MASTER</literal>, <literal>FLUSH SLAVE</literal>, and
- <literal>FLUSH TABLES WITH READ LOCK</literal>. For a syntax
- example, see <xref linkend="flush"/>. The <literal>FLUSH
- TABLES</literal>, <literal>ANALYZE TABLE</literal>,
- <literal>OPTIMIZE TABLE</literal>, and <literal>REPAIR
- TABLE</literal> statements are written to the binary log and
- thus replicated to slaves. This is not normally a problem
- because these statements do not modify table data. However,
- this can cause difficulties under certain circumstances. If
- you replicate the privilege tables in the
- <literal>mysql</literal> database and update those tables
- directly without using <literal>GRANT</literal>, you must
- issue a <literal>FLUSH PRIVILEGES</literal> on the slaves to
- put the new privileges into effect. In addition, if you use
- <literal>FLUSH TABLES</literal> when renaming a
- <literal>MyISAM</literal> table that is part of a
- <literal>MERGE</literal> table, you must issue
- <literal>FLUSH TABLES</literal> manually on the slaves.
- These statements are written to the binary log unless you
- specify <literal>NO_WRITE_TO_BINLOG</literal> or its alias
- <literal>LOCAL</literal>.
- </para>
- </listitem>
+ <para>
+ If the master uses MySQL 4.1, the same system time zone should
+ be set for both master and slave. Otherwise some statements
+ will not be replicated properly, such as statements that use
+ the <literal>NOW()</literal> or
+ <literal>FROM_UNIXTIME()</literal> functions. You can set the
+ time zone in which MySQL server runs by using the
+ <option>--timezone=<replaceable>timezone_name</replaceable></option>
+ option of the <filename>mysqld_safe</filename> script or by
+ setting the <literal>TZ</literal> environment variable. Both
+ master and slave should also have the same default connection
+ time zone setting; that is, the
+ <option>--default-time-zone</option> parameter should have the
+ same value for both master and slave. Note that this is not
+ necessary when the master is MySQL 5.0 or later.
+ </para>
- <listitem>
- <para>
- When a server shuts down and restarts, its
- <literal>MEMORY</literal> tables become empty. The master
- replicates this effect to slaves as follows: The first time
- that the master uses each <literal>MEMORY</literal> table
- after startup, it logs an event that notifies the slaves
- that the table needs to be emptied by writing a
- <literal>DELETE</literal> statement for that table to the
- binary log. See <xref linkend="memory-storage-engine"/>, for
- more information about <literal>MEMORY</literal> tables.
- </para>
- </listitem>
+ <para>
+ <literal>CONVERT_TZ(...,...,@@session.time_zone)</literal> is
+ properly replicated only if both master and slave are running
+ MySQL 5.0.4 or newer.
+ </para>
- <listitem>
- <para>
- This item does not apply when row-based replication is in
- use because in that case temporary tables aren't replicated
- at all. (See <xref linkend="replication-row-based"/>.)
- </para>
+ </section>
- <para>
- Temporary tables are replicated except in the case where you
- shut down the slave server (not just the slave threads) and
- you have replicated temporary tables that are used in
- updates that have not yet been executed on the slave. If you
- shut down the slave server, the temporary tables needed by
- those updates are no longer available when the slave is
- restarted. To avoid this problem, do not shut down the slave
- while it has temporary tables open. Instead, use the
- following procedure:
- </para>
+ <section id="replication-features-transactions">
- <orderedlist>
+ <title>Replication and transactions</title>
- <listitem>
- <para>
- Issue a <literal>STOP SLAVE</literal> statement.
- </para>
- </listitem>
+ <para>
+ It is possible to replicate transactional tables on the master
+ using non-transactional tables on the slave. For example, you
+ can replicate an <literal>InnoDB</literal> master table as a
+ <literal>MyISAM</literal> slave table. However, if you do
+ this, there are problems if the slave is stopped in the middle
+ of a <literal>BEGIN</literal>/<literal>COMMIT</literal> block
+ because the slave restarts at the beginning of the
+ <literal>BEGIN</literal> block.
+ </para>
- <listitem>
- <para>
- Use <literal>SHOW STATUS</literal> to check the value of
- the <literal>Slave_open_temp_tables</literal> variable.
- </para>
- </listitem>
+ <para>
+ In situations where transactions mix updates to transactional
+ and non-transactional tables, the order of statements in the
+ binary log is correct, and all needed statements are written
+ to the binary log even in case of a
+ <literal>ROLLBACK</literal>. However, when a second connection
+ updates the non-transactional table before the first
+ connection's transaction is complete, statements can be logged
+ out of order, because the second connection's update is
+ written immediately after it is performed, regardless of the
+ state of the transaction being performed by the first
+ connection.
+ </para>
- <listitem>
- <para>
- If the value is 0, issue a <command>mysqladmin
- shutdown</command> command to stop the slave.
- </para>
- </listitem>
+ <para>
+ Due to the non-transactional nature of
+ <literal>MyISAM</literal> tables, it is possible to have a
+ statement that only partially updates a table and returns an
+ error code. This can happen, for example, on a multiple-row
+ insert that has one row violating a key constraint, or if a
+ long update statement is killed after updating some of the
+ rows. If that happens on the master, the slave thread exits
+ and waits for the database administrator to decide what to do
+ about it unless the error code is legitimate and execution of
+ the statement results in the same error code on the slave. If
+ this error code validation behavior is not desirable, some or
+ all errors can be masked out (ignored) with the
+ <option>--slave-skip-errors</option> option.
+ </para>
- <listitem>
- <para>
- If the value is not 0, restart the slave threads with
- <literal>START SLAVE</literal>.
- </para>
- </listitem>
+ </section>
- <listitem>
- <para>
- Repeat the procedure later until the
- <literal>Slave_open_temp_tables</literal> variable is 0
- and you can stop the slave.
- </para>
- </listitem>
+ <section id="replication-features-views">
- </orderedlist>
- </listitem>
+ <title>Replication and views</title>
- <listitem>
- <para>
- The syntax for multiple-table <literal>DELETE</literal>
- statements that use table aliases changed between MySQL 4.0
- and 4.1. In MySQL 4.0, you should use the true table name to
- refer to any table from which rows should be deleted:
- </para>
+ <para>
+ Views are always replicated to slaves. Views are filtered by
+ their own name, not by the tables they refer to. This means
+ that a view can be replicated to the slave even if the view
+ contains a table that would normally be filtered out by
+ <option>replication-ignore-table</option> rules. Care should
+ therefore be taken to ensure that views do not replicate table
+ data that would normally be filtered for security reasons.
+ </para>
-<programlisting>
-DELETE test FROM test AS t1, test2 WHERE ...
-</programlisting>
+ </section>
- <para>
- In MySQL 4.1, you must use the alias:
- </para>
+ <section id="replication-features-timeout">
-<programlisting>
-DELETE t1 FROM test AS t1, test2 WHERE ...
-</programlisting>
+ <title>Replication and retries</title>
- <para>
- If you use such <literal>DELETE</literal> statements, the
- change in syntax means that a 4.0 master cannot replicate to
- 4.1 (or higher) slaves.
- </para>
- </listitem>
+ <para>
+ The global system variable
+ <literal>slave_transaction_retries</literal> affects
+ replicaiton as follows: If the replication slave SQL thread
+ fails to execute a transaction because of an
+ <literal>InnoDB</literal> deadlock or because it exceeded the
+ <literal>InnoDB</literal>
+ <literal>innodb_lock_wait_timeout</literal> value, or the
+ <literal>NDBCluster</literal>
+ <literal>TransactionDeadlockDetectionTimeout</literal> or
+ <literal>TransactionInactiveTimeout</literal> value, the
+ transaction is automatically retried
+ <literal>slave_transaction_retries</literal> times before
+ stopping with an error. The default value is 10. The total
+ retry count can be seen in the output of <literal>SHOW
+ STATUS</literal>; see
+ <xref linkend="server-status-variables"/>.
+ </para>
- <listitem>
- <para>
- It is safe to connect servers in a circular master/slave
- relationship if you use the
- <option>--log-slave-updates</option> option. That means that
- you can create a setup such as this:
- </para>
+ </section>
- <remark role="todo">
- Create a figure for this.
- </remark>
+ <section id="replication-features-directory">
-<programlisting>
-A -> B -> C -> A
-</programlisting>
+ <title>Replication and <literal>DIRECTORY</literal> statements</title>
- <para>
- However, many statements do not work correctly in this kind
- of setup unless your client code is written to take care of
- the potential problems that can occur from updates that
- occur in different sequence on different servers.
- </para>
+ <para>
+ If a <literal>DATA DIRECTORY</literal> or <literal>INDEX
+ DIRECTORY</literal> table option is used in a <literal>CREATE
+ TABLE</literal> statement on the master server, the table
+ option is also used on the slave. This can cause problems if
+ no corresponding directory exists in the slave host filesystem
+ or if it exists but is not accessible to the slave server.
+ MySQL supports an <literal>sql_mode</literal> option called
+ <literal>NO_DIR_IN_CREATE</literal>. If the slave server is
+ run with this SQL mode enabled, it ignores the <literal>DATA
+ DIRECTORY</literal> and <literal>INDEX DIRECTORY</literal>
+ table options when replicating <literal>CREATE TABLE</literal>
+ statements. The result is that <literal>MyISAM</literal> data
+ and index files are created in the table's database directory.
+ </para>
- <para>
- Server IDs are encoded in binary log events, so server A
- knows when an event that it reads was originally created by
- itself and does not execute the event (unless server A was
- started with the <option>--replicate-same-server-id</option>
- option, which is meaningful only in rare cases). Thus, there
- are no infinite loops. This type of circular setup works
- only if you perform no conflicting updates between the
- tables. In other words, if you insert data in both A and C,
- you should never insert a row in A that may have a key that
- conflicts with a row inserted in C. You should also not
- update the same rows on two servers if the order in which
- the updates are applied is significant.
- </para>
- </listitem>
+ </section>
- <listitem>
- <para>
- If a statement on a slave produces an error, the slave SQL
- thread terminates, and the slave writes a message to its
- error log. You should then connect to the slave manually and
- determine the cause of the problem. (<literal>SHOW SLAVE
- STATUS</literal> is useful for this.) Then fix the problem
- (for example, you might need to create a non-existent table)
- and run <literal>START SLAVE</literal>.
- </para>
- </listitem>
+ <section id="replication-features-flush">
- <listitem>
- <para>
- It is safe to shut down a master server and restart it
- later. When a slave loses its connection to the master, the
- slave tries to reconnect immediately and retries
- periodically if that fails. The default is to retry every 60
- seconds. This may be changed with the
- <option>--master-connect-retry</option> option. A slave also
- is able to deal with network connectivity outages. However,
- the slave notices the network outage only after receiving no
- data from the master for
- <literal>slave_net_timeout</literal> seconds. If your
- outages are short, you may want to decrease
- <literal>slave_net_timeout</literal>. See
- <xref linkend="server-system-variables"/>.
- </para>
- </listitem>
+ <title>Replication and <literal>FLUSH</literal></title>
- <listitem>
- <para>
- Shutting down the slave (cleanly) is also safe because it
- keeps track of where it left off. Unclean shutdowns might
- produce problems, especially if the disk cache was not
- flushed to disk before the system went down. Your system
- fault tolerance is greatly increased if you have a good
- uninterruptible power supply. Unclean shutdowns of the
- master may cause inconsistencies between the content of
- tables and the binary log in master; this can be avoided by
- using <literal>InnoDB</literal> tables and the
- <option>--innodb-safe-binlog</option> option on the master.
- See <xref linkend="binary-log"/>.
- </para>
+ <para>
+ Some forms of the <literal>FLUSH</literal> statement are not
+ logged because they could cause problems if replicated to a
+ slave: <literal>FLUSH LOGS</literal>, <literal>FLUSH
+ MASTER</literal>, <literal>FLUSH SLAVE</literal>, and
+ <literal>FLUSH TABLES WITH READ LOCK</literal>. For a syntax
+ example, see <xref linkend="flush"/>. The <literal>FLUSH
+ TABLES</literal>, <literal>ANALYZE TABLE</literal>,
+ <literal>OPTIMIZE TABLE</literal>, and <literal>REPAIR
+ TABLE</literal> statements are written to the binary log and
+ thus replicated to slaves. This is not normally a problem
+ because these statements do not modify table data. However,
+ this can cause difficulties under certain circumstances. If
+ you replicate the privilege tables in the
+ <literal>mysql</literal> database and update those tables
+ directly without using <literal>GRANT</literal>, you must
+ issue a <literal>FLUSH PRIVILEGES</literal> on the slaves to
+ put the new privileges into effect. In addition, if you use
+ <literal>FLUSH TABLES</literal> when renaming a
+ <literal>MyISAM</literal> table that is part of a
+ <literal>MERGE</literal> table, you must issue <literal>FLUSH
+ TABLES</literal> manually on the slaves. These statements are
+ written to the binary log unless you specify
+ <literal>NO_WRITE_TO_BINLOG</literal> or its alias
+ <literal>LOCAL</literal>.
+ </para>
- <remark role="todo">
- [js] Cut next para altogether, since the option was made
- obsolete in the previous release series?
- </remark>
+ </section>
- <para>
- <emphasis role="bold">Note</emphasis>:
- <option>--innodb-safe-binlog</option> is not needed in MySQL
- 5.1, having been made obsolete by the introduction of XA
- transaction support in MySQL 5.0. See <xref linkend="xa"/>.
- </para>
- </listitem>
+ <section id="replication-features-memory">
- <listitem>
- <para>
- A crash on the master side can result in the master's binary
- log having a final position less than the most recent
- position read by the slave, due to the master's binary log
- file not being flushed. This can cause the slave not to be
- able to replicate when the master comes back up. Setting
- <literal>sync_binlog=1</literal> in the master
- <filename>my.cnf</filename> file helps to minimize this
- problem because it causes the master to flush its binary log
- more frequently.
- </para>
- </listitem>
+ <title>Replication with the <literal>MEMORY</literal> storage engine</title>
- <listitem>
- <para>
- Due to the non-transactional nature of
- <literal>MyISAM</literal> tables, it is possible to have a
- statement that only partially updates a table and returns an
- error code. This can happen, for example, on a multiple-row
- insert that has one row violating a key constraint, or if a
- long update statement is killed after updating some of the
- rows. If that happens on the master, the slave thread exits
- and waits for the database administrator to decide what to
- do about it unless the error code is legitimate and
- execution of the statement results in the same error code on
- the slave. If this error code validation behavior is not
- desirable, some or all errors can be masked out (ignored)
- with the <option>--slave-skip-errors</option> option.
- </para>
- </listitem>
+ <para>
+ When a server shuts down and restarts, its
+ <literal>MEMORY</literal> tables become empty. The master
+ replicates this effect to slaves as follows: The first time
+ that the master uses each <literal>MEMORY</literal> table
+ after startup, it logs an event that notifies the slaves that
+ the table needs to be emptied by writing a
+ <literal>DELETE</literal> statement for that table to the
+ binary log. See <xref linkend="memory-storage-engine"/>, for
+ more information about <literal>MEMORY</literal> tables.
+ </para>
- <listitem>
- <para>
- If you update transactional tables from non-transactional
- tables inside a
- <literal>BEGIN</literal>/<literal>COMMIT</literal> sequence,
- updates to the binary log may be out of synchrony with table
- states if the non-transactional table is updated before the
- transaction commits. This occurs because the transaction is
- written to the binary log only when it is committed.
- </para>
- </listitem>
+ </section>
- <listitem>
- <para>
- In situations where transactions mix updates to
- transactional and non-transactional tables, the order of
- statements in the binary log is correct, and all needed
- statements are written to the binary log even in case of a
- <literal>ROLLBACK</literal>. However, when a second
- connection updates the non-transactional table before the
- first connection's transaction is complete, statements can
- be logged out of order, because the second connection's
- update is written immediately after it is performed,
- regardless of the state of the transaction being performed
- by the first connection.
- </para>
- </listitem>
+ <section id="replication-features-temptables">
- <listitem>
- <para>
- Floating-point values are approximate, so comparisons
- involving them are inexact. This is true for operations that
- use floating-point values explicitly, or values that are
- converted to floating-point implicitly. Comparisons of
- floating-point values might yield different results on
- master and slave servers due to differences in computer
- architecture, the compiler used to build MySQL, and so
- forth. See <xref linkend="type-conversion"/>, and
- <xref linkend="problems-with-float"/>.
- </para>
- </listitem>
+ <title>Replication and temporary tables</title>
- </itemizedlist>
+ <para>
+ This item does not apply when row-based replication is in use
+ because in that case temporary tables aren't replicated at
+ all. (See <xref linkend="replication-row-based"/>.)
+ </para>
+ <para>
+ Temporary tables are replicated except in the case where you
+ shut down the slave server (not just the slave threads) and
+ you have replicated temporary tables that are used in updates
+ that have not yet been executed on the slave. If you shut down
+ the slave server, the temporary tables needed by those updates
+ are no longer available when the slave is restarted. To avoid
+ this problem, do not shut down the slave while it has
+ temporary tables open. Instead, use the following procedure:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Issue a <literal>STOP SLAVE</literal> statement.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Use <literal>SHOW STATUS</literal> to check the value of
+ the <literal>Slave_open_temp_tables</literal> variable.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If the value is 0, issue a <command>mysqladmin
+ shutdown</command> command to stop the slave.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If the value is not 0, restart the slave threads with
+ <literal>START SLAVE</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Repeat the procedure later until the
+ <literal>Slave_open_temp_tables</literal> variable is 0
+ and you can stop the slave.
+ </para>
+ </listitem>
+
+ </orderedlist>
+
+ </section>
+
+ <section id="replication-features-slaveerrors">
+
+ <title>Slave Errors during Replication</title>
+
+ <para>
+ If a statement on a slave produces an error, the slave SQL
+ thread terminates, and the slave writes a message to its error
+ log. You should then connect to the slave manually and
+ determine the cause of the problem. (<literal>SHOW SLAVE
+ STATUS</literal> is useful for this.) Then fix the problem
+ (for example, you might need to create a non-existent table)
+ and run <literal>START SLAVE</literal>.
+ </para>
+
+ </section>
+
+ <section id="replication-features-mastershutdown">
+
+ <title>Replication during a Master shutdown</title>
+
+ <para>
+ It is safe to shut down a master server and restart it later.
+ When a slave loses its connection to the master, the slave
+ tries to reconnect immediately and retries periodically if
+ that fails. The default is to retry every 60 seconds. This may
+ be changed with the <option>--master-connect-retry</option>
+ option. A slave also is able to deal with network connectivity
+ outages. However, the slave notices the network outage only
+ after receiving no data from the master for
+ <literal>slave_net_timeout</literal> seconds. If your outages
+ are short, you may want to decrease
+ <literal>slave_net_timeout</literal>. See
+ <xref linkend="server-system-variables"/>.
+ </para>
+
+ </section>
+
+ <section id="replication-features-slaveshutdown">
+
+ <title>Replication during a Slave Shutdown</title>
+
+ <para>
+ Shutting down the slave (cleanly) is also safe because it
+ keeps track of where it left off. Unclean shutdowns might
+ produce problems, especially if the disk cache was not flushed
+ to disk before the system went down. Your system fault
+ tolerance is greatly increased if you have a good
+ uninterruptible power supply. Unclean shutdowns of the master
+ may cause inconsistencies between the content of tables and
+ the binary log in master; this can be avoided by using
+ <literal>InnoDB</literal> tables and the
+ <option>--innodb-safe-binlog</option> option on the master.
+ See <xref linkend="binary-log"/>.
+ </para>
+
+ </section>
+
+ <section id="replication-features-mastercrash">
+
+ <title>Replication Master crashes</title>
+
+ <para>
+ A crash on the master side can result in the master's binary
+ log having a final position less than the most recent position
+ read by the slave, due to the master's binary log file not
+ being flushed. This can cause the slave not to be able to
+ replicate when the master comes back up. Setting
+ <literal>sync_binlog=1</literal> in the master
+ <filename>my.cnf</filename> file helps to minimize this
+ problem because it causes the master to flush its binary log
+ more frequently.
+ </para>
+
+ </section>
+
+ <section id="replication-features-floatvalues">
+
+ <title>Replication with Floating point values</title>
+
+ <para>
+ Floating-point values are approximate, so comparisons
+ involving them are inexact. This is true for operations that
+ use floating-point values explicitly, or values that are
+ converted to floating-point implicitly. Comparisons of
+ floating-point values might yield different results on master
+ and slave servers due to differences in computer architecture,
+ the compiler used to build MySQL, and so forth. See
+ <xref linkend="type-conversion"/>, and
+ <xref linkend="problems-with-float"/>.
+ </para>
+
+ </section>
+
<section id="replication-morecolumns">
<title>Replication with more columns on slave</title>
@@ -3925,7 +4026,20 @@
<literal>LOAD DATA INFILE</literal>, and time zones.
</para>
+ <remark role="todo">
+ Following para disagrees with above statement
+ </remark>
+
<para>
+ Replication works correctly between MySQL 5.0 and 5.1 masters
+ and slaves in any combination, even if the master and slave have
+ different global character set variables, and even if the master
+ and slave have different global time zone variables. (Note that
+ this is not true in cases when the master, slave, or both are
+ running MySQL 4.1 or earlier.)
+ </para>
+
+ <para>
We recommend using the most recent MySQL version available
because replication capabilities are continually being improved.
We also recommend using the same version for both the master and
@@ -4037,142 +4151,6 @@
<question>
<para>
- How do I configure a slave if the master is running and I
- do not want to stop it?
- </para>
-
- </question>
-
- <answer>
-
- <para>
- There are several possibilities. If you have taken a
- snapshot backup of the master at some point and recorded
- the binary log filename and offset (from the output of
- <literal>SHOW MASTER STATUS</literal>) corresponding to
- the snapshot, use the following procedure:
- </para>
-
- <orderedlist>
-
- <listitem>
- <para>
- Make sure that the slave is assigned a unique server
- ID.
- </para>
- </listitem>
-
- <listitem>
- <para>
- Execute the following statement on the slave, filling
- in appropriate values for each option:
- </para>
-
-<programlisting>
-mysql> <userinput>CHANGE MASTER TO</userinput>
- -> <userinput>MASTER_HOST='<replaceable>master_host_name</replaceable>',</userinput>
- -> <userinput>MASTER_USER='<replaceable>master_user_name</replaceable>',</userinput>
- -> <userinput>MASTER_PASSWORD='<replaceable>master_pass</replaceable>',</userinput>
- -> <userinput>MASTER_LOG_FILE='<replaceable>recorded_log_file_name</replaceable>',</userinput>
- -> <userinput>MASTER_LOG_POS=<replaceable>recorded_log_position</replaceable>;</userinput>
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- Execute <literal>START SLAVE</literal> on the slave.
- </para>
- </listitem>
-
- </orderedlist>
-
- <para>
- If you do not have a backup of the master server, here is
- a quick procedure for creating one. All steps should be
- performed on the master host.
- </para>
-
- <orderedlist>
-
- <listitem>
- <para>
- Issue this statement to acquire a global read lock:
- </para>
-
-<programlisting>
-mysql> <userinput>FLUSH TABLES WITH READ LOCK;</userinput>
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- With the lock still in place, execute this command (or
- a variation of it):
- </para>
-
-<programlisting>
-shell> <userinput>tar zcf /tmp/backup.tar.gz /var/lib/mysql</userinput>
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- Issue this statement and record the output, which you
- will need later:
- </para>
-
-<programlisting>
-mysql> <userinput>SHOW MASTER STATUS;</userinput>
-</programlisting>
- </listitem>
-
- <listitem>
- <para>
- Release the lock:
- </para>
-
-<programlisting>
-mysql> <userinput>UNLOCK TABLES;</userinput>
-</programlisting>
- </listitem>
-
- </orderedlist>
-
- <para>
- An alternative to using the preceding procedure to make a
- binary copy is to make an SQL dump of the master. To do
- this, you can use <command>mysqldump
- --master-data</command> on your master and later load the
- SQL dump into your slave. However, this is slower than
- making a binary copy.
- </para>
-
- <remark role="todo">
- [pd] Following para isn't so clear...
- </remark>
-
- <para>
- Regardless of which of the two methods you use, afterward
- follow the instructions for the case when you have a
- snapshot and have recorded the log filename and offset.
- You can use the same snapshot to set up several slaves.
- Once you have the snapshot of the master, you can wait to
- set up a slave as long as the binary logs of the master
- are left intact. The two practical limitations on the
- length of time you can wait are the amount of disk space
- available to retain binary logs on the master and the
- length of time it takes the slave to catch up.
- </para>
-
- </answer>
-
- </qandaentry>
-
- <qandaentry>
-
- <question>
-
- <para>
Does the slave need to be connected to the master all the
time?
</para>
@@ -4181,10 +4159,6 @@
<answer>
- <remark role="todo">
- [pd] Is this a promise we should make?
- </remark>
-
<para>
No, it does not. The slave can go down or stay
disconnected for hours or even days, and then reconnect
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r4120 - branches/repupdate/5.1 | mcbrown | 6 Dec |
