MySQL Master-Slave Configuration: Don't Replicate a Row of a Table?

Neo · October 9, 2010, 11:23am

Anyone have a clue about this?

I have checked the MySQL documentation and it does not seem possible to exclude a row of a table from replication between Master and Slave. It seems that replication in MySQL can only be managed at the table level, not at the row level.

Does anyone know a work around for this with MySQL?

I have a table on configuration information, including options, and some of the options in the the table cannot replicate, because the configuration options are unique to the Master and Slave.

Thanks!

radoulov · October 9, 2010, 11:41am

Neo,
I assume these options are static so you could take a dump of the production db (use --master-data), then import the data in the slave, update the rows on the slave with the proper configuration and then start the replication. As far as those rows remain untouched on the master, the slave configuration will remain valid.

Neo · October 9, 2010, 11:58am

Thanks!

That is exactly what I've been doing (manually). The part I wish to avoid is editing the slave each time I upload the masterdump data into the slave.

In addition to the static data, some data is dynamic, and I want the data on the slave and master to remain unique to each other (secondary requirement, not a show stopper).

For example, when replicating this vB forum, static information is, for example, the URL of the forum (they are different in the test environment), and dynamic information is the number of users on the site. This information comes from a table called "datastore" which contains rows with various static configuration / option data and dynamic data, like who is on the site, spiders, number of users on line, etc.

If I replicate the entire datastore table, I have problem when I turn off vB plugins on the slave that remain on on the master. Yes, I can work around by manually updating the configuration on the slave each time; but then if I turn off a plugin (or turn on a dormant one) on the master, it will update the slave, which I don't want.

That's why I was hoping there was some way to just exclude some rows in the datastore table from replicating.

Also, I thought about perhaps instructing the slave to permanently lock the rows so they can't be effected by uploading a dump or master replication.

Maybe that will work?

radoulov · October 9, 2010, 12:13pm

I don't think locking will work, at first place, it will break the replication (or make it very difficult).

Just an idea: it could be possible to create a trigger on the slave with after update set the old values for those rows.

Neo · October 9, 2010, 12:45pm

Anyway, we are currently using MyISAM DB which only has table-level locking (as I just remembered). INNODB has row-level locking (but we are using MyISAM because it seems to perform better for our app).

I'll look into MySQL DB triggers. That sounds like a good avenue to explore, thanks!

---------- Post updated at 16:41 ---------- Previous update was at 16:17 ----------

According to the MySQL 5.1 docs on triggers:

I assume Master-Slave replication uses SQL statements? I tried to search and have yet to find an answer. Actually, I thought replication might not use SQL statements and used some type of API mechanism. If so, the triggers on the slave based on replication updates would not work. On the other hand, if the replication mechanism uses SQL, then triggers could work.

I will keep searching for the answer; but so far no luck.

radoulov?

---------- Post updated at 16:45 ---------- Previous update was at 16:41 ----------

I think I found it !!

According to 16.1.2. Replication Formats:

Replication works because events written to the binary log are read from the master and then processed on the slave. The events are recorded within the binary log in different formats according to the type of event. The different replication formats used correspond to the binary logging format used when the events were recorded in the master's binary log. The correlation between binary logging formats and the terms used during replication are:

*Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication (often abbreviated as SBR), which corresponds to the standard statement-based binary logging format. In MySQL 5.1.4 and earlier, binary logging and replication used this format exclusively.

*Row-based binary logging logs changes in individual table rows. When used with MySQL replication, this is known as row-based replication (often abbreviated as RBR). In row-based replication, the master writes events to the binary log that indicate how individual table rows are changed.

*As of MySQL 5.1.8, the server can change the binary logging format in real time according to the type of event using mixed-format logging.

When the mixed format is in effect, statement-based logging is used by default, but automatically switches to row-based logging in particular cases as described later. Replication using the mixed format is often referred to as mixed-based replication or mixed-format replication. For more information, see Section 5.2.4.3, �Mixed Binary Logging Format�.

Hmmm..... Lot's to think about here, especially since the slave is running:

16.1.2.1. Comparison of Statement-Based and Row-Based Replication

radoulov · October 9, 2010, 1:56pm

This is from the documentation of 5.0 (and I believe it's valid for 5.1 as well):

Replication and triggers should work as expected, I'm not sure if updating the same rows via statement and triggers on the same table (and at the same time) works though ...