Author: jstephens
Date: 2007-10-25 12:31:11 +0200 (Thu, 25 Oct 2007)
New Revision: 8316
Log:
Emphasised that RBR does not support different charsets on master and
slave.
Merged diffs into the 5.2 versions.
Modified:
trunk/refman-5.1/replication-configuration.xml
trunk/refman-5.1/replication-notes.xml
trunk/refman-5.2/replication-configuration.xml
trunk/refman-5.2/replication-notes.xml
Modified: trunk/refman-5.1/replication-configuration.xml
===================================================================
--- trunk/refman-5.1/replication-configuration.xml 2007-10-25 10:09:15 UTC (rev 8315)
+++ trunk/refman-5.1/replication-configuration.xml 2007-10-25 10:31:11 UTC (rev 8316)
Changed blocks: 6, Lines Added: 18, Lines Deleted: 15; 3442 bytes
@@ -1276,7 +1276,7 @@
<listitem>
<para>
As of MySQL 5.1.8, the binary logging format can be altered on
- the fly according the event being logged. With mixed-based
+ the fly according to the event being logged. With mixed-based
logging (and the associated <emphasis>mixed-based
replication</emphasis> (MBR)), statement-based logging is used
by default, but automatically switches to row-based logging in
@@ -1288,7 +1288,7 @@
</itemizedlist>
<para>
- Starting with MySQL 5.1.12, mixed-based replication (i.e.
+ Starting with MySQL 5.1.12, mixed-based replication (that is,
mixed-based logging) is the default format for all replication
environment unless you specify otherwise.
</para>
@@ -1310,9 +1310,9 @@
<para>
MySQL Cluster Replication makes use of row-based replication. The
- NDB storage engine is incompatible with statement-based
- replication and NDB sets row-based logging format automatically.
- For more information, see
+ <literal>NDB</literal> storage engine is incompatible with
+ statement-based replication, and <literal>NDB</literal> sets
+ row-based logging format automatically. For more information, see
<xref linkend="mysql-cluster-replication"/>.
</para>
@@ -1341,8 +1341,7 @@
<listitem>
<para>
- <link
- linkend="option_mysqld_sql_mode"><literal>SQL_MODE</literal></link>
+ <link linkend="option_mysqld_sql_mode"><literal>SQL_MODE</literal></link>
</para>
</listitem>
@@ -1360,29 +1359,25 @@
<listitem>
<para>
- <link
- linkend="option_mysqld_character_set_client"><literal>CHARACTER_SET_CLIENT</literal></link>
+ <link linkend="option_mysqld_character_set_client"><literal>CHARACTER_SET_CLIENT</literal></link>
</para>
</listitem>
<listitem>
<para>
- <link
- linkend="option_mysqld_collation_connection"><literal>COLLATION_CONNECTION</literal></link>
+ <link linkend="option_mysqld_collation_connection"><literal>COLLATION_CONNECTION</literal></link>
</para>
</listitem>
<listitem>
<para>
- <link
- linkend="option_mysqld_collation_database"><literal>COLLATION_DATABASE</literal></link>
+ <link linkend="option_mysqld_collation_database"><literal>COLLATION_DATABASE</literal></link>
</para>
</listitem>
<listitem>
<para>
- <link
- linkend="option_mysqld_collation_server"><literal>COLLATION_SERVER</literal></link>
+ <link linkend="option_mysqld_collation_server"><literal>COLLATION_SERVER</literal></link>
</para>
</listitem>
@@ -1394,6 +1389,14 @@
</itemizedlist>
+ <important>
+ <para>
+ Even though session variables relating to character sets and
+ collations are written to the binary log, replication between
+ different character sets is not supported.
+ </para>
+ </important>
+
<section id="replication-sbr-rbr">
<title>Comparison of Statement-Based Versus Row-Based Replication</title>
Modified: trunk/refman-5.1/replication-notes.xml
===================================================================
--- trunk/refman-5.1/replication-notes.xml 2007-10-25 10:09:15 UTC (rev 8315)
+++ trunk/refman-5.1/replication-notes.xml 2007-10-25 10:31:11 UTC (rev 8316)
Changed blocks: 4, Lines Added: 7, Lines Deleted: 6; 1978 bytes
@@ -207,7 +207,8 @@
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.
+ values. This is true wther you are using statement-based or
+ row-based replication.
</para>
</listitem>
@@ -891,7 +892,7 @@
The <literal>FOUND_ROWS()</literal> and
<literal>ROW_COUNT()</literal> functions are not replicated
reliably using statement-based replication. Beginning with MySQL
- 5.1.23, this function is automatically replicated using
+ 5.1.23, these functions are automatically replicated using
row-based replication. (Bug #30244)
</para>
@@ -1241,7 +1242,7 @@
<para>
When replicating tables from the master to tables on a slave
- with extra columns, the following must be met:
+ with extra columns, the following conditions must be met:
</para>
<itemizedlist>
@@ -1261,14 +1262,14 @@
<listitem>
<para>
Slave tables may contain additional columns compared to the
- Master, but the columns must appear sequentially after the
- corresponding columns on the Master.
+ master, but the columns must appear sequentially after the
+ corresponding columns on the master.
</para>
</listitem>
<listitem>
<para>
- All the matching columns on the Master and the Slave must
+ All the matching columns on the master and the slave must
have the same type.
</para>
</listitem>
Modified: trunk/refman-5.2/replication-configuration.xml
===================================================================
--- trunk/refman-5.2/replication-configuration.xml 2007-10-25 10:09:15 UTC (rev 8315)
+++ trunk/refman-5.2/replication-configuration.xml 2007-10-25 10:31:11 UTC (rev 8316)
Changed blocks: 14, Lines Added: 412, Lines Deleted: 43; 20824 bytes
@@ -384,11 +384,13 @@
<para>
If you do not specify a <literal>server-id</literal> value, it
- is set to 1. 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.
+ 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>
<para>
@@ -1238,8 +1240,12 @@
<para>
Replication works because events written to the binary log are
read from the master and then processed on the slave. The events
- are recorded in different formats according the event being
- recorded. The different formats are as follows:
+ are recorded within the binary log in different formats according
+ the type of event being recorded. The different replication
+ formats used correspond to the binary logging format used when the
+ events were recorded in the master's binary log. The correlation
+ between binary logging formats and the terms used during
+ replication are:
</para>
<itemizedlist>
@@ -1248,23 +1254,29 @@
<para>
Replication capabilities in MySQL originally were based on
propagation of SQL statements from master to slave. This is
- called <emphasis>statement-based replication</emphasis> (SBR).
+ called <emphasis>statement-based replication</emphasis> (SBR)
+ and this correlates to the standard statement-based binary
+ logging format.
</para>
</listitem>
<listitem>
<para>
- In <emphasis>row-based replication</emphasis> (RBR), the
- master writes events to the binary log that indicate how
- individual table rows are affected.
+ Row-based binary logging logs the physical changes to
+ individual table rows. In replication terms this is
+ <emphasis>row-based replication</emphasis> (RBR), the master
+ writes events to the binary log that indicate how individual
+ table rows are affected.
</para>
</listitem>
<listitem>
<para>
- With <emphasis>mixed-based replication</emphasis> (MBR),
- statement-based replication is used by default, but
- automatically switches to row-based replication in particular
+ The binary logging format can be altered on the fly according
+ to the event being logged. With mixed-based logging (and the
+ associated <emphasis>mixed-based replication</emphasis>
+ (MBR)), statement-based logging is used by default, but
+ automatically switches to row-based logging in particular
cases as described below. See
<xref linkend="binary-log-mixed"/>.
</para>
@@ -1273,16 +1285,17 @@
</itemizedlist>
<para>
- Mixed-based replication (MBR) is the default format for all
- replication environment unless you specify otherwise.
+ Mixed-based replication (that is, mixed-based logging) is the
+ default format for all replication environment unless you specify
+ otherwise.
</para>
<para>
- Starting with MySQL 5.1.20, the binary logging format used is
- partially determined by the storage engine being used and the
- statement being executed. For more information on mixed-based
- logging and the rules governing the support of different logging
- formatsion, see <xref linkend="binary-log-mixed"/>.
+ The binary logging format used is partially determined by the
+ storage engine being used and the statement being executed. For
+ more information on mixed-based logging and the rules governing
+ the support of different logging formatsion, see
+ <xref linkend="binary-log-mixed"/>.
</para>
<para>
@@ -1294,9 +1307,9 @@
<para>
MySQL Cluster Replication makes use of row-based replication. The
- NDB storage engine is incompatible with statement-based
- replication and NDB sets row-based logging format automatically.
- For more information, see
+ <literal>NDB</literal> storage engine is incompatible with
+ statement-based replication, and <literal>NDB</literal> sets
+ row-based logging format automatically. For more information, see
<xref linkend="mysql-cluster-replication"/>.
</para>
@@ -1325,8 +1338,7 @@
<listitem>
<para>
- <link
- linkend="option_mysqld_sql_mode"><literal>SQL_MODE</literal></link>
+ <link linkend="option_mysqld_sql_mode"><literal>SQL_MODE</literal></link>
</para>
</listitem>
@@ -1344,29 +1356,25 @@
<listitem>
<para>
- <link
- linkend="option_mysqld_character_set_client"><literal>CHARACTER_SET_CLIENT</literal></link>
+ <link linkend="option_mysqld_character_set_client"><literal>CHARACTER_SET_CLIENT</literal></link>
</para>
</listitem>
<listitem>
<para>
- <link
- linkend="option_mysqld_collation_connection"><literal>COLLATION_CONNECTION</literal></link>
+ <link linkend="option_mysqld_collation_connection"><literal>COLLATION_CONNECTION</literal></link>
</para>
</listitem>
<listitem>
<para>
- <link
- linkend="option_mysqld_collation_database"><literal>COLLATION_DATABASE</literal></link>
+ <link linkend="option_mysqld_collation_database"><literal>COLLATION_DATABASE</literal></link>
</para>
</listitem>
<listitem>
<para>
- <link
- linkend="option_mysqld_collation_server"><literal>COLLATION_SERVER</literal></link>
+ <link linkend="option_mysqld_collation_server"><literal>COLLATION_SERVER</literal></link>
</para>
</listitem>
@@ -1378,6 +1386,14 @@
</itemizedlist>
+ <important>
+ <para>
+ Even though session variables relating to character sets and
+ collations are written to the binary log, replication between
+ different character sets is not supported.
+ </para>
+ </important>
+
<section id="replication-sbr-rbr">
<title>Comparison of Statement-Based Versus Row-Based Replication</title>
@@ -1821,6 +1837,90 @@
</para>
<para>
+ Certain options are handled in a special way in order to ensure
+ that the active replication configuration is not inadvertently
+ altered or affected. The options affected are shown in this list:
+ </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>
+
+ <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>
+ <emphasis>The use of these options is deprecated.</emphasis> The
+ settings they alter are ignored when <command>mysqld</command> is
+ started and a warning will be provided in the
+ <command>mysqld</command> log. To configure replication, you must
+ use the <literal>CHANGE MASTER TO ...</literal> statement.
+ </para>
+
+ <para>
The <filename>master.info</filename> file format in MySQL
¤t-series; includes values corresponding to the SSL options.
In addition, the file format includes as its first line the number
@@ -1835,14 +1935,78 @@
<para>
If no <filename>master.info</filename> file exists when the slave
- server starts, then replication is not started. You must set up
- replication by using the <literal>CHANGE MASTER</literal>
- statement. See <xref linkend="change-master-to"/>. This is also
- true if you have just used <literal>RESET SLAVE</literal> and then
+ 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>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>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>CHANGE MASTER TO</literal>
+ instead.
+ </para>
+
+ <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>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 the options and variables used for
controlling replication. Many of these options can be reset while
the server is running by using the <literal>CHANGE MASTER
@@ -1924,6 +2088,58 @@
</listitem>
<listitem>
+ <para id="option_mysqld_master-connect-retry">
+ <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 id="option_mysqld_master-host">
+ <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 id="option_mysqld_master-info-file">
<indexterm>
<primary>mysqld</primary>
@@ -1946,6 +2162,52 @@
</listitem>
<listitem>
+ <para id="option_mysqld_master-password">
+ <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 id="option_mysqld_master-port">
+ <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 id="option_mysqld_master-retry-count">
<indexterm>
<primary>mysqld</primary>
@@ -1971,6 +2233,114 @@
</listitem>
<listitem>
+ <para id="option_mysqld_master-ssl">
+ <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>
+ </listitem>
+
+ <listitem>
+ <para id="option_mysqld_master-user">
+ <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.
+ 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>
<para id="option_mysqld_max-relay-log-size">
<indexterm>
<primary>mysqld</primary>
@@ -2745,11 +3115,10 @@
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>MASTER_CONNECT_RETRY</literal>
- option of the <literal>CHANGE MASTER</literal> statemet, and
- the number of reconnection attempts is limited by the
- <option>--master-retry-count</option> option. The default is
- 3600 seconds (one hour).
+ controlled by the <option>--master-connect-retry</option>
+ option and the number of reconnection attempts is limited by
+ the <option>--master-retry-count</option> option. The default
+ is 3600 seconds (one hour).
</para>
</listitem>
Modified: trunk/refman-5.2/replication-notes.xml
===================================================================
--- trunk/refman-5.2/replication-notes.xml 2007-10-25 10:09:15 UTC (rev 8315)
+++ trunk/refman-5.2/replication-notes.xml 2007-10-25 10:31:11 UTC (rev 8316)
Changed blocks: 7, Lines Added: 144, Lines Deleted: 24; 8363 bytes
@@ -207,7 +207,8 @@
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.
+ values. This is true wther you are using statement-based or
+ row-based replication.
</para>
</listitem>
@@ -887,6 +888,14 @@
INSERT INTO t2 SELECT UUID(), * FROM t1;
</programlisting>
+ <para>
+ The <literal>FOUND_ROWS()</literal> and
+ <literal>ROW_COUNT()</literal> functions are not replicated
+ reliably using statement-based replication. These functions are
+ automatically replicated using row-based replication. (Bug
+ #30244)
+ </para>
+
</section>
<section id="replication-features-mastercrash">
@@ -1216,6 +1225,15 @@
<title>Replication with More Columns on the Slave</title>
+ <note>
+ <para>
+ You can replicate from master to slave using a different
+ number of columns and (within limits) different table
+ definitions. See
+ <xref linkend="replication-features-diffcolumns"/>.
+ </para>
+ </note>
+
<para>
You may replicate from a master to a slave where the number of
columns in the table on the slave is larger than the number of
@@ -1223,8 +1241,8 @@
</para>
<para>
- When replicating Table T1 from the master to table T2 on the
- slave, the following must be met:
+ When replicating tables from the master to tables on a slave
+ with extra columns, the following conditions must be met:
</para>
<itemizedlist>
@@ -1237,37 +1255,152 @@
<listitem>
<para>
- T1 and T2 must have the same database/table name.
+ Master and slave must have the same database/table name.
</para>
</listitem>
<listitem>
<para>
- T2 may contain additional columns compared to T1, but they
- must appear sequentially after the corresponding columns in
- T1.
+ Slave tables may contain additional columns compared to the
+ master, but the columns must appear sequentially after the
+ corresponding columns on the master.
</para>
</listitem>
<listitem>
<para>
- All the matching columns in T1 and T2 must have the same
- type.
+ All the matching columns on the master and the slave must
+ have the same type.
</para>
</listitem>
<listitem>
<para>
- Every column in T2 but not in T1 must have a default value.
+ Every extra column on the slave must have a default value.
</para>
</listitem>
</itemizedlist>
+ </section>
+
+ <section id="replication-features-diffcolumns">
+
+ <title>Replication with Fewer Columns on the Slave</title>
+
<para>
- This functionality was added in MySQL 5.1.12.
+ Starting with MySQL 5.1.21 you can replicate from master to
+ slave with a different number of columns (fewer or greater) on
+ each host.
</para>
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ You must be using row-based replication.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Master and slave must have the same database/table name.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Slave tables may contain additional columns compared to the
+ Master, but the columns must appear sequentially after the
+ corresponding columns on the Master. For example, the
+ following definitions would replication correctly:
+ </para>
+
+<programlisting>mysql> CREATE TABLE master (first INT, second INT);
+mysql> CREATE TABLE slave (first INT, second INT, third INT);</programlisting>
+
+ <para>
+ But these table definitions would raise error <literal>1532
+ SQLSTATE: HY000 (ER_BINLOG_ROW_RBR_TO_SBR)</literal> because
+ the columnd definitions on the slave are in a different
+ order than the master:
+ </para>
+
+<programlisting>mysql> CREATE TABLE master (first INT, second INT);
+mysql> CREATE TABLE slave (third INT, second INT, first INT);</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Master tables can have more columns compared to the columns
+ on the slave, but the additional columns on the master must
+ appear after the matching columns in the server. The
+ following table definitions would be valid:
+ </para>
+
+<programlisting>mysql> CREATE TABLE master (first INT, second INT, third INT);
+mysql> CREATE TABLE slave (first INT, second INT);</programlisting>
+
+ <para>
+ But these table definitions would raise error 1532 because
+ the columnd definitions on the slave are in a different
+ order than the master:
+ </para>
+
+<programlisting>mysql> CREATE TABLE master (third INT, second INT, first INT);
+mysql> CREATE TABLE slave (first INT, second INT);</programlisting>
+ </listitem>
+
+ <listitem>
+ <para>
+ Columns on the master and slave should have the same type,
+ but within certain limits, this is not strictly enforced:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ For numeric column types, the types must match. There is
+ no conversion between <literal>INT</literal> and
+ <literal>FLOAT</literal> types.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>BINARY</literal>, <literal>VARBINARY</literal>,
+ <literal>CHAR</literal> and <literal>VARCHAR</literal>
+ columns are treated as equal. Therefore you can
+ replicate from <literal>CHAR</literal> to
+ <literal>BINARY</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ For <literal>CHAR</literal> and
+ <literal>BINARY</literal> column types, the column size
+ on the slave must be at least equal to the column size
+ on the master. If the column size on the master is
+ greater than on the slave, error 1532 will be raised.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ When replicating with between a master and the slave where
+ the number of columns on the slave is greater than on the
+ master, every additional column on the slave must have a
+ default value.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
</section>
<section id="replication-features-variables">
@@ -1343,15 +1476,6 @@
</para>
<para>
- Replication works correctly between MySQL 5.0 and 5.1 masters and
- slaves in any combination, even if the master and slave have
- different global character set variables, and even if the master
- and slave have different global time zone variables. (Note that
- this is not true in cases when the master, slave, or both are
- running MySQL 4.1 or earlier.)
- </para>
-
- <para>
We recommend using the most recent MySQL version available because
replication capabilities are continually being improved. We also
recommend using the same version for both the master and the
@@ -1389,10 +1513,6 @@
<title>Upgrading a Replication Setup</title>
- <remark role="todo">
- Update renamed-nodes so that replication-upgrade-5-1 points here
- </remark>
-
<para>
When you upgrade servers that participate in a replication setup,
the procedure for upgrading depends on the current server versions
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r8316 - in trunk: refman-5.1 refman-5.2 | jon | 25 Oct |
