Setting up automatically updated slaves of a selection of tables based on a SQL dump

Often you might not need to replicate everything, but only a subset of tables in a database. MySQL allows exercising fine-grained control over what to replicate and what to ignore. Unfortunately, the configuration settings are not as obvious as they might seem at first glance.

In this recipe, you will see how to replicate only a few select tables from a database.

Getting ready

The setup for this recipe is the same as for the previous one, Setting up automatically updated slaves of a server based on a SQL dump. Only the configuration options on the slave need to be changed. So instead of repeating everything here, we just present the important differences.

How to do it...

1. Follow the steps of the previous recipe up to the point where the mysqldump tool is used to extract the initial data set from the master. Use this command instead:

   $ mysqldump -uUSER -pPASS --master-data --single-transaction sakila address country city > sakila_master.sql
   

2. Go on with the steps of the previous recipe up to the point where it tells you to edit the slave machine's configuration. Change the configuration as follows instead in the [mysqld] section:

   server-id=1001
   replicate-wild-ignore-table=sakila.%
   replicate-do-table=sakila.address
   replicate-do-table=sakila.country
   replicate-do-table=sakila.city
   

3. Continue with the rest of the instructions as in the Setting up automatically updated slaves of a server based on a SQL dump recipe.

How it works...

The SQL dump file taken on the master is limited to three tables: address, country, and city. The slave's configuration also tells it to only execute statements coming from the master that targets one of these three tables (replicate-do-table directives), while overtly ignoring any other changes in the sakila database (replicate-wild-ignore-table). Even though all other statements are still retrieved from the master and stored temporarily in the relay log files on the slave, only those with modifications to one of the three tables explicitly configured are actually run. The rest are discarded.

You can choose any subset of tables, but you need to make sure to take Foreign key relationships between tables into account. In this example, the address table has a reference to the city table via the city_id column, while city in turn has a relationship with country. If you were to exclude either one of the latter and your storage engine on the slave was InnoDB, replication would break because of Foreign key violations when trying to insert an address, since its dependencies were not fulfilled.

MySQL does not help you in this respect; you must make sure to identify all tables and their relationships manually before setting up the replication.

There's more...

In this example, we clearly specified three tables by their full names. There are more options available, not only to include but also to exclude tables. See the MySQL online manual's Chapter 16.1.3.3 on Replication Slave Options and Variables for more information on these at http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html.