We have a staging server and a production server, both running their own MySQL server. Data on the production MySQL server changes all the time.

Occassionally we want to update the data on the staging MySQL database using data ob the production MySQL server. This allows us to play around on the staging server with an updated copy of the production data.

What is the recommended way of performing this clone/sync from a live server to another?

  • Take a backup of the production database and restore it as needed to your staging / non-production database. Try this link here
    – H.79
    Dec 19 '17 at 19:21

No and yes.

No -- in that you would need to dump the data from your single server; this is invasive and costly.

Yes -- in that there are several ways of setting up Replication to make this task a breeze, possibly near-instantaneous, regardless of dataset size.

With a Master (Prod) + Slave, you could disconnect the Slave to use it as Staging; it would be up to date to the moment when it was grabbed. But if you modify the Staging server, it could not be put back as the Slave.

Galera -- Have 3 (or more) nodes. Removing a node from the cluster is easy. Adding a node to the cluster is easy -- the dump+reload is automatic. Then the grabbed node becomes Staging; then throw it away.

LVM -- This requires setting up the Prod machine with LVM before building the database. However, a one-minute downtime is all it takes to grab a snapshot of the prod machine. You then have hours to copy (perhaps via rsync) to the Staging server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.