Replication will be done correctly with AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values.

The USER(), UUID(), and LOAD_FILE() functions are replicated without changes and will thus not work reliably on the slave. This is also true for CONNECTION_ID() in slave versions older than 4.1.1. The new PASSWORD() function in MySQL 4.1 is well replicated in masters from 4.1.1 and up; your slaves also must be 4.1.1 or above to replicate it. If you have older slaves and need to replicate PASSWORD() from your 4.1.x master, you must start your master with the --old-password option, so that it uses the old implementation of PASSWORD(). (Note that the PASSWORD() implementation in MySQL 4.1.0 differs from every other version of MySQL. It is best to avoid 4.1.0 in a replication situation.)

The FOREIGN_KEY_CHECKS variable is replicated as of MySQL 4.0.14. The sql_mode, UNIQUE_CHECKS, and SQL_AUTO_IS_NULL variables are replicated as of 5.0.0. The SQL_SELECT_LIMIT and table_type variables are not yet replicated.

Replication between MySQL servers using different character sets is discussed here. First, you must ALWAYS use the same global character set and collation (--default-character-set, --default-collation) on the master and the slave. Otherwise, you may get duplicate-key errors on the slave, because a key that is regarded as unique in the master's character set may not be unique in the slave's character set. Second, if the master is strictly older than MySQL 4.1.3, the character set of the session should never be made different from its global value (in other words, don't use SET NAMES, SET CHARACTER SET etc) because this character set change will not be known to the slave. If the master is 4.1.3 or newer, and the slave too, the session can freely set its local value of character set variables (NAMES, CHARACTER SET, COLLATION_CLIENT, COLLATION_SERVER etc) as these settings will be written to the binary log and then known to the slave. The session will however be prevented from changing the global value of these; as said already the master and slave must always have identical global character set values. There also is one last limitation: if on the master you have databases with different character sets from the global collation_server value, you should design your CREATE TABLE statements so that they don't implicitely rely on the default database's character set, because there currently is a bug (Bug #2326); a good workaround is to explicitely state the character set and collation in a clause of the CREATE TABLE.

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, you will have problems if the slave is stopped in the middle of a BEGIN/COMMIT block, because the slave will restart at the beginning of the BEGIN block. This issue is on our TODO and will be fixed in the near future.

Update statements that refer to user variables (that is, variables of the form @var_name) are badly replicated in 3.23 and 4.0. This is fixed in 4.1. Note that user variable names are case insensitive starting from MySQL 5.0. You should take this into account when setting up replication between 5.0 and an older version.

The slave can connect to the master using SSL if both are 4.1.1 or newer.

If a DATA DIRECTORY or INDEX DIRECTORY clause is used in a CREATE TABLE statement on the master server, the clause is also used on the slave. This can cause problems if no corresponding directory exists in the slave host filesystem or exists but is not accessible to the slave server. Starting from MySQL 4.0.15, there is a sql_mode option called NO_DIR_IN_CREATE. If the slave server is run with its SQL mode set to include this option, it will simply ignore the clauses before replicating the CREATE TABLE statement. The result is that the MyISAM data and index files are created in the table's database directory.

Although we have never heard of it actually occurring, it is theoretically possible for the data on the master and slave to become different if a query is designed in such a way that the data modification is non-deterministic; that is, left to the will of the query optimizer. (That generally is not a good practice anyway, even outside of replication!) For a detailed explanation of this issue, see the section called “Open Bugs and Design Deficiencies in MySQL”.

Before MySQL 4.1.1, FLUSH, ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements are not written to the binary log and thus are not replicated to the slaves. This is not normally a problem because these statements do not modify table data. However, it can cause difficulties under certain circumstances. If you replicate the privilege tables in the mysql database and update those tables directly without using the GRANT statement, you must issue a FLUSH PRIVILEGES statement on your slaves to put the new privileges into effect. Also if you use FLUSH TABLES when renaming a MyISAM table that is part of a MERGE table, you will have to issue FLUSH TABLES manually on the slaves. As of MySQL 4.1.1, these statements are written to the binary log (unless you specify NO_WRITE_TO_BINLOG, or its alias LOCAL). Exceptions are that FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case. (Any of them may cause problems if replicated to a slave.) For a syntax example, see FLUSH.

MySQL only supports one master and many slaves. Later we will add a voting algorithm to automatically change master if something goes wrong with the current master. We will also introduce “agent” processes to help do load balancing by sending SELECT queries to different slaves.

When a server shuts down and restarts, its MEMORY (HEAP) tables become empty. As of MySQL 4.0.18, the master replicates this effect as follows: The first time that the master uses each MEMORY table after startup, it notifies slaves that the table needs to be emptied by writing a DELETE FROM statement for the table to its binary log. See HEAP for more details.

Temporary tables are replicated with the exception of the case that you shut down the slave server (not just the slave threads) and you have some replicated temporary tables that are used in update statements that have not yet been executed on the slave. If you shut down the slave server, the temporary tables needed by those updates no longer are available when the slave starts again. To avoid this problem, do not shut down the slave while it has temporary tables open. Instead, use this procedure:

We have plans to fix this problem in the near future.

It is safe to connect servers in a circular master/slave relationship with the --log-slave-updates option specified. Note, however, that many statements will 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.

This means that you can create a setup such as this:

A -> B -> C -> A

Server IDs are encoded in the binary log events, so server A will know when an event that it reads was originally created by itself and will not execute the event (unless server A was started with the --replicate-same-server-id option, which is meaningful only in rare setups). Thus, there will be no infinite loop. But this circular setup will work 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 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 the 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, fix the problem (for example, a non-existent table), and then run START SLAVE.

It is safe to shut down a master server and restart it later. If a slave loses its connection to the master, the slave tries to reconnect immediately. If that fails, the slave retries periodically. (The default is to retry every 60 seconds. This may be changed with the --master-connect-retry option.) The slave will also be able to deal with network connectivity outages. However, the slave will notice 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 the section called “Server System Variables”.

Shutting down the slave (cleanly) is also safe, as it keeps track of where it left off. Unclean shutdowns might produce problems, especially if disk cache was not flushed to disk before the system went down. Your system fault tolerance will be 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 the section called “The Binary Log”.

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 will exit and wait for the database administrator to decide what to do about it unless the error code is legitimate and the statement execution results in the same error code. If this error code validation behavior is not desirable, some or all errors can be masked out (ignored) with the --slave-skip-errors option. This option is available starting with MySQL 3.23.47.

If you update transactional tables from non-transactional tables inside a BEGIN/COMMIT segment, updates to the binary log may be out of sync if some thread changes the non-transactional table before the transaction commits. This is because the transaction is written to the binary log only when it is committed.

Before version 4.0.15, any update to a non-transactional table is written to the binary log at once when the update is made, whereas transactional updates are written on COMMIT or not written at all if you use ROLLBACK. You must take this into account when updating both transactional tables and non-transactional tables within the same transaction. (This is true not only for replication, but also if you are using binary logging for backups.) In version 4.0.15, we changed the logging behavior for transactions that mix updates to transactional and non-transactional tables, which solves the problems (order of statements is good in the binary log, and all needed statements are written to the binary log even in case of ROLLBACK). The problem that remains is when a second connection updates the non-transactional table while the first connection's transaction is not finished yet; wrong order can still occur, because the second connection's update will be written immediately after it is done.

When a 4.x slave replicates a LOAD DATA INFILE from a 3.23 master, the values of the Exec_Master_Log_Pos and Relay_Log_Space columns of SHOW SLAVE STATUS become incorrect. The incorrectness of Exec_Master_Log_Pos will cause a problem when you stop and restart replication; so it is a good idea to correct the value before this, by doing FLUSH LOGS on the master. These bugs are already fixed in MySQL 5.0.0 slaves.

LOAD DATA INFILE is handled properly, as long as the data file still resides on the master server at the time of update propagation.

LOAD DATA LOCAL INFILE is no longer skipped on the slave as it was in 3.23.

In 3.23, RAND() in updates does not replicate properly. Use RAND(some_non_rand_expr) if you are replicating updates with RAND(). You can, for example, use UNIX_TIMESTAMP() as the argument to RAND().