Database replication

The most general way to do database replication is using the update log. See section The Update log. This requires that one database acts as a master (all data changes are done here) and one or more others as slaves. To update a slave just run mysql < update_log.

If you never do deletes, you can use timestamps.

It is possible to make a two-way updating system using both the update log (for deletes) and timestamps (on both sides). But in that case you must be able to handle confilicts when the same data has been changed in both ends. You probably want to keep the old version to help with deciding what has been updated.

Because replication in this case is done with SQL statements, you should not use the following functions in statements that updates the database, because they may return a different value in the other MySQL server:

• DATABASE()
• GET_LOCK() and RELEASE_LOCK().
• RAND()
• USER(), SYSTEM_USER() or SESSION_USER().
• VERSION()

All time functions are safe to use as the timestamp is sent to the mirror if needed. LAST_INSERT_ID() is also safe to use.

Go to the first, previous, next, last section, table of contents.