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
.
Believe it or not, I once wrote a post about why you should not do that (How can I disable utf8mb4 entirely on MySQL 5.5?). However, in the spirit of my old post and the commentary in it from @ChristopherSchultz, I will go out on a limb and tell you how you can do it, then tell you why you should not.
I once wrote a post about the home position of any empty binary log:
Over the years in this forum, I learned from someone (I think it was either Aaron Brown or Morgan Tocker) that there is a universal position for all binary logs regardless of the MySQL Version: position 4.
I once put that in an answer (Mar 05, 2013
: MySQL Replication without stopping master). In Step 06 from my answer I wrote this:
CHANGE MASTER TO
MASTER_HOST='10.1.20.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;
I also used position 4 in these other posts
Rarely do I repeat this info in any other posts for a reason. Personally, I fear that binlog events might be represented differently from version to version in terms of the size (in bytes) of each event. Believe it or not, over the past two weeks I have been upgrading DB Servers from MySQL 5.5. to MySQL 5.6. Due to mixed mode binary logging, there have been rare events when replication breaks and you cannot reset it from binlog files and positions by standard replication techniques. I have had to hose binary logs on Master, copy data, and setup replication from scratch a few times (5 out 400 VMs, but it still happened 5 times). I am very sure that replicating from a new Master to an old Slave would cause many more problems along these lines.
Therefore, I can only say that you can do it theoretically and MySQL may not object, that is, until MySQL Replication encounters a binlog event that is in a format it does not recognize and cannot interpret.
UPDATE 2014-11-18 22:32 EST
Just for official reference, this example CHANGE MASTER TO command
CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
appears in the MySQL 5.6 Documentation. It's also in the MySQL 4.1 Documentation.
Thus, position 4 has always been known (I have only known a couple of years). Notwithstanding, I trust MySQL Replication from old Master to new Slave (but not on a permanent basis). I do not trust MySQL Replication from new Master to old Slave.
UPDATE 2014-11-19 17:47 EST
Please don't go down the Circular Replication path as it just adds to the risk of lost binlog events due to different versions. You should always replicate one direction to a newer version. Then, just failover to the newer version.
Best Answer
As you already mentioned documentation that you cannot use table level filtering on replication. You might like to try another approach instead. Replicate whole database and change all tables except those you need to use to
BLACKHOLE
engine.alter table X engine=BLACKHOLE;