You are here: Home » Network » Administration » Replication of MySQL database [part 4] – Replication of the table

Replication of MySQL database [part 4] – Replication of the table

Last time, I wrote about mechanism for replicating of the database. It is simple. Just several commands and our database server make an automatic replication. We have seen also that a replication mechanism is very important and useful. In combination with binlog it allows us to create a database backup machine. It is also possible to precede with other databases e.g. PostgreSQL, MariaDB, MsSQL etc.  A lot of database servers have got a replication.

It is also interesting idea to replicate only a particular table. Of course, it is possible, as I said in the previous article. Sometimes, administrator would not like to replicate all database, because it has got only one or two important tables… the rest is, for example, worthless cache or logs. Replication of such a table would be a waste of time, space and bandwidth. MySQL mechanism provides the ability to replicate the selected table, database, or exclude it from replication process. They correspond to the following parameters:

   replicate-do-db
   replicate-ignore-db
   replicate-do-table
   replicate-ignore-table
   replicate-wild-do-table
   replicate-wild-ignore-table
   binlog-do-db
   binlog-ignore-db

In theory, they allow for filtering of what is replicated. We can replicate or not the selected table, database and several tables by wildcards. Filter rules are set in binlog. Of course, we can define which data can find one’s way to the file. It is the responsibility: binlog-do-db, binlog-ignore-db. Unfortunately, it is simple only in the theory. In practice, it is no longer so sweet and colorful, though at first glance it seemed. Try to see situation like this: we would not like to replicate database called example, so we should put option replication-ignore-db. Let’s try to do it:

 replication-ignore-db=example

It means, that we do not want to replicate example database. So, let’s try to analyze these queries:

USE test;
UPDATE example.name SET id=id+10;

These queries will be replicated. Why? Well, MySQL filters take into account only an active database set by the USE command. Thus, if I refer to the database using the syntax [database].[table “” not found /]
, then the MySQL filter will not pick this appeal and replicate request. It is not wise, but it is a standard mechanism of the MySQL database. Therefore, it is not recommended to use this type of functionality, if we are not sure what search queries go to the database. You may find that nicely configured replication will suddenly mess by this type of problem. Of course, replication can create problems like every mechanism, so administrator should deal with any failure. In the next article I will show you, how to do it and how to fix your replication.

Comments

comments