Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two MySQL servers; db1 and db2. db1 is production and db2 is for reporting. I have setup replication where db1 is master and db2 is slave. This current setup works fine.

I was given a new requirement to not replicate a field from the USER table because it contains sensitive information.

Here are the steps I implemented:

  • I created a copy of USER, called USER_CLONE that does not have the field to be omitted
  • I set up a stored proc (UPDATE_USER_CLONE) to DELETE FROM USER_CLONE and INSERT INTO USER_CLONE SELECT FROM USER
  • I added the following to my.cnf on db2: replicate-ignore-table = USER replicate-ignore-table = UPDATE_USER_CLONE

When replication starts, I get an error on the INSERT statement because USER does not exist on db2. I understand the error since replication will rerun all the db statements.

What is the right way to set this up so USER is ignored or has a field ignored, or USER_CLONE can be setup to replicate?

Thanks for any advice.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

replicate-ignore-table and the related configuration options are problematic at best. Not because they don't do what they're supposed to do -- because they do -- but rather because they have side effects that require a deeper understanding of MySQL replication.

The problem you have stems from the way the events are replicating. MySQL can replicate table data changes using two different mechanisms, statement-based and row-based, based on the setting of the global configuration variable binlog_format.

STATEMENT -- the actual queries are replicated from server to server, with each server executing the query and modifying its data set.

ROW -- the actual queries are not sent, but instead, tightly-packed row images of the changes actually caused by each query are sent.

MIXED -- the server chooses on a query-by-query basis how to replicate the events.

Importantly, when a query is replicated as a statement event, triggers on the affected tables fire on the slave in order to make the same changes to other tables that would have been made on the master. When a query replicates as a row event, triggers do not fire on the slave. Instead, the master server sends over additional row images from the affected tables so that the additional impacted tables also get updated to match the way they were on the master. The same thing is true when procedures are called -- either the individual statements in the procedure are replicated and have to be valid and execute on the slave (statement-based replication), or the rows changed by the procedure will be replicated (row-based replication).

The scenario you described cannot work unless your master server's binlog_format is set to ROW, because the queries running on the slave have to have access to the "user" table data.

Once that is set, if it were me, I would remove the replicate-ignore-table from the configuration, and instead provision the user table on the slave with the BLACKHOLE storage engine, which discards data sent to it, returning "success" on inserts and 0 rows found/affected on updates and deletes.

However, keep reading, because you are doing this the hard way.

The solution is much simpler: alter the table to move the confidential data to the far right side of the table, and then drop those columns from the slave's copy of the table. No, really:

Source and target tables for replication do not have to be identical. A table on the master can have more or fewer columns than the slave's copy of the table.

You can replicate a table from the master to the slave such that the master and slave copies of the table have differing numbers of columns, subject to the following conditions:

Columns common to both versions of the table must be defined in the same order on the master and the slave. (This is true even if both tables have the same number of columns.)

Columns common to both versions of the table must be defined before any additional columns.

http://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

I have linked to the documentation from MySQL 5.6 since that's the current GA version, but this feature is available as far back as 5.1.21. When doing things this way, you would still be advised to switch your master to binlog_format = ROW.

share|improve this answer
1  
This is the best answer I have ever seen. Thanks. –  analyticsPierce Oct 19 '13 at 4:53

You can't ignore particular field of a table, however you can ignore a particular table or database to get replicated. You can refer these documentation links:

replicate_ignore_table

replicate_ignore_database

In your case you can ignore USER table to get replicated and allow USER_CLONE to get replicated as it has sensitive field ignored.

share|improve this answer
    
Yes, if you look at my question that is what I am doing now. However, when replication runs it tries to INSERT INTO SELECT FROM USER and USER does not exist and then I get an error. Is there a way to force replication to insert the rows to a table instead of pulling the results from SELECT FROM USER? –  analyticsPierce Oct 18 '13 at 14:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.