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