We'd like to set up replication in a simple master/single slave setup, but
I can't figure out a way around an issue we'll have with multi table
The master will hold a set of databases, all of them will be replicated to
The slave will hold the replicated databases plus a set of slave-only
To this point everything works fine.
In order to prevent updates on the replicated tables on the slave, we would
like to set up privileges so that users logging into the slave do not have
the update privilege. In other words, on the slave:
update replicated_db.table set val=10 where id=1;
should fail. This is easy enough to set up by just removing the update
privilege for these databases for our users on the slave machine.
However, we would like users of the slave to be able to perform updates on
their local, non-replicated tables using data from the replicated tables in
a multi-table update statement like this:
update non_replicated_db.table, replicated_db.table
So this query includes the replicated_db in the update statement, but only
actually writes to the non-replicated one. Logically this is OK for our
setup since only the non-replicated table is altered.
However, the users on the slave machine don't have the update privilege for
the replicated_db, so this query fails, presumably b/c the privilege system
looks at all tables included in the update line rather than trying to
figure out which ones are actually changed (which would be a lot more
complex, I understand).
My question is, is there a privilege setup that will make this work? If
not, is there a simple alternative to the multi table update statement?
I've thought of doing a 'replace into' in cases where the update is linked
on the primary key, or we could select out the records that match to a
temporary table, delete from the non-replicated table and read in from the
Does anyone know of a more elegant solution or a solution via privileges?