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 updates.
The master will hold a set of databases, all of them will be replicated to the slave. The slave will hold the replicated databases plus a set of slave-only databases. 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 set non_replicated_db.table.val=replicated_db.table.val where non_replicated_db.table.val_id=replicated_db.table.val_id; 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 temp table. Does anyone know of a more elegant solution or a solution via privileges? Thanks, Ware Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
