Author: jstephens
Date: 2008-11-06 19:05:11 +0100 (Thu, 06 Nov 2008)
New Revision: 12310
Log:
Moving optvars relating to replication and binlogging to
replication-options
Adding replication and binlog optvar summary tables
Split replication-options into separate file
Rebuilt dependencies
*Part 1 of 2 - 4.1/5.0 versions*
Added:
trunk/refman-4.1/replication-options-core.xml
trunk/refman-5.0/replication-options-core.xml
Removed:
trunk/refman-4.1/replication-core.xml
trunk/refman-5.0/replication-configuration-core.xml
Renamed/Moved:
trunk/refman-4.1/replication.xml (from rev 12293,
trunk/refman-4.1/replication-core.xml)
trunk/refman-5.0/replication-configuration.xml (from rev 12293,
trunk/refman-5.0/replication-configuration-core.xml)
Modified:
trunk/refman-4.1/Makefile.depends
trunk/refman-4.1/dba-core.xml
trunk/refman-4.1/manual.xml
trunk/refman-5.0/Makefile.depends
trunk/refman-5.0/dba-core.xml
trunk/refman-5.0/replication.xml
trunk/topic-guides/topics-5.0/Makefile.depends
trunk/topic-guides/topics-common/Makefile.depends
Modified: trunk/refman-4.1/Makefile.depends
===================================================================
--- trunk/refman-4.1/Makefile.depends 2008-11-06 17:30:40 UTC (rev 12309)
+++ trunk/refman-4.1/Makefile.depends 2008-11-06 18:05:11 UTC (rev 12310)
Changed blocks: 21, Lines Added: 70, Lines Deleted: 57; 14159 bytes
@@ -68,7 +68,7 @@
../refman-4.1/metadata/programs-admin-util.idmap \
../refman-4.1/metadata/programs-client.idmap \
../refman-4.1/metadata/programs.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/sql-syntax-data-manipulation.idmap \
../refman-4.1/metadata/sql-syntax-replication.idmap \
@@ -430,7 +430,8 @@
../refman-4.1/metadata/programs-installation.idmap \
../refman-4.1/metadata/programs-server.idmap \
../refman-4.1/metadata/programs-using.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication-options-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/sql-syntax-replication.idmap \
../refman-4.1/metadata/sql-syntax-server-administration.idmap \
@@ -535,7 +536,7 @@
../refman-4.1/metadata/programs-client.idmap \
../refman-4.1/metadata/programs-miscellaneous.idmap \
../refman-4.1/metadata/programs-using.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/spatial-extensions.idmap \
../refman-4.1/metadata/sql-syntax-data-manipulation.idmap \
@@ -553,39 +554,29 @@
dynxml-local-news-4.1-manprepped.xml: $(dynxml_local_news_4_1_SOURCES)
$(dynxml_local_news_4_1_IDMAPS)
dynxml-local-news-4.1-remprepped.xml: $(dynxml_local_news_4_1_SOURCES)
$(dynxml_local_news_4_1_IDMAPS)
dynxml-local-news-4.1.xml: $(dynxml_local_news_4_1_INCLUDES)
-dynxml_local_replication_INCLUDES = \
+dynxml_local_replication_options_INCLUDES = \
../common/fixedchars.ent \
../common/phrases.ent \
- ../dynamic-docs/command-optvars/mysqld.xml \
- ../dynamic-docs/metadata-titles.en.xml \
../refman-common/urls.ent \
all-entities.ent \
- replication-core.xml \
versions.ent
-dynxml_local_replication_IMAGES =
-dynxml_local_replication_SOURCES = dynxml-local-replication.xml
$(dynxml_local_replication_INCLUDES)
-dynxml_local_replication_IDMAPS = \
- ../refman-4.1/metadata/backup.idmap \
+dynxml_local_replication_options_IMAGES =
+dynxml_local_replication_options_SOURCES = dynxml-local-replication-options.xml
$(dynxml_local_replication_options_INCLUDES)
+dynxml_local_replication_options_IDMAPS = \
../refman-4.1/metadata/dba-core.idmap \
../refman-4.1/metadata/errors-problems.idmap \
- ../refman-4.1/metadata/functions-core.idmap \
- ../refman-4.1/metadata/mysql-cluster.idmap \
- ../refman-4.1/metadata/optimization.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
- ../refman-4.1/metadata/se-innodb-core.idmap \
- ../refman-4.1/metadata/sql-syntax-replication.idmap \
- ../refman-4.1/metadata/sql-syntax-server-administration.idmap \
- ../refman-4.1/metadata/storage-engines.idmap \
- ../refman-common/metadata/bug-reports.idmap
-dynxml-local-replication.validpure: $(dynxml_local_replication_SOURCES)
-dynxml-local-replication.titles: $(dynxml_local_replication_SOURCES)
-dynxml-local-replication.useless: $(dynxml_local_replication_SOURCES)
-dynxml-local-replication.valid: $(dynxml_local_replication_SOURCES)
$(dynxml_local_replication_IDMAPS)
-dynxml-local-replication.validwarn: $(dynxml_local_replication_SOURCES)
$(dynxml_local_replication_IDMAPS)
-dynxml-local-replication-prepped.xml: $(dynxml_local_replication_SOURCES)
$(dynxml_local_replication_IDMAPS)
-dynxml-local-replication-manprepped.xml: $(dynxml_local_replication_SOURCES)
$(dynxml_local_replication_IDMAPS)
-dynxml-local-replication-remprepped.xml: $(dynxml_local_replication_SOURCES)
$(dynxml_local_replication_IDMAPS)
-dynxml-local-replication.xml: $(dynxml_local_replication_INCLUDES)
+ ../refman-4.1/metadata/replication-options-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
+ ../refman-4.1/metadata/sql-syntax-replication.idmap
+dynxml-local-replication-options.validpure: $(dynxml_local_replication_options_SOURCES)
+dynxml-local-replication-options.titles: $(dynxml_local_replication_options_SOURCES)
+dynxml-local-replication-options.useless: $(dynxml_local_replication_options_SOURCES)
+dynxml-local-replication-options.valid: $(dynxml_local_replication_options_SOURCES)
$(dynxml_local_replication_options_IDMAPS)
+dynxml-local-replication-options.validwarn: $(dynxml_local_replication_options_SOURCES)
$(dynxml_local_replication_options_IDMAPS)
+dynxml-local-replication-options-prepped.xml: $(dynxml_local_replication_options_SOURCES)
$(dynxml_local_replication_options_IDMAPS)
+dynxml-local-replication-options-manprepped.xml:
$(dynxml_local_replication_options_SOURCES) $(dynxml_local_replication_options_IDMAPS)
+dynxml-local-replication-options-remprepped.xml:
$(dynxml_local_replication_options_SOURCES) $(dynxml_local_replication_options_IDMAPS)
+dynxml-local-replication-options.xml: $(dynxml_local_replication_options_INCLUDES)
errmsgs_client_INCLUDES =
errmsgs_client_IMAGES =
errmsgs_client_SOURCES = errmsgs-client.xml $(errmsgs_client_INCLUDES)
@@ -632,7 +623,7 @@
../refman-4.1/metadata/mysql-cluster-configuration.idmap \
../refman-4.1/metadata/programs-admin-util.idmap \
../refman-4.1/metadata/programs-using.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/sql-syntax-data-definition.idmap \
../refman-4.1/metadata/sql-syntax-data-manipulation.idmap \
@@ -721,7 +712,7 @@
../refman-4.1/metadata/programs-installation.idmap \
../refman-4.1/metadata/programs-server.idmap \
../refman-4.1/metadata/programs-using.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/restrictions.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/sql-syntax-data-definition.idmap \
@@ -756,7 +747,7 @@
../refman-4.1/metadata/internationalization.idmap \
../refman-4.1/metadata/mysql-cluster-limitations.idmap \
../refman-4.1/metadata/programs-installation.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/sql-syntax-server-administration.idmap
internationalization.validpure: $(internationalization_SOURCES)
internationalization.titles: $(internationalization_SOURCES)
@@ -802,7 +793,7 @@
../refman-4.1/metadata/optimization.idmap \
../refman-4.1/metadata/programs-miscellaneous.idmap \
../refman-4.1/metadata/programs.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/spatial-extensions.idmap \
../refman-4.1/metadata/sql-syntax-data-definition.idmap \
@@ -995,7 +986,7 @@
dynxml-local-functions.xml \
dynxml-local-mysql-cluster-optvar.xml \
dynxml-local-news-4.1.xml \
- dynxml-local-replication.xml \
+ dynxml-local-replication-options.xml \
errmsgs-client.xml \
errmsgs-server.xml \
errors-problems.xml \
@@ -1035,7 +1026,7 @@
programs-server.xml \
programs-using.xml \
programs.xml \
- replication-core.xml \
+ replication.xml \
reserved-new-4.0.xml \
reserved-new-4.1.xml \
reservedwords.xml \
@@ -1164,7 +1155,8 @@
../refman-4.1/metadata/programs-server.idmap \
../refman-4.1/metadata/programs-using.idmap \
../refman-4.1/metadata/programs.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication-options-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/restrictions.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/spatial-extensions.idmap \
@@ -1678,7 +1670,8 @@
../refman-4.1/metadata/language-structure.idmap \
../refman-4.1/metadata/optimization.idmap \
../refman-4.1/metadata/programs-client.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication-options-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/sql-syntax-data-manipulation.idmap \
../refman-4.1/metadata/sql-syntax-replication.idmap \
@@ -1779,7 +1772,7 @@
../refman-4.1/metadata/optimization.idmap \
../refman-4.1/metadata/programs-client.idmap \
../refman-4.1/metadata/programs-using.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/sql-syntax-data-definition.idmap \
../refman-4.1/metadata/sql-syntax-data-manipulation.idmap \
@@ -1829,7 +1822,7 @@
../refman-4.1/metadata/optimization.idmap \
../refman-4.1/metadata/programs-admin-util.idmap \
../refman-4.1/metadata/programs-using.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/sql-syntax-server-administration.idmap
programs-admin-util.validpure: $(programs_admin_util_SOURCES)
programs-admin-util.titles: $(programs_admin_util_SOURCES)
@@ -2006,7 +1999,7 @@
../refman-4.1/metadata/programs-miscellaneous.idmap \
../refman-4.1/metadata/programs-server.idmap \
../refman-4.1/metadata/programs-using.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/sql-syntax-data-manipulation.idmap \
../refman-4.1/metadata/sql-syntax-server-administration.idmap \
../refman-4.1/metadata/sql-syntax.idmap \
@@ -2020,18 +2013,37 @@
programs-manprepped.xml: $(programs_SOURCES) $(programs_IDMAPS)
programs-remprepped.xml: $(programs_SOURCES) $(programs_IDMAPS)
-replication_core_INCLUDES =
-replication_core_IMAGES =
-replication_core_SOURCES = replication-core.xml $(replication_core_INCLUDES)
-replication_core_IDMAPS =
-replication-core.validpure: $(replication_core_SOURCES)
-replication-core.titles: $(replication_core_SOURCES)
-replication-core.useless: $(replication_core_SOURCES)
-replication-core.valid: $(replication_core_SOURCES) $(replication_core_IDMAPS)
-replication-core.validwarn: $(replication_core_SOURCES) $(replication_core_IDMAPS)
-replication-core-prepped.xml: $(replication_core_SOURCES) $(replication_core_IDMAPS)
-replication-core-manprepped.xml: $(replication_core_SOURCES) $(replication_core_IDMAPS)
-replication-core-remprepped.xml: $(replication_core_SOURCES) $(replication_core_IDMAPS)
+replication_INCLUDES = \
+ ../common/fixedchars.ent \
+ ../common/phrases.ent \
+ ../refman-common/urls.ent \
+ all-entities.ent \
+ dynxml-local-replication-options.xml \
+ versions.ent
+replication_IMAGES =
+replication_SOURCES = replication.xml $(replication_INCLUDES)
+replication_IDMAPS = \
+ ../refman-4.1/metadata/backup.idmap \
+ ../refman-4.1/metadata/dba-core.idmap \
+ ../refman-4.1/metadata/errors-problems.idmap \
+ ../refman-4.1/metadata/functions-core.idmap \
+ ../refman-4.1/metadata/mysql-cluster.idmap \
+ ../refman-4.1/metadata/optimization.idmap \
+ ../refman-4.1/metadata/replication-options-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
+ ../refman-4.1/metadata/se-innodb-core.idmap \
+ ../refman-4.1/metadata/sql-syntax-replication.idmap \
+ ../refman-4.1/metadata/sql-syntax-server-administration.idmap \
+ ../refman-4.1/metadata/storage-engines.idmap \
+ ../refman-common/metadata/bug-reports.idmap
+replication.validpure: $(replication_SOURCES)
+replication.titles: $(replication_SOURCES)
+replication.useless: $(replication_SOURCES)
+replication.valid: $(replication_SOURCES) $(replication_IDMAPS)
+replication.validwarn: $(replication_SOURCES) $(replication_IDMAPS)
+replication-prepped.xml: $(replication_SOURCES) $(replication_IDMAPS)
+replication-manprepped.xml: $(replication_SOURCES) $(replication_IDMAPS)
+replication-remprepped.xml: $(replication_SOURCES) $(replication_IDMAPS)
reserved_new_4_0_INCLUDES =
reserved_new_4_0_IMAGES =
@@ -2195,7 +2207,7 @@
../refman-4.1/metadata/optimization.idmap \
../refman-4.1/metadata/programs-admin-util.idmap \
../refman-4.1/metadata/programs-client.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/restrictions.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/sql-syntax-data-manipulation.idmap \
@@ -2242,7 +2254,8 @@
sql_syntax_replication_IDMAPS = \
../refman-4.1/metadata/dba-core.idmap \
../refman-4.1/metadata/functions-core.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication-options-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/sql-syntax-replication.idmap \
../refman-4.1/metadata/sql-syntax-server-administration.idmap
sql-syntax-replication.validpure: $(sql_syntax_replication_SOURCES)
@@ -2276,7 +2289,7 @@
../refman-4.1/metadata/optimization.idmap \
../refman-4.1/metadata/programs-admin-util.idmap \
../refman-4.1/metadata/programs-installation.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/sql-syntax-data-definition.idmap \
../refman-4.1/metadata/sql-syntax-data-manipulation.idmap \
@@ -2393,7 +2406,7 @@
../refman-4.1/metadata/programs-admin-util.idmap \
../refman-4.1/metadata/programs-client.idmap \
../refman-4.1/metadata/programs-using.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication-options-core.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/spatial-extensions.idmap \
../refman-4.1/metadata/sql-syntax-data-definition.idmap \
@@ -2428,7 +2441,7 @@
../refman-4.1/metadata/introduction.idmap \
../refman-4.1/metadata/language-structure.idmap \
../refman-4.1/metadata/programs-client.idmap \
- ../refman-4.1/metadata/replication-core.idmap \
+ ../refman-4.1/metadata/replication.idmap \
../refman-4.1/metadata/se-innodb-core.idmap \
../refman-4.1/metadata/sql-syntax-data-definition.idmap \
../refman-4.1/metadata/sql-syntax-data-manipulation.idmap \
Modified: trunk/refman-4.1/dba-core.xml
===================================================================
--- trunk/refman-4.1/dba-core.xml 2008-11-06 17:30:40 UTC (rev 12309)
+++ trunk/refman-4.1/dba-core.xml 2008-11-06 18:05:11 UTC (rev 12310)
Changed blocks: 18, Lines Added: 31, Lines Deleted: 557; 25612 bytes
@@ -220,7 +220,7 @@
<listitem>
<para>
Binary log control options: See
- <xref linkend="binary-log"/>.
+ <xref linkend="replication-options-binary-log"/>.
</para>
</listitem>
@@ -275,44 +275,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_abort-slave-event-count">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>abort-slave-event-count option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>abort-slave-event-count option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--abort-slave-event-count</option>
- </para>
-
- <para>
- When this option is set to some positive integer
- <replaceable>value</replaceable> other than 0 (the default)
- it affects replication behavior as follows: After the slave
- SQL thread has started, <replaceable>value</replaceable> log
- events are allowed to be executed; after that, the slave SQL
- thread does not receive any more events, just as if the
- network connection from the master were cut. The slave
- thread continues to run, and the output from
- <literal role="stmt">SHOW SLAVE STATUS</literal> displays
- <literal>Yes</literal> in both the
- <literal>Slave_IO_Running</literal> and the
- <literal>Slave_SQL_Running</literal> columns, but no further
- events are read from the relay log.
- </para>
-
- <para>
- This option is used internally by the MySQL test suite for
- replication testing and debugging. It is not intended for
- use in a production setting.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_allow-suspicious-udfs">
<indexterm>
<primary>mysqld</primary>
@@ -879,27 +841,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_disconnect-slave-event-count">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>disconnect-slave-event-count option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>disconnect-slave-event-count option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--disconnect-slave-event-count</option>
- </para>
-
- <para>
- This option is used internally by the MySQL test suite for
- replication testing and debugging.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_enable-named-pipe">
<indexterm>
<primary>mysqld</primary>
@@ -1163,64 +1104,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_log-bin">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>log-bin option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>log-bin option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
-
<option>--log-bin[=<replaceable>base_name</replaceable>]</option>
- </para>
-
- <para>
- Enable binary logging. The server logs all statements that
- change data to the binary log, which is used for backup and
- replication. See <xref linkend="binary-log"/>.
- </para>
-
- <para>
- The option value, if given, is the basename for the log
- sequence. The server creates binary log files in sequence by
- adding a numeric suffix to the basename. It is recommended
- that you specify a basename (see
- <xref linkend="open-bugs"/>, for the reason). Otherwise,
- MySQL uses
-
<filename><replaceable>host_name</replaceable>-bin</filename>
- as the basename.
- </para>
- </listitem>
-
- <listitem>
- <para id="option_mysqld_log-bin-index">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>log-bin-index option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>log-bin-index option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
-
<option>--log-bin-index[=<replaceable>file_name</replaceable>]</option>
- </para>
-
- <para>
- The index file for binary log filenames. See
- <xref linkend="binary-log"/>. If you omit the filename, and
- if you didn't specify one with <option>--log-bin</option>,
- MySQL uses
-
<filename><replaceable>host_name</replaceable>-bin.index</filename>
- as the filename.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_log-error">
<indexterm>
<primary>mysqld</primary>
@@ -1510,27 +1393,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_max-binlog-dump-events">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>max-binlog-dump-events option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>max-binlog-dump-events option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--max-binlog-dump-events</option>
- </para>
-
- <para>
- This option is used internally by the MySQL test suite for
- replication testing and debugging.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_memlock">
<indexterm>
<primary>mysqld</primary>
@@ -2343,28 +2205,7 @@
</listitem>
<listitem>
- <para id="option_mysqld_sporadic-binlog-dump-fail">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>sporadic-binlog-dump-fail option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>sporadic-binlog-dump-fail option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--sporadic-binlog-dump-fail</option>
- </para>
-
<para>
- This option is used internally by the MySQL test suite for
- replication testing and debugging.
- </para>
- </listitem>
-
- <listitem>
- <para>
<indexterm>
<primary>mysqld</primary>
<secondary>SSL options</secondary>
@@ -4158,35 +3999,7 @@
</listitem>
<listitem>
- <para id="option_mysqld_init_slave">
- <literal>init_slave</literal>
- </para>
-
<para>
- This variable is similar to <literal>init_connect</literal>,
- but is a string to be executed by a slave server each time
- the SQL thread starts. The format of the string is the same
- as for the <literal>init_connect</literal> variable.
- </para>
-
- <note>
- <para>
- The SQL thread sends an acknowledgement to the client
- before <literal>init_slave</literal> is executed.
- Therefore, it is not guaranteed that
- <literal>init_slave</literal> has been executed when
- <literal role="stmt">START SLAVE</literal> returns. See
- <xref linkend="start-slave"/>, for more information.
- </para>
- </note>
-
- <para>
- This variable was added in MySQL 4.1.2.
- </para>
- </listitem>
-
- <listitem>
- <para>
<literal>innodb_<replaceable>xxx</replaceable></literal>
</para>
@@ -4473,17 +4286,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_log_bin">
- <literal>log_bin</literal>
- </para>
-
- <para>
- Whether the binary log is enabled. This variable was added
- in MySQL 3.23.14. See <xref linkend="binary-log"/>.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_log_error">
<literal>log_error</literal>
</para>
@@ -4495,20 +4297,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_log_slave_updates">
- <literal>log_slave_updates</literal>
- </para>
-
- <para>
- Whether updates received by a slave server from a master
- server should be logged to the slave's own binary log.
- Binary logging must be enabled on the slave for this
- variable to have any effect. This variable was added in
- MySQL 3.23.17. See <xref linkend="replication-options"/>.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_log_slow_queries">
<literal>log_slow_queries</literal>
</para>
@@ -4669,51 +4457,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_max_binlog_cache_size">
- <literal>max_binlog_cache_size</literal>
- </para>
-
- <para>
- If a multiple-statement transaction requires more than this
- many bytes of memory, the server generates a
- <literal>Multi-statement transaction required more than
- 'max_binlog_cache_size' bytes of storage</literal> error.
- The minimum value is 4096, the maximum and default values
- are 4GB. This variable was added in MySQL 3.23.29.
- </para>
- </listitem>
-
- <listitem>
- <para id="option_mysqld_max_binlog_size">
- <literal>max_binlog_size</literal>
- </para>
-
- <para>
- If a write to the binary log causes the current log file
- size to exceed the value of this variable, the server
- rotates the binary logs (closes the current file and opens
- the next one). You cannot set this variable to more than 1GB
- or to less than 4096 bytes. (The minimum before MYSQL 4.0.14
- is 1024 bytes.) The default value is 1GB. This variable was
- added in MySQL 3.23.33.
- </para>
-
- <para>
- A transaction is written in one chunk to the binary log, so
- it is never split between several binary logs. Therefore, if
- you have big transactions, you might see binary logs larger
- than <literal>max_binlog_size</literal>.
- </para>
-
- <para>
- If <literal>max_relay_log_size</literal> is 0, the value of
- <literal>max_binlog_size</literal> applies to relay logs as
- well. <literal>max_relay_log_size</literal> was added in
- MySQL 4.0.14.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_max_connect_errors">
<literal>max_connect_errors</literal>
</para>
@@ -5651,42 +5394,7 @@
</listitem>
<listitem>
- <para id="option_mysqld_relay_log_purge">
- <literal>relay_log_purge</literal>
- </para>
-
<para>
- Disables or enables automatic purging of relay logs as soon
- as they are not needed any more. The default value is 1
- (<literal>ON</literal>). This variable was added in MySQL
- 4.1.1.
- </para>
- </listitem>
-
- <listitem>
- <para id="option_mysqld_relay_log_space_limit">
- <literal>relay_log_space_limit</literal>
- </para>
-
- <para condition="dynamic:optvar:item"
role="5.0:mysqld:relay_log_space_limit"/>
-
- <para>
- The maximum amount of space to use for all relay logs.
- </para>
- </listitem>
-
- <listitem>
- <para id="option_mysqld_rpl-recovery-rank">
- <literal>rpl_recovery_rank</literal>
- </para>
-
- <para>
- This variable is unused.
- </para>
- </listitem>
-
- <listitem>
- <para>
<literal>safe_show_database</literal>
</para>
@@ -5734,20 +5442,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_server-id">
- <literal>server_id</literal>
- </para>
-
- <para>
- The server ID. This value is set by the
- <option>--server-id</option> option. It is used for
- replication to enable master and slave servers to identify
- themselves uniquely. This variable was added in MySQL
- 3.23.26.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_shared_memory">
<literal>shared_memory</literal>
</para>
@@ -5841,83 +5535,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_slave_compressed_protocol">
- <literal>slave_compressed_protocol</literal>
- </para>
-
- <para>
- Whether to use compression of the master/slave protocol if
- both the slave and the master support it. This variable was
- added in MySQL 4.0.3.
- </para>
- </listitem>
-
- <listitem>
- <para id="option_mysqld_slave_load_tmpdir">
- <literal>slave_load_tmpdir</literal>
- </para>
-
- <para>
- The name of the directory where the slave creates temporary
- files for replicating
- <literal role="stmt" condition="load-data">LOAD DATA
- INFILE</literal> statements. This variable was added in
- MySQL 4.0.0.
- </para>
- </listitem>
-
- <listitem>
- <para id="option_mysqld_slave_net_timeout">
- <literal>slave_net_timeout</literal>
- </para>
-
- <para>
- The number of seconds to wait for more data from a
- master/slave connection before aborting the read. This
- timeout applies only to TCP/IP connections, not to
- connections made via Unix socket files, named pipes, or
- shared memory. This variable was added in MySQL 3.23.40.
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>slave_skip_errors</literal>
- </para>
-
- <para>
- Normally, replication stops when an error occurs on the
- slave. This gives you the opportunity to resolve the
- inconsistency in the data manually. This variable tells the
- slave SQL thread to continue replication when a statement
- returns any of the errors listed in the variable value. This
- variable was added in MySQL 3.23.47.
- </para>
- </listitem>
-
- <listitem>
- <para id="option_mysqld_slave_transaction_retries">
- <literal>slave_transaction_retries</literal>
- </para>
-
- <para>
- If a replication slave SQL thread fails to execute a
- transaction because of an <literal>InnoDB</literal> deadlock
- or <literal>InnoDB</literal>'s
- <literal>innodb_lock_wait_timeout</literal> or <literal>NDB
- Cluster</literal>'s
- <literal>TransactionDeadlockDetectionTimeout</literal> or
- <literal>TransactionInactiveTimeout</literal> was exceeded,
- it automatically retries
- <literal>slave_transaction_retries</literal> times before
- stopping with an error. The default in MySQL 4.1 is
- <literal>0</literal>. You must explicitly set the value to
- greater than 0 to enable the <quote>retry</quote> behavior,
- which is probably a good idea.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_slow_launch_time">
<literal>slow_launch_time</literal>
</para>
@@ -6016,19 +5633,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_sql_slave_skip_counter">
- <literal>sql_slave_skip_counter</literal>
- </para>
-
- <para>
- The number of events from the master that a slave server
- should skip. See
- <xref linkend="set-global-sql-slave-skip-counter"/>. This
- variable was added in MySQL 3.23.33.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_storage_engine">
<literal>storage_engine</literal>
</para>
@@ -6039,37 +5643,10 @@
</para>
</listitem>
- <listitem>
- <para id="option_mysqld_sync_binlog">
- <literal>sync_binlog</literal>
- </para>
+
- <para>
- If the value of this variable is greater than 0, the MySQL
- server synchronizes its binary log to disk (using
- <literal>fdatasync()</literal>) after every
- <literal>sync_binlog</literal> writes to the binary log.
- There is one write to the binary log per statement if
- autocommit is enabled, and one write per transaction
- otherwise. The default value of
- <literal>sync_binlog</literal> is 0, which does no
- synchronizing to disk. A value of 1 is the safest choice,
- because in the event of a crash you lose at most one
- statement or transaction from the binary log. However, it is
- also the slowest choice (unless the disk has a
- battery-backed cache, which makes synchronization very
- fast). This variable was added in MySQL 4.1.3.
- </para>
-
- <para>
- If the value of <literal>sync_binlog</literal> is 0 (the
- default), no extra flushing is done. The server relies on
- the operating system to flush the file contents occasionally
- as for any other file.
- </para>
- </listitem>
-
<listitem>
+
<para id="option_mysqld_sync_frm">
<literal>sync_frm</literal>
</para>
@@ -10696,6 +10273,12 @@
</note>
<para>
+ For information about server options and variables affecting the
+ operation of binary logging, see
+ <xref linkend="replication-options-binary-log"/>.
+ </para>
+
+ <para>
The binary log is not used for statements such as
<literal role="stmt">SELECT</literal> or
<literal>SHOW</literal>
that do not modify data. If you want to log all statements (for
@@ -10808,143 +10391,21 @@
A replication slave server by default does not write to its own
binary log any data modifications that are received from the
replication master. To log these modifications, start the slave
- with the <option>--log-slave-updates</option> option.
+ with the <option>--log-slave-updates</option> option (see also
+ <xref linkend="replication-options-slave"/>).
</para>
- <para>
- You can use the following options to <command>mysqld</command>
- to affect what is logged to the binary log. See also the
- discussion that follows this option list.
- </para>
+ <formalpara>
- <para>
- If you are using replication, the options described here affect
- which statements are sent by a master server to its slaves.
- There are also options for slave servers that control which
- statements received from the master to execute or ignore. For
- details, see <xref linkend="replication-options"/>.
- </para>
+ <title>Evaluation of update selection options</title>
- <itemizedlist>
+ <para>
+ The server evaluates the options for logging or ignoring
+ updates to the binary log according to the following rules:
+ </para>
- <listitem>
- <para id="option_mysqld_binlog-do-db">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>binlog-do-db option</secondary>
- </indexterm>
+ </formalpara>
- <indexterm>
- <primary>binlog-do-db option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
-
<option>--binlog-do-db=<replaceable>db_name</replaceable></option>
- </para>
-
- <para>
- Tell the server to restrict binary logging to updates for
- which the default database is
- <replaceable>db_name</replaceable> (that is, the database
- selected by <literal role="stmt">USE</literal>). All other
- databases that are not explicitly mentioned are ignored. If
- you use this option, you should ensure that you do updates
- only in the default database.
- </para>
-
- <para>
- There is an exception to this for
- <literal role="stmt">CREATE DATABASE</literal>,
- <literal role="stmt">ALTER DATABASE</literal>, and
- <literal role="stmt">DROP DATABASE</literal> statements. The
- server uses the database named in the statement (not the
- default database) to decide whether it should log the
- statement.
- </para>
-
- <para>
- An example of what does not work as you might expect: If the
- server is started with
- <literal>binlog-do-db=sales</literal>, and you run
- <literal>USE prices; UPDATE sales.january SET
- amount=amount+1000;</literal>, this statement is
- <emphasis>not</emphasis> written into the binary log.
- </para>
-
- <important>
- <para>
- To log multiple databases, use this option multiple times,
- specifying the option once for each database to be logged.
- </para>
- </important>
- </listitem>
-
- <listitem>
- <para id="option_mysqld_binlog-ignore-db">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>binlog-ignore-db option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>binlog-ignore-db option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
-
<option>--binlog-ignore-db=<replaceable>db_name</replaceable></option>
- </para>
-
- <para>
- Tell the server to suppress binary logging of updates for
- which the default database is
- <replaceable>db_name</replaceable> (that is, the database
- selected by <literal role="stmt">USE</literal>). If you use
- this option, you should ensure that you do updates only in
- the default database.
- </para>
-
- <para>
- As with the <option>--binlog-do-db</option> option, there is
- an exception for the <literal role="stmt">CREATE
- DATABASE</literal>, <literal role="stmt">ALTER
- DATABASE</literal>, and <literal role="stmt">DROP
- DATABASE</literal> statements. The server uses the database
- named in the statement (not the default database) to decide
- whether it should log the statement.
- </para>
-
- <para>
- An example of what does not work as you might expect: If the
- server is started with
- <literal>binlog-ignore-db=sales</literal>, and you run
- <literal>USE prices; UPDATE sales.january SET
- amount=amount+1000;</literal>, this statement
- <emphasis>is</emphasis> written into the binary log.
- </para>
-
- <important>
- <para>
- To ignore multiple databases, use this option multiple
- times, specifying the option once for each database to be
- ignored.
- </para>
- </important>
- </listitem>
-
- </itemizedlist>
-
- <para>
- The server evaluates the options for logging or ignoring updates
- to the binary log according to the following rules. As described
- previously, there is an exception for the
- <literal role="stmt">CREATE DATABASE</literal>,
- <literal role="stmt">ALTER DATABASE</literal>, and
- <literal role="stmt">DROP DATABASE</literal> statements. In
- those cases, the database being <emphasis>created, altered, or
- dropped</emphasis> replaces the default database in the
- following rules:
- </para>
-
<orderedlist>
<listitem>
@@ -11063,6 +10524,19 @@
</orderedlist>
+ <important>
+ <para>
+ An exception is made in the rules just given for the
+ <literal role="stmt">CREATE DATABASE</literal>,
+ <literal role="stmt">ALTER DATABASE</literal>, and
+ <literal role="stmt">DROP DATABASE</literal> statements (see
+ <xref linkend="replication-options-binary-log"/>). In those
+ cases, the database being <emphasis>created, altered, or
+ dropped</emphasis> replaces the default database when
+ determining whether to log or ignore updates.
+ </para>
+ </important>
+
<para>
For example, a slave running with only
<option>--binlog-do-db=sales</option> does not write to the
Modified: trunk/refman-4.1/manual.xml
===================================================================
--- trunk/refman-4.1/manual.xml 2008-11-06 17:30:40 UTC (rev 12309)
+++ trunk/refman-4.1/manual.xml 2008-11-06 18:05:11 UTC (rev 12310)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 606 bytes
@@ -60,7 +60,7 @@
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude"
href="storage-engines.xml"/>
- <xi:include xmlns:xi="http://www.w3.org/2001/XInclude"
href="dynxml-local-replication.xml"/>
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="replication.xml"/>
<xi:include xmlns:xi="http://www.w3.org/2001/XInclude" href="mysql-cluster.xml"/>
Added: trunk/refman-4.1/replication-options-core.xml
===================================================================
--- trunk/refman-4.1/replication.xml (rev 0)
+++ trunk/refman-4.1/replication.xml 2008-11-06 18:05:11 UTC (rev 12310)
Changed blocks: 2, Lines Added: 5908, Lines Deleted: 1; 219904 bytes
@@ -0,0 +1,2130 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE section PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd" [
+<!ENTITY % all.entities SYSTEM "all-entities.ent">
+%all.entities;
+]>
+<section id="replication-options">
+
+ <title>Replication and Binary Logging Options and Variables</title>
+
+ <para>
+ The next few sections contain information about
+ <command>mysqld</command> options and server variables that are
+ used in replication and for controlling the binary log. Options
+ and variables for use on replication masters and replication
+ slaves are covered separately, as are options and variables
+ relating to binary logging. A set of quick-reference tables
+ providing basic information about these options and variables is
+ also included (in the next section following this one).
+ </para>
+
+ <para id="option_mysqld_server-id">
+ Of particular importance is the <option>--server-id</option>
+ option, which is common to both master and slave relication
+ servers. It is used in replication to enable master and slave
+ servers to identify themselves uniquely, and was added in MySQL
+ 3.23.26. Additional information
+ </para>
+
+ <para>
+ On the master and each slave, you <emphasis>must</emphasis> use
+ the <option>--server-id</option> option to establish a unique
+ replication ID. For each server participating in replication, you
+ should pick a positive integer in the range from 1 to
+ 2<superscript>32</superscript> − 1 to act as that
+ server' ID; each ID must be different from every other ID in
+ use by any other replication master or slave. Example:
+ <literal>server-id=3</literal>.
+ </para>
+
+ <section id="replication-options-table">
+
+ <title>Replication and Binary Logging Option and Variable
Reference</title>
+
+ <para>
+ The following tables provide lists of and basic information
+ about the command-line options, server and status variables
+ applicable within <literal>mysqld</literal> relating to
+ replication and the binary log.
+ </para>
+
+ <para condition="dynamic:optvar:fullsummary"
role="4.1:mysqld:section-replication:Replication Option/Variable Summary"/>
+
+ <para>
+ <xref linkend="replication-options-master"/>, provides more
+ detailed information about options and variables relating to
+ replication master servers. For more information about options
+ and variables relating to replication slaves
+ <xref linkend="replication-options-slave"/>.
+ </para>
+
+ <para condition="dynamic:optvar:fullsummary"
role="4.1:mysqld:section-binlog:Binary Option/Variable Summary"/>
+
+ <para>
+ <xref linkend="replication-options-binary-log"/>, provides more
+ detailed information about options and variables relating to
+ binary logging. For additional general information about the
+ binary log, see <xref linkend="binary-log"/>.
+ </para>
+
+ <para>
+ For a table showing <emphasis>all</emphasis> command-line
+ options, server and status variables available for use with
+ <command>mysqld</command>, see
+ <xref linkend="mysqld-option-tables"/>.
+ </para>
+
+ </section>
+
+ <section id="replication-options-master">
+
+ <title>Replication Master Options and Variables</title>
+
+ <para>
+ This section describes the options that you can use on master
+ replication servers. You can specify these options either on the
+ command line or in an option file.
+ </para>
+
+ <para>
+ On the master and each slave, you must use the
+ <literal>server-id</literal> option to establish a unique
+ replication ID. For each server, you should pick a unique
+ positive integer in the range from 1 to
+ 2<superscript>32</superscript> − 1, and each ID must be
+ different from every other ID. Example:
+ <literal>server-id=3</literal>
+ </para>
+
+ <para>
+ For options used on the master for controlling binary logging,
+ see <xref linkend="replication-options-binary-log"/>.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para id="option_mysqld_init_slave">
+ <literal>init_slave</literal>
+ </para>
+
+ <para>
+ This variable is similar to <literal>init_connect</literal>,
+ but is a string to be executed by a slave server each time
+ the SQL thread starts. The format of the string is the same
+ as for the <literal>init_connect</literal> variable.
+ </para>
+
+ <note>
+ <para>
+ The SQL thread sends an acknowledgement to the client
+ before <literal>init_slave</literal> is executed.
+ Therefore, it is not guaranteed that
+ <literal>init_slave</literal> has been executed when
+ <literal role="stmt">START SLAVE</literal> returns. See
+ <xref linkend="start-slave"/>, for more information.
+ </para>
+ </note>
+
+ <para>
+ This variable was added in MySQL 4.1.2.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_show-slave-auth-info">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>show-slave-auth-info option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>show-slave-auth-info option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--show-slave-auth-info</option>
+ </para>
+
+ <para>
+ Display slave usernames and passwords in the output of
+ <literal role="stmt">SHOW SLAVE HOSTS</literal> on the
+ master server for slaves started with the
+ <option>--report-user</option> and
+ <option>--report-password</option> options.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_slave_compressed_protocol">
+ <literal>slave_compressed_protocol</literal>
+ </para>
+
+ <para>
+ Whether to use compression of the master/slave protocol if
+ both the slave and the master support it. This variable was
+ added in MySQL 4.0.3.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_slave_load_tmpdir">
+ <literal>slave_load_tmpdir</literal>
+ </para>
+
+ <para>
+ The name of the directory where the slave creates temporary
+ files for replicating
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> statements. This variable was added in
+ MySQL 4.0.0.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_slave_net_timeout">
+ <literal>slave_net_timeout</literal>
+ </para>
+
+ <para>
+ The number of seconds to wait for more data from a
+ master/slave connection before aborting the read. This
+ timeout applies only to TCP/IP connections, not to
+ connections made via Unix socket files, named pipes, or
+ shared memory. This variable was added in MySQL 3.23.40.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>slave_skip_errors</literal>
+ </para>
+
+ <para>
+ Normally, replication stops when an error occurs on the
+ slave. This gives you the opportunity to resolve the
+ inconsistency in the data manually. This variable tells the
+ slave SQL thread to continue replication when a statement
+ returns any of the errors listed in the variable value. This
+ variable was added in MySQL 3.23.47.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_slave_transaction_retries">
+ <literal>slave_transaction_retries</literal>
+ </para>
+
+ <para>
+ If a replication slave SQL thread fails to execute a
+ transaction because of an <literal>InnoDB</literal> deadlock
+ or <literal>InnoDB</literal>'s
+ <literal>innodb_lock_wait_timeout</literal> or <literal>NDB
+ Cluster</literal>'s
+ <literal>TransactionDeadlockDetectionTimeout</literal> or
+ <literal>TransactionInactiveTimeout</literal> was exceeded,
+ it automatically retries
+ <literal>slave_transaction_retries</literal> times before
+ stopping with an error. The default in MySQL 4.1 is
+ <literal>0</literal>. You must explicitly set the value to
+ greater than 0 to enable the <quote>retry</quote> behavior,
+ which is probably a good idea.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_disconnect-slave-event-count">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>disconnect-slave-event-count option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>disconnect-slave-event-count option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--disconnect-slave-event-count</option>
+ </para>
+
+ <para>
+ This option is used internally by the MySQL test suite for
+ replication testing and debugging.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="replication-options-slave">
+
+ <title>Replication Slave Options and Variables</title>
+
+ <remark role="todo">
+ Should we also describe some of the relevant system variables,
+ such as sync_binlog?
+ </remark>
+
+ <remark role="todo">
+ There really should be a separate section devoted to the use and
+ structure of master.info, and how it interacts with startup
+ options.
+ </remark>
+
+ <para>
+ This section describes the options that you can use on slave
+ replication servers. You can specify these options either on the
+ command line or in an option file.
+ </para>
+
+ <para>
+ On the master and each slave, you must use the
+ <literal>server-id</literal> option to establish a unique
+ replication ID. For each server, you should pick a unique
+ positive integer in the range from 1 to
+ 2<superscript>32</superscript> − 1, and each ID must be
+ different from every other ID. Example:
+ <literal>server-id=3</literal>.
+ </para>
+
+ <para>
+ Some slave server replication options are handled in a special
+ way, in the sense that each is ignored if a
+ <filename>master.info</filename> file exists when the slave
+ starts and contains a value for the option. The following
+ options are handled this way:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <option>--master-host</option>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--master-user</option>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--master-password</option>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--master-port</option>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--master-connect-retry</option>
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ As of MySQL 4.1.1, the following options also are handled
+ specially:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <option>--master-ssl</option>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--master-ssl-ca</option>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--master-ssl-capath</option>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--master-ssl-cert</option>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--master-ssl-cipher</option>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--master-ssl-key</option>
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The <filename>master.info</filename> file format in 4.1.1
+ changed to include values corresponding to the SSL options. In
+ addition, the 4.1.1 file format includes as its first line the
+ number of lines in the file. (See <xref linkend="slave-logs"/>.)
+ If you upgrade an older server to 4.1.1, the new server upgrades
+ the <filename>master.info</filename> file to the new format
+ automatically when it starts. However, if you downgrade a 4.1.1
+ or newer server to a version older than 4.1.1, you should
+ manually remove the first line before starting the older server
+ for the first time. Note that, in this case, the downgraded
+ server no longer can use an SSL connection to communicate with
+ the master.
+ </para>
+
+ <para>
+ If no <filename>master.info</filename> file exists when the
+ slave server starts, it uses the values for those options that
+ are specified in option files or on the command line. This
+ occurs when you start the server as a replication slave for the
+ very first time, or when you have run <literal role="stmt">RESET
+ SLAVE</literal> and then have shut down and restarted the slave.
+ </para>
+
+ <para>
+ If the <filename>master.info</filename> file exists when the
+ slave server starts, the server uses its contents and ignores
+ any options that correspond to the values listed in the file.
+ Thus, if you start the slave server with different values of the
+ startup options that correspond to values in the
+ <filename>master.info</filename> file, the different values have
+ no effect, because the server continues to use the
+ <filename>master.info</filename> file. To use different values,
+ you must either restart after removing the
+ <filename>master.info</filename> file or (preferably) use the
+ <literal role="stmt">CHANGE MASTER TO</literal> statement to
+ reset the values while the slave is running.
+ </para>
+
+ <para>
+ Suppose that you specify this option in your
+ <filename>my.cnf</filename> file:
+ </para>
+
+<programlisting>
+[mysqld]
+master-host=<replaceable>some_host</replaceable>
+</programlisting>
+
+ <para>
+ The first time you start the server as a replication slave, it
+ reads and uses that option from the <filename>my.cnf</filename>
+ file. The server then records the value in the
+ <filename>master.info</filename> file. The next time you start
+ the server, it reads the master host value from the
+ <filename>master.info</filename> file only and ignores the value
+ in the option file. If you modify the
+ <filename>my.cnf</filename> file to specify a different master
+ host of <replaceable>some_other_host</replaceable>, the change
+ still has no effect. You should use <literal role="stmt">CHANGE
+ MASTER TO</literal> instead.
+ </para>
+
+ <formalpara role="mnmas">
+
+ <title>MySQL Enterprise</title>
+
+ <para>
+ For expert advice regarding slave startup options subscribe to
+ the MySQL Enterprise Monitor. For more information, see
+ <ulink url="&base-url-enterprise;advisors.html"/>.
+ </para>
+
+ </formalpara>
+
+ <para>
+ Because the server gives an existing
+ <filename>master.info</filename> file precedence over the
+ startup options just described, you might prefer not to use
+ startup options for these values at all, and instead specify
+ them by using the <literal role="stmt">CHANGE MASTER
+ TO</literal> statement. See <xref linkend="change-master-to"/>.
+ </para>
+
+ <para>
+ This example shows a more extensive use of startup options to
+ configure a slave server:
+ </para>
+
+<programlisting>
+[mysqld]
+server-id=2
+master-host=db-master.mycompany.com
+master-port=3306
+master-user=pertinax
+master-password=freitag
+master-connect-retry=60
+report-host=db-slave.mycompany.com
+</programlisting>
+
+ <para>
+ The following list describes startup options for controlling
+ replication. Many of these options can be reset while the server
+ is running by using the <literal role="stmt">CHANGE MASTER
+ TO</literal> statement. Others, such as the
+ <option>--replicate-*</option> options, can be set only when the
+ slave server starts.
+ </para>
+
+ <remark role="todo">
+ Add this: Replication-related system variables are discussed
+ later in this section.
+ </remark>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>log-slave-updates option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>log-slave-updates option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--log-slave-updates</option>
+ </para>
+
+ <para>
+ Normally, a slave does not log to its own binary log any
+ updates that are received from a master server. This option
+ tells the slave to log the updates performed by its SQL
+ thread to its own binary log. For this option to have any
+ effect, the slave must also be started with the
+ <option>--log-bin</option> option to enable binary logging.
+ <option>--log-slave-updates</option> is used when you want
+ to chain replication servers. For example, you might want to
+ set up replication servers using this arrangement:
+ </para>
+
+<programlisting>
+A -> B -> C
+</programlisting>
+
+ <para>
+ Here, A serves as the master for the slave B, and B serves
+ as the master for the slave C. For this to work, B must be
+ both a master <emphasis>and</emphasis> a slave. You must
+ start both A and B with <option>--log-bin</option> to enable
+ binary logging, and B with the
+ <option>--log-slave-updates</option> option so that updates
+ received from A are logged by B to its binary log.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>log-warnings option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>log-warnings option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--log-warnings[=<replaceable>level</replaceable>]</option>
+ </para>
+
+ <para>
+ This option causes a server to print more messages to the
+ error log about what it is doing. With respect to
+ replication, the server generates warnings that it succeeded
+ in reconnecting after a network/connection failure, and
+ informs you as to how each slave thread started. This option
+ is enabled by default as of MySQL 4.0.19 and 4.1.2; to
+ disable it, use <option>--skip-log-warnings</option>. As of
+ MySQL 4.0.21 and 4.1.3, aborted connections are not logged
+ to the error log unless the value is greater than 1.
+ </para>
+
+ <para>
+ Note that the effects of this option are not limited to
+ replication. It produces warnings across a spectrum of
+ server activities.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-connect-retry option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-connect-retry option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--master-connect-retry=<replaceable>seconds</replaceable></option>
+ </para>
+
+ <para>
+ The number of seconds that the slave thread sleeps before
+ trying to reconnect to the master in case the master goes
+ down or the connection is lost. The value in the
+ <filename>master.info</filename> file takes precedence if it
+ can be read. If not set, the default is 60. Connection
+ retries are not invoked until the slave times out reading
+ data from the master according to the value of
+ <option>--slave-net-timeout</option>. The number of
+ reconnection attempts is limited by the
+ <option>--master-retry-count</option> option.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-host option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-host option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--master-host=<replaceable>host_name</replaceable></option>
+ </para>
+
+ <para>
+ The hostname or IP number of the master replication server.
+ The value in <filename>master.info</filename> takes
+ precedence if it can be read. If no master host is
+ specified, the slave thread does not start.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-info-file option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-info-file option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--master-info-file=<replaceable>file_name</replaceable></option>
+ </para>
+
+ <para>
+ The name to use for the file in which the slave records
+ information about the master. The default name is
+ <filename>master.info</filename> in the data directory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-password option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-password option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--master-password=<replaceable>password</replaceable></option>
+ </para>
+
+ <para>
+ The password of the account that the slave thread uses for
+ authentication when it connects to the master. The value in
+ the <filename>master.info</filename> file takes precedence
+ if it can be read. If not set, an empty password is assumed.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-port option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-port option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--master-port=<replaceable>port_number</replaceable></option>
+ </para>
+
+ <para>
+ The TCP/IP port number that the master is listening on. The
+ value in the <filename>master.info</filename> file takes
+ precedence if it can be read. If not set, the compiled-in
+ setting is assumed (normally 3306).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-retry-count option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-retry-count option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--master-retry-count=<replaceable>count</replaceable></option>
+ </para>
+
+ <para>
+ The number of times that the slave tries to connect to the
+ master before giving up. Reconnects are attempted at
+ intervals set by the <literal role="stmt">CHANGE MASTER
+ TO</literal> statement or
+ <option>--master-connect-retry</option> option and
+ reconnects are triggered when data reads by the slave time
+ out according to the <option>--slave-net-timeout</option>
+ option. The default value is 86400.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-ssl option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-ssl option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-ssl-ca option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-ssl-ca option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-ssl-capath option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-ssl-capath option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-ssl-cert option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-ssl-cert option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-ssl-cipher option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-ssl-cipher option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-ssl-key option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-ssl-key option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--master-ssl</option>,
+
<option>--master-ssl-ca=<replaceable>file_name</replaceable></option>,
+
<option>--master-ssl-capath=<replaceable>directory_name</replaceable></option>,
+
<option>--master-ssl-cert=<replaceable>file_name</replaceable></option>,
+
<option>--master-ssl-cipher=<replaceable>cipher_list</replaceable></option>,
+
<option>--master-ssl-key=<replaceable>file_name</replaceable></option>
+ </para>
+
+ <para>
+ These options are used for setting up a secure replication
+ connection to the master server using SSL. Their meanings
+ are the same as the corresponding <option>--ssl</option>,
+ <option>--ssl-ca</option>,
<option>--ssl-capath</option>,
+ <option>--ssl-cert</option>,
<option>--ssl-cipher</option>,
+ <option>--ssl-key</option> options that are described in
+ <xref linkend="ssl-options"/>. The values in the
+ <filename>master.info</filename> file take precedence if
+ they can be read.
+ </para>
+
+ <para>
+ These options are operational as of MySQL 4.1.1.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>master-user option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>master-user option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--master-user=<replaceable>user_name</replaceable></option>
+ </para>
+
+ <para>
+ The username of the account that the slave thread uses for
+ authentication when it connects to the master. This account
+ must have the <literal>REPLICATION SLAVE</literal>
+ privilege. <literal>FILE</literal> privilege instead.) The
+ value in the <filename>master.info</filename> file takes
+ precedence if it can be read. If the master username is not
+ set, the name <literal>test</literal> is assumed.
+ </para>
+ </listitem>
+
+ <listitem>
+ <remark role="todo">
+ [pd]This is really a system variable, not an option.
+ </remark>
+
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>max-relay-log-size option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>max-relay-log-size option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--max-relay-log-size=<replaceable>size</replaceable></option>
+ </para>
+
+ <para>
+ The size at which the server rotates relay log files
+ automatically. For more information, see
+ <xref linkend="slave-logs"/>. Default is 1GB.
+ </para>
+
+ <para>
+ This option is available as of MySQL 4.0.14.
+ </para>
+ </listitem>
+
+
+ <listitem>
+ <para id="option_mysqld_relay_log_space_limit">
+ <literal>relay_log_space_limit</literal>
+ </para>
+
+ <para condition="dynamic:optvar:item"
role="5.0:mysqld:relay_log_space_limit"/>
+
+ <para>
+ The maximum amount of space to use for all relay logs.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_rpl-recovery-rank">
+ <literal>rpl_recovery_rank</literal>
+ </para>
+
+ <para>
+ This variable is unused.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>read-only option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>read-only option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--read-only</option>
+ </para>
+
+ <para>
+ When this option is given, the server allows no updates
+ except from users that have the <literal>SUPER</literal>
+ privilege or (on a slave server) from updates performed by
+ slave threads. On a slave server, this can be useful to
+ ensure that the slave accepts updates only from its master
+ server and not from clients. This variable does not apply to
+ <literal>TEMPORARY</literal> tables.
+ </para>
+
+ <para>
+ This option is available as of MySQL 4.0.14.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>relay-log option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>relay-log option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--relay-log=<replaceable>file_name</replaceable></option>
+ </para>
+
+ <para>
+ The basename for the relay log. The default basename is
+
<filename><replaceable>host_name</replaceable>-relay-bin</filename>.
+ The server creates relay log files in sequence by adding a
+ numeric suffix to the basename. You can specify the option
+ to create hostname-independent relay log names, or if your
+ relay logs tend to be big (and you don't want to decrease
+ <literal>max_relay_log_size</literal>) and you need to put
+ them in some area different from the data directory, or if
+ you want to increase speed by balancing load between disks.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>relay-log-index option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>relay-log-index option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--relay-log-index=<replaceable>file_name</replaceable></option>
+ </para>
+
+ <para>
+ The name to use for the relay log index file. The default
+ name is
+
<filename><replaceable>host_name</replaceable>-relay-bin.index</filename>
+ in the data directory, where
+ <replaceable>host_name</replaceable> is the name of the
+ slave server.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>relay-log-info-file option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>relay-log-info-file option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--relay-log-info-file=<replaceable>file_name</replaceable></option>
+ </para>
+
+ <para>
+ The name to use for the file in which the slave records
+ information about the relay logs. The default name is
+ <filename>relay-log.info</filename> in the data directory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <remark role="todo">
+ [pd]This is really a system variable, not an option.
+ </remark>
+
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>relay-log-purge option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>relay-log-purge option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--relay-log-purge={0|1}</option>
+ </para>
+
+ <para>
+ Disable or enable automatic purging of relay logs as soon as
+ they are not needed any more. The default value is 1
+ (enabled). This is a global variable that can be changed
+ dynamically with <literal>SET GLOBAL relay_log_purge =
+ <replaceable>N</replaceable></literal>.
+ </para>
+
+ <para>
+ This option is available as of MySQL 4.1.1.
+ </para>
+ </listitem>
+
+ <listitem>
+ <remark role="todo">
+ [pd]This is really a system variable, not an option.
+ </remark>
+
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>relay-log-space-limit option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>relay-log-space-limit option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--relay-log-space-limit=<replaceable>size</replaceable></option>
+ </para>
+
+ <para>
+ This option places an upper limit on the total size in bytes
+ of all relay logs on the slave. A value of 0 means <quote>no
+ limit.</quote> This is useful for a slave server host that
+ has limited disk space. When the limit is reached, the I/O
+ thread stops reading binary log events from the master
+ server until the SQL thread has caught up and deleted some
+ unused relay logs. Note that this limit is not absolute:
+ There are cases where the SQL thread needs more events
+ before it can delete relay logs. In that case, the I/O
+ thread exceeds the limit until it becomes possible for the
+ SQL thread to delete some relay logs, because not doing so
+ would cause a deadlock (which is what happens before MySQL
+ 4.0.13). You should not set
+ <option>--relay-log-space-limit</option> to less than twice
+ the value of <option>--max-relay-log-size</option> (or
+ <option>--max-binlog-size</option> if
+ <option>--max-relay-log-size</option> is 0). In that case,
+ there is a chance that the I/O thread waits for free space
+ because <option>--relay-log-space-limit</option> is
+ exceeded, but the SQL thread has no relay log to purge and
+ is unable to satisfy the I/O thread. This forces the I/O
+ thread to temporarily ignore
+ <option>--relay-log-space-limit</option>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>replicate-do-db option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>replicate-do-db option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--replicate-do-db=<replaceable>db_name</replaceable></option>
+ </para>
+
+ <para>
+ Tell the slave to restrict replication to statements where
+ the default database (that is, the one selected by
+ <literal role="stmt">USE</literal>) is
+ <replaceable>db_name</replaceable>. To specify more than one
+ database, use this option multiple times, once for each
+ database. Note that this does not replicate cross-database
+ statements such as <literal>UPDATE
+ <replaceable>some_db.some_table</replaceable> SET
+ foo='bar'</literal> while having selected a different
+ database or no database.
+ </para>
+
+ <warning>
+ <para>
+ To specify multiple databases you
+ <emphasis>must</emphasis> use multiple instances of this
+ option. Because database names can contain commas, if you
+ supply a comma separated list then the list will be
+ treated as the name of a single database.
+ </para>
+ </warning>
+
+ <para>
+ An example of what does not work as you might expect: If the
+ slave is started with
+ <option>--replicate-do-db=sales</option> and you issue the
+ following statements on the master, the
+ <literal role="stmt">UPDATE</literal> statement is
+ <emphasis>not</emphasis> replicated:
+ </para>
+
+<programlisting>
+USE prices;
+UPDATE sales.january SET amount=amount+1000;
+</programlisting>
+
+ <para>
+ The main reason for this
+ <quote>check-just-the-default-database</quote> behavior is
+ that it's difficult from the statement alone to know whether
+ it should be replicated (for example, if you are using
+ multiple-table <literal role="stmt">DELETE</literal> or
+ multiple-table <literal role="stmt">UPDATE</literal>
+ statements that go across multiple databases). It is also
+ faster to check only the default database rather than all
+ databases if there is no need.
+ </para>
+
+ <para>
+ If you need cross-database updates to work, make sure that
+ you have MySQL 3.23.28 or later, and use
+
<option>--replicate-wild-do-table=<replaceable>db_name</replaceable>.%</option>
+ instead. See <xref linkend="replication-rules"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>replicate-do-table option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>replicate-do-table option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--replicate-do-table=<replaceable>db_name.tbl_name</replaceable></option>
+ </para>
+
+ <para>
+ Tell the slave thread to restrict replication to the
+ specified table. To specify more than one table, use this
+ option multiple times, once for each table. This works for
+ cross-database updates, in contrast to
+ <option>--replicate-do-db</option>. See
+ <xref linkend="replication-rules"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>replicate-ignore-db option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>replicate-ignore-db option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--replicate-ignore-db=<replaceable>db_name</replaceable></option>
+ </para>
+
+ <para>
+ Tells the slave to not replicate any statement where the
+ default database (that is, the one selected by
+ <literal role="stmt">USE</literal>) is
+ <replaceable>db_name</replaceable>. To specify more than one
+ database to ignore, use this option multiple times, once for
+ each database. You should not use this option if you are
+ using cross-database updates and you do not want these
+ updates to be replicated. See
+ <xref linkend="replication-rules"/>.
+ </para>
+
+ <para>
+ An example of what does not work as you might expect: If the
+ slave is started with
+ <option>--replicate-ignore-db=sales</option> and you issue
+ the following statements on the master, the
+ <literal role="stmt">UPDATE</literal> statement
+ <emphasis>is</emphasis> replicated:
+ </para>
+
+<programlisting>
+USE prices;
+UPDATE sales.january SET amount=amount+1000;
+</programlisting>
+
+ <note>
+ <para>
+ In the preceding example the statement is replicated
+ because <option>--replicate-ignore-db</option> only
+ applies to the default database (set through the
+ <literal role="stmt">USE</literal> statement). Because the
+ <literal>sales</literal> database was specified explicitly
+ in the statement, the statement has not been filtered.
+ </para>
+ </note>
+
+ <para>
+ If you need cross-database updates to work, use
+
<option>--replicate-wild-ignore-table=<replaceable>db_name</replaceable>.%</option>
+ instead. See <xref linkend="replication-rules"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>replicate-ignore-table option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>replicate-ignore-table option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--replicate-ignore-table=<replaceable>db_name.tbl_name</replaceable></option>
+ </para>
+
+ <para>
+ Tells the slave thread to not replicate any statement that
+ updates the specified table, even if any other tables might
+ be updated by the same statement. To specify more than one
+ table to ignore, use this option multiple times, once for
+ each table. This works for cross-database updates, in
+ contrast to <option>--replicate-ignore-db</option>. See
+ <xref linkend="replication-rules"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>replicate-rewrite-db option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>replicate-rewrite-db option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--replicate-rewrite-db=<replaceable>from_name</replaceable>-><replaceable>to_name</replaceable></option>
+ </para>
+
+ <para>
+ Tells the slave to translate the default database (that is,
+ the one selected by <literal role="stmt">USE</literal>) to
+ <replaceable>to_name</replaceable> if it was
+ <replaceable>from_name</replaceable> on the master. Only
+ statements involving tables are affected (not statements
+ such as <literal role="stmt">CREATE DATABASE</literal>,
+ <literal role="stmt">DROP DATABASE</literal>, and
+ <literal role="stmt">ALTER DATABASE</literal>), and only if
+ <replaceable>from_name</replaceable> is the default database
+ on the master. This does not work for cross-database
+ updates. To specify multiple rewrites, use this option
+ multiple times. The server uses the first one with a
+ <replaceable>from_name</replaceable> value that matches. The
+ database name translation is done
+ <emphasis>before</emphasis> the
+ <option>--replicate-*</option> rules are tested.
+ </para>
+
+ <para>
+ If you use this option on the command line and the
+ <quote><literal>></literal></quote> character
is special
+ to your command interpreter, quote the option value. For
+ example:
+ </para>
+
+<programlisting>
+shell> <userinput>mysqld
--replicate-rewrite-db="<replaceable>olddb</replaceable>-><replaceable>newdb</replaceable>"</userinput>
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>replicate-same-server-id option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>replicate-same-server-id option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--replicate-same-server-id</option>
+ </para>
+
+ <para>
+ To be used on slave servers. Usually you should use the
+ default setting of 0, to prevent infinite loops caused by
+ circular replication. If set to 1, the slave does not skip
+ events having its own server ID. Normally, this is useful
+ only in rare configurations. Cannot be set to 1 if
+ <option>--log-slave-updates</option> is used. Be careful
+ that starting from MySQL 4.1, by default the slave I/O
+ thread does not even write binary log events to the relay
+ log if they have the slave's server id (this optimization
+ helps save disk usage compared to 4.0). So if you want to
+ use <option>--replicate-same-server-id</option> in 4.1
+ versions, be sure to start the slave with this option before
+ you make the slave read its own events that you want the
+ slave SQL thread to execute.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>replicate-wild-do-table option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>replicate-wild-do-table option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--replicate-wild-do-table=<replaceable>db_name.tbl_name</replaceable></option>
+ </para>
+
+ <para>
+ Tells the slave thread to restrict replication to statements
+ where any of the updated tables match the specified database
+ and table name patterns. Patterns can contain the
+ <quote><literal>%</literal></quote> and
+ <quote><literal>_</literal></quote> wildcard
characters,
+ which have the same meaning as for the
+ <literal role="op">LIKE</literal> pattern-matching operator.
+ To specify more than one table, use this option multiple
+ times, once for each table. This works for cross-database
+ updates. See <xref linkend="replication-rules"/>.
+ </para>
+
+ <para>
+ Example:
+ <option>--replicate-wild-do-table=foo%.bar%</option>
+ replicates only updates that use a table where the database
+ name starts with <literal>foo</literal> and the table name
+ starts with <literal>bar</literal>.
+ </para>
+
+ <para>
+ If the table name pattern is <literal>%</literal>, it
+ matches any table name and the option also applies to
+ database-level statements (<literal role="stmt">CREATE
+ DATABASE</literal>, <literal role="stmt">DROP
+ DATABASE</literal>, and <literal role="stmt">ALTER
+ DATABASE</literal>). For example, if you use
+ <option>--replicate-wild-do-table=foo%.%</option>,
+ database-level statements are replicated if the database
+ name matches the pattern <literal>foo%</literal>.
+ </para>
+
+ <para>
+ To include literal wildcard characters in the database or
+ table name patterns, escape them with a backslash. For
+ example, to replicate all tables of a database that is named
+ <literal>my_own%db</literal>, but not replicate tables from
+ the <literal>my1ownAABCdb</literal> database, you should
+ escape the <quote><literal>_</literal></quote> and
+ <quote><literal>%</literal></quote> characters like
this:
+ <option>--replicate-wild-do-table=my\_own\%db</option>. If
+ you're using the option on the command line, you might need
+ to double the backslashes or quote the option value,
+ depending on your command interpreter. For example, with the
+ <command>bash</command> shell, you would need to type
+ <option>--replicate-wild-do-table=my\\_own\\%db</option>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>replicate-wild-ignore-table option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>replicate-wild-ignore-table option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--replicate-wild-ignore-table=<replaceable>db_name.tbl_name</replaceable></option>
+ </para>
+
+ <para>
+ Tells the slave thread not to replicate a statement where
+ any table matches the given wildcard pattern. To specify
+ more than one table to ignore, use this option multiple
+ times, once for each table. This works for cross-database
+ updates. See <xref linkend="replication-rules"/>.
+ </para>
+
+ <para>
+ Example:
+ <option>--replicate-wild-ignore-table=foo%.bar%</option>
+ does not replicate updates that use a table where the
+ database name starts with <literal>foo</literal> and the
+ table name starts with <literal>bar</literal>.
+ </para>
+
+ <para>
+ For information about how matching works, see the
+ description of the
+ <option>--replicate-wild-do-table</option> option. The rules
+ for including literal wildcard characters in the option
+ value are the same as for
+ <option>--replicate-wild-ignore-table</option> as well.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_report-host">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>report-host option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>report-host option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--report-host=<replaceable>host_name</replaceable></option>
+ </para>
+
+ <para>
+ The hostname or IP number of the slave to be reported to the
+ master during slave registration. This value appears in the
+ output of <literal role="stmt">SHOW SLAVE HOSTS</literal> on
+ the master server. Leave the value unset if you do not want
+ the slave to register itself with the master. Note that it
+ is not sufficient for the master to simply read the IP
+ number of the slave from the TCP/IP socket after the slave
+ connects. Due to NAT and other routing issues, that IP may
+ not be valid for connecting to the slave from the master or
+ other hosts.
+ </para>
+
+ <remark>
+ For the moment, this option is of no real interest; it is
+ meant for failover replication, which is not implemented
+ yet.
+ </remark>
+
+ <para>
+ This option is available as of MySQL 4.0.0.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_report-password">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>report-password option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>report-password option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--report-password=<replaceable>password</replaceable></option>
+ </para>
+
+ <para>
+ The account password of the slave to be reported to the
+ master during slave registration. This value appears in the
+ output of <literal role="stmt">SHOW SLAVE HOSTS</literal> on
+ the master server if the
+ <option>--show-slave-auth-info</option> option is given.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_report-port">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>report-port option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>report-port option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--report-port=<replaceable>slave_port_num</replaceable></option>
+ </para>
+
+ <para>
+ The TCP/IP port number for connecting to the slave, to be
+ reported to the master during slave registration. Set this
+ only if the slave is listening on a non-default port or if
+ you have a special tunnel from the master or other clients
+ to the slave. If you are not sure, do not use this option.
+ </para>
+
+ <remark>
+ For the moment, this option is of no real interest; it is
+ meant for failover replication, which is not implemented
+ yet.
+ </remark>
+
+ <para>
+ This option is available as of MySQL 4.0.0.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_report-user">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>report-user option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>report-user option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--report-user=<replaceable>user_name</replaceable></option>
+ </para>
+
+ <para>
+ The account username of the slave to be reported to the
+ master during slave registration. This value appears in the
+ output of <literal role="stmt">SHOW SLAVE HOSTS</literal> on
+ the master server if the
+ <option>--show-slave-auth-info</option> option is given.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>skip-slave-start option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>skip-slave-start option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--skip-slave-start</option>
+ </para>
+
+ <para>
+ Tells the slave server not to start the slave threads when
+ the server starts. To start the threads later, use a
+ <literal role="stmt">START SLAVE</literal> statement.
+ </para>
+ </listitem>
+
+ <listitem>
+ <remark role="todo">
+ [pd]This is really a system variable, not an option.
+ </remark>
+
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>slave_compressed_protocol option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>slave_compressed_protocol option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--slave_compressed_protocol={0|1}</option>
+ </para>
+
+ <para>
+ If this option is set to 1, use compression for the
+ slave/master protocol if both the slave and the master
+ support it. Default is 0 (no compression).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>slave-load-tmpdir option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>slave-load-tmpdir option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--slave-load-tmpdir=<replaceable>file_name</replaceable></option>
+ </para>
+
+ <para>
+ The name of the directory where the slave creates temporary
+ files. This option is by default equal to the value of the
+ <literal>tmpdir</literal> system variable. When the slave
+ SQL thread replicates a
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> statement, it extracts the file to be
+ loaded from the relay log into temporary files, and then
+ loads these into the table. If the file loaded on the master
+ is huge, the temporary files on the slave are huge, too.
+ Therefore, it might be advisable to use this option to tell
+ the slave to put temporary files in a directory located in
+ some filesystem that has a lot of available space. In that
+ case, the relay logs are huge as well, so you might also
+ want to use the <option>--relay-log</option> option to place
+ the relay logs in that filesystem.
+ </para>
+
+ <para>
+ The directory specified by this option should be located in
+ a disk-based filesystem (not a memory-based filesystem)
+ because the temporary files used to replicate
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> must survive machine restarts. The
+ directory also should not be one that is cleared by the
+ operating system during the system startup process.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>slave-net-timeout option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>slave-net-timeout option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--slave-net-timeout=<replaceable>seconds</replaceable></option>
+ </para>
+
+ <para>
+ The number of seconds to wait for more data from the master
+ before the slave considers the connection broken, aborts the
+ read, and tries to reconnect. The first retry occurs
+ immediately after the timeout. The interval between retries
+ is controlled by the <literal role="stmt">CHANGE MASTER
+ TO</literal> statement or
+ <option>--master-connect-retry</option> option and the
+ numger of reconnection attempts is limited by the
+ <option>--master-retry-count</option> option. The default is
+ 3600 seconds (one hour).
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>slave-skip-errors option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>slave-skip-errors option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--slave-skip-errors=[<replaceable>err_code1</replaceable>,<replaceable>err_code2</replaceable>,...|all]</option>
+ </para>
+
+ <para>
+ Normally, replication stops when an error occurs on the
+ slave. This gives you the opportunity to resolve the
+ inconsistency in the data manually. This option tells the
+ slave SQL thread to continue replication when a statement
+ returns any of the errors listed in the option value.
+ </para>
+
+ <para>
+ Do not use this option unless you fully understand why you
+ are getting errors. If there are no bugs in your replication
+ setup and client programs, and no bugs in MySQL itself, an
+ error that stops replication should never occur.
+ Indiscriminate use of this option results in slaves becoming
+ hopelessly out of synchrony with the master, with you having
+ no idea why this has occurred.
+ </para>
+
+ <para>
+ For error codes, you should use the numbers provided by the
+ error message in your slave error log and in the output of
+ <literal role="stmt">SHOW SLAVE STATUS</literal>.
+ <xref linkend="error-handling"/>, lists server error codes.
+ </para>
+
+ <para>
+ You can also (but should not) use the very non-recommended
+ value of <literal>all</literal> to cause the slave to ignore
+ all error messages and keeps going regardless of what
+ happens. Needless to say, if you use <literal>all</literal>,
+ there are no guarantees regarding the integrity of your
+ data. Please do not complain (or file bug reports) in this
+ case if the slave's data is not anywhere close to what it is
+ on the master. <emphasis>You have been warned</emphasis>.
+ </para>
+
+ <para>
+ Examples:
+ </para>
+
+<programlisting>
+--slave-skip-errors=1062,1053
+--slave-skip-errors=all
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_sql_slave_skip_counter">
+ <literal>sql_slave_skip_counter</literal>
+ </para>
+
+ <para>
+ The number of events from the master that a slave server
+ should skip. See
+ <xref linkend="set-global-sql-slave-skip-counter"/>. This
+ variable was added in MySQL 3.23.33.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <remark role="todo">
+ Add here: Discuss replication-related system variables.
+ sync_binlog.
+ </remark>
+
+ </section>
+
+ <section id="replication-options-binary-log">
+
+ <title>Binary Log Options and Variables</title>
+
+ <para>
+ You can use the options to <command>mysqld</command> that are
+ described in this section to affect the operation of the binary
+ log as well as to control which statements are written to the
+ binary log. For additional information about the binary log, see
+ <xref linkend="binary-log"/>.
+ </para>
+
+ <formalpara>
+
+ <title>Core options</title>
+
+ <para>
+ The following list provides information about options for
+ enabling and configuring the binary log:
+ </para>
+
+ </formalpara>
+
+ <itemizedlist>
+
+ <listitem>
+ <para id="option_mysqld_log-bin">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>log-bin option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>log-bin option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--log-bin[=<replaceable>base_name</replaceable>]</option>
+ </para>
+
+ <para>
+ Enable binary logging. The server logs all statements that
+ change data to the binary log, which is used for backup and
+ replication. See <xref linkend="binary-log"/>.
+ </para>
+
+ <para>
+ The option value, if given, is the basename for the log
+ sequence. The server creates binary log files in sequence by
+ adding a numeric suffix to the basename. It is recommended
+ that you specify a basename (see
+ <xref linkend="open-bugs"/>, for the reason). Otherwise,
+ MySQL uses
+
<filename><replaceable>host_name</replaceable>-bin</filename>
+ as the basename.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_log-bin-index">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>log-bin-index option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>log-bin-index option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--log-bin-index[=<replaceable>file_name</replaceable>]</option>
+ </para>
+
+ <para>
+ The index file for binary log filenames. See
+ <xref linkend="binary-log"/>. If you omit the filename, and
+ if you didn't specify one with <option>--log-bin</option>,
+ MySQL uses
+
<filename><replaceable>host_name</replaceable>-bin.index</filename>
+ as the filename.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_max_binlog_cache_size">
+ <literal>max_binlog_cache_size</literal>
+ </para>
+
+ <para>
+ If a multiple-statement transaction requires more than this
+ many bytes of memory, the server generates a
+ <literal>Multi-statement transaction required more than
+ 'max_binlog_cache_size' bytes of storage</literal> error.
+ The minimum value is 4096, the maximum and default values
+ are 4GB. This variable was added in MySQL 3.23.29.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_max_binlog_size">
+ <literal>max_binlog_size</literal>
+ </para>
+
+ <para>
+ If a write to the binary log causes the current log file
+ size to exceed the value of this variable, the server
+ rotates the binary logs (closes the current file and opens
+ the next one). You cannot set this variable to more than 1GB
+ or to less than 4096 bytes. (The minimum before MYSQL 4.0.14
+ is 1024 bytes.) The default value is 1GB. This variable was
+ added in MySQL 3.23.33.
+ </para>
+
+ <para>
+ A transaction is written in one chunk to the binary log, so
+ it is never split between several binary logs. Therefore, if
+ you have big transactions, you might see binary logs larger
+ than <literal>max_binlog_size</literal>.
+ </para>
+
+ <para>
+ If <literal>max_relay_log_size</literal> is 0, the value of
+ <literal>max_binlog_size</literal> applies to relay logs as
+ well. <literal>max_relay_log_size</literal> was added in
+ MySQL 4.0.14.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_sync_binlog">
+ <literal>sync_binlog</literal>
+ </para>
+
+ <para>
+ If the value of this variable is greater than 0, the MySQL
+ server synchronizes its binary log to disk (using
+ <literal>fdatasync()</literal>) after every
+ <literal>sync_binlog</literal> writes to the binary log.
+ There is one write to the binary log per statement if
+ autocommit is enabled, and one write per transaction
+ otherwise. The default value of
+ <literal>sync_binlog</literal> is 0, which does no
+ synchronizing to disk. A value of 1 is the safest choice,
+ because in the event of a crash you lose at most one
+ statement or transaction from the binary log. However, it is
+ also the slowest choice (unless the disk has a
+ battery-backed cache, which makes synchronization very
+ fast). This variable was added in MySQL 4.1.3.
+ </para>
+
+ <para>
+ If the value of <literal>sync_binlog</literal> is 0 (the
+ default), no extra flushing is done. The server relies on
+ the operating system to flush the file contents occasionally
+ as for any other file.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Additional server options, that can be used to control logging,
+ also affect the binary log. For more information about these,
+ see <xref linkend="server-options"/>.
+ </para>
+
+ <formalpara>
+
+ <title>Statement selection options</title>
+
+ <para>
+ The options in the following list affect which statements are
+ written to the binary log, and thus sent by a replication
+ master server to its slaves.
+ </para>
+
+ </formalpara>
+
+ <itemizedlist>
+
+ <listitem>
+ <para id="option_mysqld_binlog-do-db">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>binlog-do-db option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>binlog-do-db option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--binlog-do-db=<replaceable>db_name</replaceable></option>
+ </para>
+
+ <para>
+ Tell the server to restrict binary logging to updates for
+ which the default database is
+ <replaceable>db_name</replaceable> (that is, the database
+ selected by <literal role="stmt">USE</literal>). All other
+ databases that are not explicitly mentioned are ignored. If
+ you use this option, you should ensure that you do updates
+ only in the default database.
+ </para>
+
+ <para>
+ There is an exception to this for
+ <literal role="stmt">CREATE DATABASE</literal>,
+ <literal role="stmt">ALTER DATABASE</literal>, and
+ <literal role="stmt">DROP DATABASE</literal> statements. The
+ server uses the database named in the statement (not the
+ default database) to decide whether it should log the
+ statement.
+ </para>
+
+ <para>
+ An example of what does not work as you might expect: If the
+ server is started with
+ <literal>binlog-do-db=sales</literal>, and you run
+ <literal>USE prices; UPDATE sales.january SET
+ amount=amount+1000;</literal>, this statement is
+ <emphasis>not</emphasis> written into the binary log.
+ </para>
+
+ <important>
+ <para>
+ To log multiple databases, use this option multiple times,
+ specifying the option once for each database to be logged.
+ </para>
+ </important>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_binlog-ignore-db">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>binlog-ignore-db option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>binlog-ignore-db option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+
<option>--binlog-ignore-db=<replaceable>db_name</replaceable></option>
+ </para>
+
+ <para>
+ Tell the server to suppress binary logging of updates for
+ which the default database is
+ <replaceable>db_name</replaceable> (that is, the database
+ selected by <literal role="stmt">USE</literal>). If you use
+ this option, you should ensure that you do updates only in
+ the default database.
+ </para>
+
+ <para>
+ As with the <option>--binlog-do-db</option> option, there is
+ an exception for the <literal role="stmt">CREATE
+ DATABASE</literal>, <literal role="stmt">ALTER
+ DATABASE</literal>, and <literal role="stmt">DROP
+ DATABASE</literal> statements. The server uses the database
+ named in the statement (not the default database) to decide
+ whether it should log the statement.
+ </para>
+
+ <para>
+ An example of what does not work as you might expect: If the
+ server is started with
+ <literal>binlog-ignore-db=sales</literal>, and you run
+ <literal>USE prices; UPDATE sales.january SET amount =
+ amount + 1000;</literal>, this statement
+ <emphasis>is</emphasis> written into the binary log.
+ </para>
+
+ <important>
+ <para>
+ To ignore multiple databases, use this option multiple
+ times, specifying the option once for each database to be
+ ignored.
+ </para>
+ </important>
+ </listitem>
+
+
+ <listitem>
+ <para id="option_mysqld_log_slave_updates">
+ <literal>log_slave_updates</literal>
+ </para>
+
+ <para>
+ Whether updates received by a slave server from a master
+ server should be logged to the slave's own binary log.
+ Binary logging must be enabled on the slave for this
+ variable to have any effect. This variable was added in
+ MySQL 3.23.17. See <xref linkend="replication-options"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ For more detailed information about how the options in the
+ previous list are applied, see <xref linkend="binary-log"/>.
+ </para>
+
+ <para>
+ There are also options for slave servers that control which
+ statements received from the master should be executed or
+ ignored. For details, see
+ <xref linkend="replication-options-slave"/>.
+ </para>
+
+ <formalpara>
+
+ <title>Testing and debugging options</title>
+
+ <para>
+ The following binary log options are used in replication
+ testing and debugging. They are not intended for use in normal
+ operations.
+ </para>
+
+ </formalpara>
+
+ <itemizedlist>
+
+ <listitem>
+ <para id="option_mysqld_max-binlog-dump-events">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>max-binlog-dump-events option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>max-binlog-dump-events option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--max-binlog-dump-events</option>
+ </para>
+
+ <para>
+ This option is used internally by the MySQL test suite for
+ replication testing and debugging.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_sporadic-binlog-dump-fail">
+ <indexterm>
+ <primary>mysqld</primary>
+ <secondary>sporadic-binlog-dump-fail option</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>sporadic-binlog-dump-fail option</primary>
+ <secondary>mysqld</secondary>
+ </indexterm>
+
+ <option>--sporadic-binlog-dump-fail</option>
+ </para>
+
+ <para>
+ This option is used internally by the MySQL test suite for
+ replication testing and debugging.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ </section>
Copied: trunk/refman-4.1/replication.xml (from rev 12293,
trunk/refman-4.1/replication-core.xml)
===================================================================
--- trunk/refman-4.1/replication.xml (rev 0)
+++ trunk/refman-4.1/replication.xml 2008-11-06 18:05:11 UTC (rev 12310)
@@ -0,0 +1,3777 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd" [
+<!ENTITY % all.entities SYSTEM "all-entities.ent">
+ %all.entities;
+]>
+<chapter id="replication">
+
+ <title>Replication</title>
+
+ <remark role="dynamic-dependency-list"/>
+
+ <indexterm>
+ <primary>replication</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>databases</primary>
+ <secondary>replicating</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>increasing with replication</primary>
+ <secondary>speed</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>speed</primary>
+ <secondary>increasing with replication</secondary>
+ </indexterm>
+
+ <remark>
+ Need better intro summary of what the sections here are
+ </remark>
+
+ <para>
+ Replication capabilities allowing the databases on one MySQL server
+ to be duplicated on another were introduced in MySQL 3.23.15. This
+ chapter describes the various replication features provided by
+ MySQL. It introduces replication concepts, shows how to set up
+ replication servers, and serves as a reference to the available
+ replication options. It also provides a list of frequently asked
+ questions (with answers), and troubleshooting advice for solving
+ problems.
+ </para>
+
+ <formalpara role="mnmas">
+
+ <title>MySQL Enterprise</title>
+
+ <para>
+ The MySQL Enterprise Monitor provides numerous advisors that
+ provide immediate feedback about replication-related problems. For
+ more information, see
+ <ulink url="&base-url-enterprise;advisors.html"/>.
+ </para>
+
+ </formalpara>
+
+ <para>
+ For a description of the syntax of replication-related SQL
+ statements, see <xref linkend="sql-syntax-replication"/>.
+ </para>
+
+ <section id="replication-intro">
+
+ <title>Introduction to Replication</title>
+
+ <para>
+ MySQL 3.23.15 and up features support for one-way, asynchronous
+ replication, in which one server acts as the master, while one or
+ more other servers act as slaves. This is in contrast to the
+ <emphasis>synchronous</emphasis> replication which is a
+ characteristic of MySQL Cluster (see
+ <xref linkend="mysql-cluster"/>).
+ </para>
+
+ <para>
+ In single-master replication, the master server writes updates to
+ its binary log files and maintains an index of those files to keep
+ track of log rotation. The binary log files serve as a record of
+ updates to be sent to any slave servers. When a slave connects to
+ its master, it informs the master of the position up to which the
+ slave read the logs at its last successful update. The slave
+ receives any updates that have taken place since that time, and
+ then blocks and waits for the master to notify it of new updates.
+ </para>
+
+ <para>
+ A slave server can itself serve as a master if you want to set up
+ chained replication servers.
+ </para>
+
+ <para>
+ When you are using replication, all updates to the tables that are
+ replicated should be performed on the master server. Otherwise,
+ you must always be careful to avoid conflicts between updates that
+ users make to tables on the master and updates that they make to
+ tables on the slave.
+ </para>
+
+ <para>
+ Replication offers benefits for robustness, speed, and system
+ administration:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Robustness is increased with a master/slave setup. In the
+ event of problems with the master, you can switch to the slave
+ as a backup.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Better response time for clients can be achieved by splitting
+ the load for processing client queries between the master and
+ slave servers. <literal role="stmt">SELECT</literal> queries
+ may be sent to the slave to reduce the query processing load
+ of the master. Statements that modify data should still be
+ sent to the master so that the master and slave do not get out
+ of synchrony. This load-balancing strategy is effective if
+ non-updating queries dominate, but that is the normal case.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Another benefit of using replication is that you can perform
+ database backups using a slave server without disturbing the
+ master. The master continues to process updates while the
+ backup is being made. See <xref linkend="backup"/>.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="replication-implementation">
+
+ <title>Replication Implementation Overview</title>
+
+ <indexterm>
+ <primary>master/slave setup</primary>
+ </indexterm>
+
+ <para>
+ MySQL replication is based on the master server keeping track of
+ all changes to your databases (updates, deletes, and so on) in its
+ binary logs. Therefore, to use replication, you must enable binary
+ logging on the master server. See <xref linkend="binary-log"/>.
+ </para>
+
+ <para>
+ Each slave server receives from the master the saved updates that
+ the master has recorded in its binary log, so that the slave can
+ execute the same updates on its copy of the data.
+ </para>
+
+ <para>
+ It is <emphasis>extremely</emphasis> important to realize that the
+ binary log is simply a record starting from the fixed point in
+ time at which you enable binary logging. Any slaves that you set
+ up need copies of the databases on your master <emphasis>as they
+ existed at the moment you enabled binary logging on the
+ master</emphasis>. If you start your slaves with databases that
+ are not in the same state as those on the master when the binary
+ log was started, your slaves are quite likely to fail.
+ </para>
+
+ <para>
+ After the slave has been set up with a copy of the master's data,
+ it connects to the master and waits for updates to process. If the
+ master fails, or the slave loses connectivity with your master,
+ the slave keeps trying to connect periodically until it is able to
+ resume listening for updates. The <literal role="stmt">CHANGE
+ MASTER TO</literal> statement or
+ <option>--master-connect-retry</option> option controls the retry
+ interval. The default is 60 seconds.
+ </para>
+
+ <para>
+ Each slave keeps track of where it left off when it last read from
+ its master server. The master has no knowledge of how many slaves
+ it has or which ones are up to date at any given time.
+ </para>
+
+ </section>
+
+ <section id="replication-implementation-details">
+
+ <title>Replication Implementation Details</title>
+
+ <para>
+ MySQL replication capabilities are implemented using three threads
+ (one on the master server and two on the slave). When a
+ <literal role="stmt">START SLAVE</literal> statement is issued on
+ a slave server, the slave creates an I/O thread, which connects to
+ the master and asks it to send the updates recorded in its binary
+ logs. The master creates a thread to send the binary log contents
+ to the slave. This thread can be identified as the <literal>Binlog
+ Dump</literal> thread in the output of <literal role="stmt">SHOW
+ PROCESSLIST</literal> on the master. The slave I/O thread reads
+ the updates that the master <literal>Binlog Dump</literal> thread
+ sends and copies them to local files, known as <emphasis>relay
+ logs</emphasis>, in the slave's data directory. The third thread
+ is the SQL thread, which the slave creates to read the relay logs
+ and to execute the updates they contain.
+ </para>
+
+ <formalpara role="mnmas">
+
+ <title>MySQL Enterprise</title>
+
+ <para>
+ For constant monitoring of the status of slaves subscribe to the
+ MySQL Enterprise Monitor. For more information, see
+ <ulink url="&base-url-enterprise;advisors.html"/>.
+ </para>
+
+ </formalpara>
+
+ <para>
+ In the preceding description, there are three threads per
+ master/slave connection. A master that has multiple slaves creates
+ one thread for each currently-connected slave, and each slave has
+ its own I/O and SQL threads.
+ </para>
+
+ <para>
+ For versions of MySQL before 4.0.2, replication involves only two
+ threads (one on the master and one on the slave). The slave I/O
+ and SQL threads are combined as a single thread, and no relay log
+ files are used.
+ </para>
+
+ <para>
+ The slave uses two threads so that reading updates from the master
+ and executing them can be separated into two independent tasks.
+ Thus, the task of reading statements is not slowed down if
+ statement execution is slow. For example, if the slave server has
+ not been running for a while, its I/O thread can quickly fetch all
+ the binary log contents from the master when the slave starts,
+ even if the SQL thread lags far behind. If the slave stops before
+ the SQL thread has executed all the fetched statements, the I/O
+ thread has at least fetched everything so that a safe copy of the
+ statements is stored locally in the slave's relay logs, ready for
+ execution the next time that the slave starts. This enables the
+ master server to purge its binary logs sooner because it no longer
+ needs to wait for the slave to fetch their contents.
+ </para>
+
+ <para>
+ The <literal role="stmt">SHOW PROCESSLIST</literal> statement
+ provides information that tells you what is happening on the
+ master and on the slave regarding replication. See
+ <xref linkend="thread-information"/>, for descriptions of all
+ replicated-related states.
+ </para>
+
+ <para>
+ The following example illustrates how the three threads show up in
+ the output from <literal role="stmt">SHOW PROCESSLIST</literal>.
+ The output format is that used by <literal role="stmt">SHOW
+ PROCESSLIST</literal> as of MySQL version 4.0.15, when the content
+ of the <literal>State</literal> column was changed to be more
+ meaningful compared to earlier versions.
+ </para>
+
+ <para>
+ On the master server, the output from <literal role="stmt">SHOW
+ PROCESSLIST</literal> looks like this:
+ </para>
+
+<programlisting>
+mysql> <userinput>SHOW PROCESSLIST\G</userinput>
+*************************** 1. row ***************************
+ Id: 2
+ User: root
+ Host: localhost:32931
+ db: NULL
+Command: Binlog Dump
+ Time: 94
+ State: Has sent all binlog to slave; waiting for binlog to
+ be updated
+ Info: NULL
+</programlisting>
+
+ <para>
+ Here, thread 2 is a <literal>Binlog Dump</literal> replication
+ thread for a connected slave. The <literal>State</literal>
+ information indicates that all outstanding updates have been sent
+ to the slave and that the master is waiting for more updates to
+ occur. If you see no <literal>Binlog Dump</literal> threads on a
+ master server, this means that replication is not running —
+ that is, that no slaves are currently connected.
+ </para>
+
+ <para>
+ On the slave server, the output from <literal role="stmt">SHOW
+ PROCESSLIST</literal> looks like this:
+ </para>
+
+<programlisting>
+mysql> <userinput>SHOW PROCESSLIST\G</userinput>
+*************************** 1. row ***************************
+ Id: 10
+ User: system user
+ Host:
+ db: NULL
+Command: Connect
+ Time: 11
+ State: Waiting for master to send event
+ Info: NULL
+*************************** 2. row ***************************
+ Id: 11
+ User: system user
+ Host:
+ db: NULL
+Command: Connect
+ Time: 11
+ State: Has read all relay log; waiting for the slave I/O
+ thread to update it
+ Info: NULL
+</programlisting>
+
+ <para>
+ This information indicates that thread 10 is the I/O thread that
+ is communicating with the master server, and thread 11 is the SQL
+ thread that is processing the updates stored in the relay logs. At
+ the time that the <literal role="stmt">SHOW PROCESSLIST</literal>
+ was run, both threads were idle, waiting for further updates.
+ </para>
+
+ <para>
+ The value in the <literal>Time</literal> column can show how late
+ the slave is compared to the master. See
+ <xref linkend="replication-faq"/>.
+ </para>
+
+ <section id="slave-logs">
+
+ <title>Replication Relay and Status Files</title>
+
+ <para>
+ By default, relay log filenames have the form
+
<filename><replaceable>host_name</replaceable>-relay-bin.<replaceable>nnnnnn</replaceable></filename>,
+ where <replaceable>host_name</replaceable> is the name of the
+ slave server host and <replaceable>nnnnnn</replaceable> is a
+ sequence number. Successive relay log files are created using
+ successive sequence numbers, beginning with
+ <literal>000001</literal> (<literal>001</literal> in
MySQL 4.0
+ or older). The slave uses an index file to track the relay log
+ files currently in use. The default relay log index filename is
+
<filename><replaceable>host_name</replaceable>-relay-bin.index</filename>.
+ By default, the slave server creates relay log files in its data
+ directory.
+ </para>
+
+ <para>
+ The default filenames for relay logs and relay log index files
+ can be overridden with, respectively, the
+ <option>--relay-log</option> and
+ <option>--relay-log-index</option> server options (see
+ <xref linkend="replication-options"/>). For this reason,
+ changing a replication slave's hostname can cause
+ replication to fail with the errors <errortext>Failed to open
+ the relay log</errortext> and <errortext>Could not find target
+ log during relay log initialization</errortext>. This is a known
+ issue which we intend to fix in a future MySQL release (see Bug
+ #2122). If you anticipate that a slave's hostname may
+ change in the future (for example, if networking is set up on
+ the slave such that its hostname can be modified via DHCP), then
+ you can use these options to prevent this problem from
+ occurring. However, if you encounter this issue, one way to work
+ around it is to stop the slave server, prepend the contents of
+ the old relay log index file to the new one, then restart the
+ slave. On a Unix system, this can be done as shown here, where
+ <replaceable>new_host_name</replaceable> is the new hostname and
+ <replaceable>old_host_name</replaceable> is the old one:
+
+<programlisting>
+shell< <userinput>cat
<replaceable>new_host_name</replaceable>-relay-bin.index >>
<replaceable>old_host_name</replaceable>-relay-bin.index</userinput>
+
+shell< <userinput>mv
<replaceable>old_host_name</replaceable>-relay-bin.index
<replaceable>new_host_name</replaceable>-relay-bin.index</userinput>
+</programlisting>
+ </para>
+
+ <para>
+ Relay logs have the same format as binary logs and can be read
+ using <command>mysqlbinlog</command>. The SQL thread
+ automatically deletes each relay log file as soon as it has
+ executed all events in the file and no longer needs it. There is
+ no explicit mechanism for deleting relay logs because the SQL
+ thread takes care of doing so. However, as of MySQL 4.0.14,
+ <literal>FLUSH LOGS</literal> rotates relay logs, which
+ influences when the SQL thread deletes them.
+ </para>
+
+ <para>
+ A slave server creates a new relay log file under the following
+ conditions:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Each time the I/O thread starts.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When the logs are flushed; for example, with <literal>FLUSH
+ LOGS</literal> or <command>mysqladmin flush-logs</command>.
+ (This creates a new relay log only as of MySQL 4.0.14.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When the size of the current relay log file becomes too
+ large. The meaning of <quote>too large</quote> is determined
+ as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If the value of <literal>max_relay_log_size</literal> is
+ greater than 0, that is the maximum relay log file size.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If the value of <literal>max_relay_log_size</literal> is
+ 0, <literal>max_binlog_size</literal> determines the
+ maximum relay log file size.
+ <literal>max_binlog_size</literal> always determines the
+ relay log size before MySQL 4.0.14, the first version in
+ which <literal>max_relay_log_size</literal> appears.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ A slave replication server creates two additional small files in
+ the data directory. These <emphasis>status files</emphasis> are
+ named <filename>master.info</filename> and
+ <filename>relay-log.info</filename> by default. Their names can
+ be changed by using the <option>--master-info-file</option> and
+ <option>--relay-log-info-file</option> options. See
+ <xref linkend="replication-options"/>.
+ </para>
+
+ <para>
+ The two status files contain information like that shown in the
+ output of the <literal role="stmt">SHOW SLAVE STATUS</literal>
+ statement, which is discussed in
+ <xref linkend="replication-slave-sql"/>. Because the status
+ files are stored on disk, they survive a slave server's
+ shutdown. The next time the slave starts up, it reads the two
+ files to determine how far it has proceeded in reading binary
+ logs from the master and in processing its own relay logs.
+ </para>
+
+ <para>
+ The I/O thread updates the <filename>master.info</filename>
+ file. Before MySQL 4.1, the following table shows the
+ correspondence between the lines in the file and the columns
+ displayed by <literal role="stmt">SHOW SLAVE STATUS</literal>.
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="15*"/>
+ <colspec colwidth="85*"/>
+ <tbody>
+ <row>
+ <entry><emphasis
role="bold">Line</emphasis></entry>
+ <entry><emphasis
role="bold">Description</emphasis></entry>
+ </row>
+ <row>
+ <entry>1</entry>
+ <entry><literal>Master_Log_File</literal></entry>
+ </row>
+ <row>
+ <entry>2</entry>
+
<entry><literal>Read_Master_Log_Pos</literal></entry>
+ </row>
+ <row>
+ <entry>3</entry>
+ <entry><literal>Master_Host</literal></entry>
+ </row>
+ <row>
+ <entry>4</entry>
+ <entry><literal>Master_User</literal></entry>
+ </row>
+ <row>
+ <entry>5</entry>
+ <entry>Password (not shown by <literal role="stmt">SHOW SLAVE
STATUS</literal>)</entry>
+ </row>
+ <row>
+ <entry>6</entry>
+ <entry><literal>Master_Port</literal></entry>
+ </row>
+ <row>
+ <entry>7</entry>
+ <entry><literal>Connect_Retry</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ <para>
+ As of MySQL 4.1, the file includes a line count and information
+ about SSL options:
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="15*"/>
+ <colspec colwidth="85*"/>
+ <tbody>
+ <row>
+ <entry><emphasis
role="bold">Line</emphasis></entry>
+ <entry><emphasis
role="bold">Description</emphasis></entry>
+ </row>
+ <row>
+ <entry>1</entry>
+ <entry>Number of lines in the file</entry>
+ </row>
+ <row>
+ <entry>2</entry>
+ <entry><literal>Master_Log_File</literal></entry>
+ </row>
+ <row>
+ <entry>3</entry>
+
<entry><literal>Read_Master_Log_Pos</literal></entry>
+ </row>
+ <row>
+ <entry>4</entry>
+ <entry><literal>Master_Host</literal></entry>
+ </row>
+ <row>
+ <entry>5</entry>
+ <entry><literal>Master_User</literal></entry>
+ </row>
+ <row>
+ <entry>6</entry>
+ <entry>Password (not shown by <literal role="stmt">SHOW SLAVE
STATUS</literal>)</entry>
+ </row>
+ <row>
+ <entry>7</entry>
+ <entry><literal>Master_Port</literal></entry>
+ </row>
+ <row>
+ <entry>8</entry>
+ <entry><literal>Connect_Retry</literal></entry>
+ </row>
+ <row>
+ <entry>9</entry>
+
<entry><literal>Master_SSL_Allowed</literal></entry>
+ </row>
+ <row>
+ <entry>10</entry>
+
<entry><literal>Master_SSL_CA_File</literal></entry>
+ </row>
+ <row>
+ <entry>11</entry>
+
<entry><literal>Master_SSL_CA_Path</literal></entry>
+ </row>
+ <row>
+ <entry>12</entry>
+ <entry><literal>Master_SSL_Cert</literal></entry>
+ </row>
+ <row>
+ <entry>13</entry>
+ <entry><literal>Master_SSL_Cipher</literal></entry>
+ </row>
+ <row>
+ <entry>14</entry>
+ <entry><literal>Master_SSL_Key</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ <para>
+ The SQL thread updates the <filename>relay-log.info</filename>
+ file. The following table shows the correspondence between the
+ lines in the file and the columns displayed by
+ <literal role="stmt">SHOW SLAVE STATUS</literal>.
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="15*"/>
+ <colspec colwidth="85*"/>
+ <tbody>
+ <row>
+ <entry><emphasis
role="bold">Line</emphasis></entry>
+ <entry><emphasis
role="bold">Description</emphasis></entry>
+ </row>
+ <row>
+ <entry>1</entry>
+ <entry><literal>Relay_Log_File</literal></entry>
+ </row>
+ <row>
+ <entry>2</entry>
+ <entry><literal>Relay_Log_Pos</literal></entry>
+ </row>
+ <row>
+ <entry>3</entry>
+
<entry><literal>Relay_Master_Log_File</literal></entry>
+ </row>
+ <row>
+ <entry>4</entry>
+
<entry><literal>Exec_Master_Log_Pos</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ <para>
+ The contents of the <filename>relay-log.info</filename> file and
+ the states shown by the <literal>SHOW SLAVE STATES</literal>
+ command may not match if the <filename>relay-log.info</filename>
+ file has not been flushed to disk. Ideally, you should only view
+ <filename>relay-log.info</filename> on a slave that is offline
+ (i.e. <literal>mysqld</literal> is not running). For a running
+ system, <literal role="stmt">SHOW SLAVE STATUS</literal> should
+ be used.
+ </para>
+
+ <remark role="todo">
+ This stuff about backups is out of place. We haven't even
+ described how to set up replication yet, much less worry about
+ backups. Create a new Backup Issues within this chapter?
+ </remark>
+
+ <para>
+ When you back up the slave's data, you should back up these two
+ status files as well, along with the relay log files. They are
+ needed to resume replication after you restore the slave's data.
+ If you lose the relay logs but still have the
+ <filename>relay-log.info</filename> file, you can check it to
+ determine how far the SQL thread has executed in the master
+ binary logs. Then you can use <literal role="stmt">CHANGE MASTER
+ TO</literal> with the <literal>MASTER_LOG_FILE</literal> and
+ <literal>MASTER_LOG_POS</literal> options to tell the slave to
+ re-read the binary logs from that point. Of course, this
+ requires that the binary logs still exist on the master server.
+ </para>
+
+ <para>
+ If your slave is subject to replicating
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> statements, you should also back up any
+ <filename>SQL_LOAD-*</filename> files that exist in the
+ directory that the slave uses for this purpose. The slave needs
+ these files to resume replication of any interrupted
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> operations. The directory location is specified
+ using the <option>--slave-load-tmpdir</option> option. If this
+ option is not specified, the directory location is the value of
+ the <literal>tmpdir</literal> system variable.
+ </para>
+
+ </section>
+
+ </section>
+
+ <section id="replication-howto">
+
+ <title>How to Set Up Replication</title>
+
+ <para>
+ This section briefly describes how to set up complete replication
+ of a MySQL server. It assumes that you want to replicate all
+ databases on the master and have not previously configured
+ replication. You must shut down your master server briefly to
+ complete the steps outlined here.
+ </para>
+
+ <para>
+ This procedure is written in terms of setting up a single slave,
+ but you can repeat it to set up multiple slaves.
+ </para>
+
+ <para>
+ Although this method is the most straightforward way to set up a
+ slave, it is not the only one. For example, if you have a snapshot
+ of the master's data, and the master already has its server ID set
+ and binary logging enabled, you can set up a slave without
+ shutting down the master or even blocking updates to it. For more
+ details, please see <xref linkend="replication-faq"/>.
+ </para>
+
+ <para>
+ If you want to administer a MySQL replication setup, we suggest
+ that you read this entire chapter through and try all statements
+ mentioned in <xref linkend="replication-master-sql"/>, and
+ <xref linkend="replication-slave-sql"/>. You should also
+ familiarize yourself with the replication startup options
+ described in <xref linkend="replication-options"/>.
+ </para>
+
+ <note>
+ <para>
+ This procedure and some of the replication SQL statements shown
+ in later sections refer to the <literal>SUPER</literal>
+ privilege. Prior to MySQL 4.0.2, use the
+ <literal>PROCESS</literal> privilege instead.
+ </para>
+ </note>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Make sure that you have a recent version of MySQL installed on
+ the master and slaves, and that these versions are compatible
+ according to the table shown in
+ <xref linkend="replication-compatibility"/>.
+ </para>
+
+ <para>
+ If you encounter a problem, please do not report it as a bug
+ until you have verified that the problem is present in the
+ latest MySQL release.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Set up an account on the master server that the slave server
+ can use to connect. This account must be given the
+ <literal>REPLICATION SLAVE</literal> privilege. If the account
+ is used only for replication (which is recommended), you don't
+ need to grant any additional privileges.
+ </para>
+
+ <formalpara role="mnmas">
+
+ <title>MySQL Enterprise</title>
+
+ <para>
+ Subscribers to the MySQL Enterprise Monitor are quickly
+ notified if there is a replication master and no account
+ with the <literal>REPLICATION SLAVE</literal> privilege. For
+ more information, see
+ <ulink url="&base-url-enterprise;advisors.html"/>.
+ </para>
+
+ </formalpara>
+
+ <para>
+ Suppose that your domain is <literal>mydomain.com</literal>
+ and that you want to create an account with a username of
+ <literal>repl</literal> such that slave servers can use the
+ account to access the master server from any host in your
+ domain using a password of <literal>slavepass</literal>. To
+ create the account, use this
+ <literal role="stmt">GRANT</literal> statement:
+ </para>
+
+<programlisting>
+mysql> <userinput>GRANT REPLICATION SLAVE ON *.*</userinput>
+ -> <userinput>TO 'repl'@'%.mydomain.com' IDENTIFIED BY
'slavepass';</userinput>
+</programlisting>
+
+ <para>
+ For MySQL versions older than 4.0.2, the <literal>REPLICATION
+ SLAVE</literal> privilege does not exist. Grant the
+ <literal>FILE</literal> privilege instead:
+ </para>
+
+<programlisting>
+mysql> <userinput>GRANT FILE ON *.*</userinput>
+ -> <userinput>TO 'repl'@'%.mydomain.com' IDENTIFIED BY
'slavepass';</userinput>
+</programlisting>
+
+ <para>
+ For additional information about setting up user accounts and
+ privileges, see <xref linkend="user-account-management"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Flush all the tables and block write statements by executing a
+ <literal>FLUSH TABLES WITH READ LOCK</literal> statement:
+ </para>
+
+<programlisting>
+mysql> <userinput>FLUSH TABLES WITH READ LOCK;</userinput>
+</programlisting>
+
+ <para>
+ For example, if you are using <literal>InnoDB</literal>
+ tables, you should use the <command><literal>InnoDB</literal>
+ Hot Backup</command> tool to obtain a consistent snapshot.
+ This tool records the log name and offset corresponding to the
+ snapshot to be later used on the slave. <command>Hot
+ Backup</command> is a non-free (commercial) tool that is not
+ included in the standard MySQL distribution. See the
+ <command><literal>InnoDB</literal> Hot Backup</command>
home
+ page at <ulink url="http://www.innodb.com/hot-backup"/> for
+ detailed information.
+ </para>
+
+ <para>
+ Otherwise, you can obtain a reliable binary snapshot of
+ <literal>InnoDB</literal> tables only after shutting down the
+ MySQL Server.
+ </para>
+
+ <para>
+ An alternative that works for both <literal>MyISAM</literal>
+ and <literal>InnoDB</literal> tables is to take an SQL dump of
+ the master instead of a binary copy as described in the
+ preceding discussion. For this, you can use <command>mysqldump
+ --master-data</command> on your master and later load the SQL
+ dump file into your slave. However, this is slower than doing
+ a binary copy.
+ </para>
+
+ <para>
+ Leave running the client from which you issue the
+ <literal>FLUSH TABLES</literal> statement so that the read
+ lock remains in effect. (If you exit the client, the lock is
+ released.) Then take a snapshot of the data on your master
+ server.
+ </para>
+
+ <para>
+ The easiest way to create a snapshot is to use an archiving
+ program to make a binary backup of the databases in your
+ master's data directory. For example, use
+ <command>tar</command> on Unix, or
+ <command>PowerArchiver</command>,
<command>WinRAR</command>,
+ <command>WinZip</command>, or any similar software on Windows.
+ To use <command>tar</command> to create an archive that
+ includes all databases, change location into the master
+ server's data directory, then execute this command:
+ </para>
+
+<programlisting>
+shell> <userinput>tar -cvf /tmp/mysql-snapshot.tar .</userinput>
+</programlisting>
+
+ <para>
+ If you want the archive to include only a database called
+ <literal>this_db</literal>, use this command instead:
+ </para>
+
+<programlisting>
+shell> <userinput>tar -cvf /tmp/mysql-snapshot.tar
./this_db</userinput>
+</programlisting>
+
+ <para>
+ Then copy the archive file to the <filename>/tmp</filename>
+ directory on the slave server host. On that machine, change
+ location into the slave's data directory, and unpack the
+ archive file using this command:
+ </para>
+
+<programlisting>
+shell> <userinput>tar -xvf /tmp/mysql-snapshot.tar</userinput>
+</programlisting>
+
+ <para>
+ You may not want to replicate the <literal>mysql</literal>
+ database if the slave server has a different set of user
+ accounts from those that exist on the master. In this case,
+ you should exclude it from the archive. You also need not
+ include any log files in the archive, or the
+ <filename>master.info</filename> or
+ <filename>relay-log.info</filename> files.
+ </para>
+
+ <para>
+ While the read lock placed by <literal>FLUSH TABLES WITH READ
+ LOCK</literal> is in effect, read the value of the current
+ binary log name and offset on the master:
+ </para>
+
+<programlisting>
+mysql > <userinput>SHOW MASTER STATUS;</userinput>
++---------------+----------+--------------+------------------+
+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
++---------------+----------+--------------+------------------+
+| mysql-bin.003 | 73 | test | manual,mysql |
++---------------+----------+--------------+------------------+
+</programlisting>
+
+ <para>
+ The <literal>File</literal> column shows the name of the log
+ and <literal>Position</literal> shows the offset within the
+ file. In this example, the binary log file is
+ <literal>mysql-bin.003</literal> and the offset is 73. Record
+ these values. You need them later when you are setting up the
+ slave. They represent the replication coordinates at which the
+ slave should begin processing new updates from the master.
+ </para>
+
+ <para>
+ If the master has been running previously without binary
+ logging enabled, the log name and position values displayed by
+ <literal role="stmt">SHOW MASTER STATUS</literal> or
+ <command>mysqldump --master-data</command> will be empty. In
+ that case, the values that you need to use later when
+ specifying the slave's log file and position are the empty
+ string (<literal>''</literal>) and
<literal>4</literal>.
+ </para>
+
+ <para>
+ After you have taken the snapshot and recorded the log name
+ and offset, you can re-enable write activity on the master:
+ </para>
+
+<programlisting>
+mysql> <userinput>UNLOCK TABLES;</userinput>
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Make sure that the <literal>[mysqld]</literal> section of the
+ <filename>my.cnf</filename> file on the master host includes a
+ <literal>log-bin</literal> option. The section should also
+ have a
+
<literal>server-id=<replaceable>master_id</replaceable></literal>
+ option, where <replaceable>master_id</replaceable> must be a
+ positive integer value from 1 to
+ 2<superscript>32</superscript> − 1. For example:
+ </para>
+
+<programlisting>
+[mysqld]
+log-bin=mysql-bin
+server-id=1
+</programlisting>
+
+ <para>
+ If those options are not present, add them and restart the
+ server. The server cannot act as a replication master unless
+ binary logging is enabled.
+ </para>
+
+ <note>
+ <para>
+ For the greatest possible durability and consistency in a
+ replication setup using <literal>InnoDB</literal> with
+ transactions, you should use
+ <literal>innodb_flush_log_at_trx_commit=1</literal>,
+ <literal>sync_binlog=1</literal>, and
+ <literal>innodb_safe_binlog</literal> in your master
+ <filename>my.cnf</filename> file.
+ </para>
+ </note>
+ </listitem>
+
+ <listitem>
+ <para>
+ Stop the server that is to be used as a slave and add the
+ following lines to its <filename>my.cnf</filename> file:
+ </para>
+
+<programlisting>
+[mysqld]
+server-id=<replaceable>slave_id</replaceable>
+</programlisting>
+
+ <para>
+ The <replaceable>slave_id</replaceable> value, like the
+ <replaceable>master_id</replaceable> value, must be a positive
+ integer value from 1 to 2<superscript>32</superscript> −
+ 1. In addition, it is necessary that the ID of the slave be
+ different from the ID of the master. For example:
+ </para>
+
+<programlisting>
+[mysqld]
+server-id=2
+</programlisting>
+
+ <para>
+ If you are setting up multiple slaves, each one must have a
+ unique <literal>server-id</literal> value that differs from
+ that of the master and from each of the other slaves. Think of
+ <literal>server-id</literal> values as something similar to IP
+ addresses: These IDs uniquely identify each server instance in
+ the community of replication partners.
+ </para>
+
+ <para>
+ If you do not specify a <literal>server-id</literal> value, it
+ is set to 1 if you have not defined
+ <literal>master-host</literal>; otherwise it is set to 2. Note
+ that in the case of <literal>server-id</literal> omission, a
+ master refuses connections from all slaves, and a slave
+ refuses to connect to a master. Thus, omitting
+ <literal>server-id</literal> is good only for backup with a
+ binary log.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you made a binary backup of the master server's data, copy
+ it to the slave server's data directory before starting the
+ slave. Make sure that the privileges on the files and
+ directories are correct. The system account that you use to
+ run the slave server must be able to read and write the files,
+ just as on the master.
+ </para>
+
+ <para>
+ If you made a backup using <command>mysqldump</command>, start
+ the slave first. The dump file is loaded in a later step.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Start the slave server. If it has been replicating previously,
+ start the slave server with the
+ <option>--skip-slave-start</option> option so that it doesn't
+ immediately try to connect to its master. You also may want to
+ start the slave server with the
+ <option>--log-warnings</option> option to get more messages in
+ the error log about problems (for example, network or
+ connection problems). The option is enabled by default as of
+ MySQL 4.0.19 and 4.1.2, but as of MySQL 4.0.21 and 4.1.3,
+ aborted connections are not logged to the error log unless the
+ value is greater than 1.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If you made a backup of the master server's data using
+ <command>mysqldump</command>, load the dump file into the
+ slave server:
+ </para>
+
+<programlisting>
+shell> <userinput>mysql -u root -p < dump_file.sql</userinput>
+</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Execute the following statement on the slave, replacing the
+ option values with the actual values relevant to your system:
+ </para>
+
+<programlisting>
+mysql> <userinput>CHANGE MASTER TO</userinput>
+ ->
<userinput>MASTER_HOST='<replaceable>master_host_name</replaceable>',</userinput>
+ ->
<userinput>MASTER_USER='<replaceable>replication_user_name</replaceable>',</userinput>
+ ->
<userinput>MASTER_PASSWORD='<replaceable>replication_password</replaceable>',</userinput>
+ ->
<userinput>MASTER_LOG_FILE='<replaceable>recorded_log_file_name</replaceable>',</userinput>
+ ->
<userinput>MASTER_LOG_POS=<replaceable>recorded_log_position</replaceable>;</userinput>
+</programlisting>
+
+ <note>
+ <para>
+ Replication cannot use Unix socket files. You must be able
+ to connect to the master MySQL server using TCP/IP.
+ </para>
+ </note>
+
+ <para>
+ The following table shows the maximum allowable length for the
+ string-valued options:
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="20*"/>
+ <colspec colwidth="20*"/>
+ <tbody>
+ <row>
+ <entry><literal>MASTER_HOST</literal></entry>
+ <entry>60</entry>
+ </row>
+ <row>
+ <entry><literal>MASTER_USER</literal></entry>
+ <entry>16</entry>
+ </row>
+ <row>
+ <entry><literal>MASTER_PASSWORD</literal></entry>
+ <entry>32</entry>
+ </row>
+ <row>
+ <entry><literal>MASTER_LOG_FILE</literal></entry>
+ <entry>255</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+ </listitem>
+
+ <listitem>
+ <para>
+ Start the slave threads:
+ </para>
+
+<programlisting>
+mysql> <userinput>START SLAVE;</userinput>
+</programlisting>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ After you have performed this procedure, the slave should connect
+ to the master and catch up on any updates that have occurred since
+ the snapshot was taken.
+ </para>
+
+ <para>
+ If you have forgotten to set the <literal>server-id</literal>
+ option for the master, slaves cannot connect to it.
+ </para>
+
+ <para>
+ If you have forgotten to set the <literal>server-id</literal>
+ option for the slave, you get the following error in the slave's
+ error log:
+ </para>
+
+<programlisting>
+Warning: You should set server-id to a non-0 value if master_host
+is set; we will force server id to 2, but this MySQL server will
+not act as a slave.
+</programlisting>
+
+ <para>
+ You also find error messages in the slave's error log if it is not
+ able to replicate for any other reason.
+ </para>
+
+ <para>
+ Once a slave is replicating, you can find in its data directory
+ one file named <filename>master.info</filename> and another named
+ <filename>relay-log.info</filename>. The slave uses these two
+ files to keep track of how much of the master's binary log it has
+ processed. Do <emphasis>not</emphasis> remove or edit these files
+ unless you know exactly what you are doing and fully understand
+ the implications. Even in that case, it is preferred that you use
+ the <literal role="stmt">CHANGE MASTER TO</literal> statement to
+ change replication parameters. The slave will use the values
+ specified in the statement to update the status files
+ automatically.
+ </para>
+
+ <note>
+ <para>
+ The content of <filename>master.info</filename> overrides some
+ of the server options specified on the command line or in
+ <filename>my.cnf</filename>. See
+ <xref linkend="replication-options"/>, for more details.
+ </para>
+ </note>
+
+ <para>
+ Once you have a snapshot of the master, you can use it to set up
+ other slaves by following the slave portion of the procedure just
+ described. You do not need to take another snapshot of the master;
+ you can use the same one for each slave.
+ </para>
+
+ </section>
+
+ <section id="replication-compatibility">
+
+ <title>Replication Compatibility Between MySQL Versions</title>
+
+ <para>
+ MySQL supports replication from one major version to the next
+ higher major version. For example, you can replicate from a master
+ running MySQL 4.0 to a slave running MySQL 4.1, from a master
+ running MySQL 4.1 to a slave running MySQL 5.0, and so on.
+ </para>
+
+ <note>
+ <para>
+ The original binary log format was developed in MySQL 3.23. It
+ was changed in MySQL 4.0.
+ </para>
+
+ <para>
+ You <emphasis>cannot</emphasis> replicate from a master that
+ uses a newer binary log format to a slave that uses an older
+ format — for example, from MySQL 4.1 to MySQL 3.23. (In
+ general, MySQL does not support replication from newer masters
+ to older slaves.) This also has significant consequences for
+ upgrading servers in a replication setup, as described in
+ <xref linkend="replication-upgrade"/>.
+ </para>
+ </note>
+
+ <para>
+ As far as replication is concerned, the binary log format used by
+ all MySQL 4.0 and MySQL 4.1 releases is identical. However,
+ replication from a 4.1 master to a 4.0 slave is unsupported; it
+ has not been tested thoroughly, and no further development or bug
+ fixing is planned for this master/slave combination. Although the
+ binary log format is the same for 4.0 and 4.1, there are other
+ constraints, such as SQL-level compatibility issues. For example,
+ a 4.1 master cannot replicate to a 4.0 slave if the replicated
+ statements use SQL features available in 4.1 but not 4.0. In
+ addition, there are known issues with trying to replicate from a
+ 4.0 or 4.1 master to a 5.1 slave (Bug #31240).
+ </para>
+
+ <para>
+ As a general rule, we recommended using recent MySQL versions,
+ because replication (and other) capabilities are continually being
+ improved. We also recommend upgrading masters and slaves running
+ alpha or beta releases to GA (production) releases.
+ </para>
+
+ <para>
+ For more information on potential replication issues, see
+ <xref linkend="replication-features"/>.
+ </para>
+
+ </section>
+
+ <section id="replication-upgrade">
+
+ <title>Upgrading a Replication Setup</title>
+
+ <para>
+ When you upgrade servers that participate in a replication setup,
+ the procedure for upgrading depends on the current server versions
+ and the version to which you are upgrading.
+ </para>
+
+ <section id="replication-upgrade-4-0">
+
+ <title>Upgrading Replication to 4.0 or 4.1</title>
+
+ <para>
+ This section applies to upgrading replication from MySQL 3.23 to
+ 4.0 or 4.1. A 4.0 server should be 4.0.3 or newer, as mentioned
+ in <xref linkend="replication-compatibility"/>.
+ </para>
+
+ <para>
+ When you upgrade a master from MySQL 3.23 to MySQL 4.0 or 4.1,
+ you should first ensure that all the slaves of this master are
+ at 4.0 or 4.1. If that is not the case, you should first upgrade
+ your slaves: Shut down each one, upgrade it, restart it, and
+ restart replication.
+ </para>
+
+ <para>
+ The upgrade can safely be done using the following procedure,
+ assuming that you have a 3.23 master to upgrade and the slaves
+ are 4.0 or 4.1. Note that after the master has been upgraded,
+ you should not restart replication using any old 3.23 binary
+ logs, because this unfortunately confuses the 4.0 or 4.1 slaves.
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Block all updates on the master by issuing a <literal>FLUSH
+ TABLES WITH READ LOCK</literal> statement.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Wait until all the slaves have caught up with all changes
+ from the master server. Use <literal role="stmt">SHOW MASTER
+ STATUS</literal> on the master to obtain its current binary
+ log file and position. Then, for each slave, use those
+ values with a <literal>SELECT MASTER_POS_WAIT()</literal>
+ statement. The statement blocks on the slave and returns
+ when the slave has caught up. Then run
+ <literal role="stmt">STOP SLAVE</literal> on the slave.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Stop the master server and upgrade it to MySQL 4.0 or 4.1.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Restart the master server and record the name of its newly
+ created binary log. You can obtain the name of the file by
+ issuing a <literal role="stmt">SHOW MASTER STATUS</literal>
+ statement on the master. Then issue these statements on each
+ slave:
+ </para>
+
+<programlisting>
+mysql> <userinput>CHANGE MASTER TO
MASTER_LOG_FILE='binary_log_name',</userinput>
+ -> <userinput>MASTER_LOG_POS=4;</userinput>
+mysql> <userinput>START SLAVE;</userinput>
+</programlisting>
+ </listitem>
+
+ </orderedlist>
+
+ </section>
+
+ </section>
+
+ <section id="replication-features">
+
+ <title>Replication Features and Known Problems</title>
+
+ <indexterm>
+ <primary>options</primary>
+ <secondary>replication</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>replication options</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>limitations</primary>
+ <secondary>replication</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>replication limitations</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>my.cnf file</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>files</primary>
+ <secondary>my.cnf</secondary>
+ </indexterm>
+
+ <para>
+ In general, replication compatibility at the SQL level requires
+ that any features used be supported by both the master and the
+ slave servers. If you use a feature on a master server that is
+ available only as of a given version of MySQL, you cannot
+ replicate to a slave that is older than that version. Such
+ incompatibilities are likely to occur between series, so that, for
+ example, you cannot replicate from MySQL ¤t-series; to
+ &previous-series;. However, these incompatibilities also can occur
+ for within-series replication. For example, the
+ <literal role="func">CONVERT_TZ()</literal> function is available
+ in MySQL 4.1.3 and up. If you use this function on the master
+ server, you cannot replicate to a slave server that is older than
+ MySQL 4.1.3.
+ </para>
+
+ <para>
+ Another compatibility problem can be encountered when you are
+ attempting to replicate from an older master to a newer slave, and
+ you make use of identifiers on the master that are reserved words
+ in the newer MySQL version running on the slave. An example of
+ this is using a table column named <literal>current_user</literal>
+ on a 4.0 master that is replicating to a 4.1 or higher slave,
+ because <literal>CURRENT_USER</literal> is a reserved word
+ beginning in MySQL 4.1. Replication can fail in such cases with
+ Error 1064 <errortext>You have an error in your SQL
+ syntax...</errortext>, <emphasis>even if a database or table named
+ using the reserved word or a table having a column named using the
+ reserved word is excluded from replication</emphasis>. This is due
+ to the fact that each SQL statement must be parsed by the slave
+ prior to execution, so that the slave knows which database object
+ or objects would be effected by the statement; only after the
+ statement is parsed can the slave apply any filtering rules
+ defined by <option>--replicate-do-db</option>,
+ <option>--replicate-do-table</option>,
+ <option>--replicate-ignore-db</option>, and
+ <option>--replicate-ignore-ignore</option>.
+ </para>
+
+ <para>
+ To work around the problem of database, table, or column names on
+ the master which would be regarded as reserved words by the slave,
+ do one of the following:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Use one or more <literal role="stmt">ALTER TABLE</literal>
+ statements on the master to change the names of any database
+ objects where these names would be considered reserved words
+ on the slave, and change any SQL statements that use the old
+ names to use the new names instead.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ In any SQL statements using these database object names, set
+ the names off using backtick characters
+ (<literal>`</literal>).
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ For listings of reserved words by MySQL version, see
+ <ulink
url="http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-optvar.html">Reserved
+ Words</ulink>,.in the <citetitle>MySQL Server Version
+ Reference</citetitle>.
+ </para>
+
+ <para>
+ The following list provides details about what is supported and
+ what is not. Additional information specific to
+ <literal>InnoDB</literal> and replication is given in
+ <xref linkend="innodb-and-mysql-replication"/>.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Replication of <literal>AUTO_INCREMENT</literal>,
+ <literal role="func">LAST_INSERT_ID()</literal>, and
+ <literal role="type">TIMESTAMP</literal> values is done
+ correctly, subject to the following exceptions.
+ </para>
+
+ <para>
+ <literal>INSERT DELAYED ... VALUES(LAST_INSERT_ID())</literal>
+ inserts a different value on the master and the slave. (Bug
+ #20819)
+ </para>
+
+ <para>
+ Adding an <literal>AUTO_INCREMENT</literal> column to a table
+ with <literal role="stmt">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>
+
+ <important>
+ <para>
+ 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>
+ </important>
+
+ <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 role="stmt">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>
+
+<programlisting>
+DROP t1;
+ALTER TABLE t2 RENAME t1;
+</programlisting>
+
+ <para>
+ See also <xref linkend="alter-table-problems"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal role="func">USER()</literal>,
+ <literal role="func">CURRENT_USER()</literal>,
+ <literal role="func">UUID()</literal>, and
+ <literal role="func">LOAD_FILE()</literal> functions are
+ replicated without change and thus do not work reliably on the
+ slave. This is also true for
+ <literal role="func">CONNECTION_ID()</literal> in slave
+ versions older than 4.1.1. The
+ <emphasis role="bold">new</emphasis>
+ <literal role="func">PASSWORD()</literal> function in MySQL
+ 4.1 is well replicated in masters from 4.1.1 and up; your
+ slaves also must be 4.1.1 or above to replicate it. If you
+ have older slaves and need to replicate
+ <literal role="func">PASSWORD()</literal> from your 4.1.x
+ master, you must start your master with the
+ <option>--old-password</option> option, so that it uses the
+ old implementation of
+ <literal role="func">PASSWORD()</literal>. (Note that the
+ <literal role="func">PASSWORD()</literal> implementation in
+ MySQL 4.1.0 differs from every other version of MySQL. It is
+ best to avoid 4.1.0 in a replication scenario.)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal role="func">GET_LOCK()</literal>,
+ <literal role="func">RELEASE_LOCK()</literal>,
+ <literal role="func">IS_FREE_LOCK()</literal>, and
+ <literal role="func">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 role="func">FOUND_ROWS()</literal> function is
+ also not replicated reliably. A workaround is to store the
+ result of the function call in a user variable, and then use
+ that in the <literal role="stmt">INSERT</literal> statement.
+ For example, if you wish to store the result in a table named
+ <literal>mytable</literal>, you might normally do so like
+ this:
+
+<programlisting>
+SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;
+INSERT INTO mytable VALUES( FOUND_ROWS() );
+</programlisting>
+
+ However, if you are replicating <literal>mytable</literal>,
+ then you should use <literal>SELECT INTO</literal>, and then
+ store the variable in the table, like this:
+
+<programlisting>
+SELECT SQL_CALC_FOUND_ROWS INTO @found_rows FROM mytable LIMIT 1;
+INSERT INTO mytable VALUES(@found_rows);
+</programlisting>
+
+ In this way, the user variable is replicated as part of the
+ context, and applied on the slave correctly.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ User privileges are replicated only if the
+ <literal>mysql</literal> database is replicated. That is, the
+ <literal role="stmt">GRANT</literal>,
+ <literal role="stmt">REVOKE</literal>,
+ <literal role="stmt">SET PASSWORD</literal>, and
+ <literal role="stmt">DROP USER</literal> (available as of
+ MySQL 4.1.1) statements take effect on the slave only if the
+ replication setup includes the <literal>mysql</literal>
+ database.
+ </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. That option is available as of MySQL 4.0.13. The slave
+ will recognize that issuing privilege-related SQL statements
+ won't have an effect, and thus not execute those statements.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>FOREIGN_KEY_CHECKS</literal> variable is
+ replicated as of MySQL 4.0.14. The
+ <literal>SQL_MODE</literal>,
<literal>UNIQUE_CHECKS</literal>,
+ <literal>SQL_AUTO_IS_NULL</literal>, and
+ <literal>storage_engine</literal> (also known as
+ <literal>table_type</literal>) variables are not replicated in
+ MySQL 4.1 or earlier versions.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The following applies to replication between MySQL servers
+ that use different character sets:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ You must <emphasis>always</emphasis> use the same
+ <emphasis>global</emphasis> character set and collation on
+ the master and 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.
+ </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>
+ <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>
+ If on the master you have databases with different
+ character sets from the global
+ <literal>collation_server</literal> value, you should
+ design your <literal role="stmt">CREATE TABLE</literal>
+ statements so that they do not implicitly rely on the
+ default database's character set, because there currently
+ is a bug (Bug #2326); a good workaround is to state the
+ character set and collation explicitly in
+ <literal role="stmt">CREATE TABLE</literal>.
+ </para>
+ </listitem>
+
+ </orderedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ 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 role="func">NOW()</literal> or
+ <literal role="func">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. Also
+ starting from version 4.1.3 both master and slave should have
+ the same default connection time zone set, that is the
+ <option>--default-time-zone</option> parameter should have the
+ same value for both master and slave.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal
role="func">CONVERT_TZ(...,...,@@global.time_zone)</literal>
+ is not properly replicated.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Session variables are not replicated properly when used in
+ statements which update tables; for example: <literal>SET
+ MAX_JOIN_SIZE=1000; INSERT INTO mytable
+ VALUES(@@MAX_JOIN_SIZE);</literal> will not insert the same
+ data on the master and on the slave.
+ </para>
+ </listitem>
+
+ <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, there are
+ issues that you should consider before you do this:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ 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>
+ When the storage engine type of the slave is
+ non-transactional, transactions on the master that mix
+ updates of transactional and non-transactional tables
+ should be avoided because they can cause inconsistency of
+ the data between the master's transactional table and the
+ slave's non-transactional table. That is, such
+ transactions can lead to master storage engine-specific
+ behavior with the possible effect of replication going out
+ of synchrony. MySQL does not issue a warning about this
+ currently, so extra care should be taken when replicating
+ transactional tables from the master to non-transactional
+ ones on the slaves.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Update statements that refer to user-defined variables (that
+ is, variables of the form
+
<literal>@<replaceable>var_name</replaceable></literal>) are
+ badly replicated in 3.23 and 4.0. This is fixed in 4.1.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The slave can connect to the master using SSL if both are
+ 4.1.1 or newer.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Starting from MySQL 4.1.11, there is a global system variable
+ <literal>slave_transaction_retries</literal>: 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> or the NDBCLUSTER
+ <literal>TransactionDeadlockDetectionTimeout</literal> or
+ <literal>TransactionInactiveTimeout</literal> value, the
+ transaction automatically retries
+ <literal>slave_transaction_retries</literal> times before
+ stopping with an error. The default value is 0 in MySQL 4.1.
+ Starting from MySQL 4.1.11, the total retry count can be seen
+ in <literal role="stmt">SHOW STATUS</literal>; see
+ <xref linkend="server-status-variables"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If a <literal>DATA DIRECTORY</literal> or <literal>INDEX
+ DIRECTORY</literal> table option is used in a
+ <literal role="stmt">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. As of MySQL 4.0.15, there is
+ 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 role="stmt">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>
+
+ <listitem>
+ <para>
+ 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 non-deterministic; that 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>
+
+ <listitem>
+ <para>
+ If on the master a
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> is interrupted (for example, by a integrity
+ constraint violation or a killed connection), the slave skips
+ this <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> entirely. This means that if this command
+ permanently inserted or updated table records before being
+ interrupted, these modifications are <emphasis>not</emphasis>
+ replicated to the slave.
+ </para>
+
+ <para>
+ In addition, <literal role="stmt" condition="load-data">LOAD
+ DATA INFILE</literal> does not replicate correctly when
+ <option>--binlog-do-db</option> is used. (Bug #19662)
+ </para>
+
+ <para>
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> also does not replicate well from 4.0 and
+ earlier masters to 5.1 or later slaves. In such cases, it is
+ best to upgrade the master to 5.0 or later. (Bug #31240)
+ </para>
+
+ <para>
+ The <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> statement's
+ <literal>CONCURRENT</literal> option is not replicated; that
+ is, <literal>LOAD DATA CONCURRENT INFILE</literal> is
+ replicated as <literal role="stmt" condition="load-data">LOAD
+ DATA INFILE</literal>, and <literal>LOAD DATA CONCURRENT LOCAL
+ INFILE</literal> is replicated as <literal>LOAD DATA LOCAL
+ INFILE</literal>. (Bug #34628)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Before MySQL 4.1.1, the <literal role="stmt">FLUSH</literal>,
+ <literal role="stmt">ANALYZE TABLE</literal>,
+ <literal role="stmt">OPTIMIZE TABLE</literal>, and
+ <literal role="stmt">REPAIR TABLE</literal> statements are not
+ written to the binary log and thus are not replicated to the
+ slaves. This is not normally a problem because these
+ statements do not modify table data. However, it 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 the
+ <literal role="stmt">GRANT</literal> statement, you must issue
+ a <literal>FLUSH PRIVILEGES</literal> statement on your slaves
+ to put the new privileges into effect. Also if you use
+ <literal>FLUSH TABLES</literal> when renaming a
+ <literal>MyISAM</literal> table that is part of a
+ <literal>MERGE</literal> table, you have to issue
+ <literal>FLUSH TABLES</literal> manually on the slaves. As of
+ MySQL 4.1.1, these statements are written to the binary log
+ (unless you specify <literal>NO_WRITE_TO_BINLOG</literal>, or
+ its alias <literal>LOCAL</literal>). Exceptions are that
+ <literal>FLUSH LOGS</literal>, <literal>FLUSH
+ MASTER</literal>, <literal>FLUSH SLAVE</literal>, and
+ <literal>FLUSH TABLES WITH READ LOCK</literal> are not logged
+ in any case. (Any of these may cause problems if replicated to
+ a slave.) For a syntax example, see <xref linkend="flush"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ MySQL 4.1 and earlier support only replication scenarios
+ involving one master and many slaves.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When a server shuts down and restarts, its
+ <literal>MEMORY</literal> (<literal>HEAP</literal>)
tables
+ become empty. As of MySQL 4.0.18, 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 role="stmt">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>
+
+ <listitem>
+ <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 role="stmt">STOP SLAVE</literal>
+ statement.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Use <literal role="stmt">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 SQL thread with
+ <literal>START SLAVE SQL_THREAD</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>
+ </listitem>
+
+ <listitem>
+ <para>
+ The syntax for multiple-table
+ <literal role="stmt">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>
+
+<programlisting>
+DELETE test FROM test AS t1, test2 WHERE ...
+</programlisting>
+
+ <para>
+ In MySQL 4.1, you must use the alias:
+ </para>
+
+<programlisting>
+DELETE t1 FROM test AS t1, test2 WHERE ...
+</programlisting>
+
+ <para>
+ If you use such <literal role="stmt">DELETE</literal>
+ statements, the change in syntax means that a 4.0 master
+ cannot replicate to 4.1 (or higher) slaves.
+ </para>
+ </listitem>
+
+ <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>
+
+ <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>
+ </listitem>
+
+ <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 role="stmt">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 role="stmt">START SLAVE</literal>.
+ </para>
+ </listitem>
+
+ <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 <literal role="stmt">CHANGE MASTER
+ TO</literal> statement or
+ <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>
+
+ <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>
+ </listitem>
+
+ <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>
+
+ <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. This option is
+ available starting with MySQL 3.23.47.
+ </para>
+ </listitem>
+
+ <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>
+
+ <caution>
+ <para>
+ You should not use transactions in a replication environment
+ that update both transactional and non-transactional tables.
+ </para>
+ </caution>
+ </listitem>
+
+ <listitem>
+ <para>
+ Before version 4.0.15, any update to a non-transactional table
+ is written to the binary log at once when the update is made,
+ whereas transactional updates are written on
+ <literal>COMMIT</literal> or not written at all if you use
+ <literal>ROLLBACK</literal>. You must take this into account
+ when updating both transactional tables and non-transactional
+ tables within the same transaction. (This is true not only for
+ replication, but also if you are using binary logging for
+ backups.)
+ </para>
+
+ <para>
+ As of version 4.0.15, we changed the logging behavior for
+ transactions that mix updates to transactional and
+ non-transactional tables, which solves the problems (order of
+ statements is good in the binary log, and all needed
+ statements are written to the binary log even in case of
+ <literal>ROLLBACK</literal>). The problem that remains is that
+ when a second connection updates the non-transactional table
+ while the first connection's transaction is not finished yet,
+ incorrect ordering can still occur because the second
+ connection's update is written immediately after it is done.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ When a 4.x slave replicates a
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> from a 3.23 master, the values of the
+ <literal>Exec_Master_Log_Pos</literal> and
+ <literal>Relay_Log_Space</literal> columns of
+ <literal role="stmt">SHOW SLAVE STATUS</literal> become
+ incorrect. The inaccuracy in
+ <literal>Exec_Master_Log_Pos</literal> causes problems when
+ you stop and restart replication; so it is a good idea to
+ correct the value before this, by doing <literal>FLUSH
+ LOGS</literal> on the master.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Replication slaves do not write replicated queries to the slow
+ query log, even if the same queries were written to the slow
+ query log on the master. This is a known issue which we intend
+ to fix in a future version of MySQL. (Bug #23300)
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The following problems with replication in MySQL 3.23 are fixed in
+ MySQL 4.0:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal role="stmt" condition="load-data">LOAD DATA
+ INFILE</literal> is handled properly, as long as the data file
+ still resides on the master server at the time of update
+ propagation.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>LOAD DATA LOCAL INFILE</literal> is no longer skipped
+ on the slave as it was in 3.23.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ In 3.23, <literal role="func">RAND()</literal> in updates does
+ not replicate properly. Use
+ <literal role="func">RAND(some_non_rand_expr)</literal> if you
+ are replicating updates with
+ <literal role="func">RAND()</literal>. You can, for example,
+ use <literal role="func">UNIX_TIMESTAMP()</literal> as the
+ argument to <literal role="func">RAND()</literal>.
+ </para>
+ </listitem>
+
+ <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>
+
+ </itemizedlist>
+
+ </section>
+
+ <xi:include xmlns:xi="http://www.w3.org/2001/XInclude"
href="dynxml-local-replication-options.xml"/>
+
+ <section id="replication-rules">
+
+ <title>How Servers Evaluate Replication Rules</title>
+
+ <para>
+ If a master server does not write a statement to its binary log,
+ the statement is not replicated. If the server does log the
+ statement, the statement is sent to all slaves and each slave
+ determines whether to execute it or ignore it.
+ </para>
+
+ <para>
+ On the master side, decisions about which statements to log are
+ based on the <option>--binlog-do-db</option> and
+ <option>--binlog-ignore-db</option> options that control binary
+ logging. For a description of the rules that servers use in
+ evaluating these options, see <xref linkend="binary-log"/>.
+ </para>
+
+ <para>
+ On the slave side, decisions about whether to execute or ignore
+ statements received from the master are made according to the
+ <option>--replicate-*</option> options that the slave was started
+ with. (See <xref linkend="replication-options"/>.) The slave
+ evaluates these options using the following procedure, which first
+ checks the database-level options and then the table-level
+ options.
+ </para>
+
+ <para>
+ In the simplest case, when there are no
+ <option>--replicate-*</option> options, the procedure yields the
+ result that the slave executes all statements that it receives
+ from the master. Otherwise, the result depends on the particular
+ options given. In general, to make it easier to determine what
+ effect an option set will have, it is recommended that you avoid
+ mixing <quote>do</quote> and <quote>ignore</quote> options,
or
+ wildcard and non-wildcard options.
+ </para>
+
+ <para>
+ <emphasis role="bold">Stage 1. Check the database
+ options.</emphasis>
+ </para>
+
+ <para>
+ At this stage, the slave checks whether there are any
+ <option>--replicate-do-db</option> or
+ <option>--replicate-ignore-db</option> options that specify
+ database-specific conditions:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: Permit the statement and proceed to
+ the table-checking stage.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: Test the options using the same
+ rules as for the <option>--binlog-do-db</option> and
+ <option>--binlog-ignore-db</option> options to determine
+ whether to permit or ignore the statement. What is the result
+ of the test?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>Permit</emphasis>: Do not execute the statement
+ immediately. Defer the decision and proceed to the
+ table-checking stage.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Ignore</emphasis>: Ignore the statement and
+ exit.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ This stage can permit a statement for further option-checking, or
+ cause it to be ignored. However, statements that are permitted at
+ this stage are not actually executed yet. Instead, they pass to
+ the following stage that checks the table options.
+ </para>
+
+ <para>
+ <emphasis role="bold">Stage 2. Check the table options.</emphasis>
+ </para>
+
+ <para>
+ If the slave reaches this point, it executes all statements if
+ there are no table options. If there are <quote>do</quote> table
+ options, the statement must match one of them if it is to be
+ executed; otherwise, it is ignored. If there are any
+ <quote>ignore</quote> options, all statements are executed except
+ those that match any <literal>ignore</literal> option. The
+ following steps describe how this evaluation occurs in more
+ detail.
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Are there any <option>--replicate-*-table</option> options?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: There are no table restrictions,
+ so all statements match. Execute the statement and exit.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: There are table restrictions.
+ Evaluate the tables to be updated against them. There
+ might be multiple tables to update, so loop through the
+ following steps for each table looking for a matching
+ option (first the non-wild options, and then the wild
+ options). Only tables that are to be updated are compared
+ to the options. For example, if the statement is
+ <literal>INSERT INTO sales SELECT * FROM prices</literal>,
+ only <literal>sales</literal> is compared to the options).
+ </para>
+
+ <important>
+ <para>
+ If several tables are to be updated (multiple-table
+ statement), <emphasis>every</emphasis> table referenced
+ in the statement must have a matching
+ <option>--replicate-do-table</option> rule. If any table
+ to be updated does not have a matching
+ <option>--replicate-do-table</option> rule, then the
+ update is not replicated.
+ </para>
+ </important>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Are there any <option>--replicate-do-table</option> options?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: Proceed to the next step.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: Does the table match any of
+ them?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: Proceed to the next step.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: Execute the statement and
+ exit.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Are there any <option>--replicate-ignore-table</option>
+ options?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: Proceed to the next step.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: Does the table match any of
+ them?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: Proceed to the next step.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: Ignore the statement and
+ exit.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Are there any <option>--replicate-wild-do-table</option>
+ options?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: Proceed to the next step.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: Does the table match any of
+ them?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: Proceed to the next step.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: Execute the statement and
+ exit.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Are there any <option>--replicate-wild-ignore-table</option>
+ options?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: Proceed to the next step.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: Does the table match any of
+ them?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: Proceed to the next step.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: Ignore the statement and
+ exit.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ No <option>--replicate-*-table</option> option was matched. Is
+ there another table to test against these options?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: We have now tested all tables to
+ be updated and could not match any option. Are there
+ <option>--replicate-do-table</option> or
+ <option>--replicate-wild-do-table</option> options?
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <emphasis>No</emphasis>: There were no
+ <quote>do</quote> table options, so no explicit
+ <quote>do</quote> match is required. Execute the
+ statement and exit.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: There were
<quote>do</quote>
+ table options, so the statement is executed only with
+ an explicit match to one of them. Ignore the statement
+ and exit.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ <emphasis>Yes</emphasis>: Loop.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ Examples:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ No <option>--replicate-*</option> options at all
+ </para>
+
+ <para>
+ The slave executes all statements that it receives from the
+ master.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--replicate-*-db</option> options, but no table
+ options
+ </para>
+
+ <para>
+ The slave permits or ignores statements using the database
+ options. Then it executes all statements permitted by those
+ options because there are no table restrictions.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <option>--replicate-*-table</option> options, but no database
+ options
+ </para>
+
+ <para>
+ All statements are permitted at the database-checking stage
+ because there are no database conditions. The slave executes
+ or ignores statements based on the table options.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A mix of database and table options
+ </para>
+
+ <para>
+ The slave permits or ignores statements using the database
+ options. Then it evaluates all statements permitted by those
+ options according to the table options. In some cases, this
+ process can yield what might seem a counterintuitive result.
+ Consider the following set of options:
+ </para>
+
+<programlisting>
+[mysqld]
+replicate-do-db = db1
+replicate-do-table = db2.mytbl2
+</programlisting>
+
+ <para>
+ Suppose that <literal>db1</literal> is the default database
+ and the slave receives this statement:
+ </para>
+
+<programlisting>
+INSERT INTO mytbl1 VALUES(1,2,3);
+</programlisting>
+
+ <para>
+ The database is <literal>db1</literal>, which matches the
+ <option>--replicate-do-db</option> option at the
+ database-checking stage. The algorithm then proceeds to the
+ table-checking stage. If there were no table options, the
+ statement would be executed. However, because the options
+ include a <quote>do</quote> table option, the statement must
+ match if it is to be executed. The statement does not match,
+ so it is ignored. (The same would happen for any table in
+ <literal>db1</literal>.)
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="replication-faq">
+
+ <title>Replication FAQ</title>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: How do I configure a slave if
+ the master is running and I do not want to stop it?
+ </para>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: 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 role="stmt">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 role="stmt">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>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: Does the slave need to be
+ connected to the master all the time?
+ </para>
+
+ <remark role="todo">
+ [pd] Is this a promise we should make?
+ </remark>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: No, it does not. The slave can
+ go down or stay disconnected for hours or even days, and then
+ reconnect and catch up on updates. For example, you can set up a
+ master/slave relationship over a dial-up link where the link is up
+ only sporadically and for short periods of time. The implication
+ of this is that, at any given time, the slave is not guaranteed to
+ be in synchrony with the master unless you take some special
+ measures.
+ </para>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: How do I know how late a slave
+ is compared to the master? In other words, how do I know the date
+ of the last statement replicated by the slave?
+ </para>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: If the slave is 4.1.1 or
+ newer, read the <literal>Seconds_Behind_Master</literal> column in
+ <literal role="stmt">SHOW SLAVE STATUS</literal>, which shows the
+ number of seconds that the slave SQL thread is behind processing
+ the master binary log. A high number (or an increasing one) can
+ indicate that the slave is unable to cope with the large number of
+ queries from the master.
+ </para>
+
+ <para>
+ A value of 0 for <literal>Seconds_Behind_Master</literal> can
+ usually be interpreted as meaning that the slave has caught up
+ with the master, but there are some cases where this is not
+ strictly true. For example, this can occur if the network
+ connection between master and slave is broken but the slave I/O
+ thread has not yet noticed this — that is,
+ <literal>slave_net_timeout</literal> has not yet elapsed.
+ </para>
+
+ <para>
+ It is also possible that transient values for
+ <literal>Seconds_Behind_Master</literal> may not reflect the
+ situation accurately. When the slave SQL thread has caught up on
+ I/O, <literal>Seconds_Behind_Master</literal> displays 0; but when
+ the slave I/O thread is still queuing up a new event,
+ <literal>Seconds_Behind_Master</literal> may show a large value
+ until the SQL thread finishes executing the new event. This is
+ especially likely when the events have old timestamps; in such
+ cases, if you execute <literal role="stmt">SHOW SLAVE
+ STATUS</literal> several times in a relatively short peiod, you
+ may see this value change back and forth repeatedly between 0 and
+ a relatively large value.
+ </para>
+
+ <para>
+ For versions of MySQL prior to 4.1.1, it is possible to determine
+ how far behind the slave is only if <literal role="stmt">SHOW
+ SLAVE STATUS</literal> on the slave shows that the SQL thread is
+ running (or for MySQL 3.23, that the slave thread is running), and
+ that the thread has executed at least one event from the master.
+ See <xref linkend="replication-implementation-details"/>.
+ </para>
+
+ <remark role="todo">
+ Check following in light of changes to TIMESTAMP in recent
+ versions. /JS
+ </remark>
+
+ <para>
+ When the slave SQL thread executes an event read from the master,
+ it modifies its own time to the event timestamp. (This is why
+ <literal role="type">TIMESTAMP</literal> is well replicated.) In
+ the <literal>Time</literal> column in the output of
+ <literal role="stmt">SHOW PROCESSLIST</literal>, the number of
+ seconds displayed for the slave SQL thread is the number of
+ seconds between the timestamp of the last replicated event and the
+ real time of the slave machine. You can use this to determine the
+ date of the last replicated event. Note that if your slave has
+ been disconnected from the master for one hour, and then
+ reconnects, you may immediately see <literal>Time</literal> values
+ like 3600 for the slave SQL thread in <literal role="stmt">SHOW
+ PROCESSLIST</literal>. This is because the slave is executing
+ statements that are one hour old.
+ </para>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: How do I force the master to
+ block updates until the slave catches up?
+ </para>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: Use the following procedure:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ On the master, execute these statements:
+ </para>
+
+<programlisting>
+mysql> <userinput>FLUSH TABLES WITH READ LOCK;</userinput>
+mysql> <userinput>SHOW MASTER STATUS;</userinput>
+</programlisting>
+
+ <para>
+ Record the replication coordinates (the log filename and
+ offset) from the output of the <literal>SHOW</literal>
+ statement.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ On the slave, issue the following statement, where the
+ arguments to the
+ <literal role="func">MASTER_POS_WAIT()</literal> function are
+ the replication coordinate values obtained in the previous
+ step:
+ </para>
+
+<programlisting>
+mysql> <userinput>SELECT
MASTER_POS_WAIT('<replaceable>log_name</replaceable>',
<replaceable>log_offset</replaceable>);</userinput>
+</programlisting>
+
+ <para>
+ The <literal role="stmt">SELECT</literal> statement blocks
+ until the slave reaches the specified log file and offset. At
+ that point, the slave is in synchrony with the master and the
+ statement returns.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ On the master, issue the following statement to allow the
+ master to begin processing updates again:
+ </para>
+
+<programlisting>
+mysql> <userinput>UNLOCK TABLES;</userinput>
+</programlisting>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: What issues should I be aware
+ of when setting up two-way replication?
+ </para>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: MySQL replication currently
+ does not support any locking protocol between master and slave to
+ guarantee the atomicity of a distributed (cross-server) update. In
+ other words, it is possible for client A to make an update to
+ co-master 1, and in the meantime, before it propagates to
+ co-master 2, client B could make an update to co-master 2 that
+ makes the update of client A work differently than it did on
+ co-master 1. Thus, when the update of client A makes it to
+ co-master 2, it produces tables that are different from what you
+ have on co-master 1, even after all the updates from co-master 2
+ have also propagated. This means that you should not chain two
+ servers together in a two-way replication relationship unless you
+ are sure that your updates can safely happen in any order, or
+ unless you take care of mis-ordered updates somehow in the client
+ code.
+ </para>
+
+ <para>
+ You should also realize that two-way replication actually does not
+ improve performance very much (if at all) as far as updates are
+ concerned. Each server must do the same number of updates, just as
+ you would have a single server do. The only difference is that
+ there is a little less lock contention, because the updates
+ originating on another server are serialized in one slave thread.
+ Even this benefit might be offset by network delays.
+ </para>
+
+ <indexterm>
+ <primary>performance</primary>
+ <secondary>improving</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>increasing</primary>
+ <secondary>performance</secondary>
+ </indexterm>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: How can I use replication to
+ improve performance of my system?
+ </para>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: You should set up one server
+ as the master and direct all writes to it. Then configure as many
+ slaves as you have the budget and rackspace for, and distribute
+ the reads among the master and the slaves. You can also start the
+ slaves with the <option>--skip-innodb</option>,
+ <option>--skip-bdb</option>,
+ <option>--low-priority-updates</option>, and
+ <option>--delay-key-write=ALL</option> options to get speed
+ improvements on the slave end. In this case, the slave uses
+ non-transactional <literal>MyISAM</literal> tables instead of
+ <literal>InnoDB</literal> and <literal>BDB</literal> tables
to get
+ more speed by eliminating transactional overhead.
+ </para>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: What should I do to prepare
+ client code in my own applications to use performance-enhancing
+ replication?
+ </para>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: If the part of your code that
+ is responsible for database access has been properly
+ abstracted/modularized, converting it to run with a replicated
+ setup should be very smooth and easy. Change the implementation of
+ your database access to send all writes to the master, and to send
+ reads to either the master or a slave. If your code does not have
+ this level of abstraction, setting up a replicated system gives
+ you the opportunity and motivation to it clean up. Start by
+ creating a wrapper library or module that implements the following
+ functions:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>safe_writer_connect()</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>safe_reader_connect()</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>safe_reader_statement()</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>safe_writer_statement()</literal>
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <literal>safe_</literal> in each function name means that the
+ function takes care of handling all error conditions. You can use
+ different names for the functions. The important thing is to have
+ a unified interface for connecting for reads, connecting for
+ writes, doing a read, and doing a write.
+ </para>
+
+ <para>
+ Then convert your client code to use the wrapper library. This may
+ be a painful and scary process at first, but it pays off in the
+ long run. All applications that use the approach just described
+ are able to take advantage of a master/slave configuration, even
+ one involving multiple slaves. The code is much easier to
+ maintain, and adding troubleshooting options is trivial. You need
+ modify only one or two functions; for example, to log how long
+ each statement took, or which statement among those issued gave
+ you an error.
+ </para>
+
+ <para>
+ If you have written a lot of code, you may want to automate the
+ conversion task by using the <command>replace</command> utility
+ that comes with standard MySQL distributions, or just write your
+ own conversion script. Ideally, your code uses consistent
+ programming style conventions. If not, then you are probably
+ better off rewriting it anyway, or at least going through and
+ manually regularizing it to use a consistent style.
+ </para>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: When and how much can MySQL
+ replication improve the performance of my system?
+ </para>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: MySQL replication is most
+ beneficial for a system that processes frequent reads and
+ infrequent writes. In theory, by using a
+ single-master/multiple-slave setup, you can scale the system by
+ adding more slaves until you either run out of network bandwidth,
+ or your update load grows to the point that the master cannot
+ handle it.
+ </para>
+
+ <para>
+ To determine how many slaves you can use before the added benefits
+ begin to level out, and how much you can improve performance of
+ your site, you need to know your query patterns, and to determine
+ empirically by benchmarking the relationship between the
+ throughput for reads (reads per second, or
+ <literal>reads</literal>) and for writes
+ (<literal>writes</literal>) on a typical master and a typical
+ slave. The example here shows a rather simplified calculation of
+ what you can get with replication for a hypothetical system.
+ </para>
+
+ <para>
+ Let's say that system load consists of 10% writes and 90% reads,
+ and we have determined by benchmarking that
+ <literal>reads</literal> is 1200 − 2 ×
+ <literal>writes</literal>. In other words, the system can do 1,200
+ reads per second with no writes, the average write is twice as
+ slow as the average read, and the relationship is linear. Let us
+ suppose that the master and each slave have the same capacity, and
+ that we have one master and <replaceable>N</replaceable> slaves.
+ Then we have for each server (master or slave):
+ </para>
+
+ <para>
+ <literal>reads = 1200 − 2 × writes</literal>
+ </para>
+
+ <para>
+ <literal>reads = 9 × writes /
(<replaceable>N</replaceable>
+ + 1)</literal> (reads are split, but writes go to all servers)
+ </para>
+
+ <para>
+ <literal>9 × writes / (<replaceable>N</replaceable> +
1) + 2
+ × writes = 1200</literal>
+ </para>
+
+ <para>
+ <literal>writes = 1200 / (2 +
+ 9/(<replaceable>N</replaceable>+1))</literal>
+ </para>
+
+ <para>
+ The last equation indicates the maximum number of writes for
+ <replaceable>N</replaceable> slaves, given a maximum possible read
+ rate of 1,200 per minute and a ratio of nine reads per write.
+ </para>
+
+ <para>
+ This analysis yields the following conclusions:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ If <replaceable>N</replaceable> = 0 (which means we have no
+ replication), our system can handle about 1200/11 = 109 writes
+ per second.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If <replaceable>N</replaceable> = 1, we get up to 184 writes
+ per second.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If <replaceable>N</replaceable> = 8, we get up to 400 writes
+ per second.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If <replaceable>N</replaceable> = 17, we get up to 480 writes
+ per second.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Eventually, as <replaceable>N</replaceable> approaches
+ infinity (and our budget negative infinity), we can get very
+ close to 600 writes per second, increasing system throughput
+ about 5.5 times. However, with only eight servers, we increase
+ it nearly four times.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Note that these computations assume infinite network bandwidth and
+ neglect several other factors that could be significant on your
+ system. In many cases, you may not be able to perform a
+ computation similar to the one just shown that accurately predicts
+ what will happen on your system if you add
+ <replaceable>N</replaceable> replication slaves. However,
+ answering the following questions should help you decide whether
+ and by how much replication will improve the performance of your
+ system:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ What is the read/write ratio on your system?
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ How much more write load can one server handle if you reduce
+ the reads?
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For how many slaves do you have bandwidth available on your
+ network?
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: How can I use replication to
+ provide redundancy or high availability?
+ </para>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: With the currently available
+ features, you would have to set up a master and a slave (or
+ several slaves), and to write a script that monitors the master to
+ check whether it is up. Then instruct your applications and the
+ slaves to change master in case of failure. Some suggestions:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ To tell a slave to change its master, use the
+ <literal role="stmt">CHANGE MASTER TO</literal> statement.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A good way to keep your applications informed as to the
+ location of the master is by having a dynamic DNS entry for
+ the master. With <literal>bind</literal> you can use
+ <filename>nsupdate</filename> to dynamically update your DNS.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Run your slaves with the <option>--log-bin</option> option and
+ without <option>--log-slave-updates</option>. In this way, the
+ slave is ready to become a master as soon as you issue
+ <literal role="stmt">STOP SLAVE</literal>;
+ <literal role="stmt">RESET MASTER</literal>, and
+ <literal role="stmt">CHANGE MASTER TO</literal> statement on
+ the other slaves. For example, assume that you have the
+ following setup:
+ </para>
+
+ <remark role="todo">
+ Replace this with an actual diagram.
+ </remark>
+
+<programlisting>
+ WC
+ \
+ v
+ WC----> M
+ / | \
+ / | \
+ v v v
+ S1 S2 S3
+</programlisting>
+
+ <para>
+ In this diagram, <literal>M</literal> means the master,
+ <literal>S</literal> the slaves, <literal>WC</literal>
the
+ clients issuing database writes and reads; clients that issue
+ only database reads are not represented, because they need not
+ switch. <literal>S1</literal>, <literal>S2</literal>,
and
+ <literal>S3</literal> are slaves running with
+ <option>--log-bin</option> and without
+ <option>--log-slave-updates</option>. Because updates received
+ by a slave from the master are not logged in the binary log
+ unless <option>--log-slave-updates</option> is specified, the
+ binary log on each slave is empty initially. If for some
+ reason <literal>M</literal> becomes unavailable, you can pick
+ one of the slaves to become the new master. For example, if
+ you pick <literal>S1</literal>, all
<literal>WC</literal>
+ should be redirected to <literal>S1</literal>, which will log
+ updates to its binary log. <literal>S2</literal> and
+ <literal>S3</literal> should then replicate from
+ <literal>S1</literal>.
+ </para>
+
+ <para>
+ The reason for running the slave without
+ <option>--log-slave-updates</option> is to prevent slaves from
+ receiving updates twice in case you cause one of the slaves to
+ become the new master. Suppose that <literal>S1</literal> has
+ <option>--log-slave-updates</option> enabled. Then it will
+ write updates that it receives from <literal>M</literal> to
+ its own binary log. When <literal>S2</literal> changes from
+ <literal>M</literal> to <literal>S1</literal> as its
master,
+ it may receive updates from <literal>S1</literal> that it has
+ already received from <literal>M</literal>
+ </para>
+
+ <para>
+ Make sure that all slaves have processed any statements in
+ their relay log. On each slave, issue <literal>STOP SLAVE
+ IO_THREAD</literal>, then check the output of
+ <literal role="stmt">SHOW PROCESSLIST</literal> until you see
+ <literal>Has read all relay log</literal>. When this is true
+ for all slaves, they can be reconfigured to the new setup. On
+ the slave <literal>S1</literal> being promoted to become the
+ master, issue <literal role="stmt">STOP SLAVE</literal> and
+ <literal role="stmt">RESET MASTER</literal>.
+ </para>
+
+ <para>
+ On the other slaves <literal>S2</literal> and
+ <literal>S3</literal>, use <literal role="stmt">STOP
+ SLAVE</literal> and <literal>CHANGE MASTER TO
+ MASTER_HOST='S1'</literal> (where <literal>'S1'</literal>
+ represents the real hostname of <literal>S1</literal>). To
+ <literal>CHANGE MASTER</literal>, add all information about
+ how to connect to <literal>S1</literal> from
+ <literal>S2</literal> or <literal>S3</literal>
+ (<replaceable>user</replaceable>,
+ <replaceable>password</replaceable>,
+ <replaceable>port</replaceable>). In <literal>CHANGE
+ MASTER</literal>, there is no need to specify the name of
+ <literal>S1</literal>'s binary log or binary log position to
+ read from: We know it is the first binary log and position 4,
+ which are the defaults for <literal>CHANGE MASTER</literal>.
+ Finally, use <literal role="stmt">START SLAVE</literal> on
+ <literal>S2</literal> and <literal>S3</literal>.
+ </para>
+
+ <para>
+ Then instruct all <literal>WC</literal> to direct their
+ statements to <literal>S1</literal>. From that point on, all
+ updates statements sent by <literal>WC</literal> to
+ <literal>S1</literal> are written to the binary log of
+ <literal>S1</literal>, which then contains every update
+ statement sent to <literal>S1</literal> since
+ <literal>M</literal> died.
+ </para>
+
+ <para>
+ The result is this configuration:
+ </para>
+
+ <remark role="todo">
+ Replace with actual diagram (graphic).
+ </remark>
+
+<programlisting>
+ WC
+ /
+ |
+ WC | M(unavailable)
+ \ |
+ \ |
+ v v
+ S1<--S2 S3
+ ^ |
+ +-------+
+</programlisting>
+
+ <para>
+ When <literal>M</literal> is up again, you must issue on it
+ the same <literal>CHANGE MASTER</literal> as that issued on
+ <literal>S2</literal> and <literal>S3</literal>, so
that
+ <literal>M</literal> becomes a slave of
<literal>S1</literal>
+ and picks up all the <literal>WC</literal> writes that it
+ missed while it was down. To make <literal>M</literal> a
+ master again (because it is the most powerful machine, for
+ example), use the preceding procedure as if
+ <literal>S1</literal> was unavailable and
<literal>M</literal>
+ was to be the new master. During this procedure, do not forget
+ to run <literal role="stmt">RESET MASTER</literal> on
+ <literal>M</literal> before making
<literal>S1</literal>,
+ <literal>S2</literal>, and <literal>S3</literal> slaves
of
+ <literal>M</literal>. Otherwise, they may pick up old
+ <literal>WC</literal> writes from before the point at which
+ <literal>M</literal> became unavailable.
+ </para>
+
+ <para>
+ Note that there is no synchronization between the different
+ slaves to a master. Some slaves might be ahead of others. This
+ means that the concept outlined in the previous example might
+ not work. In practice, however, the relay logs of different
+ slaves will most likely not be far behind the master, so it
+ would work, anyway (but there is no guarantee).
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: How do I prevent GRANT and
+ REVOKE statements from replicating to slave machines?
+ </para>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: Start the server with the
+ <option>--replicate-wild-ignore-table=mysql.%</option> option.
+ </para>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: Does replication work on mixed
+ operating systems (for example, the master runs on Linux while
+ slaves run on Mac OS X and Windows)?
+ </para>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: Yes.
+ </para>
+
+ <para>
+ <emphasis role="bold">Q</emphasis>: Does replication work on mixed
+ hardware architectures (for example, the master runs on a 64-bit
+ machine while slaves run on 32-bit machines)?
+ </para>
+
+ <para>
+ <emphasis role="bold">A</emphasis>: Yes.
+ </para>
+
+ </section>
+
+ <section id="replication-problems">
+
+ <title>Troubleshooting Replication</title>
+
+ <para>
+ If you have followed the instructions, and your replication setup
+ is not working, the first thing to do is <emphasis>check the error
+ log for messages</emphasis>. Many users have lost time by not
+ doing this soon enough after encountering problems.
+ </para>
+
+ <para>
+ If you cannot tell from the error log what the problem was, try
+ the following techniques:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Verify that the master has binary logging enabled by issuing a
+ <literal role="stmt">SHOW MASTER STATUS</literal> statement.
+ If logging is enabled, <literal>Position</literal> is
+ non-zero. If binary logging is not enabled, verify that you
+ are running the master with the <option>--log-bin</option> and
+ <option>--server-id</option> options.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Verify that the slave is running. Use
+ <literal role="stmt">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. For example,
+ <option>--skip-slave-start</option> prevents the slave threads
+ from starting until you issue a <literal role="stmt">START
+ SLAVE</literal> statement.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ If the slave is running, check whether it established a
+ connection to the master. Use <literal role="stmt">SHOW
+ PROCESSLIST</literal>, find the I/O and SQL threads and check
+ their <literal>State</literal> column to see what 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,
+ the master hostname, your DNS setup, whether the master is
+ actually running, and whether it is reachable from the slave.
+ </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 the
+ slave stops unexpectedly, 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>
+
+ <formalpara role="mnmas">
+
+ <title>MySQL Enterprise</title>
+
+ <para>
+ For immediate notification whenever a slave stops, subscribe
+ to the MySQL Enterprise Monitor. For more information, see
+ <ulink url="&base-url-enterprise;advisors.html"/>.
+ </para>
+
+ </formalpara>
+ </listitem>
+
+ <listitem>
+ <para>
+ If a statement that succeeded on the master refuses to run on
+ the slave, try the following procedure if it is not feasible
+ to do a full database resynchronization by deleting the
+ slave's databases and copying a new snapshot from the master:
+ </para>
+
+ <orderedlist>
+
+ <listitem>
+ <para>
+ Determine whether the affected table on the slave is
+ different from the master table. Try to understand how
+ this happened. Then make the slave's table identical to
+ the master's and run <literal role="stmt">START
+ SLAVE</literal>.
+ </para>
+ </listitem>
+
+ <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 role="func">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 role="func">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 of MySQL, please
+ report the problem. If you are running an older version,
+ try upgrading to the latest production release to
+ determine whether the problem persists.
+ </para>
+ </listitem>
+
+ </orderedlist>
+ </listitem>
+
+ </itemizedlist>
+
+ </section>
+
+ <section id="replication-bugs">
+
+ <title>How to Report Replication Bugs or Problems</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>
+ If you have a repeatable test case that demonstrates the bug,
+ please enter it into our bugs database using the instructions
+ given in <xref linkend="bug-reports"/>. If you have a
+ <quote>phantom</quote> problem (one that you cannot duplicate at
+ will), use the following procedure:
+ </para>
+
+ <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 of 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>
+ 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>
+
+ <listitem>
+ <para>
+ All binary logs from the master
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ All binary logs from the slave
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The output of <literal role="stmt">SHOW MASTER
+ STATUS</literal> from the master at the time you
+ discovered the problem
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The output of <literal role="stmt">SHOW SLAVE
+ STATUS</literal> from the slave at the time you discovered
+ the problem
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Error logs from the master and the slave
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Use <command>mysqlbinlog</command> to examine the binary logs.
+ The following should be helpful to find the problem statement.
+ <replaceable>log_pos</replaceable> and
+ <replaceable>log_file</replaceable> are the
+ <literal>Master_Log_File</literal> and
+ <literal>Read_Master_Log_Pos</literal> values from
+ <literal role="stmt">SHOW SLAVE STATUS</literal>.
+ </para>
+
+<programlisting>
+shell> <userinput>mysqlbinlog -j
<replaceable>log_pos</replaceable>
<replaceable>log_file</replaceable> | head</userinput>
+</programlisting>
+ </listitem>
+
+ </orderedlist>
+
+ <para>
+ After you have collected the evidence for the problem, try to
+ isolate it as a separate test case first. Then enter the problem
+ with as much information as possible into our bugs database using
+ the instructions at <xref linkend="bug-reports"/>.
+ </para>
+
+ </section>
+
+</chapter>
Modified: trunk/refman-5.0/Makefile.depends
===================================================================
--- trunk/refman-5.0/Makefile.depends 2008-11-06 17:30:40 UTC (rev 12309)
+++ trunk/refman-5.0/Makefile.depends 2008-11-06 18:05:11 UTC (rev 12310)
Changed blocks: 16, Lines Added: 63, Lines Deleted: 46; 13630 bytes
@@ -452,9 +452,9 @@
../refman-5.0/metadata/programs-installation.idmap \
../refman-5.0/metadata/programs-server-core.idmap \
../refman-5.0/metadata/programs-using.idmap \
- ../refman-5.0/metadata/replication-configuration-core.idmap \
../refman-5.0/metadata/replication-implementation.idmap \
../refman-5.0/metadata/replication-notes.idmap \
+ ../refman-5.0/metadata/replication-options-core.idmap \
../refman-5.0/metadata/replication.idmap \
../refman-5.0/metadata/se-bdb-core.idmap \
../refman-5.0/metadata/se-innodb-core.idmap \
@@ -935,34 +935,31 @@
dynxml-local-releasenotes-es-5.0-manprepped.xml:
$(dynxml_local_releasenotes_es_5_0_SOURCES) $(dynxml_local_releasenotes_es_5_0_IDMAPS)
dynxml-local-releasenotes-es-5.0-remprepped.xml:
$(dynxml_local_releasenotes_es_5_0_SOURCES) $(dynxml_local_releasenotes_es_5_0_IDMAPS)
dynxml-local-releasenotes-es-5.0.xml: $(dynxml_local_releasenotes_es_5_0_INCLUDES)
-dynxml_local_replication_configuration_INCLUDES = \
+dynxml_local_replication_options_INCLUDES = \
../common/fixedchars.ent \
../common/phrases.ent \
- ../dynamic-docs/command-optvars/mysqld.xml \
- ../dynamic-docs/metadata-titles.en.xml \
../refman-common/urls.ent \
all-entities.ent \
- replication-configuration-core.xml \
versions.ent
-dynxml_local_replication_configuration_IMAGES =
-dynxml_local_replication_configuration_SOURCES =
dynxml-local-replication-configuration.xml
$(dynxml_local_replication_configuration_INCLUDES)
-dynxml_local_replication_configuration_IDMAPS = \
+dynxml_local_replication_options_IMAGES =
+dynxml_local_replication_options_SOURCES = dynxml-local-replication-options.xml
$(dynxml_local_replication_options_INCLUDES)
+dynxml_local_replication_options_IDMAPS = \
../refman-5.0/metadata/dba-core.idmap \
../refman-5.0/metadata/errors-problems.idmap \
- ../refman-5.0/metadata/optimization.idmap \
- ../refman-5.0/metadata/replication-configuration-core.idmap \
../refman-5.0/metadata/replication-implementation.idmap \
+ ../refman-5.0/metadata/replication-notes.idmap \
+ ../refman-5.0/metadata/replication-options-core.idmap \
../refman-5.0/metadata/sql-syntax-replication.idmap \
- ../refman-5.0/metadata/sql-syntax-server-administration.idmap
-dynxml-local-replication-configuration.validpure:
$(dynxml_local_replication_configuration_SOURCES)
-dynxml-local-replication-configuration.titles:
$(dynxml_local_replication_configuration_SOURCES)
-dynxml-local-replication-configuration.useless:
$(dynxml_local_replication_configuration_SOURCES)
-dynxml-local-replication-configuration.valid:
$(dynxml_local_replication_configuration_SOURCES)
$(dynxml_local_replication_configuration_IDMAPS)
-dynxml-local-replication-configuration.validwarn:
$(dynxml_local_replication_configuration_SOURCES)
$(dynxml_local_replication_configuration_IDMAPS)
-dynxml-local-replication-configuration-prepped.xml:
$(dynxml_local_replication_configuration_SOURCES)
$(dynxml_local_replication_configuration_IDMAPS)
-dynxml-local-replication-configuration-manprepped.xml:
$(dynxml_local_replication_configuration_SOURCES)
$(dynxml_local_replication_configuration_IDMAPS)
-dynxml-local-replication-configuration-remprepped.xml:
$(dynxml_local_replication_configuration_SOURCES)
$(dynxml_local_replication_configuration_IDMAPS)
-dynxml-local-replication-configuration.xml:
$(dynxml_local_replication_configuration_INCLUDES)
+ ../refman-5.0/metadata/stored-programs-views.idmap
+dynxml-local-replication-options.validpure: $(dynxml_local_replication_options_SOURCES)
+dynxml-local-replication-options.titles: $(dynxml_local_replication_options_SOURCES)
+dynxml-local-replication-options.useless: $(dynxml_local_replication_options_SOURCES)
+dynxml-local-replication-options.valid: $(dynxml_local_replication_options_SOURCES)
$(dynxml_local_replication_options_IDMAPS)
+dynxml-local-replication-options.validwarn: $(dynxml_local_replication_options_SOURCES)
$(dynxml_local_replication_options_IDMAPS)
+dynxml-local-replication-options-prepped.xml: $(dynxml_local_replication_options_SOURCES)
$(dynxml_local_replication_options_IDMAPS)
+dynxml-local-replication-options-manprepped.xml:
$(dynxml_local_replication_options_SOURCES) $(dynxml_local_replication_options_IDMAPS)
+dynxml-local-replication-options-remprepped.xml:
$(dynxml_local_replication_options_SOURCES) $(dynxml_local_replication_options_IDMAPS)
+dynxml-local-replication-options.xml: $(dynxml_local_replication_options_INCLUDES)
dynxml_local_se_bdb_INCLUDES = \
../common/fixedchars.ent \
../common/phrases.ent \
@@ -1009,6 +1006,7 @@
../refman-5.0/metadata/installing-core.idmap \
../refman-5.0/metadata/news-5.0-core.idmap \
../refman-5.0/metadata/programs-using.idmap \
+ ../refman-5.0/metadata/replication-options-core.idmap \
../refman-5.0/metadata/se-innodb-core.idmap \
../refman-5.0/metadata/sql-syntax-data-definition.idmap \
../refman-5.0/metadata/sql-syntax-data-manipulation.idmap \
@@ -1670,7 +1668,7 @@
dynxml-local-programs-server.xml \
dynxml-local-releasenotes-cs-5.0.xml \
dynxml-local-releasenotes-es-5.0.xml \
- dynxml-local-replication-configuration.xml \
+ dynxml-local-replication-options.xml \
dynxml-local-se-bdb.xml \
dynxml-local-se-innodb.xml \
dynxml-local-se-myisam.xml \
@@ -1733,7 +1731,7 @@
releasenotes-cs.xml \
releasenotes-es-5.0-core.xml \
releasenotes-es.xml \
- replication-configuration-core.xml \
+ replication-configuration.xml \
replication-implementation.xml \
replication-notes.xml \
replication-solutions.xml \
@@ -1916,9 +1914,10 @@
../refman-5.0/metadata/programs.idmap \
../refman-5.0/metadata/releasenotes-cs.idmap \
../refman-5.0/metadata/releasenotes-es.idmap \
- ../refman-5.0/metadata/replication-configuration-core.idmap \
+ ../refman-5.0/metadata/replication-configuration.idmap \
../refman-5.0/metadata/replication-implementation.idmap \
../refman-5.0/metadata/replication-notes.idmap \
+ ../refman-5.0/metadata/replication-options-core.idmap \
../refman-5.0/metadata/replication-solutions.idmap \
../refman-5.0/metadata/replication.idmap \
../refman-5.0/metadata/restrictions.idmap \
@@ -2844,18 +2843,34 @@
releasenotes-es-manprepped.xml: $(releasenotes_es_SOURCES) $(releasenotes_es_IDMAPS)
releasenotes-es-remprepped.xml: $(releasenotes_es_SOURCES) $(releasenotes_es_IDMAPS)
-replication_configuration_core_INCLUDES =
-replication_configuration_core_IMAGES =
-replication_configuration_core_SOURCES = replication-configuration-core.xml
$(replication_configuration_core_INCLUDES)
-replication_configuration_core_IDMAPS =
-replication-configuration-core.validpure: $(replication_configuration_core_SOURCES)
-replication-configuration-core.titles: $(replication_configuration_core_SOURCES)
-replication-configuration-core.useless: $(replication_configuration_core_SOURCES)
-replication-configuration-core.valid: $(replication_configuration_core_SOURCES)
$(replication_configuration_core_IDMAPS)
-replication-configuration-core.validwarn: $(replication_configuration_core_SOURCES)
$(replication_configuration_core_IDMAPS)
-replication-configuration-core-prepped.xml: $(replication_configuration_core_SOURCES)
$(replication_configuration_core_IDMAPS)
-replication-configuration-core-manprepped.xml: $(replication_configuration_core_SOURCES)
$(replication_configuration_core_IDMAPS)
-replication-configuration-core-remprepped.xml: $(replication_configuration_core_SOURCES)
$(replication_configuration_core_IDMAPS)
+replication_configuration_INCLUDES = \
+ ../common/fixedchars.ent \
+ ../common/phrases.ent \
+ ../refman-common/urls.ent \
+ all-entities.ent \
+ dynxml-local-replication-options.xml \
+ versions.ent
+replication_configuration_IMAGES =
+replication_configuration_SOURCES = replication-configuration.xml
$(replication_configuration_INCLUDES)
+replication_configuration_IDMAPS = \
+ ../refman-5.0/metadata/dba-core.idmap \
+ ../refman-5.0/metadata/errors-problems.idmap \
+ ../refman-5.0/metadata/optimization.idmap \
+ ../refman-5.0/metadata/replication-configuration.idmap \
+ ../refman-5.0/metadata/replication-implementation.idmap \
+ ../refman-5.0/metadata/replication-notes.idmap \
+ ../refman-5.0/metadata/replication-options-core.idmap \
+ ../refman-5.0/metadata/sql-syntax-replication.idmap \
+ ../refman-5.0/metadata/sql-syntax-server-administration.idmap \
+ ../refman-5.0/metadata/stored-programs-views.idmap
+replication-configuration.validpure: $(replication_configuration_SOURCES)
+replication-configuration.titles: $(replication_configuration_SOURCES)
+replication-configuration.useless: $(replication_configuration_SOURCES)
+replication-configuration.valid: $(replication_configuration_SOURCES)
$(replication_configuration_IDMAPS)
+replication-configuration.validwarn: $(replication_configuration_SOURCES)
$(replication_configuration_IDMAPS)
+replication-configuration-prepped.xml: $(replication_configuration_SOURCES)
$(replication_configuration_IDMAPS)
+replication-configuration-manprepped.xml: $(replication_configuration_SOURCES)
$(replication_configuration_IDMAPS)
+replication-configuration-remprepped.xml: $(replication_configuration_SOURCES)
$(replication_configuration_IDMAPS)
replication_implementation_INCLUDES = \
../common/fixedchars.ent \
@@ -2868,8 +2883,8 @@
replication_implementation_IDMAPS = \
../refman-5.0/metadata/dba-core.idmap \
../refman-5.0/metadata/optimization.idmap \
- ../refman-5.0/metadata/replication-configuration-core.idmap \
../refman-5.0/metadata/replication-notes.idmap \
+ ../refman-5.0/metadata/replication-options-core.idmap \
../refman-5.0/metadata/sql-syntax-replication.idmap
replication-implementation.validpure: $(replication_implementation_SOURCES)
replication-implementation.titles: $(replication_implementation_SOURCES)
@@ -2931,9 +2946,10 @@
replication_solutions_IDMAPS = \
../refman-5.0/metadata/dba-core.idmap \
../refman-5.0/metadata/programs-client-core.idmap \
- ../refman-5.0/metadata/replication-configuration-core.idmap \
+ ../refman-5.0/metadata/replication-configuration.idmap \
../refman-5.0/metadata/replication-solutions.idmap \
- ../refman-5.0/metadata/sql-syntax-replication.idmap
+ ../refman-5.0/metadata/sql-syntax-replication.idmap \
+ ../refman-5.1/metadata/replication-configuration-core.idmap
replication-solutions.validpure: $(replication_solutions_SOURCES)
replication-solutions.titles: $(replication_solutions_SOURCES)
replication-solutions.useless: $(replication_solutions_SOURCES)
@@ -2946,8 +2962,6 @@
replication_INCLUDES = \
../common/fixedchars.ent \
../common/phrases.ent \
- ../dynamic-docs/command-optvars/mysqld.xml \
- ../dynamic-docs/metadata-titles.en.xml \
../refman-common/images/published/multi-db.png \
../refman-common/images/published/redundancy-after.png \
../refman-common/images/published/redundancy-before.png \
@@ -2955,8 +2969,8 @@
../refman-common/images/published/submaster-performance.png \
../refman-common/urls.ent \
all-entities.ent \
- dynxml-local-replication-configuration.xml \
- replication-configuration-core.xml \
+ dynxml-local-replication-options.xml \
+ replication-configuration.xml \
replication-implementation.xml \
replication-notes.xml \
replication-solutions.xml \
@@ -2975,9 +2989,10 @@
../refman-5.0/metadata/mysql-cluster.idmap \
../refman-5.0/metadata/optimization.idmap \
../refman-5.0/metadata/programs-client-core.idmap \
- ../refman-5.0/metadata/replication-configuration-core.idmap \
+ ../refman-5.0/metadata/replication-configuration.idmap \
../refman-5.0/metadata/replication-implementation.idmap \
../refman-5.0/metadata/replication-notes.idmap \
+ ../refman-5.0/metadata/replication-options-core.idmap \
../refman-5.0/metadata/replication-solutions.idmap \
../refman-5.0/metadata/se-innodb-core.idmap \
../refman-5.0/metadata/se-memory.idmap \
@@ -2985,6 +3000,7 @@
../refman-5.0/metadata/sql-syntax-replication.idmap \
../refman-5.0/metadata/sql-syntax-server-administration.idmap \
../refman-5.0/metadata/sql-syntax-transactions.idmap \
+ ../refman-5.0/metadata/stored-programs-views.idmap \
../refman-5.1/metadata/replication-configuration-core.idmap \
../refman-common/metadata/bug-reports.idmap
replication.validpure: $(replication_SOURCES)
@@ -3065,7 +3081,7 @@
../refman-common/images/published/blackhole-1.png
se_blackhole_SOURCES = se-blackhole.xml $(se_blackhole_INCLUDES)
se_blackhole_IDMAPS = \
- ../refman-5.0/metadata/replication-configuration-core.idmap
+ ../refman-5.0/metadata/replication-options-core.idmap
se-blackhole.validpure: $(se_blackhole_SOURCES)
se-blackhole.titles: $(se_blackhole_SOURCES)
se-blackhole.useless: $(se_blackhole_SOURCES)
@@ -3367,7 +3383,8 @@
sql_syntax_replication_IDMAPS = \
../refman-5.0/metadata/dba-core.idmap \
../refman-5.0/metadata/functions-core.idmap \
- ../refman-5.0/metadata/replication-configuration-core.idmap \
+ ../refman-5.0/metadata/replication-configuration.idmap \
+ ../refman-5.0/metadata/replication-options-core.idmap \
../refman-5.0/metadata/sql-syntax-replication.idmap \
../refman-5.0/metadata/sql-syntax-server-administration.idmap
sql-syntax-replication.validpure: $(sql_syntax_replication_SOURCES)
@@ -3543,7 +3560,7 @@
../refman-5.0/metadata/programs-admin-util-core.idmap \
../refman-5.0/metadata/programs-client-core.idmap \
../refman-5.0/metadata/programs-using.idmap \
- ../refman-5.0/metadata/replication-configuration-core.idmap \
+ ../refman-5.0/metadata/replication-options-core.idmap \
../refman-5.0/metadata/se-bdb-core.idmap \
../refman-5.0/metadata/se-innodb-core.idmap \
../refman-5.0/metadata/spatial-extensions.idmap \
Modified: trunk/refman-5.0/dba-core.xml
===================================================================
--- trunk/refman-5.0/dba-core.xml 2008-11-06 17:30:40 UTC (rev 12309)
+++ trunk/refman-5.0/dba-core.xml 2008-11-06 18:05:11 UTC (rev 12310)
Changed blocks: 18, Lines Added: 35, Lines Deleted: 868; 36965 bytes
@@ -215,7 +215,7 @@
<listitem>
<para>
Binary log control options: See
- <xref linkend="binary-log"/>.
+ <xref linkend="replication-options-binary-log"/>.
</para>
</listitem>
@@ -934,29 +934,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_disconnect-slave-event-count">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>disconnect-slave-event-count option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>disconnect-slave-event-count option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--disconnect-slave-event-count</option>
- </para>
-
- <para condition="dynamic:optvar:item"
role="5.0:mysqld:disconnect-slave-event-count"/>
-
- <para>
- This option is used internally by the MySQL test suite for
- replication testing and debugging.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_enable-named-pipe">
<indexterm>
<primary>mysqld</primary>
@@ -1343,134 +1320,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_log-bin">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>log-bin option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>log-bin option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
-
<option>--log-bin[=<replaceable>base_name</replaceable>]</option>
- </para>
-
- <para condition="dynamic:optvar:item" role="5.0:mysqld:log-bin"/>
-
- <para>
- Enable binary logging. The server logs all statements that
- change data to the binary log, which is used for backup and
- replication. See <xref linkend="binary-log"/>.
- </para>
-
- <para>
- The option value, if given, is the basename for the log
- sequence. The server creates binary log files in sequence by
- adding a numeric suffix to the basename. It is recommended
- that you specify a basename (see
- <xref linkend="open-bugs"/>, for the reason). Otherwise,
- MySQL uses
-
<filename><replaceable>host_name</replaceable>-bin</filename>
- as the basename.
- </para>
- </listitem>
-
- <listitem>
- <para id="option_mysqld_log-bin-index">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>log-bin-index option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>log-bin-index option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
-
<option>--log-bin-index[=<replaceable>file_name</replaceable>]</option>
- </para>
-
- <para condition="dynamic:optvar:item" role="5.0:mysqld:log-bin-index"/>
-
- <para>
- The index file for binary log filenames. See
- <xref linkend="binary-log"/>. If you omit the filename, and
- if you didn't specify one with <option>--log-bin</option>,
- MySQL uses
-
<filename><replaceable>host_name</replaceable>-bin.index</filename>
- as the filename.
- </para>
- </listitem>
-
- <listitem>
- <para id="option_mysqld_log-bin-trust-function-creators">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>log-bin-trust-function-creators option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>log-bin-trust-function-creators option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--log-bin-trust-function-creators[={0|1}]</option>
- </para>
-
- <para condition="dynamic:optvar:item"
role="5.0:mysqld:log-bin-trust-function-creators"/>
-
- <para>
- With no argument or an argument of 1, this option sets the
- <literal>log_bin_trust_function_creators</literal> system
- variable to 1. With an argument of 0, this option sets the
- system variable to 0.
- <literal>log_bin_trust_function_creators</literal> affects
- how MySQL enforces restrictions on stored function and
- trigger creation. See
- <xref linkend="stored-programs-logging"/>.
- </para>
-
- <para>
- This option was added in MySQL 5.0.16.
- </para>
- </listitem>
-
- <listitem>
- <para id="option_mysqld_log-bin-trust-routine-creators">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>log-bin-trust-routine-creators option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>log-bin-trust-routine-creators option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--log-bin-trust-routine-creators[={0|1}]</option>
- </para>
-
- <para condition="dynamic:optvar:item"
role="5.0:mysqld:log-bin-trust-routine-creators"/>
-
- <para>
- This is the old name for
- <option>--log-bin-trust-function-creators</option>. Before
- MySQL 5.0.16, it also applies to stored procedures, not just
- stored functions and sets the
- <literal>log_bin_trust_routine_creators</literal> system
- variable. As of 5.0.16, this option is deprecated. It is
- recognized for backward compatibility but its use results in
- a warning.
- </para>
-
- <para>
- This option was added in MySQL 5.0.6.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_log-error">
<indexterm>
<primary>mysqld</primary>
@@ -1785,29 +1634,6 @@
</listitem>
<listitem>
- <para id="option_mysqld_max-binlog-dump-events">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>max-binlog-dump-events option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>max-binlog-dump-events option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--max-binlog-dump-events</option>
- </para>
-
- <para condition="dynamic:optvar:item"
role="5.0:mysqld:max-binlog-dump-events"/>
-
- <para>
- This option is used internally by the MySQL test suite for
- replication testing and debugging.
- </para>
- </listitem>
-
- <listitem>
<para id="option_mysqld_memlock">
<indexterm>
<primary>mysqld</primary>
@@ -2593,30 +2419,7 @@
</listitem>
<listitem>
- <para id="option_mysqld_sporadic-binlog-dump-fail">
- <indexterm>
- <primary>mysqld</primary>
- <secondary>sporadic-binlog-dump-fail option</secondary>
- </indexterm>
-
- <indexterm>
- <primary>sporadic-binlog-dump-fail option</primary>
- <secondary>mysqld</secondary>
- </indexterm>
-
- <option>--sporadic-binlog-dump-fail</option>
- </para>
-
- <para condition="dynamic:optvar:item"
role="5.0:mysqld:sporadic-binlog-dump-fail"/>
-
<para>
- This option is used internally by the MySQL test suite for
- replication testing and debugging.
- </para>
- </listitem>
-
- <listitem>
- <para>
<indexterm>
<primary>mysqld</primary>
<secondary>SSL options</secondary>
@@ -3356,278 +3159,6 @@
</remark>
<listitem>
- <para id="option_mysqld_auto-increment-increment">
- <literal>auto_increment_increment</literal>
- </para>
-
- <para condition="dynamic:optvar:item"
role="5.0:mysqld:auto-increment-increment"/>
-
- <para>
- <literal>auto_increment_increment</literal> and
- <literal>auto_increment_offset</literal> are intended for
- use with master-to-master replication, and can be used to
- control the operation of <literal>AUTO_INCREMENT</literal>
- columns. Both variables have global and session values, and
- each can assume an integer value between 1 and 65,535
- inclusive. Setting the value of either of these two
- variables to 0 causes its value to be set to 1 instead.
- Attempting to set the value of either of these two variables
- to an integer greater than 65,535 or less than 0 causes its
- value to be set to 65,535 instead. Attempting to set the
- value of <literal>auto_increment_increment</literal> or
- <literal>auto_increment_offset</literal> to a non-integer
- value gives rise to an error, and the actual value of the
- variable remains unchanged.
- </para>
-
- <para>
- These two variables affect <literal>AUTO_INCREMENT</literal>
- column behavior as follows:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>auto_increment_increment</literal> controls the
- interval between successive column values. For example:
- </para>
-
-<programlisting>
-mysql> <userinput>SHOW VARIABLES LIKE 'auto_inc%';</userinput>
-+--------------------------+-------+
-| Variable_name | Value |
-+--------------------------+-------+
-| auto_increment_increment | 1 |
-| auto_increment_offset | 1 |
-+--------------------------+-------+
-2 rows in set (0.00 sec)
-
-mysql> <userinput>CREATE TABLE autoinc1</userinput>
- -> <userinput>(col INT NOT NULL AUTO_INCREMENT PRIMARY
KEY);</userinput>
- Query OK, 0 rows affected (0.04 sec)
-
-mysql> <userinput>SET @@auto_increment_increment=10;</userinput>
-Query OK, 0 rows affected (0.00 sec)
-
-mysql> <userinput>SHOW VARIABLES LIKE 'auto_inc%';</userinput>
-+--------------------------+-------+
-| Variable_name | Value |
-+--------------------------+-------+
-| auto_increment_increment | 10 |
-| auto_increment_offset | 1 |
-+--------------------------+-------+
-2 rows in set (0.01 sec)
-
-mysql> <userinput>INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL),
(NULL);</userinput>
-Query OK, 4 rows affected (0.00 sec)
-Records: 4 Duplicates: 0 Warnings: 0
-
-mysql> <userinput>SELECT col FROM autoinc1;</userinput>
-+-----+
-| col |
-+-----+
-| 1 |
-| 11 |
-| 21 |
-| 31 |
-+-----+
-4 rows in set (0.00 sec)
-</programlisting>
-
- <para>
- (Note how <literal role="stmt">SHOW VARIABLES</literal>
- is used here to obtain the current values for 