- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 15.3 Replication Notes and Tips
- 15.3.1 Replication Features and Issues
- 15.3.1.1 Replication and AUTO_INCREMENT
- 15.3.1.2 Replication and Character Sets
- 15.3.1.3 Replication DIRECTORY Statements
- 15.3.1.4 Replication with Floating-Point Values
- 15.3.1.5 Replication and FLUSH
- 15.3.1.6 Replication and Functions
- 15.3.1.7 Replication and LOAD ... Operations
- 15.3.1.8 Replication During a Master Crash
- 15.3.1.9 Replication During a Master Shutdown
- 15.3.1.10 Replication with MEMORY Tables
- 15.3.1.11 Replication and the Query Optimizer
- 15.3.1.12 Replication and Reserved Words
- 15.3.1.13 Slave Errors during Replication
- 15.3.1.14 Replication during a Slave Shutdown
- 15.3.1.15 Replication and Temporary Tables
- 15.3.1.16 Replication Retries and Timeouts
- 15.3.1.17 Replication and Time Zones
- 15.3.1.18 Replication and Transactions
- 15.3.1.19 Replication and Triggers
- 15.3.1.20 Replication and User Privileges
- 15.3.1.21 Replication and Variables
- 15.3.1.22 Replication and Views
- 15.3.2 Replication Compatibility Between MySQL Versions
- 15.3.3 Upgrading a Replication Setup
- 15.3.4 Replication FAQ
- 15.3.5 Troubleshooting Replication
- 15.3.6 How to Report Replication Bugs or Problems
[+/-]
- 15.3.1.1. Replication and
AUTO_INCREMENT - 15.3.1.2. Replication and Character Sets
- 15.3.1.3. Replication
DIRECTORYStatements - 15.3.1.4. Replication with Floating-Point Values
- 15.3.1.5. Replication and
FLUSH - 15.3.1.6. Replication and Functions
- 15.3.1.7. Replication and
LOAD ...Operations - 15.3.1.8. Replication During a Master Crash
- 15.3.1.9. Replication During a Master Shutdown
- 15.3.1.10. Replication with
MEMORYTables - 15.3.1.11. Replication and the Query Optimizer
- 15.3.1.12. Replication and Reserved Words
- 15.3.1.13. Slave Errors during Replication
- 15.3.1.14. Replication during a Slave Shutdown
- 15.3.1.15. Replication and Temporary Tables
- 15.3.1.16. Replication Retries and Timeouts
- 15.3.1.17. Replication and Time Zones
- 15.3.1.18. Replication and Transactions
- 15.3.1.19. Replication and Triggers
- 15.3.1.20. Replication and User Privileges
- 15.3.1.21. Replication and Variables
- 15.3.1.22. 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.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 13.2.6.5, “InnoDB and MySQL Replication”.
Replication issues with regard to stored routines and triggers is described in Section 18.5, “Binary Logging of Stored Routines and Triggers”.
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.