ChapTEr 8 ■ DEmonsTraTion of high availabiliTy TEChniquEs

What I DON’t WaNt CertaIN Data repLICateD?

sometimes there may be data you do not want replicated. in this case, you can use the replication filters

built into mysql to tell the server to not replicate certain data. There are filters available on the master

(binary log options) and the slaves (replication options). They can be inclusive or exclusive.

setting a filter on the master ensures the data matching the filter is not saved in the binary log and

thus never transmitted to the slaves. setting a filter on the slave ensures that, while the data is

being transmitted, it is discarded on the slave. Think of it this way, if you want some data to never be

replicated, use the master filters. if you want one or more slaves but not all to not get the data, use

slave filters.

binary log filters include --binlog-do-db and --binlog-ignore-db. replication filters include

--replicate-do-db, --replicate-ignore-db, --replicate-do-table, and --replicate-ignore-

table. see the online mysql reference manual for more information about setting and maintaining the

filters along with pitfalls for using them (http://dev.mysql.com/doc/refman/5.7/en/replication-

options-binary-log.html) and (http://dev.mysql.com/doc/refman/5.7/en/replication-

options-slave.html).

Finally, the code will be written as a class that you can place in your library and include in any client
code you want. For the purposes of demonstrating the code, I will include test code, but you can remove this
and use the class in a library. The class is named select_read_server and is written in Python but could
easily be written in whatever language you choose.

Write the Code

The class needs only a single public method named get_next_server() that returns a dictionary of the
server ID, host, and port. There are a number of helper methods needed, which will be made private
(designated by starting with an underscore character in the name).
We need a method to connect to the master and retrieve a list of all the slaves and store that information
in memory. We name this method _get_server_list(). Next, we need a method to record the server
ID of the slave chosen so that any other clients can read it. We name this method _set_server_id().
Recall we will use the database to store the server ID chosen and will use locking to prevent other clients
from interrupting the update to the table. Thus, _set_server_id() must execute the locking mechanism
described earlier. We also need a method to read the server ID from the table. We name this method get
current_server_id(). Finally, we include a simple loop to demonstrate how to use the class to retrieve the
information for the next slave. Listing 8-4 shows the complete code.

Listing 8-4. Simple Read Scaling Server Selector

Demonstration of a simple round robin read scaling slave selector

Use this class in your own application to choose the next slave

in the topology.

Note: you must have a database and table setup on the master as

follows: