Replication - High Performance MySQL - page 466

without knowing where they originated? Why does MySQL care whether the server ID
is globally unique? The answer to this question lies in how MySQL prevents an infinite
loop in replication. When the replication SQL thread reads the relay log, it discards
any event whose server ID matches its own. This breaks infinite loops in replication.
Preventing infinite loops is important for some of the more useful replication topolo-
gies, such as master-master replication. 5
If you're having trouble getting replication set up, the server ID is one
of the things you should check. It's not enough to just inspect the
@@server_id variable. It has a default value, but replication won't work
unless it's explicitly set, either in my.cnf or via a SET command. If you
use a SET command, be sure you update the configuration file too, or
your settings won't survive a server restart.
Replication Filters
Replication filtering options let you replicate just part of a server's data, which is much
less of a good thing than you might think. There are two kinds of replication filters:
those that filter events out of the binary log on the master, and those that filter events
coming from the relay log on the replica. Figure 10-3 illustrates the two types.
Figure 10-3. Replication filtering options
The options that control binary log filtering are binlog_do_db and binlog_ignore_db .
You should not enable these, as we'll explain in a moment, unless you think you'll enjoy
explaining to your boss why the data is gone permanently and can't be recovered.
5. Statements running around in infinite loops are also one of the many joys of multi-server ring replication
topologies, which we'll show later. Avoid ring replication like the plague.