Am 24. Jul, 2010 schwätzte Alex Dean so:

moin moin,

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

That's my goal. First I need to get all of the DBen going to at least one
slave and make sure we've backing up everything that needs to be backed
up.

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

This is per DB? If db1 is a slave DB, I can set it as read-only, but keep
db2, which is a local DB, as read-write?

> 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

I still want the replicated parts to be read-only. Yes, I want reporting
environments seperate from production slaves.

> 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.)

Great idea. This will be the hardest to push through, but I think we can
do it.

> 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.

I'll have to think about this one. I see your point and agree with it. I
might instead tackle it via audits. I like audits for the potential of
catching slow corruption. Might do both :).

ciao,

der.hans
-- 
#  http://www.LuftHans.com/Classes        http://www.TwoGeekTechs.com/
#  Director of Engineering, FonWallet Transaction Solutions, Inc.
#  Fairy Tale, n.: A horror story to prepare children for the newspapers.