Menu:
replication-bugs:: How to Report Replication Bugs or Problems
File: manual.info.tmp, Node: replication-features, Next: replication-compatibility, Prev: replication-notes, Up: replication-notes
Menu:
replication-features-views:: Replication and Views
The following sections provide information about what is supported and what is not in MySQL replication, and about specific issues and situations that may occur when replicating certain statements.
Statement-based replication depends on compatibility at the SQL level between the source and replica. In other words, successful statement-based replication requires that any SQL features used be supported by both the source and the replica servers. If you use a feature on the source server that is available only in the current version of MySQL, you cannot replicate to a replica that uses an earlier version of MySQL. Such incompatibilities can also occur within a release series as well as between versions.
If you are planning to use statement-based replication between MySQL 5.7 and a previous MySQL release series, it is a good idea to consult the edition of the 'MySQL Reference Manual' corresponding to the earlier release series for information regarding the replication characteristics of that series.
With MySQL's statement-based replication, there may be issues with replicating stored routines or triggers. You can avoid these issues by using MySQL's row-based replication instead. For a detailed list of issues, see note stored-programs-logging::. For more information about row-based logging and row-based replication, see note binary-log-formats::, and *note replication-formats::.
For additional information specific to replication and 'InnoDB', see note innodb-and-mysql-replication::. For information relating to replication with NDB Cluster, see note mysql-cluster-replication::.
File: manual.info.tmp, Node: replication-features-auto-increment, Next: replication-features-blackhole, Prev: replication-features, Up: replication-features
16.4.1.1 Replication and AUTO_INCREMENT .......................................
Statement-based replication of 'AUTO_INCREMENT', 'LAST_INSERT_ID()', and *note 'TIMESTAMP': datetime. values is done correctly, subject to the following exceptions:
When using statement-based replication prior to MySQL 5.7.1, 'AUTO_INCREMENT' columns in tables on the replica must match the same columns on the source; that is, 'AUTO_INCREMENT' columns must be replicated to 'AUTO_INCREMENT' columns.
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 *note 'INSERT': 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 *note 'InnoDB': innodb-storage-engine. 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 *note 'ALTER TABLE': alter-table. might not produce the same ordering of the rows on the replica and the source. 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 source and replica, 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:
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;Important:
To guarantee the same ordering on both source and replica, the 'ORDER BY' clause must name all columns of 't1'.
The instructions just given are subject to the limitations of note 'CREATE TABLE ... LIKE': 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 note 'CREATE TABLE': 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:
DROP t1;
ALTER TABLE t2 RENAME t1;See also *note alter-table-problems::.
File: manual.info.tmp, Node: replication-features-blackhole, Next: replication-features-charset, Prev: replication-features-auto-increment, Up: replication-features
16.4.1.2 Replication and BLACKHOLE Tables .........................................
The *note 'BLACKHOLE': blackhole-storage-engine. storage engine accepts data but discards it and does not store it. When performing binary logging, all inserts to such tables are always logged, regardless of the logging format in use. Updates and deletes are handled differently depending on whether statement based or row based logging is in use. With the statement based logging format, all statements affecting 'BLACKHOLE' tables are logged, but their effects ignored. When using row-based logging, updates and deletes to such tables are simply skipped--they are not written to the binary log. A warning is logged whenever this occurs (Bug #13004581).
For this reason we recommend when you replicate to tables using the *note 'BLACKHOLE': blackhole-storage-engine. storage engine that you have the 'binlog_format' server variable set to 'STATEMENT', and not to either 'ROW' or 'MIXED'.
File: manual.info.tmp, Node: replication-features-charset, Next: replication-features-checksum-table, Prev: replication-features-blackhole, Up: replication-features
16.4.1.3 Replication and Character Sets .......................................
The following applies to replication between MySQL servers that use different character sets:
File: manual.info.tmp, Node: replication-features-checksum-table, Next: replication-features-create-if-not-exists, Prev: replication-features-charset, Up: replication-features
16.4.1.4 Replication and CHECKSUM TABLE .......................................
*note 'CHECKSUM TABLE': checksum-table. returns a checksum that is calculated row by row, using a method that depends on the table row storage format. The storage format is not guaranteed to remain the same between MySQL versions, so the checksum value might change following an upgrade.
File: manual.info.tmp, Node: replication-features-create-if-not-exists, Next: replication-features-create-select, Prev: replication-features-checksum-table, Up: replication-features
16.4.1.5 Replication of CREATE ... IF NOT EXISTS Statements ...........................................................
MySQL applies these rules when various 'CREATE ... IF NOT EXISTS' statements are replicated:
Every *note 'CREATE DATABASE IF NOT EXISTS': create-database. statement is replicated, whether or not the database already exists on the source.
Similarly, every note 'CREATE TABLE IF NOT EXISTS': create-table. statement without a note 'SELECT': select. is replicated, whether or not the table already exists on the source. This includes note 'CREATE TABLE IF NOT EXISTS ... LIKE': create-table-like. Replication of note 'CREATE TABLE IF NOT EXISTS ... SELECT': create-table-select. follows somewhat different rules; see *note replication-features-create-select::, for more information.
*note 'CREATE EVENT IF NOT EXISTS': create-event. is always replicated, whether or not the event named in the statement already exists on the source.
File: manual.info.tmp, Node: replication-features-create-select, Next: replication-features-create-alter-drop-server, Prev: replication-features-create-if-not-exists, Up: replication-features
16.4.1.6 Replication of CREATE TABLE ... SELECT Statements ..........................................................
This section discusses how MySQL replicates *note 'CREATE TABLE ... SELECT': create-table-select. statements.
MySQL 5.7 does not allow a note 'CREATE TABLE ... SELECT': create-table-select. statement to make any changes in tables other than the table that is created by the statement. Some older versions of MySQL permitted these statements to do so; this means that, when using replication between a MySQL 5.6 or later replica and a source running a previous version of MySQL, a note 'CREATE TABLE ... SELECT': create-table-select. statement causing changes in other tables on the source fails on the replica, causing replication to stop. To prevent this from happening, you should use row-based replication, rewrite the offending statement before running it on the source, or upgrade the source to MySQL 5.7. (If you choose to upgrade the source, keep in mind that such a *note 'CREATE TABLE ... SELECT': create-table-select. statement fails following the upgrade unless it is rewritten to remove any side effects on other tables.)
These behaviors are not dependent on MySQL version:
note 'CREATE TABLE ... SELECT': create-table-select. always performs an implicit commit (note implicit-commit::).
If destination table does not exist, logging occurs as follows. It does not matter whether 'IF NOT EXISTS' is present.
* 'STATEMENT' or 'MIXED' format: The statement is logged as
written.
* 'ROW' format: The statement is logged as a *note 'CREATE
TABLE': create-table. statement followed by a series of
insert-row events.If the statement fails, nothing is logged. This includes the case that the destination table exists and 'IF NOT EXISTS' is not given.
When the destination table exists and 'IF NOT EXISTS' is given, MySQL 5.7 ignores the statement completely; nothing is inserted or logged.
File: manual.info.tmp, Node: replication-features-create-alter-drop-server, Next: replication-features-current-user, Prev: replication-features-create-select, Up: replication-features
16.4.1.7 Replication of CREATE SERVER, ALTER SERVER, and DROP SERVER ....................................................................
The statements note 'CREATE SERVER': create-server, note 'ALTER SERVER': alter-server, and *note 'DROP SERVER': drop-server. are not written to the binary log, regardless of the binary logging format that is in use.
File: manual.info.tmp, Node: replication-features-current-user, Next: replication-features-drop-if-exists, Prev: replication-features-create-alter-drop-server, Up: replication-features
16.4.1.8 Replication of CURRENT_USER() ......................................
The following statements support use of the 'CURRENT_USER()' function to take the place of the name of, and possibly the host for, an affected user or a definer:
*note 'DROP USER': drop-user.
*note 'RENAME USER': rename-user.
*note 'GRANT': grant.
*note 'REVOKE': revoke.
*note 'CREATE FUNCTION': create-function.
*note 'CREATE PROCEDURE': create-procedure.
*note 'CREATE TRIGGER': create-trigger.
*note 'CREATE EVENT': create-event.
*note 'CREATE VIEW': create-view.
*note 'ALTER EVENT': alter-event.
*note 'ALTER VIEW': alter-view.
*note 'SET PASSWORD': set-password.
When binary logging is enabled and 'CURRENT_USER()' or 'CURRENT_USER' is used as the definer in any of these statements, MySQL Server ensures that the statement is applied to the same user on both the source and the replica when the statement is replicated. In some cases, such as statements that change passwords, the function reference is expanded before it is written to the binary log, so that the statement includes the user name. For all other cases, the name of the current user on the source is replicated to the replica as metadata, and the replica applies the statement to the current user named in the metadata, rather than to the current user on the replica.
File: manual.info.tmp, Node: replication-features-drop-if-exists, Next: replication-features-differing-tables, Prev: replication-features-current-user, Up: replication-features
16.4.1.9 Replication of DROP ... IF EXISTS Statements .....................................................
The note 'DROP DATABASE IF EXISTS': drop-database, note 'DROP TABLE IF EXISTS': drop-table, and *note 'DROP VIEW IF EXISTS': drop-view. statements are always replicated, even if the database, table, or view to be dropped does not exist on the source. This is to ensure that the object to be dropped no longer exists on either the source or the replica, once the replica has caught up with the source.
'DROP ... IF EXISTS' statements for stored programs (stored procedures and functions, triggers, and events) are also replicated, even if the stored program to be dropped does not exist on the source.
File: manual.info.tmp, Node: replication-features-differing-tables, Next: replication-features-directory, Prev: replication-features-drop-if-exists, Up: replication-features
16.4.1.10 Replication with Differing Table Definitions on Source and Replica ............................................................................
Menu:
replication-features-different-data-types:: Replication of Columns Having Different Data Types
Source and target tables for replication do not have to be identical. A table on the source can have more or fewer columns than the replica's copy of the table. In addition, corresponding table columns on the source and the replica can use different data types, subject to certain conditions.
Note:
Replication between tables which are partitioned differently from one another is not supported. See *note replication-features-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 source and the replica. Additional conditions are discussed, with examples, in the following two sections.
File: manual.info.tmp, Node: replication-features-more-columns, Next: replication-features-different-data-types, Prev: replication-features-differing-tables, Up: replication-features-differing-tables
16.4.1.11 Replication with More Columns on Source or Replica ............................................................
You can replicate a table from the source to the replica such that the source and replica 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 source and the replica.
(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 *note 'ALTER TABLE': alter-table. statement on the replica 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 source and the replica, is defined by the following *note 'CREATE TABLE': create-table. statement:
CREATE TABLE t (
c1 INT,
c2 INT,
c3 INT
);Suppose that the *note 'ALTER TABLE': alter-table. statement shown here is executed on the replica:
ALTER TABLE t ADD COLUMN cnew1 INT AFTER c3;The previous *note 'ALTER TABLE': alter-table. is permitted on the replica because the columns 'c1', 'c2', and 'c3' that are common to both versions of table 't' remain grouped together in both versions of the table, before any columns that differ.
However, the following *note 'ALTER TABLE': alter-table. statement cannot be executed on the replica without causing replication to break:
ALTER TABLE t ADD COLUMN cnew2 INT AFTER c2;Replication fails after execution on the replica of the *note 'ALTER TABLE': alter-table. statement just shown, because the new column 'cnew2' comes between columns common to both versions of 't'.
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 *note data-type-defaults::).
In addition, when the replica's copy of the table has more columns than the source'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 source
The following table definitions are valid and replicate correctly:
source> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
replica> 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 replica than they are on the source:
source> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);
replica> 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 source appears before the definitions of the columns common to both versions of the table:
source> CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);
replica> CREATE TABLE t1 (c1 INT, c2 INT);More columns on the replica
The following table definitions are valid and replicate correctly:
source> CREATE TABLE t1 (c1 INT, c2 INT);
replica> 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 source and the replica:
source> CREATE TABLE t1 (c1 INT, c2 INT);
replica> 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 replica's version of the table appears before the definitions for the columns which are common to both versions of the table:
source> CREATE TABLE t1 (c1 INT, c2 INT);
replica> CREATE TABLE t1 (c3 INT, c1 INT, c2 INT);The following table definitions fail because the replica's version of the table has additional columns compared to the source's version, and the two versions of the table use different data types for the common column 'c2':
source> CREATE TABLE t1 (c1 INT, c2 BIGINT);
replica> CREATE TABLE t1 (c1 INT, c2 INT, c3 INT);File: manual.info.tmp, Node: replication-features-different-data-types, Prev: replication-features-more-columns, Up: replication-features-differing-tables
16.4.1.12 Replication of Columns Having Different Data Types ............................................................
Corresponding columns on the source's and the replica'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.
It is usually 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 source) to a column having a different data type (on the replica); when the data type of the source's version of the column is promoted to a type that is the same size or larger on the replica, this is known as attribute promotion.
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 source or the replica. 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.
Important:
Whether you use statement-based or row-based replication, the replica's copy of the table cannot contain more columns than the source'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 source would also execute successfully on the replica, 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 replica, 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 replica's copy of a table to 'BIGINT', any insert into that column on the source that succeeds should also succeed on the replica, since it is impossible to have a legal 'TINYINT' value that is large enough to exceed a 'BIGINT' column.
Prior to MySQL 5.7.1, when using statement-based replication, 'AUTO_INCREMENT' columns were required to be the same on both the source and the replica; otherwise, updates could be applied to the wrong table on the replica. (Bug #12669186)
Row-based replication: attribute promotion and demotion
Row-based 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 'slave_type_conversions' global server variable controls the type conversion mode used on the replica. This variable takes a set of values from the following table, which shows the effects of each mode on the replica's type-conversion behavior:
Mode Effect
'ALL_LOSSY'
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
'INT' column to be converted to 'TINYINT' (a
lossy conversion), but not a 'TINYINT' column
to an 'INT' column (non-lossy). Attempting
the latter conversion in this case would
cause replication to stop with an error on
the replica.
'ALL_NON_LOSSY'
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 'ALL_LOSSY' mode. If
only 'ALL_NON_LOSSY' is set, but not
'ALL_LOSSY', then attempting a conversion
that would result in the loss of data (such
as 'INT' to 'TINYINT', or 'CHAR(25)' to
'VARCHAR(20)') causes the replica to stop
with an error.
'ALL_LOSSY,ALL_NON_LOSSY' When this mode is set, all supported type conversions are permitted, whether or not they are lossy conversions.
'ALL_SIGNED'
Treat promoted integer types as signed values (the default behavior).
'ALL_UNSIGNED'
Treat promoted integer types as unsigned values.
'ALL_SIGNED,ALL_UNSIGNED' Treat promoted integer types as signed if possible, otherwise as unsigned.
[empty]
When 'slave_type_conversions' is not set, no attribute promotion or demotion is permitted; this means that all columns in the source and target tables must be of the same types.
This mode is the default.When an integer type is promoted, its signedness is not preserved. By default, the replica treats all such values as signed. Beginning with MySQL 5.7.2, you can control this behavior using 'ALL_SIGNED', 'ALL_UNSIGNED', or both. (Bug#15831300) 'ALL_SIGNED' tells the replica to treat all promoted integer types as signed; 'ALL_UNSIGNED' instructs it to treat these as unsigned. Specifying both causes the replica to treat the value as signed if possible, otherwise to treat it as unsigned; the order in which they are listed is not significant. Neither 'ALL_SIGNED' nor 'ALL_UNSIGNED' has any effect if at least one of 'ALL_LOSSY' or 'ALL_NONLOSSY' is not also used.
Changing the type conversion mode requires restarting the replica with the new 'slave_type_conversions' setting.
Supported conversions
Supported conversions between different but similar data types are shown in the following list:
Between any of the integer types note 'TINYINT': integer-types, note 'SMALLINT': integer-types, note 'MEDIUMINT': integer-types, note 'INT': integer-types, and *note 'BIGINT': integer-types.
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 ensuring 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 UNSIGNED' non-lossily to 'SMALLINT', but not to 'TINYINT'.
Between any of the decimal types note 'DECIMAL': fixed-point-types, note 'FLOAT': floating-point-types, note 'DOUBLE': floating-point-types, and note 'NUMERIC': fixed-point-types.
'FLOAT' to 'DOUBLE' is a non-lossy conversion; 'DOUBLE' to 'FLOAT' can only be handled lossily. A conversion from 'DECIMAL(M,D)' to 'DECIMAL(M',D')' where 'D' >= D' and '(M'-D') >= (M-D') is non-lossy; for any case where 'M' < M', 'D' < D', 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 *note precision-math-rounding::, for information about how this is done for decimal types.
Between any of the string types note 'CHAR': char, note 'VARCHAR': char, and *note 'TEXT': blob, including conversions between different widths.
Conversion of a 'CHAR', 'VARCHAR', or 'TEXT' to a 'CHAR', 'VARCHAR', or 'TEXT' column the same size or larger is never lossy. Lossy conversion is handled by inserting only the first N characters of the string on the replica, where N is the width of the target column.
Important:
Replication between columns using different character sets is not supported.
Between any of the binary data types note 'BINARY': binary-varbinary, note 'VARBINARY': binary-varbinary, and *note 'BLOB': blob, including conversions between different widths.
Conversion of a 'BINARY', 'VARBINARY', or 'BLOB' to a 'BINARY', 'VARBINARY', or 'BLOB' column the same size or larger is never lossy. Lossy conversion is handled by inserting only the first N bytes of the string on the replica, where N is the width of the target column.
Between any 2 *note 'BIT': bit-type. columns of any 2 sizes.
When inserting a value from a 'BIT(M)' column into a 'BIT(M')' column, where 'M' > M', the most significant bits of the 'BIT(M')' columns are cleared (set to zero) and the M bits of the 'BIT(M)' value are set as the least significant bits of the 'BIT(M')' column.
When inserting a value from a source 'BIT(M)' column into a target 'BIT(M')' column, where 'M' < M', the maximum possible value for the 'BIT(M')' 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.
File: manual.info.tmp, Node: replication-features-directory, Next: replication-features-floatvalues, Prev: replication-features-differing-tables, Up: replication-features
16.4.1.13 Replication and DIRECTORY Table Options .................................................
If a 'DATA DIRECTORY' or 'INDEX DIRECTORY' table option is used in a note 'CREATE TABLE': create-table. statement on the source server, the table option is also used on the replica. This can cause problems if no corresponding directory exists in the replica host's file system or if it exists but is not accessible to the replica server. This can be overridden by using the 'NO_DIR_IN_CREATE' server SQL mode on the replica, which causes the replica to ignore the 'DATA DIRECTORY' and 'INDEX DIRECTORY' table options when replicating note 'CREATE TABLE': create-table. statements. The result is that 'MyISAM' data and index files are created in the table's database directory.
For more information, see *note sql-mode::.
File: manual.info.tmp, Node: replication-features-floatvalues, Next: replication-features-fractional-seconds, Prev: replication-features-directory, Up: replication-features
16.4.1.14 Replication and Floating-Point Values ...............................................
With statement-based replication, values are converted from decimal to binary. Because conversions between decimal and binary representations of them may be approximate, comparisons involving floating-point values are inexact. This is true for operations that use floating-point values explicitly, or that use values that are converted to floating-point implicitly. Comparisons of floating-point values might yield different results on source and replica servers due to differences in computer architecture, the compiler used to build MySQL, and so forth. See note type-conversion::, and note problems-with-float::.
File: manual.info.tmp, Node: replication-features-fractional-seconds, Next: replication-features-flush, Prev: replication-features-floatvalues, Up: replication-features
16.4.1.15 Replication and Fractional Seconds Support ....................................................
MySQL 5.7 permits fractional seconds for note 'TIME': time, note 'DATETIME': datetime, and note 'TIMESTAMP': datetime. values, with up to microseconds (6 digits) precision. See note fractional-seconds::.
There may be problems replicating from a source server that understands fractional seconds to an older replica (MySQL 5.6.3 and earlier) that does not:
For *note 'CREATE TABLE': create-table. statements containing columns that have an FSP (fractional seconds precision) value greater than 0, replication fails due to parser errors.
Statements that use temporal data types with an FSP value of 0 work with statement-based logging but not row-based logging. In the latter case, the data types have binary formats and type codes on the source that differ from those on the replica.
Some expression results differ on source and replica. Examples: On the source, the 'timestamp' system variable returns a value that includes a microseconds fractional part; on the replica, it returns an integer. On the source, functions that return a result that includes the current time (such as 'CURTIME()', 'SYSDATE()', or 'UTC_TIMESTAMP()') interpret an argument as an FSP value and the return value includes a fractional seconds part of that many digits. On the replica, these functions permit an argument but ignore it.
File: manual.info.tmp, Node: replication-features-flush, Next: replication-features-functions, Prev: replication-features-fractional-seconds, Up: replication-features
16.4.1.16 Replication and FLUSH ...............................
Some forms of the note 'FLUSH': flush. statement are not logged because they could cause problems if replicated to a replica: 'FLUSH LOGS' and 'FLUSH TABLES WITH READ LOCK'. For a syntax example, see note flush::. The 'FLUSH TABLES', note 'ANALYZE TABLE': analyze-table, note 'OPTIMIZE TABLE': optimize-table, and *note 'REPAIR TABLE': repair-table. statements are written to the binary log and thus replicated to replicas. This is not normally a problem because these statements do not modify table data.
However, this behavior can cause difficulties under certain circumstances. If you replicate the privilege tables in the 'mysql' database and update those tables directly without using *note 'GRANT': grant, you must issue a 'FLUSH PRIVILEGES' on the replicas to put the new privileges into effect. In addition, if you use 'FLUSH TABLES' when renaming a 'MyISAM' table that is part of a 'MERGE' table, you must issue 'FLUSH TABLES' manually on the replicas. These statements are written to the binary log unless you specify 'NO_WRITE_TO_BINLOG' or its alias 'LOCAL'.
File: manual.info.tmp, Node: replication-features-functions, Next: replication-features-invoked, Prev: replication-features-flush, Up: replication-features
16.4.1.17 Replication and System Functions ..........................................
Certain functions do not replicate well under some conditions:
The 'USER()', 'CURRENT_USER()' (or 'CURRENT_USER'), 'UUID()', 'VERSION()', and 'LOAD_FILE()' functions are replicated without change and thus do not work reliably on the replica unless row-based replication is enabled. (See *note replication-formats::.)
'USER()' and 'CURRENT_USER()' are automatically replicated using row-based replication when using 'MIXED' mode, and generate a warning in 'STATEMENT' mode. (See also *note replication-features-current-user::.) This is also true for 'VERSION()' and 'RAND()'.
For 'NOW()', the binary log includes the timestamp. This means that the value as returned by the call to this function on the source is replicated to the replica. To avoid unexpected results when replicating between MySQL servers in different time zones, set the time zone on both source and replica. For more information, see *note replication-features-timezone::.
To explain the potential problems when replicating between servers which are in different time zones, suppose that the source is located in New York, the replica is located in Stockholm, and both servers are using local time. Suppose further that, on the source, you create a table 'mytable', perform an *note 'INSERT': insert. statement on this table, and then select from the table, as shown here:
mysql> CREATE TABLE mytable (mycol TEXT);
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO mytable VALUES ( NOW() );
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM mytable;
+---------------------+
| mycol |
+---------------------+
| 2009-09-01 12:00:00 |
+---------------------+
1 row in set (0.00 sec)Local time in Stockholm is 6 hours later than in New York; so, if you issue 'SELECT NOW()' on the replica at that exact same instant, the value '2009-09-01 18:00:00' is returned. For this reason, if you select from the replica's copy of 'mytable' after the note 'CREATE TABLE': create-table. and note 'INSERT': insert. statements just shown have been replicated, you might expect 'mycol' to contain the value '2009-09-01 18:00:00'. However, this is not the case; when you select from the replica's copy of 'mytable', you obtain exactly the same result as on the source:
mysql> SELECT * FROM mytable;
+---------------------+
| mycol |
+---------------------+
| 2009-09-01 12:00:00 |
+---------------------+
1 row in set (0.00 sec)Unlike 'NOW()', the 'SYSDATE()' function is not replication-safe because it is not affected by 'SET TIMESTAMP' statements in the binary log and is nondeterministic if statement-based logging is used. This is not a problem if row-based logging is used.
An alternative is to use the '--sysdate-is-now' option to cause 'SYSDATE()' to be an alias for 'NOW()'. This must be done on the source and the replica to work correctly. In such cases, a warning is still issued by this function, but can safely be ignored as long as '--sysdate-is-now' is used on both the source and the replica.
'SYSDATE()' is automatically replicated using row-based replication when using 'MIXED' mode, and generates a warning in 'STATEMENT' mode.
See also *note replication-features-timezone::.
The following restriction applies to statement-based replication only, not to row-based replication. The 'GET_LOCK()', 'RELEASE_LOCK()', 'IS_FREE_LOCK()', and 'IS_USED_LOCK()' functions that handle user-level locks are replicated without the replica knowing the concurrency context on the source. Therefore, these functions should not be used to insert into a source table because the content on the replica would differ. For example, do not issue a statement such as 'INSERT INTO mytable VALUES(GET_LOCK(...))'.
These functions are automatically replicated using row-based replication when using 'MIXED' mode, and generate a warning in 'STATEMENT' mode.
As a workaround for the preceding limitations when statement-based replication is in effect, you can use the strategy of saving the problematic function result in a user variable and referring to the variable in a later statement. For example, the following single-row *note 'INSERT': insert. is problematic due to the reference to the 'UUID()' function:
INSERT INTO t VALUES(UUID());To work around the problem, do this instead:
SET @my_uuid = UUID();
INSERT INTO t VALUES(@my_uuid);That sequence of statements replicates because the value of '@my_uuid' is stored in the binary log as a user-variable event prior to the note 'INSERT': insert. statement and is available for use in the note 'INSERT': insert.
The same idea applies to multiple-row inserts, but is more cumbersome to use. For a two-row insert, you can do this:
SET @my_uuid1 = UUID(); @my_uuid2 = UUID();
INSERT INTO t VALUES(@my_uuid1),(@my_uuid2);However, if the number of rows is large or unknown, the workaround is difficult or impracticable. For example, you cannot convert the following statement to one in which a given individual user variable is associated with each row:
INSERT INTO t2 SELECT UUID(), * FROM t1;Within a stored function, 'RAND()' replicates correctly as long as it is invoked only once during the execution of the function. (You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the source and replica.)
The 'FOUND_ROWS()' and 'ROW_COUNT()' functions are not replicated reliably using statement-based replication. A workaround is to store the result of the function call in a user variable, and then use that in the *note 'INSERT': insert. statement. For example, if you wish to store the result in a table named 'mytable', you might normally do so like this:
SELECT SQL_CALC_FOUND_ROWS FROM mytable LIMIT 1;
INSERT INTO mytable VALUES( FOUND_ROWS() );However, if you are replicating 'mytable', you should use *note 'SELECT ... INTO': select-into, and then store the variable in the table, like this:
SELECT SQL_CALC_FOUND_ROWS INTO @found_rows FROM mytable LIMIT 1;
INSERT INTO mytable VALUES(@found_rows);In this way, the user variable is replicated as part of the context, and applied on the replica correctly.
These functions are automatically replicated using row-based replication when using 'MIXED' mode, and generate a warning in 'STATEMENT' mode. (Bug #12092, Bug #30244)
Prior to MySQL 5.7.3, the value of 'LAST_INSERT_ID()' was not replicated correctly if any filtering options such as '--replicate-ignore-db' and '--replicate-do-table' were enabled on the replica. (Bug #17234370, BUG# 69861)
File: manual.info.tmp, Node: replication-features-invoked, Next: replication-features-limit, Prev: replication-features-functions, Up: replication-features
16.4.1.18 Replication of Invoked Features .........................................
Replication of invoked features such as loadable functions and stored programs (stored procedures and functions, triggers, and events) provides the following characteristics:
The effects of the feature are always replicated.
The following statements are replicated using statement-based replication:
* *note 'CREATE EVENT': create-event.
* *note 'ALTER EVENT': alter-event.
* *note 'DROP EVENT': drop-event.
* *note 'CREATE PROCEDURE': create-procedure.
* *note 'DROP PROCEDURE': drop-procedure.
* *note 'CREATE FUNCTION': create-function.
* *note 'DROP FUNCTION': drop-function.
* *note 'CREATE TRIGGER': create-trigger.
* *note 'DROP TRIGGER': drop-trigger.However, the effects of features created, modified, or dropped using these statements are replicated using row-based replication.
Note:
Attempting to replicate invoked features using statement-based replication produces the warning 'Statement is not safe to log in statement format'. For example, trying to replicate a loadable function with statement-based replication generates this warning because it currently cannot be determined by the MySQL server whether the function is deterministic. If you are absolutely certain that the invoked feature's effects are deterministic, you can safely disregard such warnings.
In the case of note 'CREATE EVENT': create-event. and note 'ALTER EVENT': alter-event.:
* The status of the event is set to 'SLAVESIDE_DISABLED' on the
replica regardless of the state specified (this does not apply
to *note 'DROP EVENT': drop-event.).
* The source on which the event was created is identified on the
replica by its server ID. The 'ORIGINATOR' column in the
Information Schema *note 'EVENTS':
information-schema-events-table. table and the 'originator'
column in 'mysql.event' store this information. See *note
information-schema-events-table::, and *note show-events::,
for more information.The feature implementation resides on the replica in a renewable state so that if the source fails, the replica can be used as the source without loss of event processing.
To determine whether there are any scheduled events on a MySQL server that were created on a different server (that was acting as a replication source server), query the Information Schema *note 'EVENTS': information-schema-events-table. table in a manner similar to what is shown here:
SELECT EVENT_SCHEMA, EVENT_NAME
FROM INFORMATION_SCHEMA.EVENTS
WHERE STATUS = 'SLAVESIDE_DISABLED';Alternatively, you can use the *note 'SHOW EVENTS': show-events. statement, like this:
SHOW EVENTS
WHERE STATUS = 'SLAVESIDE_DISABLED';When promoting a replica having such events to a replication source server, you must enable each event using *note 'ALTER EVENT EVENT_NAME ENABLE': alter-event, where EVENT_NAME is the name of the event.
If more than one source was involved in creating events on this replica, and you wish to identify events that were created only on a given source having the server ID SOURCE_ID, modify the previous query on the *note 'EVENTS': information-schema-events-table. table to include the 'ORIGINATOR' column, as shown here:
SELECT EVENT_SCHEMA, EVENT_NAME, ORIGINATOR
FROM INFORMATION_SCHEMA.EVENTS
WHERE STATUS = 'SLAVESIDE_DISABLED'
AND ORIGINATOR = 'SOURCE_ID'You can employ 'ORIGINATOR' with the *note 'SHOW EVENTS': show-events. statement in a similar fashion:
SHOW EVENTS
WHERE STATUS = 'SLAVESIDE_DISABLED'
AND ORIGINATOR = 'SOURCE_ID'Before enabling events that were replicated from the source, you should disable the MySQL Event Scheduler on the replica (using a statement such as 'SET GLOBAL event_scheduler = OFF;'), run any necessary *note 'ALTER EVENT': alter-event. statements, restart the server, then re-enable the Event Scheduler on the replica afterward (using a statement such as 'SET GLOBAL event_scheduler = ON;')-
If you later demote the new source back to being a replica, you must disable manually all events enabled by the note 'ALTER EVENT': alter-event. statements. You can do this by storing in a separate table the event names from the note 'SELECT': select. statement shown previously, or using *note 'ALTER EVENT': alter-event. statements to rename the events with a common prefix such as 'replicated_' to identify them.
If you rename the events, then when demoting this server back to being a replica, you can identify the events by querying the *note 'EVENTS': information-schema-events-table. table, as shown here:
SELECT CONCAT(EVENT_SCHEMA, '.', EVENT_NAME) AS 'Db.Event'
FROM INFORMATION_SCHEMA.EVENTS
WHERE INSTR(EVENT_NAME, 'replicated_') = 1;File: manual.info.tmp, Node: replication-features-limit, Next: replication-features-load-data, Prev: replication-features-invoked, Up: replication-features
16.4.1.19 Replication and LIMIT ...............................
Statement-based replication of 'LIMIT' clauses in note 'DELETE': delete, note 'UPDATE': update, and *note 'INSERT ... SELECT': insert-select. statements is unsafe since the order of the rows affected is not defined. (Such statements can be replicated correctly with statement-based replication only if they also contain an 'ORDER BY' clause.) When such a statement is encountered:
When using 'STATEMENT' mode, a warning that the statement is not safe for statement-based replication is now issued.
When using 'STATEMENT' mode, warnings are issued for DML statements containing 'LIMIT' even when they also have an 'ORDER BY' clause (and so are made deterministic). This is a known issue. (Bug #42851)
When using 'MIXED' mode, the statement is now automatically replicated using row-based mode.
File: manual.info.tmp, Node: replication-features-load-data, Next: replication-features-max-allowed-packet, Prev: replication-features-limit, Up: replication-features
16.4.1.20 Replication and LOAD DATA ...................................
note 'LOAD DATA': load-data. is considered unsafe for statement-based logging (see note replication-rbr-safe-unsafe::). When 'binlog_format=MIXED' is set, the statement is logged in row-based format. When 'binlog_format=STATEMENT' is set, note that *note 'LOAD DATA': load-data. does not generate a warning, unlike other unsafe statements.
When note 'mysqlbinlog': mysqlbinlog. reads log events for note 'LOAD DATA': load-data. statements logged in statement-based format, a generated local file is created in a temporary directory. These temporary files are not automatically removed by note 'mysqlbinlog': mysqlbinlog. or any other MySQL program. If you do use note 'LOAD DATA': load-data. statements with statement-based binary logging, you should delete the temporary files yourself after you no longer need the statement log. For more information, see *note mysqlbinlog::.
File: manual.info.tmp, Node: replication-features-max-allowed-packet, Next: replication-features-memory, Prev: replication-features-load-data, Up: replication-features
16.4.1.21 Replication and max_allowed_packet ............................................
'max_allowed_packet' sets an upper limit on the size of any single message between the MySQL server and clients, including replicas. If you are replicating large column values (such as might be found in note 'TEXT': blob. or note 'BLOB': blob. columns) and 'max_allowed_packet' is too small on the source, the source fails with an error, and the replica shuts down the replication I/O thread. If 'max_allowed_packet' is too small on the replica, this also causes the replica to stop the replication I/O thread.
Row-based replication sends all columns and column values for updated rows from the source to the replica, including values of columns that were not actually changed by the update. This means that, when you are replicating large column values using row-based replication, you must take care to set 'max_allowed_packet' large enough to accommodate the largest row in any table to be replicated, even if you are replicating updates only, or you are inserting only relatively small values.
On a multi-threaded replica ('slave_parallel_workers > 0'), ensure that the system variable 'slave_pending_jobs_size_max' is set to a value equal to or greater than the setting for the 'max_allowed_packet' system variable on the source. The default setting for 'slave_pending_jobs_size_max', 128M, is twice the default setting for 'max_allowed_packet', which is 64M. 'max_allowed_packet' limits the packet size that the source can send, but the addition of an event header can produce a binary log event exceeding this size. Also, in row-based replication, a single event can be significantly larger than the 'max_allowed_packet' size, because the value of 'max_allowed_packet' only limits each column of the table.
The replica actually accepts packets up to the limit set by its 'slave_max_allowed_packet' setting, which default to the maximum setting of 1GB, to prevent a replication failure due to a large packet. However, the value of 'slave_pending_jobs_size_max' controls the memory that is made available on the replica to hold incoming packets. The specified memory is shared among all the replica worker queues.
The value of 'slave_pending_jobs_size_max' is a soft limit, and if an unusually large event (consisting of one or multiple packets) exceeds this size, the transaction is held until all the replica workers have empty queues, and then processed. All subsequent transactions are held until the large transaction has been completed. So although unusual events larger than 'slave_pending_jobs_size_max' can be processed, the delay to clear the queues of all the replica workers and the wait to queue subsequent transactions can cause lag on the replica and decreased concurrency of the replica workers. 'slave_pending_jobs_size_max' should therefore be set high enough to accommodate most expected event sizes.
File: manual.info.tmp, Node: replication-features-memory, Next: replication-features-mysqldb, Prev: replication-features-max-allowed-packet, Up: replication-features
16.4.1.22 Replication and MEMORY Tables .......................................
When a replication source server shuts down and restarts, its note 'MEMORY': memory-storage-engine. tables become empty. To replicate this effect to replicas, the first time that the source uses a given note 'MEMORY': memory-storage-engine. table after startup, it logs an event that notifies replicas that the table must be emptied by writing a note 'DELETE': delete. or (from MySQL 5.7.32) note 'TRUNCATE TABLE': truncate-table. statement for that table to the binary log. This generated event is identifiable by a comment in the binary log, and if GTIDs are in use on the server, it has a GTID assigned. The statement is always logged in statement format, even if the binary logging format is set to 'ROW', and it is written even if 'read_only' or 'super_read_only' mode is set on the server. Note that the replica still has outdated data in a *note 'MEMORY': memory-storage-engine. table during the interval between the source's restart and its first use of the table. To avoid this interval when a direct query to the replica could return stale data, you can set the 'init_file' system variable to name a file containing statements that populate the 'MEMORY' table on the source at startup.
When a replica server shuts down and restarts, its *note 'MEMORY': memory-storage-engine. tables become empty. This causes the replica to be out of synchrony with the source and may lead to other failures or cause the replica to stop:
Row-format updates and deletes received from the source may fail with 'Can't find record in 'MEMORY_TABLE''.
Statements such as *note 'INSERT INTO ... SELECT FROM MEMORY_TABLE': insert-select. may insert a different set of rows on the source and replica.
The replica also writes a note 'DELETE': delete. or (from MySQL 5.7.32) note 'TRUNCATE TABLE': truncate-table. statement to its own binary log, which is passed on to any downstream replicas, causing them to empty their own *note 'MEMORY': memory-storage-engine. tables.
The safe way to restart a replica that is replicating note 'MEMORY': memory-storage-engine. tables is to first drop or delete all rows from the note 'MEMORY': memory-storage-engine. tables on the source and wait until those changes have replicated to the replica. Then it is safe to restart the replica.
An alternative restart method may apply in some cases. When 'binlog_format=ROW', you can prevent the replica from stopping if you set 'slave_exec_mode=IDEMPOTENT' before you start the replica again. This allows the replica to continue to replicate, but its note 'MEMORY': memory-storage-engine. tables still differ from those on the source. This is acceptable if the application logic is such that the contents of note 'MEMORY': memory-storage-engine. tables can be safely lost (for example, if the note 'MEMORY': memory-storage-engine. tables are used for caching). 'slave_exec_mode=IDEMPOTENT' applies globally to all tables, so it may hide other replication errors in non-note 'MEMORY': memory-storage-engine. tables.
(The method just described is not applicable in NDB Cluster, where 'slave_exec_mode' is always 'IDEMPOTENT', and cannot be changed.)
The size of note 'MEMORY': memory-storage-engine. tables is limited by the value of the 'max_heap_table_size' system variable, which is not replicated (see note replication-features-variables::). A change in 'max_heap_table_size' takes effect for 'MEMORY' tables that are created or updated using note 'ALTER TABLE ... ENGINE = MEMORY': alter-table. or note 'TRUNCATE TABLE': truncate-table. following the change, or for all *note 'MEMORY': memory-storage-engine. tables following a server restart. If you increase the value of this variable on the source without doing so on the replica, it becomes possible for a table on the source to grow larger than its counterpart on the replica, leading to inserts that succeed on the source but fail on the replica with 'Table is full' errors. This is a known issue (Bug #48666). In such cases, you must set the global value of 'max_heap_table_size' on the replica as well as on the source, then restart replication. It is also recommended that you restart both the source and replica MySQL servers, to insure that the new value takes complete (global) effect on each of them.
See note memory-storage-engine::, for more information about note 'MEMORY': memory-storage-engine. tables.
File: manual.info.tmp, Node: replication-features-mysqldb, Next: replication-features-optimizer, Prev: replication-features-memory, Up: replication-features
16.4.1.23 Replication of the mysql System Database ..................................................
Data modification statements made to tables in the 'mysql' database are replicated according to the value of 'binlog_format'; if this value is 'MIXED', these statements are replicated using row-based format. However, statements that would normally update this information indirectly--such note 'GRANT': grant, note 'REVOKE': revoke, and statements manipulating triggers, stored routines, and views--are replicated to replicas using statement-based replication.
File: manual.info.tmp, Node: replication-features-optimizer, Next: replication-features-partitioning, Prev: replication-features-mysqldb, Up: replication-features
16.4.1.24 Replication and the Query Optimizer .............................................
It is possible for the data on the source and replica to become different if a statement is written in such a way that the data modification is nondeterministic; that is, left up the query optimizer. (In general, this is not a good practice, even outside of replication.) Examples of nondeterministic statements include note 'DELETE': delete. or note 'UPDATE': update. statements that use 'LIMIT' with no 'ORDER BY' clause; see *note replication-features-limit::, for a detailed discussion of these.
File: manual.info.tmp, Node: replication-features-partitioning, Next: replication-features-repair-table, Prev: replication-features-optimizer, Up: replication-features
16.4.1.25 Replication and Partitioning ......................................
Replication is supported between partitioned tables as long as they use the same partitioning scheme and otherwise have the same structure except where an exception is specifically allowed (see *note replication-features-differing-tables::).
Replication between tables having different partitioning is generally not supported. This because statements (such as *note 'ALTER TABLE ... DROP PARTITION': alter-table-partition-operations.) acting directly on partitions in such cases may produce different results on source and replica. In the case where a table is partitioned on the source but not on the replica, any statements operating on partitions on the source's copy of the replica fail on the replica. When the replica's copy of the table is partitioned but the source's copy is not, statements acting on partitions cannot be run on the source without causing errors there.
Due to these dangers of causing replication to fail entirely (on account of failed statements) and of inconsistencies (when the result of a partition-level SQL statement produces different results on source and replica), we recommend that insure that the partitioning of any tables to be replicated from the source is matched by the replica's versions of these tables.
File: manual.info.tmp, Node: replication-features-repair-table, Next: replication-features-reserved-words, Prev: replication-features-partitioning, Up: replication-features
16.4.1.26 Replication and REPAIR TABLE ......................................
When used on a corrupted or otherwise damaged table, it is possible for the note 'REPAIR TABLE': repair-table. statement to delete rows that cannot be recovered. However, any such modifications of table data performed by this statement are not replicated, which can cause source and replica to lose synchronization. For this reason, in the event that a table on the source becomes damaged and you use note 'REPAIR TABLE': repair-table. to repair it, you should first stop replication (if it is still running) before using *note 'REPAIR TABLE': repair-table, then afterward compare the source's and replica's copies of the table and be prepared to correct any discrepancies manually, before restarting replication.
File: manual.info.tmp, Node: replication-features-reserved-words, Next: replication-features-shutdowns, Prev: replication-features-repair-table, Up: replication-features
16.4.1.27 Replication and Reserved Words ........................................
You can encounter problems when you attempt to replicate from an older source to a newer replica and you make use of identifiers on the source that are reserved words in the newer MySQL version running on the replica. An example of this is using a table column named 'virtual' on a 5.6 source that is replicating to a 5.7 or higher replica because 'VIRTUAL' is a reserved word beginning in MySQL 5.7. Replication can fail in such cases with Error 1064 'You have an error in your SQL syntax...', even if a database or table named using the reserved word or a table having a column named using the reserved word is excluded from replication. This is due to the fact that each SQL event must be parsed by the replica prior to execution, so that the replica knows which database object or objects would be affected; only after the event is parsed can the replica apply any filtering rules defined by '--replicate-do-db', '--replicate-do-table', '--replicate-ignore-db', and '--replicate-ignore-table'.
To work around the problem of database, table, or column names on the source which would be regarded as reserved words by the replica, do one of the following:
Use one or more *note 'ALTER TABLE': alter-table. statements on the source to change the names of any database objects where these names would be considered reserved words on the replica, and change any SQL statements that use the old names to use the new names instead.
In any SQL statements using these database object names, write the names as quoted identifiers using backtick characters ('`').
For listings of reserved words by MySQL version, see Keywords and Reserved Words in MySQL 5.7 (https://dev.mysql.com/doc/mysqld-version-reference/en/keywords-5-7.html), in the 'MySQL Server Version Reference'. For identifier quoting rules, see *note identifiers::.
File: manual.info.tmp, Node: replication-features-shutdowns, Next: replication-features-errors, Prev: replication-features-reserved-words, Up: replication-features
16.4.1.28 Replication and Source or Replica Shutdowns .....................................................
It is safe to shut down a source server and restart it later. When a replica loses its connection to the source, the replica tries to reconnect immediately and retries periodically if that fails. The default is to retry every 60 seconds. This may be changed with the note 'CHANGE MASTER TO': change-master-to. statement. A replica also is able to deal with network connectivity outages. However, the replica notices the network outage only after receiving no data from the source for 'slave_net_timeout' seconds. If your outages are short, you may want to decrease 'slave_net_timeout'. See note replication-solutions-unexpected-replica-halt::.
An unclean shutdown (for example, a crash) on the source side can result in the source's binary log having a final position less than the most recent position read by the replica, due to the source's binary log file not being flushed. This can cause the replica not to be able to replicate when the source comes back up. Setting 'sync_binlog=1' in the source's 'my.cnf' file helps to minimize this problem because it causes the source to flush its binary log more frequently. For the greatest possible durability and consistency in a replication setup using 'InnoDB' with transactions, you should also set 'innodb_flush_log_at_trx_commit=1'. With this setting, the contents of the 'InnoDB' redo log buffer are written out to the log file at each transaction commit and the log file is flushed to disk. Note that the durability of transactions is still not guaranteed with this setting, because operating systems or disk hardware may tell *note 'mysqld': mysqld. that the flush-to-disk operation has taken place, even though it has not.
Shutting down a replica cleanly is safe because it keeps track of where it left off. However, be careful that the replica does not have temporary tables open; see *note replication-features-temptables::. Unclean shutdowns might produce problems, especially if the disk cache was not flushed to disk before the problem occurred:
For transactions, the replica commits and then updates 'relay-log.info'. If an unexpected exit occurs between these two operations, relay log processing proceeds further than the information file indicates and the replica re-executes the events from the last transaction in the relay log after it has been restarted.
A similar problem can occur if the replica updates 'relay-log.info' but the server host crashes before the write has been flushed to disk. To minimize the chance of this occurring, set 'sync_relay_log_info=1' in the replica 'my.cnf' file. Setting 'sync_relay_log_info' to 0 causes no writes to be forced to disk and the server relies on the operating system to flush the file from time to time.
The fault tolerance of your system for these types of problems is greatly increased if you have a good uninterruptible power supply.
File: manual.info.tmp, Node: replication-features-errors, Next: replication-features-sql-mode, Prev: replication-features-shutdowns, Up: replication-features
16.4.1.29 Replica Errors During Replication ...........................................
If a statement produces the same error (identical error code) on both the source and the replica, the error is logged, but replication continues.
If a statement produces different errors on the source and the replica, the replication SQL thread terminates, and the replica writes a message to its error log and waits for the database administrator to decide what to do about the error. This includes the case that a statement produces an error on the source or the replica, but not both. To address the issue, connect to the replica manually and determine the cause of the problem. note 'SHOW SLAVE STATUS': show-slave-status. is useful for this. Then fix the problem and run note 'START SLAVE': start-slave. For example, you might need to create a nonexistent table before you can start the replica again.
Note:
If a temporary error is recorded in the replica's error log, you do not necessarily have to take any action suggested in the quoted error message. Temporary errors should be handled by the client retrying the transaction. For example, if the replication SQL thread records a temporary error relating to a deadlock, you do not need to restart the transaction manually on the replica, unless the replication SQL thread subsequently terminates with a nontemporary error message.
If this error code validation behavior is not desirable, some or all errors can be masked out (ignored) with the '--slave-skip-errors' option.
For nontransactional storage engines such as 'MyISAM', it is possible to have a statement that only partially updates a table and returns an error code. This can happen, for example, on a multiple-row insert that has one row violating a key constraint, or if a long update statement is killed after updating some of the rows. If that happens on the source, the replica expects execution of the statement to result in the same error code. If it does not, the replication SQL thread stops as described previously.
If you are replicating between tables that use different storage engines on the source and replica, keep in mind that the same statement might produce a different error when run against one version of the table, but not the other, or might cause an error for one version of the table, but not the other. For example, since 'MyISAM' ignores foreign key constraints, an note 'INSERT': insert. or note 'UPDATE': update. statement accessing an 'InnoDB' table on the source might cause a foreign key violation but the same statement performed on a 'MyISAM' version of the same table on the replica would produce no such error, causing replication to stop.
File: manual.info.tmp, Node: replication-features-sql-mode, Next: replication-features-temptables, Prev: replication-features-errors, Up: replication-features
16.4.1.30 Replication and Server SQL Mode .........................................
Using different server SQL mode settings on the source and the replica may cause the same *note 'INSERT': insert. statements to be handled differently on the source and the replica, leading the source and replica to diverge. For best results, you should always use the same server SQL mode on the source and on the replica. This advice applies whether you are using statement-based or row-based replication.
If you are replicating partitioned tables, using different SQL modes on the source and the replica is likely to cause issues. At a minimum, this is likely to cause the distribution of data among partitions to be different in the source's and replica's copies of a given table. It may also cause inserts into partitioned tables that succeed on the source to fail on the replica.
For more information, see note sql-mode::. In particular, see note sql-mode-changes::, which describes changes in MySQL 5.7, so that you can assess whether your applications are affected.
File: manual.info.tmp, Node: replication-features-temptables, Next: replication-features-timeout, Prev: replication-features-sql-mode, Up: replication-features
16.4.1.31 Replication and Temporary Tables ..........................................
The discussion in the following paragraphs does not apply when 'binlog_format=ROW' because, in that case, temporary tables are not replicated; this means that there are never any temporary tables on the replica to be lost in the event of an unplanned shutdown by the replica. The remainder of this section applies only when using statement-based or mixed-format replication. Loss of replicated temporary tables on the replica can be an issue, whenever 'binlog_format' is 'STATEMENT' or 'MIXED', for statements involving temporary tables that can be logged safely using statement-based format. For more information about row-based replication and temporary tables, see *note replication-rbr-usage-temptables::.
Safe replica shutdown when using temporary tables
Temporary tables are replicated except in the case where you stop the replica server (not just the replication threads) and you have replicated temporary tables that are open for use in updates that have not yet been executed on the replica. If you stop the replica server, the temporary tables needed by those updates are no longer available when the replica is restarted. To avoid this problem, do not shut down the replica while it has temporary tables open. Instead, use the following procedure:
Issue a 'STOP SLAVE SQL_THREAD' statement.
Use *note 'SHOW STATUS': show-status. to check the value of the 'Slave_open_temp_tables' variable.
If the value is not 0, restart the replication SQL thread with 'START SLAVE SQL_THREAD' and repeat the procedure later.
When the value is 0, issue a *note 'mysqladmin shutdown': mysqladmin. command to stop the replica.
Temporary tables and replication options
By default, all temporary tables are replicated; this happens whether or not there are any matching '--replicate-do-db', '--replicate-do-table', or '--replicate-wild-do-table' options in effect. However, the '--replicate-ignore-table' and '--replicate-wild-ignore-table' options are honored for temporary tables. The exception is that to enable correct removal of temporary tables at the end of a session, a replica always replicates a 'DROP TEMPORARY TABLE IF EXISTS' statement, regardless of any exclusion rules that would normally apply for the specified table.
A recommended practice when using statement-based or mixed-format replication is to designate a prefix for exclusive use in naming temporary tables that you do not want replicated, then employ a '--replicate-wild-ignore-table' option to match that prefix. For example, you might give all such tables names beginning with 'norep' (such as 'norepmytable', 'norepyourtable', and so on), then use '--replicate-wild-ignore-table=norep%' to prevent them from being replicated.
File: manual.info.tmp, Node: replication-features-timeout, Next: replication-features-timezone, Prev: replication-features-temptables, Up: replication-features
16.4.1.32 Replication Retries and Timeouts ..........................................
The global system variable 'slave_transaction_retries' affects replication as follows: If the replication SQL thread fails to execute a transaction because of an 'InnoDB' deadlock or because it exceeded the 'InnoDB' 'innodb_lock_wait_timeout' value, or the note 'NDB': mysql-cluster. 'TransactionDeadlockDetectionTimeout' or 'TransactionInactiveTimeout' value, the replica automatically retries the transaction 'slave_transaction_retries' times before stopping with an error. The default value is 10. The total retry count can be seen in the output of note 'SHOW STATUS': show-status.; see *note server-status-variables::.
File: manual.info.tmp, Node: replication-features-timezone, Next: replication-features-transaction-inconsistencies, Prev: replication-features-timeout, Up: replication-features
16.4.1.33 Replication and Time Zones ....................................
By default, source and replica servers assume that they are in the same time zone. If you are replicating between servers in different time zones, the time zone must be set on both source and replica. Otherwise, statements depending on the local time on the source are not replicated properly, such as statements that use the 'NOW()' or 'FROM_UNIXTIME()' functions.
Verify that your combination of settings for the system time zone ('system_time_zone'), server current time zone (the global value of 'time_zone'), and per-session time zones (the session value of 'time_zone') on the source and replica is producing the correct results. In particular, if the 'time_zone' system variable is set to the value 'SYSTEM', indicating that the server time zone is the same as the system time zone, this can cause the source and replica to apply different time zones. For example, a source could write the following statement in the binary log:
SET @@session.time_zone='SYSTEM';If this source and its replica have a different setting for their system time zones, this statement can produce unexpected results on the replica, even if the replica's global 'time_zone' value has been set to match the source's. For an explanation of MySQL Server's time zone settings, and how to change them, see *note time-zone-support::.
See also *note replication-features-functions::.
File: manual.info.tmp, Node: replication-features-transaction-inconsistencies, Next: replication-features-transactions, Prev: replication-features-timezone, Up: replication-features
16.4.1.34 Replication and Transaction Inconsistencies .....................................................
Inconsistencies in the sequence of transactions that have been executed from the relay log can occur depending on your replication configuration. This section explains how to avoid inconsistencies and solve any problems they cause.
The following types of inconsistencies can exist:
Half-applied transactions. A transaction which updates non-transactional tables has applied some but not all of its changes.
Gaps. A gap is a transaction that has not been fully applied, even though some transaction later in the sequence has been applied. Gaps can only appear when using a multithreaded replica. To avoid gaps occurring, set 'slave_preserve_commit_order=1', which requires 'slave_parallel_type=LOGICAL_CLOCK', and that 'log-bin' and 'log-slave-updates' are also enabled. Note that 'slave_preserve_commit_order=1' does not preserve the order of non-transactional DML updates, so these might commit before transactions that precede them in the relay log, which might result in gaps.
Source binary log position lag. Even in the absence of gaps, it is possible that transactions after 'Exec_master_log_pos' have been applied. That is, all transactions up to point 'N' have been applied, and no transactions after 'N' have been applied, but 'Exec_master_log_pos' has a value smaller than 'N'. In this situation, 'Exec_master_log_pos' is a 'low-water mark' of the transactions applied, and lags behind the position of the most recently applied transaction. This can only happen on multithreaded replicas. Enabling 'slave_preserve_commit_order' does not prevent source binary log position lag.
The following scenarios are relevant to the existence of half-applied transactions, gaps, and source binary log position lag:
While replication threads are running, there may be gaps and half-applied transactions.
*note 'mysqld': mysqld. shuts down. Both clean and unclean shutdown abort ongoing transactions and may leave gaps and half-applied transactions.
*note 'KILL': kill. of replication threads (the SQL thread when using a single-threaded replica, the coordinator thread when using a multithreaded replica). This aborts ongoing transactions and may leave gaps and half-applied transactions.
Error in applier threads. This may leave gaps. If the error is in a mixed transaction, that transaction is half-applied. When using a multithreaded replica, workers which have not received an error complete their queues, so it may take time to stop all threads.
note 'STOP SLAVE': stop-slave. when using a multithreaded replica. After issuing note 'STOP SLAVE': stop-slave, the replica waits for any gaps to be filled and then updates 'Exec_master_log_pos'. This ensures it never leaves gaps or source binary log position lag, unless any of the cases above applies, in other words, before note 'STOP SLAVE': stop-slave. completes, either an error happens, or another thread issues note 'KILL': kill, or the server restarts. In these cases, *note 'STOP SLAVE': stop-slave. returns successfully.
If the last transaction in the relay log is only half-received and the multithreaded replica coordinator has started to schedule the transaction to a worker, then *note 'STOP SLAVE': stop-slave. waits up to 60 seconds for the transaction to be received. After this timeout, the coordinator gives up and aborts the transaction. If the transaction is mixed, it may be left half-completed.
note 'STOP SLAVE': stop-slave. when the ongoing transaction updates transactional tables only, in which case it is rolled back and note 'STOP SLAVE': stop-slave. stops immediately. If the ongoing transaction is mixed, *note 'STOP SLAVE': stop-slave. waits up to 60 seconds for the transaction to complete. After this timeout, it aborts the transaction, so it may be left half-completed.
The global variable 'rpl_stop_slave_timeout' is unrelated to the process of stopping the replication threads. It only makes the client that issues *note 'STOP SLAVE': stop-slave. return to the client, but the replication threads continue to try to stop.
If a replication channel has gaps, it has the following consequences:
The replica database is in a state that may never have existed on the source.
The field 'Exec_master_log_pos' in *note 'SHOW SLAVE STATUS': show-slave-status. is only a 'low-water mark'. In other words, transactions appearing before the position are guaranteed to have committed, but transactions after the position may have committed or not.
note 'CHANGE MASTER TO': change-master-to. statements for that channel fail with an error, unless the applier threads are running and the note 'CHANGE MASTER TO': change-master-to. statement only sets receiver options.
If *note 'mysqld': mysqld. is started with '--relay-log-recovery', no recovery is done for that channel, and a warning is printed.
If note 'mysqldump': mysqldump. is used with '--dump-slave', it does not record the existence of gaps; thus it prints note 'CHANGE MASTER TO': change-master-to. with 'RELAY_LOG_POS' set to the 'low-water mark' position in 'Exec_master_log_pos'.
After applying the dump on another server, and starting the replication threads, transactions appearing after the position are replicated again. Note that this is harmless if GTIDs are enabled (however, in that case it is not recommended to use '--dump-slave').
If a replication channel has source binary log position lag but no gaps, cases 2 to 5 above apply, but case 1 does not.
The source binary log position information is persisted in binary format in the internal table 'mysql.slave_worker_info'. note 'START SLAVE [SQL_THREAD]': start-slave. always consults this information so that it applies only the correct transactions. This remains true even if 'slave_parallel_workers' has been changed to 0 before note 'START SLAVE': start-slave, and even if note 'START SLAVE': start-slave. is used with 'UNTIL' clauses. note 'START SLAVE UNTIL SQL_AFTER_MTS_GAPS': start-slave. only applies as many transactions as needed in order to fill in the gaps. If *note 'START SLAVE': start-slave. is used with 'UNTIL' clauses that tell it to stop before it has consumed all the gaps, then it leaves remaining gaps.
Warning:
note 'RESET SLAVE': reset-slave. removes the relay logs and resets the replication position. Thus issuing note 'RESET SLAVE': reset-slave. on a replica with gaps means the replica loses any information about the gaps, without correcting the gaps.
When GTID-based replication is in use, from MySQL 5.7.28 a multithreaded replica checks first whether 'MASTER_AUTO_POSITION' is set to 'ON', and if it is, omits the step of calculating the transactions that should be skipped or not skipped. In that situation, the old relay logs are not required for the recovery process.
File: manual.info.tmp, Node: replication-features-transactions, Next: replication-features-triggers, Prev: replication-features-transaction-inconsistencies, Up: replication-features
16.4.1.35 Replication and Transactions ......................................
Mixing transactional and nontransactional statements within the same transaction
In general, you should avoid transactions that update both transactional and nontransactional tables in a replication environment. You should also avoid using any statement that accesses both transactional (or temporary) and nontransactional tables and writes to any of them.
The server uses these rules for binary logging:
If the initial statements in a transaction are nontransactional, they are written to the binary log immediately. The remaining statements in the transaction are cached and not written to the binary log until the transaction is committed. (If the transaction is rolled back, the cached statements are written to the binary log only if they make nontransactional changes that cannot be rolled back. Otherwise, they are discarded.)
For statement-based logging, logging of nontransactional statements is affected by the 'binlog_direct_non_transactional_updates' system variable. When this variable is 'OFF' (the default), logging is as just described. When this variable is 'ON', logging occurs immediately for nontransactional statements occurring anywhere in the transaction (not just initial nontransactional statements). Other statements are kept in the transaction cache and logged when the transaction commits. 'binlog_direct_non_transactional_updates' has no effect for row-format or mixed-format binary logging.
Transactional, nontransactional, and mixed statements
To apply those rules, the server considers a statement nontransactional if it changes only nontransactional tables, and transactional if it changes only transactional tables. A statement that references both nontransactional and transactional tables and updates any of the tables involved, is considered a 'mixed' statement. (In some past MySQL versions, only a statement that updated both nontransactional and transactional tables was considered mixed.) Mixed statements, like transactional statements, are cached and logged when the transaction commits.
A mixed statement that updates a transactional table is considered unsafe if the statement also performs either of the following actions:
Updates or reads a temporary table
Reads a nontransactional table and the transaction isolation level is less than REPEATABLE_READ
A mixed statement following the update of a transactional table within a transaction is considered unsafe if it performs either of the following actions:
Updates any table and reads from any temporary table
Updates a nontransactional table and 'binlog_direct_non_transactional_updates' is OFF
For more information, see *note replication-rbr-safe-unsafe::.
Note:
A mixed statement is unrelated to mixed binary logging format.
In situations where transactions mix updates to transactional and nontransactional tables, the order of statements in the binary log is correct, and all needed statements are written to the binary log even in case of a *note 'ROLLBACK': commit. However, when a second connection updates the nontransactional table before the first connection transaction is complete, statements can be logged out of order because the second connection update is written immediately after it is performed, regardless of the state of the transaction being performed by the first connection.
Using different storage engines on source and replica
It is possible to replicate transactional tables on the source using nontransactional tables on the replica. For example, you can replicate an 'InnoDB' source table as a 'MyISAM' replica table. However, if you do this, there are problems if the replica is stopped in the middle of a note 'BEGIN': commit. ... note 'COMMIT': commit. block because the replica restarts at the beginning of the *note 'BEGIN': commit. block.
It is also safe to replicate transactions from note 'MyISAM': myisam-storage-engine. tables on the source to transactional tables, such as tables that use the note 'InnoDB': innodb-storage-engine. storage engine, on the replica. In such cases, an 'AUTOCOMMIT=1' statement issued on the source is replicated, thus enforcing 'AUTOCOMMIT' mode on the replica.
When the storage engine type of the replica is nontransactional, transactions on the source that mix updates of transactional and nontransactional tables should be avoided because they can cause inconsistency of the data between the source transactional table and the replica nontransactional table. That is, such transactions can lead to source storage engine-specific behavior with the possible effect of replication going out of synchrony. MySQL does not issue a warning about this currently, so extra care should be taken when replicating transactional tables from the source to nontransactional tables on the replicas.
Changing the binary logging format within transactions
The 'binlog_format' and 'binlog_checksum' system variables are read-only as long as a transaction is in progress.
Every transaction (including 'autocommit' transactions) is recorded in the binary log as though it starts with a note 'BEGIN': commit. statement, and ends with either a note 'COMMIT': commit. or a note 'ROLLBACK': commit. statement. This is even true for statements affecting tables that use a nontransactional storage engine (such as note 'MyISAM': myisam-storage-engine.).
Note:
For restrictions that apply specifically to XA transactions, see *note xa-restrictions::.
File: manual.info.tmp, Node: replication-features-triggers, Next: replication-features-truncate, Prev: replication-features-transactions, Up: replication-features
16.4.1.36 Replication and Triggers ..................................
With statement-based replication, triggers executed on the source also execute on the replica. With row-based replication, triggers executed on the source do not execute on the replica. Instead, the row changes on the source resulting from trigger execution are replicated and applied on the replica.
This behavior is by design. If under row-based replication the replica applied the triggers as well as the row changes caused by them, the changes would in effect be applied twice on the replica, leading to different data on the source and the replica.
If you want triggers to execute on both the source and the replica, perhaps because you have different triggers on the source and replica, you must use statement-based replication. However, to enable replica-side triggers, it is not necessary to use statement-based replication exclusively. It is sufficient to switch to statement-based replication only for those statements where you want this effect, and to use row-based replication the rest of the time.
A statement invoking a trigger (or function) that causes an update to an 'AUTO_INCREMENT' column is not replicated correctly using statement-based replication. MySQL 5.7 marks such statements as unsafe. (Bug #45677)
A trigger can have triggers for different combinations of trigger event (note 'INSERT': insert, note 'UPDATE': update, *note 'DELETE': delete.) and action time ('BEFORE', 'AFTER'), but before MySQL 5.7.2 cannot have multiple triggers that have the same trigger event and action time. MySQL 5.7.2 lifts this limitation and multiple triggers are permitted. This change has replication implications for upgrades and downgrades.
For brevity, 'multiple triggers' here is shorthand for 'multiple triggers that have the same trigger event and action time.'
Upgrades. Suppose that you upgrade an old server that does not support multiple triggers to MySQL 5.7.2 or higher. If the new server is a replication source server and has old replicas that do not support multiple triggers, an error occurs on those replicas if a trigger is created on the source for a table that already has a trigger with the same trigger event and action time. To avoid this problem, upgrade the replicas first, then upgrade the source.
Downgrades. If you downgrade a server that supports multiple triggers to an older version that does not, the downgrade has these effects:
For each table that has triggers, all trigger definitions remain in the '.TRG' file for the table. However, if there are multiple triggers with the same trigger event and action time, the server executes only one of them when the trigger event occurs. For information about '.TRG' files, see the Table Trigger Storage section of the MySQL Server Doxygen documentation, available at https://dev.mysql.com/doc/index-other.html.
If triggers for the table are added or dropped subsequent to the downgrade, the server rewrites the table's '.TRG' file. The rewritten file retains only one trigger per combination of trigger event and action time; the others are lost.
To avoid these problems, modify your triggers before downgrading. For each table that has multiple triggers per combination of trigger event and action time, convert each such set of triggers to a single trigger as follows:
For each trigger, create a stored routine that contains all the code in the trigger. Values accessed using 'NEW' and 'OLD' can be passed to the routine using parameters. If the trigger needs a single result value from the code, you can put the code in a stored function and have the function return the value. If the trigger needs multiple result values from the code, you can put the code in a stored procedure and return the values using 'OUT' parameters.
Drop all triggers for the table.
Create one new trigger for the table that invokes the stored routines just created. The effect for this trigger is thus the same as the multiple triggers it replaces.
File: manual.info.tmp, Node: replication-features-truncate, Next: replication-features-user-names, Prev: replication-features-triggers, Up: replication-features
16.4.1.37 Replication and TRUNCATE TABLE ........................................
note 'TRUNCATE TABLE': truncate-table. is normally regarded as a DML statement, and so would be expected to be logged and replicated using row-based format when the binary logging mode is 'ROW' or 'MIXED'. However this caused issues when logging or replicating, in 'STATEMENT' or 'MIXED' mode, tables that used transactional storage engines such as note 'InnoDB': innodb-storage-engine. when the transaction isolation level was 'READ COMMITTED' or 'READ UNCOMMITTED', which precludes statement-based logging.
note 'TRUNCATE TABLE': truncate-table. is treated for purposes of logging and replication as DDL rather than DML so that it can be logged and replicated as a statement. However, the effects of the statement as applicable to note 'InnoDB': innodb-storage-engine. and other transactional tables on replicas still follow the rules described in *note truncate-table:: governing such tables. (Bug #36763)
File: manual.info.tmp, Node: replication-features-user-names, Next: replication-features-variables, Prev: replication-features-truncate, Up: replication-features
16.4.1.38 Replication and User Name Length ..........................................
The maximum length of MySQL user names was increased from 16 characters to 32 characters in MySQL 5.7.8. Replication of user names longer than 16 characters to a replica that supports only shorter user names fails. However, this should occur only when replicating from a newer source to an older replica, which is not a recommended configuration.
File: manual.info.tmp, Node: replication-features-variables, Next: replication-features-views, Prev: replication-features-user-names, Up: replication-features
16.4.1.39 Replication and Variables ...................................
System variables are not replicated correctly when using 'STATEMENT' mode, except for the following variables when they are used with session scope:
'auto_increment_increment'
'auto_increment_offset'
'character_set_client'
'character_set_connection'
'character_set_database'
'character_set_server'
'collation_connection'
'collation_database'
'collation_server'
'foreign_key_checks'
'identity'
'last_insert_id'
'lc_time_names'
'pseudo_thread_id'
'sql_auto_is_null'
'time_zone'
'timestamp'
'unique_checks'
When 'MIXED' mode is used, the variables in the preceding list, when used with session scope, cause a switch from statement-based to row-based logging. See *note binary-log-mixed::.
'sql_mode' is also replicated except for the 'NO_DIR_IN_CREATE' mode; the replica always preserves its own value for 'NO_DIR_IN_CREATE', regardless of changes to it on the source. This is true for all replication formats.
However, when *note 'mysqlbinlog': mysqlbinlog. parses a 'SET @@sql_mode = MODE' statement, the full MODE value, including 'NO_DIR_IN_CREATE', is passed to the receiving server. For this reason, replication of such a statement may not be safe when 'STATEMENT' mode is in use.
The 'default_storage_engine' system variable is not replicated, regardless of the logging mode; this is intended to facilitate replication between different storage engines.
The 'read_only' system variable is not replicated. In addition, the enabling this variable has different effects with regard to temporary tables, table locking, and the *note 'SET PASSWORD': set-password. statement in different MySQL versions.
The 'max_heap_table_size' system variable is not replicated. Increasing the value of this variable on the source without doing so on the replica can lead eventually to 'Table is full' errors on the replica when trying to execute note 'INSERT': insert. statements on a note 'MEMORY': memory-storage-engine. table on the source that is thus permitted to grow larger than its counterpart on the replica. For more information, see *note replication-features-memory::.
In statement-based replication, session variables are not replicated properly when used in statements that update tables. For example, the following sequence of statements do not insert the same data on the source and the replica:
SET max_join_size=1000;
INSERT INTO mytable VALUES(@@max_join_size);This does not apply to the common sequence:
SET time_zone=...;
INSERT INTO mytable VALUES(CONVERT_TZ(..., ..., @@time_zone));Replication of session variables is not a problem when row-based replication is being used, in which case, session variables are always replicated safely. See *note replication-formats::.
The following session variables are written to the binary log and honored by the replica when parsing the binary log, regardless of the logging format:
'sql_mode'
'foreign_key_checks'
'unique_checks'
'character_set_client'
'collation_connection'
'collation_database'
'collation_server'
'sql_auto_is_null'
Important:
Even though session variables relating to character sets and collations are written to the binary log, replication between different character sets is not supported.
To help reduce possible confusion, we recommend that you always use the same setting for the 'lower_case_table_names' system variable on both source and replica, especially when you are running MySQL on platforms with case-sensitive file systems.
File: manual.info.tmp, Node: replication-features-views, Prev: replication-features-variables, Up: replication-features
16.4.1.40 Replication and Views ...............................
Views are always replicated to replicas. Views are filtered by their own name, not by the tables they refer to. This means that a view can be replicated to the replica even if the view contains a table that would normally be filtered out by 'replication-ignore-table' rules. Care should therefore be taken to ensure that views do not replicate table data that would normally be filtered for security reasons.
Replication from a table to a same-named view is supported using statement-based logging, but not when using row-based logging. Trying to do so when row-based logging is in effect causes an error. (Bug #11752707, Bug #43975)
File: manual.info.tmp, Node: replication-compatibility, Next: replication-upgrade, Prev: replication-features, Up: replication-notes
MySQL supports replication from one release series to the next higher release series. For example, you can replicate from a source running MySQL 5.6 to a replica running MySQL 5.7, from a source running MySQL 5.7 to a replica running MySQL 8.0, and so on. However, you may encounter difficulties when replicating from an older source to a newer replica if the source uses statements or relies on behavior no longer supported in the version of MySQL used on the replica. For example, foreign key names longer than 64 characters are no longer supported from MySQL 8.0.
The use of more than two MySQL Server versions is not supported in replication setups involving multiple sources, regardless of the number of source or replica MySQL servers. This restriction applies not only to release series, but to version numbers within the same release series as well. For example, if you are using a chained or circular replication setup, you cannot use MySQL 5.7.22, MySQL 5.7.23, and MySQL 5.7.24 concurrently, although you could use any two of these releases together.
Important:
It is strongly recommended to use the most recent release available within a given MySQL release series because replication (and other) capabilities are continually being improved. It is also recommended to upgrade sources and replicas that use early releases of a release series of MySQL to GA (production) releases when the latter become available for that release series.
Replication from newer sources to older replicas may be possible, but is generally not supported. This is due to a number of factors:
Binary log format changes
The binary log format can change between major releases. While we attempt to maintain backward compatibility, this is not always possible.
This also has significant implications for upgrading replication servers; see *note replication-upgrade::, for more information.
For more information about row-based replication, see *note replication-formats::.
SQL incompatibilities
You cannot replicate from a newer source to an older replica using statement-based replication if the statements to be replicated use SQL features available on the source but not on the replica.
However, if both the source and the replica support row-based replication, and there are no data definition statements to be replicated that depend on SQL features found on the source but not on the replica, you can use row-based replication to replicate the effects of data modification statements even if the DDL run on the source is not supported on the replica.
For more information on potential replication issues, see *note replication-features::.
File: manual.info.tmp, Node: replication-upgrade, Next: replication-problems, Prev: replication-compatibility, Up: replication-notes
When you upgrade servers that participate in a replication topology, you need to take into account each server's role in the topology and look out for issues specific to replication. For general information and instructions for upgrading a MySQL Server instance, see *note upgrading::.
As explained in *note replication-compatibility::, MySQL supports replication from a source running one release series to a replica running the next higher release series, but does not support replication from a source running a later release to a replica running an earlier release. A replica at an earlier release might not have the required capability to process transactions that can be handled by the source at a later release. You must therefore upgrade all of the replicas in a replication topology to the target MySQL Server release, before you upgrade the source server to the target release. In this way you will never be in the situation where a replica still at the earlier release is attempting to handle transactions from a source at the later release.
In a replication topology where there are multiple sources (multi-source replication), the use of more than two MySQL Server versions is not supported, regardless of the number of source or replica MySQL servers. This restriction applies not only to release series, but to version numbers within the same release series as well. For example, you cannot use MySQL 5.7.22, MySQL 5.7.24, and MySQL 5.7.28 concurrently in such a setup, although you could use any two of these releases together.
If you need to downgrade the servers in a replication topology, the source must be downgraded before the replicas are downgraded. On the replicas, you must ensure that the binary log and relay log have been fully processed, and remove them before proceeding with the downgrade.
Behavior Changes Between Releases
Although this upgrade sequence is correct, it is possible to still encounter replication difficulties when replicating from a source at an earlier release that has not yet been upgraded, to a replica at a later release that has been upgraded. This can happen if the source uses statements or relies on behavior that is no longer supported in the later release installed on the replica. You can use MySQL Shell's upgrade checker utility 'util.checkForServerUpgrade()' to check MySQL 5.7 server instances or MySQL 8.0 server instances for upgrade to a GA MySQL 8.0 release. The utility identifies anything that needs to be fixed for that server instance so that it does not cause an issue after the upgrade, including features and behaviors that are no longer available in the later release. See Upgrade Checker Utility (https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html) for information on the upgrade checker utility.
If you are upgrading an existing replication setup from a version of MySQL that does not support global transaction identifiers (GTIDs) to a version that does, only enable GTIDs on the source and the replicas when you have made sure that the setup meets all the requirements for GTID-based replication. See *note replication-gtids-howto:: for information about converting binary log file position based replication setups to use GTID-based replication.
Changes affecting operations in strict SQL mode ('STRICT_TRANS_TABLES' or 'STRICT_ALL_TABLES') may result in replication failure on an upgraded replica. If you use statement-based logging ('binlog_format=STATEMENT'), if a replica is upgraded before the source, the source executes statements which succeed there but which may fail on the replica and so cause replication to stop. To deal with this, stop all new statements on the source and wait until the replicas catch up, then upgrade the replicas. Alternatively, if you cannot stop new statements, temporarily change to row-based logging on the source ('binlog_format=ROW') and wait until all replicas have processed all binary logs produced up to the point of this change, then upgrade the replicas.
The default character set has changed from 'latin1' to 'utf8mb4' in MySQL 8.0. In a replicated setting, when upgrading from MySQL 5.7 to 8.0, it is advisable to change the default character set back to the character set used in MySQL 5.7 before upgrading. After the upgrade is completed, the default character set can be changed to 'utf8mb4'. Assuming that the previous defaults were used, one way to preserve them is to start the server with these lines in the 'my.cnf' file:
[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ciStandard Upgrade Procedure
To upgrade a replication topology, follow the instructions in *note upgrading:: for each individual MySQL Server instance, using this overall procedure:
Upgrade the replicas first. On each replica instance:
* Carry out the preliminary checks and steps described in
Preparing Your Installation for Upgrade
(https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html).
* Shut down MySQL Server.
* Upgrade the MySQL Server binaries or packages.
* Restart MySQL Server.
* If you have upgraded to a release earlier than MySQL 8.0.16,
invoke *note 'mysql_upgrade': mysql-upgrade. manually to
upgrade the system tables and schemas. When the server is
running with global transaction identifiers (GTIDs) enabled
('gtid_mode=ON'), do not enable binary logging by *note
'mysql_upgrade': mysql-upgrade. (so do not use the
'--write-binlog' option). Then shut down and restart the
server.
* If you have upgraded to MySQL 8.0.16 or later, do not invoke
*note 'mysql_upgrade': mysql-upgrade. From that release,
MySQL Server performs the entire MySQL upgrade procedure,
disabling binary logging during the upgrade.
* Restart replication using a 'START REPLICA'
(https://dev.mysql.com/doc/refman/8.0/en/start-replica.html)
or *note 'START SLAVE': start-slave. statement.When all the replicas have been upgraded, follow the same steps to upgrade and restart the source server, with the exception of the 'START REPLICA' (https://dev.mysql.com/doc/refman/8.0/en/start-replica.html) or *note 'START SLAVE': start-slave. statement. If you made a temporary change to row-based logging or to the default character set, you can revert the change now.
Upgrade Procedure With Table Repair Or Rebuild
Some upgrades may require that you drop and re-create database objects when you move from one MySQL series to the next. For example, collation changes might require that table indexes be rebuilt. Such operations, if necessary, are detailed at *note upgrading-from-previous-series::. It is safest to perform these operations separately on the replicas and the source, and to disable replication of these operations from the source to the replica. To achieve this, use the following procedure:
Stop all the replicas and upgrade the binaries or packages. Restart them with the '--skip-slave-start' option, or from MySQL 8.0.24, the 'skip_slave_start' (https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#sysvar_skip_slave_start) system variable, so that they do not connect to the source. Perform any table repair or rebuilding operations needed to re-create database objects, such as use of 'REPAIR TABLE' or 'ALTER TABLE', or dumping and reloading tables or triggers.
Disable the binary log on the source. To do this without restarting the source, execute a 'SET sql_log_bin = OFF' statement. Alternatively, stop the source and restart it with the '--skip-log-bin' option. If you restart the source, you might also want to disallow client connections. For example, if all clients connect using TCP/IP, enable the 'skip_networking' system variable when you restart the source.
With the binary log disabled, perform any table repair or rebuilding operations needed to re-create database objects. The binary log must be disabled during this step to prevent these operations from being logged and sent to the replicas later.
Re-enable the binary log on the source. If you set 'sql_log_bin' to 'OFF' earlier, execute a 'SET sql_log_bin = ON' statement. If you restarted the source to disable the binary log, restart it without '--skip-log-bin', and without enabling the 'skip_networking' system variable so that clients and replicas can connect.
Restart the replicas, this time without the '--skip-slave-start' option or 'skip_slave_start' (https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#sysvar_skip_slave_start) system variable.
File: manual.info.tmp, Node: replication-problems, Next: replication-bugs, Prev: replication-upgrade, Up: replication-notes
If you have followed the instructions but your replication setup is not working, the first thing to do is check the error log for messages. Many users have lost time by not doing this soon enough after encountering problems.
If you cannot tell from the error log what the problem was, try the following techniques:
Verify that the source has binary logging enabled by issuing a *note 'SHOW MASTER STATUS': show-master-status. statement. If logging is enabled, 'Position' is nonzero. If binary logging is not enabled, verify that you are running the source server with the '--log-bin' option.
Verify that the 'server_id' system variable was set at startup on both the source and replica and that the ID value is unique on each server.
Verify that the replica is running. Use note 'SHOW SLAVE STATUS': show-slave-status. to check whether the 'Slave_IO_Running' and 'Slave_SQL_Running' values are both 'Yes'. If not, verify the options that were used when starting the replica server. For example, '--skip-slave-start' prevents the replica threads from starting until you issue a note 'START SLAVE': start-slave. statement.
If the replica is running, check whether it established a connection to the source. Use note 'SHOW PROCESSLIST': show-processlist, find the I/O and SQL threads and check their 'State' column to see what they display. See note replication-threads::. If the replication I/O thread state says 'Connecting to master', check the following:
* Verify the privileges for the user being used for replication
on the source.
* Check that the host name of the source is correct and that you
are using the correct port to connect to the source. The port
used for replication is the same as used for client network
communication (the default is '3306'). For the host name,
ensure that the name resolves to the correct IP address.
* Check the configuration file to see whether the
'skip_networking' system variable has been enabled on the
source or replica to disable networking. If so, comment the
setting or remove it.
* If the source has a firewall or IP filtering configuration,
ensure that the network port being used for MySQL is not being
filtered.
* Check that you can reach the source by using 'ping' or
'traceroute'/'tracert' to reach the host.If the replica was running previously but has stopped, the reason usually is that some statement that succeeded on the source failed on the replica. This should never happen if you have taken a proper snapshot of the source, and never modified the data on the replica outside of the replication threads. If the replica stops unexpectedly, it is a bug or you have encountered one of the known replication limitations described in note replication-features::. If it is a bug, see note replication-bugs::, for instructions on how to report it.
If a statement that succeeded on the source refuses to run on the replica, try the following procedure if it is not feasible to do a full database resynchronization by deleting the replica's databases and copying a new snapshot from the source:
Determine whether the affected table on the replica is different from the table on the source. Try to understand how this happened. Then make the replica's table identical to the source's and run *note 'START SLAVE': start-slave.
If the preceding step does not work or does not apply, try to understand whether it would be safe to make the update manually (if needed) and then ignore the next statement from the source.
If you decide that the replica can skip the next statement from the source, issue the following statements:
mysql> SET GLOBAL sql_slave_skip_counter = N;
mysql> START SLAVE;The value of N should be 1 if the next statement from the source does not use 'AUTO_INCREMENT' or 'LAST_INSERT_ID()'. Otherwise, the value should be 2. The reason for using a value of 2 for statements that use 'AUTO_INCREMENT' or 'LAST_INSERT_ID()' is that they take two events in the binary log of the source.
See also *note set-global-sql-slave-skip-counter::.
If you are sure that the replica started out perfectly synchronized with the source, and that no one has updated the tables involved outside of the replication threads, then presumably the discrepancy is the result of a bug. If you are running the most recent version of MySQL, please report the problem. If you are running an older version, try upgrading to the latest production release to determine whether the problem persists.
File: manual.info.tmp, Node: replication-bugs, Prev: replication-problems, Up: replication-notes
When you have determined that there is no user error involved, and replication still either does not work at all or is unstable, it is time to send us a bug report. We need to obtain as much information as possible from you to be able to track down the bug. Please spend some time and effort in preparing a good bug report.
If you have a repeatable test case that demonstrates the bug, please enter it into our bugs database using the instructions given in *note bug-reports::. If you have a 'phantom' problem (one that you cannot duplicate at will), use the following procedure:
Verify that no user error is involved. For example, if you update the replica outside of the replication thread, the data goes out of synchrony, and you can have unique key violations on updates. In this case, the replication SQL thread stops and waits for you to clean up the tables manually to bring them into synchrony. This is not a replication problem. It is a problem of outside interference causing replication to fail.
Run the replica with the '--log-slave-updates' and '--log-bin' options. These options cause the replica to log the updates that it receives from the source into its own binary logs.
Save all evidence before resetting the replication state. If we have no information or only sketchy information, it becomes difficult or impossible for us to track down the problem. The evidence you should collect is:
* All binary log files from the source
* All binary log files from the replica
* The output of *note 'SHOW MASTER STATUS': show-master-status.
from the source at the time you discovered the problem
* The output of *note 'SHOW SLAVE STATUS': show-slave-status.
from the replica at the time you discovered the problem
* Error logs from the source and the replicaUse note 'mysqlbinlog': mysqlbinlog. to examine the binary logs. The following should be helpful to find the problem statement. LOG_FILE and LOG_POS are the 'Master_Log_File' and 'Read_Master_Log_Pos' values from note 'SHOW SLAVE STATUS': show-slave-status.
$> mysqlbinlog --start-position=LOG_POS LOG_FILE | headAfter you have collected the evidence for the problem, try to isolate it as a separate test case first. Then enter the problem with as much information as possible into our bugs database using the instructions at *note bug-reports::.
File: manual.info.tmp, Node: group-replication, Next: mysql-shell-userguide, Prev: replication, Up: Top
17 Group Replication ********************
Menu:
group-replication-technical-details:: Group Replication Technical Details
This chapter explains MySQL Group Replication and how to install, configure and monitor groups. MySQL Group Replication is a MySQL Server plugin that enables you to create elastic, highly-available, fault-tolerant replication topologies.
Groups can operate in a single-primary mode with automatic primary election, where only one server accepts updates at a time. Alternatively, for more advanced users, groups can be deployed in multi-primary mode, where all servers can accept updates, even if they are issued concurrently.
There is a built-in group membership service that keeps the view of the group consistent and available for all servers at any given point in time. Servers can leave and join the group and the view is updated accordingly. Sometimes servers can leave the group unexpectedly, in which case the failure detection mechanism detects this and notifies the group that the view has changed. This is all automatic.
The chapter is structured as follows:
*note group-replication-background:: provides an introduction to groups and how Group Replication works.
*note group-replication-getting-started:: explains how to configure multiple MySQL Server instances to create a group.
*note group-replication-requirements-and-limitations:: explains architecture and setup requirements and limitations for Group Replication.
*note group-replication-monitoring:: explains how to monitor a group.
*note group-replication-operations:: explains how to work with a group.
*note group-replication-security:: explains how to secure a group.
Upgrading Group Replication (https://dev.mysql.com/doc/refman/8.0/en/group-replication-upgrade.html) explains how to upgrade a group.
*note group-replication-options:: is a reference for the system variables specific to Group Replication.
*note group-replication-frequently-asked-questions:: provides answers to some technical questions about deploying and operating Group Replication.
*note group-replication-technical-details:: provides in-depth information about how Group Replication works.
File: manual.info.tmp, Node: group-replication-background, Next: group-replication-getting-started, Prev: group-replication, Up: group-replication