I agree with Alex but this just follows logical design of separation.
You dev, qa, prod, ha, and dr environments should be separated and
used for those set purposes. Unfortunately I also know that if this
most elementary step has not been take it is usually due to a lack of
required resources not simple oversight. But even if you do that
should not replace the monitoring scripts or, as you mentioned, the
commitment to repeatable and documented automated operations.
BTW Alex if all you want to do is verify you are in sync you can do a
checksum on the tables in both servers. If they are in sync the
checksums will match - again, great automated monitoring check.
On Sat, Jul 24, 2010 at 10:24 AM, Alex Dean <
alex@crackpot.org> wrote:
>
> 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
>
---------------------------------------------------
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)