How to auto replicate specific columns only from a table in MySQL

Question

I want to make one master DB which replicates specific user data into other databases such as:

• username
• pass (hashed)
• email
• pin (hashed)
• timezone (int)

They are all in one table and running on MySQL 5.5.37 (possible to update to 5.6).

The reason I want to do this is because I have multiple sites using different databases and they all have different logins. Once I get this done I can enable SSO across them and save more than 50 % of the users a big hassle to not signup more that once on the different sites.

Where should I start? Is there any good info to read? I've been looking in the MySQL help files but could not find any info on how to do this for specific rows.

Answer 1

Why not create one separate db for your SSO, which is accessible for each site? Copying data around will create problems when two sites are editing "the same field".

If you only want to use a copy to set up, you could:

CREATE TABLE newdb.users LIKE otherdb.users;
INSERT newdb.users SELECT * FROM otherdb.users;

If you really want to continuously synchronise tables between databases, you will have to do it with a custom script. Most important to consider in that case, will be the question of what to do when conflicts arise? But I would really consider creating one specific db for SSO.

Answer 2

Short answer: MySQL does not support this feature.

Longer version: The minimum granularity for replication filters is at the table level.

If using only MySQL (and no script in between) you can achieve similar by having a shadow table on the master which you apply triggers to insert into. The resulting trigger changes can then be replicated to the slave in the desired format. Noting that this would require row-based-replication enabled to work, otherwise triggers fire on slaves.

If using a script in between, you can quite easily watch for changes in the replication stream and apply any transformations. I have an example of how to do this here.

Answer 3

I actually managed to do it with engine=FEDERATED to get the syncing done. No need to replicate and all info is up to date. Site specific fields are captured in a seperate table.