MySQL grant use?

[Alex Dean]

On Jul 23, 2010, at 8:01 PM, der.hans wrote:

> Am 23. Jul, 2010 schwätzte Alex Dean so:
>
>>
>> Hm.  Are users on the slave modifying tables which get replicated  
>> from the master?  That's grandma's recipe for data stew.  You can't  
>> do that if you
>
> Yeah and yeah. I'm going to change that.
>
>> have any interest in data integrity.  If the slave has some tables  
>> which only exist on the slave, that's probably not a huge issue.   
>> If you want to describe the situation in a little more detail we  
>> can help comment further on how bad/not-bad it all is. :)
>
> Engineering is doing some reporting off the slave. To do so, they've  
> been
> mucking with slave data. There was also some temporary restoral of old
> data.
>

I think you should consider replicating to multiple slaves.  1 is for  
backup, and is totally read-only.

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_read_only

Another slave, for reporting, can have more relaxed rules.  If people  
want to mess with the data there, no harm.  If they screw it up too  
horribly, restore a backup onto the reporting database and continue as  
before.  You need to get them to script any changes they're making, so  
they're easy to re-apply after you restore the testing database.  (If  
there are certain columns they add, or aggregating tables they find  
useful, they should be able to run 1 script to re-create them.  This  
removes the "we can't restore, it'll ruin all my custom work!"  
complaint.)

Plan to periodically take a backup from your live database, and use  
that re-init your slaves.  MySQL replication is quite good, but I  
still have seen some odd situations where replication can mess up.   
Since a full live diff of the 2 servers (which would check all data  
and verify you're in sync) isn't really feasible, I think it's safest  
to take backups from the prod server on a somewhat-infrequent basis.

alex