- 19.3 Replication Notes and Tips
- 19.3.1 Replication Features and Issues
- 19.3.1.1 Replication and AUTO_INCREMENT
- 19.3.1.2 Replication and Character Sets
- 19.3.1.3 Replication of CREATE TABLE ... SELECT Statements
- 19.3.1.4 Replication and DIRECTORY Statements
- 19.3.1.5 Replication of Invoked Features
- 19.3.1.6 Replication with Floating-Point Values
- 19.3.1.7 Replication and FLUSH
- 19.3.1.8 Replication and System Functions
- 19.3.1.9 Replication and LIMIT
- 19.3.1.10 Replication and LOAD DATA
- 19.3.1.11 Replication During a Master Crash
- 19.3.1.12 Replication During a Master Shutdown
- 19.3.1.13 Replication with MEMORY Tables
- 19.3.1.14 Replication of the System mysql Database
- 19.3.1.15 Replication and Reserved Words
- 19.3.1.16 Slave Errors during Replication
- 19.3.1.17 Replication during a Slave Shutdown
- 19.3.1.18 Replication and Temporary Tables
- 19.3.1.19 Replication Retries and Timeouts
- 19.3.1.20 Replication and Time Zones
- 19.3.1.21 Replication and Transactions
- 19.3.1.22 Replication with Differing Tables on Master and Slave
- 19.3.1.23 Replication and Variables
- 19.3.1.24 Replication and Views
- 19.3.2 Replication Compatibility Between MySQL Versions
- 19.3.3 Upgrading a Replication Setup
- 19.3.4 Replication FAQ
- 19.3.5 Troubleshooting Replication
- 19.3.6 How to Report Replication Bugs or Problems
[+/-]
- 19.3.1.1. Replication and
AUTO_INCREMENT - 19.3.1.2. Replication and Character Sets
- 19.3.1.3. Replication of
CREATE TABLE ... SELECTStatements - 19.3.1.4. Replication and
DIRECTORYStatements - 19.3.1.5. Replication of Invoked Features
- 19.3.1.6. Replication with Floating-Point Values
- 19.3.1.7. Replication and
FLUSH - 19.3.1.8. Replication and System Functions
- 19.3.1.9. Replication and
LIMIT - 19.3.1.10. Replication and
LOAD DATA - 19.3.1.11. Replication During a Master Crash
- 19.3.1.12. Replication During a Master Shutdown
- 19.3.1.13. Replication with
MEMORYTables - 19.3.1.14. Replication of the System
mysqlDatabase - 19.3.1.15. Replication and Reserved Words
- 19.3.1.16. Slave Errors during Replication
- 19.3.1.17. Replication during a Slave Shutdown
- 19.3.1.18. Replication and Temporary Tables
- 19.3.1.19. Replication Retries and Timeouts
- 19.3.1.20. Replication and Time Zones
- 19.3.1.21. Replication and Transactions
- 19.3.1.22. Replication with Differing Tables on Master and Slave
- 19.3.1.23. Replication and Variables
- 19.3.1.24. Replication and Views
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.1 to
5.0. 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.1 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 sections provide details about what is supported and
what is not, and about specific issues and situations that may
occur when replicating certain statements. Additional information
specific to InnoDB and replication is given in
Section 13.6.6.5, “InnoDB and MySQL Replication”. For information
relating to replication with MySQL Cluster, see
MySQL Cluster 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 instead. For a detailed list of issues, see Section 22.4, “Binary Logging of Stored Routines and Triggers”. For a description of row-based replication, see Section 19.1.2, “Replication Formats”.
User Comments
FWI. I just spent several hours trying to figure out why my two servers in chained replication A -> B -> A were not replicateing correctly when last week they were. After much agonizing i determined that only tables that had a timestamp colum where effected. It turned out one of the servers times had fallen behind by almost an hour. When that server issued an update the other server was ignoring the request (i can only assume because it figured that record had an older timestamp and therefore ignored it.) While this is probably the best behaviour it can catch you unaware so i thought i would put it up here to help anyone else in a similar situation. BTW I was using MS Access to access the database so it could have been a problem in Access, MyODBC or MySQL, I'm not entirely sure.
CONNECTION_ID() is replicated correctly in statement-based replication, even though on the slave everything is run from a single connection. Each binlog entry contains the thread_id which ran the query, so this can be reliably recreated on the slave.
Do not use LOAD DATA FROM MASTER if your master is 4.1 and slave 5.0 and your tables use DECIMAL. The data is hopelessly currupted.
I guess this is implied in the incompatibilites but I have not seen it stated specifically.
I am testing prior to doing this live but have decided not to make use of LOAD DATA FROM MASTER at all in case there are other such problems.
Be aware that in current server versions (5.0.45), replication will fail if you use the NO_BACKSLASH_ESCAPES on a 'global' configuration (like in my.cnf).
See bug http://bugs.mysql.com/bug.php?id=27552
I use deleting requests from slave in one of my projects to save requests history on master. So I noticed that INSERT IGNORE query with affected rows 0 wouldn't be replicated.
So if you want to get asynchronous data on slave use INSERT ON DUPLICATE KEY UPDATE or REPLACE.
Add your own comment.