MySQL in a Nutshell, 2nd Edition

Merits of Replication

One of the difficulties of maintaining a large and active MySQL database is making clean backups without having to bring down the server. Performing a backup while a server is running can slow down a system considerably. Additionally, backups made on active servers can result in inconsistent data because a related table may be changed while another is being copied. Taking down the server ensures consistency of data, but it interrupts MySQL service to users. Sometimes this is necessary and unavoidable, but daily server outages for backing up data may be an unacceptable choice. A simple alternative is to set up replication of MySQL, so that one or more redundant servers maintain a consistent and continuous copy of the main MySQL server’s databases, and can be taken down for backups while the main server continues serving the users.

Typically, replication is primarily a matter of configuring multiple servers to the one where users submit their updates, known in this context as a master server, which houses the data and handles client requests. The server logs all data changes to a binary log, locally. The master in turn informs another MySQL server (a slave server), which contains a copy of the master’s databases, and of any additions to its binary log. The slave in turn makes these same changes to its databases. The slave can either reexecute the master’s SQL statements locally, or just copy over changes to the master’s databases. There are other uses for replication ...