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
---------------------------------------------------
PLUG-discuss mailing list -
PLUG-discuss@lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
(text/plain)