Copyright (C) 2000-2012 |
GNU Info (mysql.info)Replication FeaturesReplication Features and Known Problems --------------------------------------- Below is an explanation of what is supported and what is not: * Replication will be done correctly with `AUTO_INCREMENT', `LAST_INSERT_ID', and `TIMESTAMP' values. * `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()' for the argument to `RAND()'. * You have to use the same character set (`--default-character-set') on the master and the slave. If not, you may get duplicate key errors on the slave, because a key that is regarded as unique on the master may not be that in the other character set. * `LOAD DATA INFILE' will be handled properly as long as the file still resides on the master server at the time of update propagation. `LOAD LOCAL DATA INFILE' will be skipped. * Update queries that use user variables are not replication-safe (yet). * `FLUSH' commands are not stored in the binary log and are because of this not replicated to the slaves. This is not normally a problem as `FLUSH' doesn't change anything. This does however mean that if you update the `MySQL' privilege tables directly without using `GRANT' statement and you replicate the `MySQL' privilege database, you must do a `FLUSH PRIVILEGES' on your slaves to put the new privileges into effect. * Temporary tables starting in 3.23.29 are replicated properly with the exception of the case when you shut down slave server ( not just slave thread), you have some temporary tables open, and the are used in subsequent updates. To deal with this problem, to shut down the slave, do `SLAVE STOP', then check `Slave_open_temp_tables' variable to see if it is 0, then issue `mysqladmin shutdown'. If the number is not 0, restart the slave thread with `SLAVE START' and see if you have better luck next time. There will be a cleaner solution, but it has to wait until version 4.0. In earlier versions temporary tables are not being replicated properly - we recommend that you either upgrade, or execute `SET SQL_LOG_BIN=0' on your clients before all queries with temp tables. * MySQL only supports one master and many slaves. We will in 4.x add a voting algorithm to automatically change master if something goes wrong with the current master. We will also introduce 'agent' processes to help doing load balancing by sending select queries to different slaves. * Starting in Version 3.23.26, it is safe to connect servers in a circular master-slave relationship with `log-slave-updates' enabled. Note, however, that many queries will not work right in this kind of setup unless your client code is written to take care of the potential problems that can happen from updates that occur in different sequence on different servers. This means that you can do a setup like the following: A -> B -> C -> A This setup will only works if you only do non conflicting updates between the tables. In other words, if you insert data in A and C, you should never insert a row in A that may have a conflicting key with a row insert in C. You should also not update the sam rows on two servers if the order in which the updates are applied matters. Note that the log format has changed in Version 3.23.26 so that pre-3.23.26 slaves will not be able to read it. * If the query on the slave gets an error, the slave thread will terminate, and a message will appear in the `.err' file. You should then connect to the slave manually, fix the cause of the error (for example, non-existent table), and then run `SLAVE START' sql command (available starting in Version 3.23.16). In Version 3.23.15, you will have to restart the server. * If connection to the master is lost, the slave will retry immediately, and then in case of failure every `master-connect-retry' (default 60) seconds. Because of this, it is safe to shut down the master, and then restart it after a while. The slave will also be able to deal with network connectivity outages. * 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 synced before the system died. Your system fault tolerance will be greatly increased if you have a good UPS. * If the master is listening on a non-standard port, you will also need to specify this with `master-port' parameter in `my.cnf' . * In Version 3.23.15, all of the tables and databases will be replicated. Starting in Version 3.23.16, you can restrict replication to a set of databases with `replicate-do-db' directives in `my.cnf' or just exclude a set of databases with `replicate-ignore-db'. Note that up until Version 3.23.23, there was a bug that did not properly deal with `LOAD DATA INFILE' if you did it in a database that was excluded from replication. * Starting in Version 3.23.16, `SET SQL_LOG_BIN = 0' will turn off replication (binary) logging on the master, and `SET SQL_LOG_BIN = 1' will turn in back on - you must have the process privilege to do this. * Starting in Version 3.23.19, you can clean up stale replication leftovers when something goes wrong and you want a clean start with `FLUSH MASTER' and `FLUSH SLAVE' commands. In Version 3.23.26 we have renamed them to `RESET MASTER' and `RESET SLAVE' respectively to clarify what they do. The old `FLUSH' variants still work, though, for compatibility. * Starting in Version 3.23.21, you can use `LOAD TABLE FROM MASTER' for network backup and to set up replication initially. We have recently received a number of bug reports concerning it that we are investigating, so we recommend that you use it only in testing until we make it more stable. * Starting in Version 3.23.23, you can change masters and adjust log position with `CHANGE MASTER TO'. * Starting in Version 3.23.23, you tell the master that updates in certain databases should not be logged to the binary log with `binlog-ignore-db'. * Starting in Version 3.23.26, you can use `replicate-rewrite-db' to tell the slave to apply updates from one database on the master to the one with a different name on the slave. * Starting in Version 3.23.28, you can use `PURGE MASTER LOGS TO 'log-name'' to get rid of old logs while the slave is running. automatically generated by info2www version 1.2.2.9 |