On 18 January 2012 16:51, Mark Robson wrote: > Hi,
> By far the best way of doing this, would be to set up a read-only replica
> (perhaps using MySQL replication) or snapshot of your production database.
> Using DML statements (such as delete, insert, update) is not the only
> possible way of breaking your database; a reporting tool could also run
> stupid queries which consume all the server's resources (for example,
> creating a massive temporary table which causes the server to run out of
> disc space), or in an extreme case crash the server (MySQL generally crashes
> if it runs out of memory or address space, or the OOM-killer might kill it).
> So setting up a separate server (with a separate IO subsystem as well, i.e.
> NOT a VM on the same host sharing a disc) is definitely desirable in lots of
> Additionally, it could run a different MySQL version from your production
> server, which is handy if you wanted to hold your production server back to
> an old (known, tested) version but upgrade your reporting DB, for instance.
This is what we are now doing, pretty much, though cron dumps and
restores rather than replication.