16.5 Replication

MySQL supports replication capabilities that allow the contents of databases on one server to be made available on another server. MySQL replication uses a master/slave architecture:

• The server that manages the original databases is the master.

• Any server that manages a copy of the original databases is a slave.

• A given master server can have many slaves, but a slave can have only a single master.

A replication slave is set up initially by transferring an exact copy of the to-be-replicated databases from the master server to the slave server. Thereafter, each replicated database is kept synchronized to the original database. The basis for communication is the master server's binary log:

• When the master server makes modifications to its databases, it records the changes in its binary log files.

• Changes recorded in the binary log are sent to each slave server, which makes the changes to its copy of the replicated databases. Slave servers that aren't connected to the master when a statement is recorded receive the statement the next time they connect.

By default, the master server logs updates for all databases, and the slave server replicates all updates that it receives from the master. For more fine-grained control, it's possible to tell a master which databases to log updates for, and to tell a slave which of those updates that it receives from the master to apply. You can either name databases to be replicated (in which case those not named are ignored), or you can name databases to ignore (in which case those not named are replicated). The master host options are --binlog-do-db and --binlog-ignore-db. The slave host options are --replicate-do-db and --replicate-ignore-db.

The following example illustrates how this works, using the options that enable replication for specific databases. Suppose that a master server has three databases named a, b, and c. You can elect to replicate only databases a and b when you start the master server by placing these options in an option file read by that server:

[mysqld]

binlog-do-db = a

binlog-do-db = b

With those options, the master server will log updates only for the named databases to the binary log. Thus, any slave server that connects to the master will receive information only for databases a and b.

A slave that wants to filter the updates received may do so. A slave that takes no such action will replicate databases a and b. For a slave that should replicate only database a, you can start it with these lines in an option file:

[mysqld]

replicate-do-db = a

For a slave that should replicate only database b, you can start it with these lines in an option file:

[mysqld]

replicate-do-db = b

Note that these options apply only when the given database happens to be the default database. Statements that name a database explicitly are always written to the binary log.