|
|
| Applications |
| Mailing Lists |
| Miscellaneous |
| Operating Systems |
| Programming |
svn commit - mysqldoc@docsrva: r8316 - in trunk: refman-5.1 refman-5.2
From: <jon(at)mysql.com>
Date: Thu Oct 25 2007 - 06:31:12 EDT
+
+ <para>
+
+ <para>
-
- <para>
Date: Thu Oct 25 2007 - 06:31:12 EDT
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 @@
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 mixed-based
replication (MBR)), statement-based logging is used
by default, but automatically switches to row-based logging in
@@ -1288,7 +1288,7 @@
- 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.
@@ -1310,9 +1310,9 @@
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
+ NDB storage engine is incompatible with
+ statement-based replication, and NDB sets
+ row-based logging format automatically. For more information, see
@@ -1341,8 +1341,7 @@
- SQL_MODE
+ <link linkend="option_mysqld_sql_mode"><literal>SQL_MODE</literal></link>
@@ -1360,29 +1359,25 @@
- CHARACTER_SET_CLIENT
+ <link linkend="option_mysqld_character_set_client"><literal>CHARACTER_SET_CLIENT</literal></link>
- COLLATION_CONNECTION
+ <link linkend="option_mysqld_collation_connection"><literal>COLLATION_CONNECTION</literal></link>
- COLLATION_DATABASE
+ <link linkend="option_mysqld_collation_database"><literal>COLLATION_DATABASE</literal></link>
- COLLATION_SERVER
+ <link linkend="option_mysqld_collation_server"><literal>COLLATION_SERVER</literal></link>
@@ -1394,6 +1389,14 @@
</itemizedlist>
++ ++ Even though session variables relating to character sets and + collations are written to the binary log, replication between + different character sets is not supported. + +Comparison of Statement-Based Versus Row-Based Replication
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 global
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.
@@ -891,7 +892,7 @@
The FOUND_ROWS() and
ROW_COUNT() 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)
@@ -1241,7 +1242,7 @@
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:
@@ -1261,14 +1262,14 @@
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.
- 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.
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 @@
If you do not specify a server-id value, it
- is set to 1. Note that in the case of
- server-id omission, a master refuses
- connections from all slaves, and a slave refuses to connect to a
- master. Thus, omitting server-id is good only
- for backup with a binary log.
+ is set to 1 if you have not defined
+ master-host ; otherwise it is set to 2. Note
+ that in the case of server-id omission, a
+ master refuses connections from all slaves, and a slave refuses
+ to connect to a master. Thus, omitting
+ server-id is good only for backup with a
+ binary log.
@@ -1238,8 +1240,12 @@
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:
@@ -1248,23 +1254,29 @@
Replication capabilities in MySQL originally were based on
propagation of SQL statements from master to slave. This is
- called statement-based replication (SBR).
+ called <emphasis>statement-based replication</emphasis> (SBR)
+ and this correlates to the standard statement-based binary
+ logging format.
- In row-based replication (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.
- With mixed-based replication (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
@@ -1273,16 +1285,17 @@
- 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.
- 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
@@ -1294,9 +1307,9 @@
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
+ NDB storage engine is incompatible with
+ statement-based replication, and NDB sets
+ row-based logging format automatically. For more information, see
@@ -1325,8 +1338,7 @@
- SQL_MODE
+ <link linkend="option_mysqld_sql_mode"><literal>SQL_MODE</literal></link>
@@ -1344,29 +1356,25 @@
- CHARACTER_SET_CLIENT
+ <link linkend="option_mysqld_character_set_client"><literal>CHARACTER_SET_CLIENT</literal></link>
- COLLATION_CONNECTION
+ <link linkend="option_mysqld_collation_connection"><literal>COLLATION_CONNECTION</literal></link>
- COLLATION_DATABASE
+ <link linkend="option_mysqld_collation_database"><literal>COLLATION_DATABASE</literal></link>
- COLLATION_SERVER
+ <link linkend="option_mysqld_collation_server"><literal>COLLATION_SERVER</literal></link>
@@ -1378,6 +1386,14 @@
</itemizedlist>
++ ++ Even though session variables relating to character sets and + collations are written to the binary log, replication between + different character sets is not supported. + +Comparison of Statement-Based Versus Row-Based Replication @@ -1821,6 +1837,90 @@+ 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: + + ++ + + ++ + ++
+ <option>--master-host</option>
++ + ++
+ <option>--master-user</option>
++ + ++
+ <option>--master-password</option>
++ + ++
+ <option>--master-port</option>
++ + ++
+ <option>--master-connect-retry</option>
++ + ++
+ <option>--master-ssl</option>
++ + ++
+ <option>--master-ssl-ca</option>
++ + ++
+ <option>--master-ssl-capath</option>
++ + ++
+ <option>--master-ssl-cert</option>
++ + ++
+ <option>--master-ssl-cipher</option>
++ + ++
+ <option>--master-ssl-key</option>
++ The use of these options is deprecated. The + settings they alter are ignored whenmysqld is + started and a warning will be provided in the +mysqld log. To configure replication, you must + use theCHANGE MASTER TO ... statement. +
+
+ <para>
The master.info 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 @@
If no master.info file exists when the slave
- server starts, then replication is not started. You must set up
- replication by using the CHANGE MASTER
- statement. See RESET SLAVE 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 RESET SLAVE and then
have shut down and restarted the slave.
+ If the master.info 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
+ master.info file, the different values have
+ no effect, because the server continues to use the
+ master.info file. To use different values,
+ you must either restart after removing the
+ master.info file or (preferably) use the
+ CHANGE MASTER TO statement to reset the values
+ while the slave is running.
+
+
+
+ Suppose that you specify this option in your
+ my.cnf file:
+
+
+
+[mysqld]
+master-host=some_host
+
+
+
+ The first time you start the server as a replication slave, it
+ reads and uses that option from the my.cnf
+ file. The server then records the value in the
+ master.info file. The next time you start the
+ server, it reads the master host value from the
+ master.info file only and ignores the value
+ in the option file. If you modify the my.cnf
+ file to specify a different master host of
+ some_other_host , the change still has
+ no effect. You should use CHANGE MASTER TO
+ instead.
+
+
+
+ Because the server gives an existing
+ master.info 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 CHANGE MASTER TO statement. See
+
+
+
+ This example shows a more extensive use of startup options to
+ configure a slave server:
+
+
+
+[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
+
+
+ <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 CHANGE MASTER
@@ -1924,6 +2088,58 @@
+
+ <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>
+
+
+
+ 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.
+
+
+
+
+
+ <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>
+
+
+
+ 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.
+
+
+
+
mysqld
@@ -1946,6 +2162,52 @@
+
+ <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>
+
+
+
+ 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.
+
+
+
+
+
+ <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>
+
+
+
+ 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).
+
+
+
+
mysqld
@@ -1971,6 +2233,114 @@
+
+ <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>
+
+
+
+ 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.
+
+
+
+
+
+ <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>
+
+
+
+ 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.
+
+
+
+
mysqld
@@ -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 MASTER_CONNECT_RETRY
- option of the CHANGE MASTER statemet, and
- the number of reconnection attempts is limited by the
- 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).
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 global
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.
@@ -887,6 +888,14 @@
INSERT INTO t2 SELECT UUID(), * FROM t1;
</programlisting>
++ The +FOUND_ROWS() and +ROW_COUNT() functions are not replicated + reliably using statement-based replication. These functions are + automatically replicated using row-based replication. (Bug + #30244) +
@@ -1216,6 +1225,15 @@
<title>Replication with More Columns on the Slave</title>
++ ++
+ 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"/>.
+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 @@ - 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:
@@ -1237,37 +1255,152 @@
- T1 and T2 must have the same database/table name.
+ Master and slave must have the same database/table name.
- 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.
- 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.
- Every column in T2 but not in T1 must have a default value.
+ Every extra column on the slave must have a default value.
+
+
+
+
+ Replication with Fewer Columns on the Slave
+
- 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>
+ You must be using row-based replication.
+ </para>
+
+
+
+ <para>
+ Master and slave must have the same database/table name.
+ </para>
+
+
+
+ <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>
+
+mysql> CREATE TABLE master (first INT, second INT);
+mysql> CREATE TABLE slave (first INT, second INT, third INT);
+
+ <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>
+
+mysql> CREATE TABLE master (first INT, second INT);
+mysql> CREATE TABLE slave (third INT, second INT, first INT);
+
+
+
+ <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>
+
+mysql> CREATE TABLE master (first INT, second INT, third INT);
+mysql> CREATE TABLE slave (first INT, second INT);
+
+ <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>
+
+mysql> CREATE TABLE master (third INT, second INT, first INT);
+mysql> CREATE TABLE slave (first INT, second INT);
+
+
+
+ <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>
+
+
+
+ <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>
+
+
+
+
@@ -1343,15 +1476,6 @@
- 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>
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
-- MySQL Code Commits Mailing List For list archives: http://lists.mysql.com/commits To unsubscribe: http://lists.mysql.com/commits?unsub=lists@pantek.comReceived on Thu Oct 25 06:31:23 2007
This archive was generated by hypermail 2.1.8 : Thu Jul 03 2008 - 10:39:56 EDT
|
Contact Us Legal Notices Order Services Online Pantek Home Privacy Policy IT news Site Map Pantek Library |