In this article, Vikram gives us a sneak-peek under the hood of MySQL to see what makes it tick, all the while explaining the various MySQL subsystems and how they interact with each other. This excerpt comes from Chapter two of MySQL: The Complete Reference (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004).
Replication is a data distribution mechanism that allows you to place copies of tables and databases in remote locations so that users can more easily access them.
Product databases are typical of such replication mechanisms—for example, a national or global company might have a common product database that is updated centrally but that is used locally by each office. Rather than forcing applications to query this table remotely every time it’s needed, it is more cost effective to distribute a copy to everyone, thus incurring the transmission overhead only once for each office.
Prior to release 3.23.15, MySQL had no replication capabilities; subsequent to this release, and continuing to the present day, MySQL supports one-way replication, with one database as the master and one or more databases as the slaves.
The replication mechanism relies on a MySQL log that tracks all changes made to a database. The master ships this log to the slave, which then applies the log to its own data. Nothing about a slave prevents users from updating it outside the context of replication, so great care must be taken to ensure that this doesn’t happen, since the relative assurance of synchronization will otherwise be lost and subsequent log playbacks might even fail.
Remember: this is chapter two of MySQL: The Complete Reference, by Vikram Vaswani (McGraw-Hill/Osborne, ISBN 0-07-222477-0, 2004). Vikram is the founder of Melonfire, and has had numerous articles featured on Dev Shed. Buy this book now.