Replication - High Performance MySQL - page 478

The simplest way to do this is to partition the data into different databases on the
master, and then replicate each database to a different replica server. For example, if
you want to replicate data for each department in your company to a different replica,
you can create databases called sales , marketing , procurement , and so on. Each replica
should then have a replicate_wild_do_table configuration option that limits its data
to the given database. Here's the configuration option for the sales database:
replicate_wild_do_table = sales.%
Filtering with a distribution master is also useful. For example, if you want to replicate
just part of a heavily loaded server across a slow or very expensive network, you can
use a local distribution master with Blackhole tables and filtering rules. The distribution
master can have replication filters that remove undesired entries from its logs. This can
help avoid dangerous logging settings on the master, and it doesn't require you to
transfer all the logs across the network to the remote replicas.
Separating functions
Many applications have a mixture of online transaction processing (OLTP) and online
analytical processing (OLAP) queries. OLTP queries tend to be short and transactional.
OLAP queries are usually large and slow and don't require absolutely up-to-date data.
The two types of queries also place very different stresses on the server. Thus, they
perform best on servers that are configured differently and perhaps even use different
storage engines and hardware.
A common solution to this problem is to replicate the OLTP server's data to replicas
specifically designed for the OLAP workload. These replicas can have different hard-
ware, configurations, indexes, and/or storage engines. If you dedicate a replica to OLAP
queries, you might also be able to tolerate more replication lag or otherwise degraded
quality of service on that replica. That might mean you can use it for tasks that would
result in unacceptable performance on a nondedicated replica, such as executing very
long-running queries.
No special replication setup is required, although you might choose to omit some of
the data from the master if you'll achieve significant savings by not having it on the
replica. Filtering out even a small amount of data with replication filters on the relay
log might help reduce I/O and cache activity.
Data archiving
You can archive data on a replica server—that is, keep it on the replica but remove it
from the master—by running delete queries on the master and ensuring that those
queries don't execute on the replica. There are two common ways to do this: one is to
selectively disable binary logging on the master, and the other is to use replicate
_ignore_db rules on the replica. (Yes, both are dangerous.)