Visit Our UNIX and Linux User Community

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

Thread Tools Search this Thread
Top Forums Web Development MySQL Master-Slave Configuration: Don't Replicate a Row of a Table?
# 1  
Old 10-09-2010
MySQL Master-Slave Configuration: Don't Replicate a Row of a Table?

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.

# 2  
Old 10-09-2010
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.
This User Gave Thanks to radoulov For This Post:
# 3  
Old 10-09-2010
Originally Posted by radoulov
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.

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?
# 4  
Old 10-09-2010
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.
This User Gave Thanks to radoulov For This Post:
# 5  
Old 10-09-2010
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:


MySQL triggers are activated by SQL statements only. They are not activated by changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL Server
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.


---------- 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, “Mixed Binary Logging Format”.
Hmmm..... Lot's to think about here, especially since the slave is running:

mysql Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (x86_64) Comparison of Statement-Based and Row-Based Replication
# 6  
Old 10-09-2010
This is from the documentation of 5.0 (and I believe it's valid for 5.1 as well):

23.5.9: Do triggers work with replication? Triggers and replication in MySQL 5.0 work in the same way as in most other database systems: Actions carried out through triggers on a master are not replicated to a slave server. Instead, triggers that exist on tables that reside on a MySQL master server need to be created on the corresponding tables on any MySQL slave servers so that the triggers activate on the slaves as well as the master.
For more information, see Section, “Replication and Triggers”.
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 ...

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. IP Networking

DNS question about initial Master/Slave setup

Hey everyone. I'm creating a DNS master/slave server set up. I have the configurations all done I believe, the master has the required zone file, and the named.conf file has the allow transfer and allow query stuff set. The slave has it's own configs set. My question is that when initially... (1 Reply)
Discussion started by: Lost in Cyberia
1 Replies

2. Programming

How to wait the slave to be finished first then execute the master--MPI C++?

Hi, How to wait the slave to be finished first then execute the master? Can someone give me the specific function? Or the detailed example. Thanks~ (1 Reply)
Discussion started by: wanliushao
1 Replies

3. UNIX Desktop Questions & Answers

How can I replicate master master and master master MySQL databse replication and HA?

I have an application desigend in PHP and MySQl running on apache web server that I is running on a Amazon EC2 server Centos. I want to implement the master-master and master slave replication and high availability disaster recovery on this application database. For this I have created two... (0 Replies)
Discussion started by: Palak Sharma
0 Replies

4. Programming

Asynchronous communication between master and slave threads

I am writing a process that has a master thread and a set of slave threads. Master thread is supposed to get jobs dynamically and assign to slave thread which is free. Master also get results back from slaves once a job is done. The number of slaves should be adjustable dynamically based on job... (1 Reply)
Discussion started by: tamil.pamaran
1 Replies

5. Shell Programming and Scripting

mysql how to select a specific row from a table

i have a table records ------------ id | user | time | event 91 admin | 12:00 | hi 92 admin | 11:00 | hi 93 admin | 12:00 | bye 94 admin | 13:00 | bye 95 root | 12:00 | hi 96 root | 12:30 | hi 97 root | 12:56 | hi how could i only select and display only the user and event from... (6 Replies)
Discussion started by: kpddong
6 Replies

6. AIX

Slave NIS server configuration change

Hello Everybody, I have a question regarding SLAVE NIS SERVER in aix. We are using NIS master of Sun Solaris 9.0 which is on different subnet i.e. And Our slave server is having AIX 5.3 installed which is on subnet. I have a query regarding its name and ip address... (0 Replies)
Discussion started by: jit15975
0 Replies

7. SCO

master and slave in lan network

hello , i setup a lan network , but i don't know how configure master and slave in the lan network please help me:confused: (2 Replies)
Discussion started by: hossein
2 Replies

8. UNIX for Advanced & Expert Users

NIS master / slave problems

Our NIS master server went down. We have since fixed it and brought it back up. However all of are machines still point to the slave server when looking at it with ypwhich. My question is how do i point the servers back to the master. Frank (2 Replies)
Discussion started by: frankkahle
2 Replies

Featured Tech Videos