5.7 Replication Features and Known Problems

 <  Day Day Up  >  

The following list explains what is supported and what is not. Additional InnoDB -specific information about replication is given in Section 9.7.5, "InnoDB and MySQL Replication."

  • 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.

  • You must use the same character set ( --default- character-set ) 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 character set may not be unique in the slave character set. Character sets will be replicated in 5.0.x.

  • 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 an 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 Section 1.8.7.3, " 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.)

  • 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 Section 8.3, "The MEMORY (HEAP) Storage Engine" 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:

    1. Issue a STOP SLAVE statement.

    2. Use SHOW STATUS to check the value of the Slave_open_temp_tables variable.

    3. If the value is 0, issue a mysqladmin shutdown command to shut down the slave.

    4. If the value is not 0, restart the slave threads with START SLAVE .

    5. Repeat the procedure later to see if you have better luck next time.

    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. 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 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 Section 4.2.3, "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.

  • 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.

The following table lists replication problems in MySQL 3.23 that are fixed in MySQL 4.0:

  • 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() .

 <  Day Day Up  >  


MySQL AB MySQL Administrator[ap]s Guide
MySQL AB MySQL Administrator[ap]s Guide
ISBN: 782142591
EAN: N/A
Year: 2004
Pages: 138

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net