[+/-]
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.
In all cases where the source and target tables do not have identical definitions, the following must be true in order for replication to work:
You must be using row-based replication. (Using
MIXED
for the binary logging format
does not work.)
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.
Columns common to both versions of the table must be defined before any additional columns.
Each “extra” column in the version of the table having more columns must have a default value.
A column's default value is determined by a number of
factors, including its type, whether it is defined
with a DEFAULT
option, whether it
is declared as NULL
, and the server
SQL mode in effect at the time of its creation; for
more information, see
Section 10.1.4, “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 Error
1532
(ER_BINLOG_ROW_RBR_TO_SBR
)
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 Error 1532 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 Error 1532 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 Error 1532 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 c2
:
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, 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); this is sometimes known as
attribute promotion because 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.
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 allowed; 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.
Statement-based replication.
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
in a TINYINT
column into a
BIGINT
column as well; it follows that,
even if you change the type of a TINYINT
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
exceed a BIGINT
column.
Row-based replication.
For row-based replication, the case is not so simple, due to
the fact that changes rather than statements are replicated,
and these changes are transmitted from master to slave using
formats that do not always map directly to MySQL server
column datatypes. For example, with row-based binary
logging, you cannot replicate between different
INT
subtypes, such as from
TINYINT
to BIGINT
because changes to columns of these types are represented
differently from one another in the binary log. However, you
can replicate from BLOB
to
TEXT
because changes to
BLOB
and TEXT
columns
are represented using the same format in the binary log.
Supported conversions for attribute promotion when using row-based replication are shown in the following table.
From (Master) | To (Slave) |
---|---|
BINARY |
CHAR |
BLOB |
TEXT |
CHAR |
BINARY |
DECIMAL |
NUMERIC |
NUMERIC |
DECIMAL |
TEXT |
BLOB |
VARBINARY |
VARCHAR |
VARCHAR |
VARBINARY |
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 can replicate from a
CHAR(10)
column on the master to a column
that uses BINARY(10)
or
BINARY(25)
on the slave, but you cannot
replicate from a CHAR(10)
column on the
master to BINARY(5)
column on the slave.
For DECIMAL
and
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)
works, but not from a
NUMERIC(5,4)
to a
DECIMAL(5,3)
.
MySQL does not support attribute promotion of any of the following data types to or from any other data type when using row-based replication:
User Comments
Add your own comment.