MySQL Forums
Forum List  »  Replication

Re: Is limited replication by excluding columns possible?
Posted by: Sergey Petrunya
Date: September 05, 2005 05:25PM

Erling Westenvik wrote:
> Case:
>
> We have a backend MySQL 4.0.20 server with several
> millions of records in about 50 tables. The number
> of records increase in the 10 to 100k per day. On
> another location we are going to have a frontend
> 4.1.10a server that should have most of the
> tables, all of the records from those tables, but
> - and this is for security reasons - not all the
> columns. I guess the whole thing boils down to:
>
> Question:
>
> If the slave user is granted access only to the
> columns we would like it to access, will it then
> create what it can and go on replicating this
> correct?
>
> Possible answers:
>
> [ ] Yes, as long as columns required for index
> definitions are accessible.
> [x] No, the master binlog would make the slave
> flip.
MySQL uses statement-based replication in version 4.x. That means that master writes the update queries into binary log, and slave executes them. Suppose some column C is present in the table on the master but is absent on the slave. Suppose then you run some data-updating query on the master that uses column C. Column C is not present on the slave, so the query will fail on the slave and replication will stop.

Also you have a potential security breach on the slave because slave sees secret data in the binary logs.

If you need this really badly, and you can separate statements that use/update secret data from all other statements, you could have different table definitions on master and slave and update data like this:

INSERT INTO tbl (public_data) VALUES (....) -- no secret data, gets written to binlog
SET SQL_LOG_BIN=0;
UPDATE tbl SET secret_data = '...' WHERE ... -- has secret data, is not written to binlog
SET SQL_LOG_BIN=1;

This solution is of course very fragile and requires great care on the master in order not to leak secret data.

The only other option I see is to syncrhonize data manually.

Sergey Petrunia, Software Developer
MySQL AB, www.mysql.com
My blog: http://s.petrunia.net/blog

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Is limited replication by excluding columns possible?
2169
September 05, 2005 05:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.