- Documentation Library
- Table of Contents
- MySQL 5.5 Manual
- MySQL 5.4 Manual
- MySQL 5.1 Manual
- MySQL 5.0 Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
- 14 Replication
- 14.1 Introduction to Replication
- 14.2 Replication Implementation Overview
- 14.3 Replication Implementation Details
- 14.4 How to Set Up Replication
- 14.5 Replication Compatibility Between MySQL Versions
- 14.6 Upgrading a Replication Setup
- 14.7 Replication Features and Known Problems
- 14.8 Replication and Binary Logging Options and Variables
- 14.9 How Servers Evaluate Replication Filtering Rules
- 14.10 Replication FAQ
- 14.11 Troubleshooting Replication
- 14.12 How to Report Replication Bugs or Problems
MySQL replication is based on the master server keeping track of all changes to your databases (updates, deletes, and so on) in its binary logs. Therefore, to use replication, you must enable binary logging on the master server. See Section 5.3.4, “The Binary Log”.
Each slave server receives from the master the saved updates that the master has recorded in its binary log, so that the slave can execute the same updates on its copy of the data.
It is extremely important to realize that the binary log is simply a record starting from the fixed point in time at which you enable binary logging. Any slaves that you set up need copies of the databases on your master as they existed at the moment you enabled binary logging on the master. If you start your slaves with databases that are not in the same state as those on the master when the binary log was started, your slaves are quite likely to fail.
After the slave has been set up with a copy of the master's data,
it connects to the master and waits for updates to process. If the
master fails, or the slave loses connectivity with your master,
the slave keeps trying to connect periodically until it is able to
resume listening for updates. The CHANGE
MASTER TO statement or
--master-connect-retry option
controls the retry interval. The default is 60 seconds.
Each slave keeps track of where it left off when it last read from its master server. The master has no knowledge of how many slaves it has or which ones are up to date at any given time.
User Comments
When setting up replication from one database to another on the same machine I *strongly* recommend compiling MySQL once as the master server and once as the slave server, both with their separate 'localstatedir' and 'port' given in the "configure" command. Then give each its own my.cnf in its respective 'localstatedir'. Finally, make sure either the master's 'bin' is in your PATH if your want to work with master or the slave's 'bin' is in your path if you want to connect to the slave. Otherwise you *will* make an error sooner or later regarding to which server you are connected.
And passing --port on the command line does not help: unexpectedly, it does NOT override the value found in my.cnf.
Good luck!
"After the slave has been set up with a copy of the master's data, it will simply connect to the master and wait for updates to process. If the master goes away or the slave loses connectivity with your master, it will keep trying to connect periodically until it is able to reconnect and resume listening for updates. The retry interval is controlled by the --master-connect-retry option. The default is 60 seconds."
Note the above is not the case in MySQL 4.0.22 -- documentation might need to be updated to clarify which version it is talking about. (FWIW, just had a master die, come back up, and 10 minutes later the slaves still thought they were connected to the first instance.)
For tables other than MyISAM, a work around is to create the table structure on the slave server with ENGINE=FEDERATED, pointing at the server table.
Once it is properly setup, you can alter the table to the engine of your choice, and a local copy will be made. (e.g. ALTER TABLE my_data ENGINE=InnoDB; )
This have to be done on a per table basis, though.
Add your own comment.