Understanding Replication

Replication works as follows: The slave starts with an exact copy of the data on the master. Binary logging is then enabled on the master, and the slave connects to the master periodically and views the binary log to see what changes have been made since the last time it connected. The slave will then automatically repeat these statements on its server. The master.info file on the slave allows it to keep track of which point it is at on the master binary log. The relation between the master binary log and the slave master.info file is important; if these two fall out of sync, the data may no longer be identical on both servers. Replication can be useful as a form of backup (against disk error, not human error) and also to speed up performance. It is a practical way of running multiple databases, particularly in an environment where SELECT statements far outnumber INSERT or UPDATE statements. (The slaves can then be optimized entirely for SELECT statements, and the master can handle the INSERT and UPDATE statements.)

Replication is not a solution to all performance problems, though. Updates still need to be repeated on the slave, and although they are done more optimally, if your MyISAM tables are performing too many updates and becoming locked too frequently, then converting them to InnoDB may be a better solution than hoping replication will solve the problem. Also, there will be a lag between the updates being replicated on the slaves, the length of which is dependent upon your network capacity and the database servers themselves. So, it is not possible for your application to simply assume it can use the master or a slave as a database server. A record on the master may not immediately appear on the slave, which could cause problems for an application.

Replication is normally performed in a hierarchical manner (as shown in Figures 12.1 and 12.2), but you can set up circular structures (as shown in Figures 12.3 and 12.4). Your client code needs to make sure there are no conflicts, though; otherwise, replication can easily fail because of data irregularities, so Figures 12.3 and 12.4 are much less common structures.

Figure 12.1: One master, one slave

Figure 12.2: One master, many slaves

Figure 12.3: Master/slave circular relationship

Figure 12.4: Master/slave "daisy chain"

There is no need for the connection to be continuous. If the link is broken for whatever reason, the slave will keep trying to reconnect and will start updating from where it left off as soon as the link is restored.