Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 (such as load balancing), but the concern of this tutorial is using replication for data backups and resiliency. Also, it’s easy to set up multiple slaves for each server, but one is probably enough if you’re using replication only for backups.

As a backup method, you can set up a separate server to be a slave, and then once a day (or however often you prefer) turn off replication to make a clean backup of the slave server’s databases. When you’re finished making the backup, replication can then be restarted and the slave will automatically query the master for changes to the master’s data that the slave missed while it was offline.

Replication is an excellent feature built into the MySQL core. It doesn’t require you to buy or install any additional software. You just physically set up a slave server and configure MySQL on both servers appropriately to begin replication. Then it’s a matter of developing a script to routinely stop the replication process, make a backup of the slave’s data, and restart replication.

To understand how to make replication efficient and robust in a particular environment, let’s look in detail at the steps that MySQL goes through to maintain a replicated server. The process is different depending on the version of MySQL your servers are using. This chapter applies primarily to version 4.0 or higher of MySQL. There were some significant improvements made in version 4.0 related to how replication activities are processed, making it much more dependable. Therefore, it is recommended that you upgrade your servers if they are using an older version. You should upgrade one release at a time, and use the same version of MySQL on both the master and all the slave servers. Otherwise, you may experience problems with authenticating the servers, incompatible table schemas, and other such problems.