Source and target tables for replication do not have to be identical. A table on the master can have more or fewer columns than the slave's copy of the table. In addition, corresponding table columns on the master and the slave can use different data types, subject to certain conditions.
Replication between tables which are partitioned differently from one another is not supported. See Section 126.96.36.199, “Replication and Partitioning”.
In all cases where the source and target tables do not have identical definitions, the database and table names must be the same on both the master and the slave. Additional conditions are discussed, with examples, in the following two sections.
You can replicate a table from the master to the slave such that the master and slave copies of the table have differing numbers of columns, subject to the following conditions:
Columns common to both versions of the table must be defined in the same order on the master and the slave.
(This is true even if both tables have the same number of columns.)
Columns common to both versions of the table must be defined before any additional columns.
This means that executing an
ALTER TABLEstatement on the slave where a new column is inserted into the table within the range of columns common to both tables causes replication to fail, as shown in the following example:
Suppose that a table
t, existing on the master and the slave, is defined by the following
CREATE TABLE t ( c1 INT, c2 INT, c3 INT );
Suppose that the
ALTER TABLEstatement shown here is executed on the slave:
ALTER TABLE t ADD COLUMN cnew1 INT AFTER c3;
ALTER TABLEis permitted on the slave because the columns
c3that are common to both versions of table
tremain grouped together in both versions of the table, before any columns that differ.
However, the following
ALTER TABLEstatement cannot be executed on the slave without causing replication to break:
ALTER TABLE t ADD COLUMN cnew2 INT AFTER c2;
Replication fails after execution on the slave of the
ALTER TABLEstatement just shown, because the new column
cnew2comes between columns common to both versions of
Each “extra” column in the version of the table having more columns must have a default value.Note
A column's default value is determined by a number of factors, including its type, whether it is defined with a
DEFAULToption, whether it is declared as
NULL, and the server SQL mode in effect at the time of its creation; for more information, see Section 11.6, “Data Type Default Values”).
In addition, when the slave's copy of the table has more columns than the master's copy, each column common to the tables must use the same data type in both tables.
Examples. The following examples illustrate some valid and invalid table definitions:
More columns on the master. The following table definitions are valid and replicate correctly:
master> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT); slave> CREATE TABLE t1 (c1 INT, c2 INT);
The following table definitions would raise an error because the definitions of the columns common to both versions of the table are in a different order on the slave than they are on the master:
master> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT); slave> CREATE TABLE t1 (c2 INT, c1 INT);
The following table definitions would also raise an error because the definition of the extra column on the master appears before the definitions of the columns common to both versions of the table:
master> CREATE TABLE t1 (c3 INT, c1 INT, c2 INT); slave> CREATE TABLE t1 (c1 INT, c2 INT);
More columns on the slave. The following table definitions are valid and replicate correctly:
master> CREATE TABLE t1 (c1 INT, c2 INT); slave> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
The following definitions raise an error because the columns common to both versions of the table are not defined in the same order on both the master and the slave:
master> CREATE TABLE t1 (c1 INT, c2 INT); slave> CREATE TABLE t1 (c2 INT, c1 INT, c3 INT);
The following table definitions also raise an error because the definition for the extra column in the slave's version of the table appears before the definitions for the columns which are common to both versions of the table:
master> CREATE TABLE t1 (c1 INT, c2 INT); slave> CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);
The following table definitions fail because the slave's
version of the table has additional columns compared to the
master's version, and the two versions of the table use
different data types for the common column
master> CREATE TABLE t1 (c1 INT, c2 BIGINT); slave> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
Corresponding columns on the master's and the slave's copies of the same table ideally should have the same data type. However, beginning with MySQL 5.1.21, this is not always strictly enforced, as long as certain conditions are met.
All other things being equal, it is always possible to
replicate from a column of a given data type to another column
of the same type and same size or width, where applicable, or
larger. For example, you can replicate from a
CHAR(10) column to another
CHAR(10), or from a
CHAR(10) column to a
CHAR(25) column without any problems. In
certain cases, it also possible to replicate from a column
having one data type (on the master) to a column having a
different data type (on the slave); when the data type of the
master's version of the column is promoted to a type that
is the same size or larger on the slave, this is known as
Attribute promotion can be used with both statement-based and row-based replication, and is not dependent on the storage engine used by either the master or the slave. However, the choice of logging format does have an effect on the type conversions that are permitted; the particulars are discussed later in this section.
Whether you use statement-based or row-based replication, the slave's copy of the table cannot contain more columns than the master's copy if you wish to employ attribute promotion.
When using statement-based replication, a simple rule of
thumb to follow is, “If the statement run on the
master would also execute successfully on the slave, it
should also replicate successfully”. In other words,
if the statement uses a value that is compatible with the
type of a given column on the slave, the statement can be
replicated. For example, you can insert any value that fits
TINYINT column into a
BIGINT column as well; it follows that,
even if you change the type of a
column in the slave's copy of a table to
BIGINT, any insert into that column on
the master that succeeds should also succeed on the slave,
since it is impossible to have a legal
TINYINT value that is large enough to
Prior to MySQL 5.5.30, when using statement-based replication,
AUTO_INCREMENT columns were required to be
the same on both the master and the slave; otherwise, updates
could be applied to the wrong table on the slave. (Bug
Row-based replication: attribute promotion and demotion.
Formerly, due to the fact that in row-based replication
changes rather than statements are replicated, and that
these changes are transmitted using formats that do not
always map directly to MySQL server column data types, you
could not replicate between different subtypes of the same
general type (for example, from
subtypes). However, beginning with MySQL 5.5.3, MySQL
Replication supports attribute promotion and demotion
between smaller data types and larger types. It is also
possible to specify whether or not to permit lossy
(truncated) or non-lossy conversions of demoted column
values, as explained later in this section.
Lossy and non-lossy conversions. In the event that the target type cannot represent the value being inserted, a decision must be made on how to handle the conversion. If we permit the conversion but truncate (or otherwise modify) the source value to achieve a “fit” in the target column, we make what is known as a lossy conversion. A conversion which does not require truncation or similar modifications to fit the source column value in the target column is a non-lossy conversion.
Type conversion modes (slave_type_conversions variable).
The setting of the
global server variable controls the type conversion mode
used on the slave. This variable takes a set of values from
the following table, which shows the effects of each mode on
the slave's type-conversion behavior:
In this mode, type conversions that would mean loss of information are permitted.
This does not imply that non-lossy conversions are
permitted, merely that only cases requiring either
lossy conversions or no conversion at all are
permitted; for example, enabling
only this mode permits an
This mode permits conversions that do not require truncation or other special handling of the source value; that is, it permits conversions where the target type has a wider range than the source type.
Setting this mode has no bearing on whether lossy
conversions are permitted; this is controlled with the
When this mode is set, all supported type conversions are permitted, whether or not they are lossy conversions.
This mode is the default.
Changing the type conversion mode requires restarting the
slave with the new
Supported conversions. Supported conversions between different but similar data types are shown in the following list:
This includes conversions between the signed and unsigned versions of these types.
Lossy conversions are made by truncating the source value to the maximum (or minimum) permitted by the target column. For insuring non-lossy conversions when going from unsigned to signed types, the target column must be large enough to accommodate the range of values in the source column. For example, you can demote
TINYINT UNSIGNEDnon-lossily to
SMALLINT, but not to
DOUBLEis a non-lossy conversion;
FLOATcan only be handled lossily. A conversion from
D') >= (
D) are non-lossy; for any case where
, or both, only a lossy conversion can be made.
For any of the decimal types, if a value to be stored cannot be fit in the target type, the value is rounded down according to the rounding rules defined for the server elsewhere in the documentation. See Section 12.19.4, “Rounding Behavior”, for information about how this is done for decimal types.
Conversion of a
TEXTcolumn the same size or larger is never lossy. Lossy conversion is handled by inserting only the first
Ncharacters of the string on the slave, where
Nis the width of the target column.Important
Replication between columns using different character sets is not supported.
Conversion of a
BLOBcolumn the same size or larger is never lossy. Lossy conversion is handled by inserting only the first
Nbytes of the string on the slave, where
Nis the width of the target column.
Between any 2
BITcolumns of any 2 sizes.
When inserting a value from a
BIT(column into a
, the most significant bits of the
BIT(columns are cleared (set to zero) and the
Mbits of the
BIT(value are set as the least significant bits of the
When inserting a value from a source
BIT(column into a target
, the maximum possible value for the
BIT(column is assigned; in other words, an “all-set” value is assigned to the target column.
Conversions between types not in the previous list are not permitted.
Replication type conversions in MySQL 5.5.3 and earlier.
Prior to MySQL 5.5.3, with row-based binary logging, you
could not replicate between different
subtypes, such as from
BIGINT, because changes to columns of
these types were represented differently from one another in
the binary log when using row-based logging. (However, you
could replicate from
TEXT using row-based replication because
TEXT columns were represented using the
same format in the binary log.)
Supported conversions for attribute promotion when using row-based replication prior to MySQL 5.5.3 are shown in the following table:
|From (Master)||To (Slave)|
In all cases, the size or width of the column on the slave
must be equal to or greater than that of the column on the
master. For example, you could replicate from a
CHAR(10) column on the master to a column
BINARY(25) on the slave, but you could
not replicate from a
CHAR(10) column on
the master to
BINARY(5) column on the
Any unique index (including primary keys) having a prefix must use a prefix of the same length on both master and slave; in such cases, differing prefix lengths are disallowed. It is possible to use a nonunique index whose prefix length differs between master and slave, but this can cause serious performance issues, particularly when the prefix used on the master is longer. This is due to the fact that 2 unique prefixes of a given length may no longer be unique at a shorter length; for example, the words catalogue and catamount have the 5-character prefixes catal and catam, respectively, but share the same 4-character prefix (cata). This can lead to queries that use such indexes executing less efficiently on the slave, when a shorter prefix is employed in the slave' definition of the same index than on the master.
NUMERIC columns, both the
mantissa (M) and the number of decimals
(D) must be the same size or larger on
the slave as compared with the master. For example,
replication from a
NUMERIC(5,4) to a
DECIMAL(6,4) worked, but not from a
NUMERIC(5,4) to a
Prior to MySQL 5.5.3, MySQL replication did not support attribute promotion of any of the following data types to or from any other data type when using row-based replication: