MySQL in a Nutshell, 2nd Edition

Chapter 8. Replication Statements and Functions

This chapter includes a tutorial on setting up and using replication, a list of SQL statements and functions used specifically with replication, and an explanation of replication states that will be useful for checking whether replication is operating as needed. The replication SQL statements and functions covered in this chapter are:

CHANGE MASTER TO, LOAD DATA FROM MASTER, LOAD TABLE...FROM MASTER, MASTER_POS_WAIT(), PURGE MASTER LOGS, RESET MASTER, RESET SLAVE, SET GLOBAL SQL_SLAVE_SKIP_COUNTER, SET SQL_LOG_BIN, SHOW BINARY LOGS,SHOW MASTER LOGS, SHOW BINLOG EVENTS, SHOW MASTER STATUS, SHOW SLAVE HOSTS, SHOW SLAVE STATUS, START SLAVE, STOP SLAVE.

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 ...