5.8. Replication Features and Known Problems
In general, replication compatibility at the SQL level requires that any features used be supported by both the master and the slave servers. If you use a feature on a master server that is available only as of a given version of MySQL, you cannot replicate to a slave that is older than that version. Such incompatibilities are likely to occur between series, so that, for example, you cannot replicate from MySQL 5.0 to 4.1. However, these incompatibilities also can occur for within-series replication. For example, the SLEEP() function is available in MySQL 5.0.12 and up. If you use this function on the master server, you cannot replicate to a slave server that is older than MySQL 5.0.12.
If you are planning to use replication between 5.0 and a previous version of MySQL you should consult the edition of the "MySQL Reference Manual" corresponding to the earlier release series for information regarding the replication characteristics of that series.
The following list provides details about what is supported and what is not. Additional InnoDB-specific information about replication is given in Section 126.96.36.199, "InnoDB and MySQL Replication."
With MySQL's classic 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 (RBR) instead. For a description of row-based replication, see Section 5.3, "Row-Based Replication."
Known issue: In MySQL 5.0.17, the syntax for CREATE TRIGGER changed to include a DEFINER clause for specifying which access privileges to check at trigger invocation time. However, if you attempt to replicate from a master server older than MySQL 5.0.17 to a slave running MySQL 5.0.17 through 5.0.19, replication of CREATE TRIGGER statements fails on the slave with a Definer not fully qualified error. A workaround is to create triggers on the master using a version-specific comment embedded in each CREATE TRIGGER statement:
CREATE /*!50017 DEFINER = 'root @ localhost */ TRIGGER ... ;
CREATE TRIGGER statements written this way will replicate to newer slaves, which pick up the DEFINER clause from the comment and execute successfully. This slave problem is fixed as of MySQL 5.0.20.
Replication of AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values is done correctly.
The USER(), UUID(), and LOAD_FILE() functions are replicated without change and thus do not work reliably on the slave unless row-based replication is enabled. (See Section 5.3, "Row-Based Replication.")
User privileges are replicated only if the mysql database is replicated. That is, the GRANT, REVOKE, SET PASSWORD, CREATE USER, and DROP USER statements take effect on the slave only if the replication setup includes the mysql database.
If you're replicating all databases, but don't want statements that affect user privileges to be replicated, set up the slave to not replicate the mysql database, using the --replicate-wild-ignore-table=mysql.% option. The slave will recognize that issuing privilege-related SQL statements won't have an effect, and thus not execute those statements.
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 slave knowing the concurrency context on master. Therefore, these functions should not be used to insert into a master's table because the content on the slave would differ. (For example, do not issue a statement such as INSERT INTO mytable VALUES(GET_LOCK(...)).)
The FOREIGN_KEY_CHECKS, SQL_MODE, UNIQUE_CHECKS, and SQL_AUTO_IS_NULL variables are all replicated in MySQL 5.0. The storage_engine system variable (also known as table_type) is not yet replicated, which is a good thing for replication between different storage engines.
Starting from MySQL 5.0.3 (master and slave), replication works even if the master and slave have different global character set variables. Starting from MySQL 5.0.4 (master and slave), replication works even if the master and slave have different global time zone variables.
The following applies to replication between MySQL servers that use different character sets:
If the master uses MySQL 4.1, you must always use the same global character set and collation on the master and the slave, regardless of the MySQL version running on the slave. (These are controlled by the --character-set-server and --collation-server options.) Otherwise, you may get duplicate-key errors on the slave, because a key that is unique in the master character set might not be unique in the slave character set. Note that this is not a cause for concern when master and slave are both MySQL 5.0 or later.
If the master is older than MySQL 4.1.3, the character set of any client should never be made different from its global value because this character set change is not known to the slave. In other words, clients should not use SET NAMES, SET CHARACTER SET, and so forth. If both the master and the slave are 4.1.3 or newer, clients can freely set session values for character set variables because these settings are written to the binary log and so are known to the slave. That is, clients can use SET NAMES or SET CHARACTER SET or can set variables such as collation_client or collation_server. However, clients are prevented from changing the global value of these variables; as stated previously, the master and slave must always have identical global character set values.
If you have databases on the master with character sets that differ from the global character_set_server
value, you should design your CREATE TABLE
statements so that tables in those databases do not implicitly rely on the database default character set (see Bug #2326 at http://bugs.mysql.com/2326
). A good workaround is to state the character set and collation explicitly in CREATE TABLE
If the master uses MySQL 4.1, the same system time zone should be set for both master and slave. Otherwise some statements will not be replicated properly, such as statements that use the NOW() or FROM_UNIXTIME() functions. You can set the time zone in which MySQL server runs by using the --timezone=timezone_name option of the mysqld_safe script or by setting the TZ environment variable. Both master and slave should also have the same default connection time zone setting; that is, the --default-time-zone parameter should have the same value for both master and slave. Note that this is not necessary when the master is MySQL 5.0 or later.
CONVERT_TZ(...,...,@@global.time_zone) is not properly replicated.
CONVERT_TZ(...,...,@@session.time_zone) is properly replicated only if the master and slave are from MySQL 5.0.4 or newer.
Session variables are not replicated properly when used in statements that update tables. For example, SET MAX_JOIN_SIZE=1000 followed by INSERT INTO mytable VALUES(@@MAX_JOIN_SIZE) will not insert the same data on the master and the slave. This does not apply to the common sequence of SET TIME_ZONE=... followed by INSERT INTO mytable VALUES(CONVERT_TZ(...,...,@@time_zone)), which replicates correctly as of MySQL 5.0.4.
Replication of session variables is not a problem when row-based replication is being used. See Section 5.3, "Row-Based Replication."
It is possible to replicate transactional tables on the master using non-transactional tables on the slave. For example, you can replicate an InnoDB master table as a MyISAM slave table. However, if you do this, there are problems if the slave is stopped in the middle of a BEGIN/ COMMIT block because the slave restarts at the beginning of the BEGIN block.
Update statements that refer to user-defined variables (that is, variables of the form @var_name) are replicated correctly in MySQL 5.0. However, this is not true for versions prior to 4.1. Note that user variable names are case insensitive starting in MySQL 5.0. You should take this into account when setting up replication between MySQL 5.0 and older versions.
Slaves can connect to masters using SSL.
In MySQL 5.0 (starting from 5.0.3), there is a global system variable slave_transaction_ retries: If the replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or because it exceeded the InnoDB innodb_lock_wait_timeout or the NDBCluster transactionDeadlockDetectionTimeout or transactionInactiveTimeout value, the transaction automatically retries slave_transaction_retries times before stopping with an error. The default value is 10. Starting from MySQL 5.0.4, the total retry count can be seen in the output of SHOW STATUS; see Section 4.2.4, "Server Status Variables."
If a DATA DIRECTORY or INDEX DIRECTORY table option is used in a CREATE TABLE statement on the master server, the table option is also used on the slave. This can cause problems if no corresponding directory exists in the slave host filesystem or if it exists but is not accessible to the slave server. MySQL supports an sql_mode option called NO_DIR_IN_CREATE. If the slave server is run with this SQL mode enabled, it ignores the DATA DIRECTORY and INDEX DIRECTORY table options when replicating CREATE TABLE statements. The result is that MyISAM data and index files are created in the table's database directory.
The following restriction applies to statement-based replication only, not to row-based replication: It is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is non-deterministic; that is, it is left to the will of the query optimizer. (This is in general not a good practice, even outside of replication.)
The following applies only if either the master or the slave is running MySQL version 5.0.3 or older: If on the master a LOAD DATA INFILE is interrupted (integrity constraint violation, killed connection, and so on), the slave skips the LOAD DATA INFILE entirely. This means that if this command permanently inserted or updated table records before being interrupted, these modifications are not replicated to the slave.
Some forms of the FLUSH statement are not logged because they could cause problems if replicated to a slave: FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK. The FLUSH TABLES, ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements are written to the binary log and thus replicated to slaves. This is not normally a problem because these statements do not modify table data. However, this can cause difficulties under certain circumstances. If you replicate the privilege tables in the mysql database and update those tables directly without using GRANT, you must issue a FLUSH PRIVILEGES on the slaves 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 slaves. These statements are written to the binary log unless you specify NO_WRITE_TO_BINLOG or its alias LOCAL.
MySQL supports only one master and many slaves. In the future we plan to add a voting algorithm for changing the master automatically in the event of problems with the current master. We also plan to introduce agent processes to help perform load balancing by sending SELECT queries to different slaves.
When a server shuts down and restarts, its MEMORY (HEAP) tables become empty. The master replicates this effect to slaves as follows: The first time that the master uses each MEMORY table after startup, it logs an event that notifies the slaves that the table needs to be emptied by writing a DELETE statement for that table to the binary log. See Section 8.4, "The MEMORY (HEAP) Storage Engine" for more information.
Note that this item does not apply when row-based replication is in use because row-based replication does not require that temporary tables be replicated at all. (See Section 5.3, "Row-Based Replication.")
Temporary tables are replicated except in the case where you shut down the slave server (not just the slave threads) and you have replicated temporary tables that are used in updates that have not yet been executed on the slave. If you shut down the slave server, the temporary tables needed by those updates are no longer available when the slave is restarted. To avoid this problem, do not shut down the slave while it has temporary tables open. Instead, use the following procedure:
Issue a STOP SLAVE statement.
Use SHOW STATUS to check the value of the Slave_open_temp_tables variable.
If the value is 0, issue a mysqladmin shutdown command to stop the slave.
If the value is not 0, restart the slave threads with START SLAVE.
Repeat the procedure later until the Slave_open_temp_tables variable is 0 and you can stop the slave.
The syntax for multiple-table DELETE statements that use table aliases changed between MySQL 4.0 and 4.1. In MySQL 4.0, you should use the true table name to refer to any table from which rows should be deleted:
DELETE test FROM test AS t1, test2 WHERE ...
In MySQL 4.1, you must use the alias:
DELETE t1 FROM test AS t1, test2 WHERE ...
If you use such DELETE statements, the change in syntax means that a 4.0 master cannot replicate to slaves from 4.1 or higher.
It is safe to connect servers in a circular master/slave relationship if you use the --log-slave-updates option. That means that you can create a setup such as this:
However, many statements do not work correctly in this kind of setup unless your client code is written to take care of the potential problems that can occur from updates that occur in different sequence on different servers.
Server IDs are encoded in binary log events, so server A knows when an event that it reads was originally created by itself and does not execute the event (unless server A was started with the --replicate-same-server-id option, which is meaningful only in rare cases). Thus, there are no infinite loops. This type of circular setup works only if you perform no conflicting updates between the tables. In other words, if you insert data in both A and C, you should never insert a row in A that may have a key that conflicts with a row inserted in C. You should also not update the same rows on two servers if the order in which the updates are applied is significant.
If a statement on a slave produces an error, the slave SQL thread terminates, and the slave writes a message to its error log. You should then connect to the slave manually and determine the cause of the problem. (SHOW SLAVE STATUS is useful for this.) Then fix the problem (for example, you might need to create a non-existent table) and run START SLAVE.
It is safe to shut down a master server and restart it later. When a slave loses its connection to the master, the slave tries to reconnect immediately and retries periodically if that fails. The default is to retry every 60 seconds. This may be changed with the --master-connect-retry option. A slave also is able to deal with network connectivity outages. However, the slave notices the network outage only after receiving no data from the master for slave_net_timeout seconds. If your outages are short, you may want to decrease slave_net_timeout. See Section 4.2.2, "Server System Variables."
Shutting down the slave (cleanly) is also safe because it keeps track of where it left off. Unclean shutdowns might produce problems, especially if the disk cache was not flushed to disk before the system went down. Your system fault tolerance is greatly increased if you have a good uninterruptible power supply. Unclean shutdowns of the master may cause inconsistencies between the content of tables and the binary log in master; this can be avoided by using InnoDB tables and the --innodb-safe-binlog option on the master. See Section 4.12.3, "The Binary Log."
Note: --innodb-safe-binlog is unneeded as of MySQL 5.0.3, having been made obsolete by the introduction of XA transaction support.
Due to the non-transactional nature of MyISAM tables, 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 master, the slave thread exits and waits for the database administrator to decide what to do about it unless the error code is legitimate and execution of the statement results in the same error code on the slave. If this error code validation behavior is not desirable, some or all errors can be masked out (ignored) with the --slave-skip-errors option.
If you update transactional tables from non-transactional tables inside a BEGIN/ COMMIT sequence, updates to the binary log may be out of synchrony with table states if the non-transactional table is updated before the transaction commits. This occurs because the transaction is written to the binary log only when it is committed.
In situations where transactions mix updates to transactional and non-transactional 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 ROLLBACK. However, when a second connection updates the non-transactional table before the first connection's transaction is complete, statements can be logged out of order, because the second connection's update is written immediately after it is performed, regardless of the state of the transaction being performed by the first connection.