17.4.1.1 Replication and AUTO_INCREMENT

Statement-based replication of
AUTO_INCREMENT,
LAST_INSERT_ID(), and
TIMESTAMP values is carried out
subject to the following exceptions:

A statement invoking a trigger or function that causes an
update to an AUTO_INCREMENT column is not
replicated correctly using statement-based replication.
These statements are marked as unsafe. (Bug #45677)

An INSERT into a table that
has a composite primary key that includes an
AUTO_INCREMENT column that is not the
first column of this composite key is not safe for
statement-based logging or replication. These statements are
marked as unsafe. (Bug #11754117, Bug #45670)

This issue does not affect tables using the
InnoDB storage engine, since an
InnoDB table with an
AUTO_INCREMENT
column requires at least one key where the auto-increment
column is the only or leftmost column.

Adding an AUTO_INCREMENT column to a
table with ALTER TABLE 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
AUTO_INCREMENT number. Assuming that you
want to add an AUTO_INCREMENT column to a
table t1 that has columns
col1 and col2, the
following statements produce a new table
t2 identical to t1 but
with an AUTO_INCREMENT column:

To guarantee the same ordering on both master and slave,
the ORDER BY clause must name
all columns of t1.

The instructions just given are subject to the limitations
of CREATE
TABLE ... LIKE: Foreign key definitions are
ignored, as are the DATA DIRECTORY and
INDEX DIRECTORY table options. If a table
definition includes any of those characteristics, create
t2 using a CREATE
TABLE statement that is identical to the one used
to create t1, but with the addition of
the AUTO_INCREMENT column.

Regardless of the method used to create and populate the
copy having the AUTO_INCREMENT column,
the final step is to drop the original table and then rename
the copy: