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