问题原文
原帖链接 · EdwinJ · CC BY-SA 4.0
I know there are lots of variables here, and they're highly dependent upon the applications in the environment as well as the needs of the org. I read this post first, as the question was similar. [https://serverfault.com/questions/380701/replicate-main-mysql-db-to-a-development-server-to-play-with-real-data][1]
I thought I could add some depth from my perspective to the question and hopefully get some recommendations very specific to our environment.
- We have a standalone server that hosts an online Learning Management System.
- The LMS has thousands of users and its db is being written to quite regularly.
- The desire is to take a "snapshot" of the db, and replicate it onto the development server for testing, new builds, etc.
- The snapshot needs to be relatively close (within a few days atmost) to the live data because of the dynamic nature of the db.
- The dev server needs to be read/write, but never affect the prod server at all.
- The data in the db will have PII and needs to be sanitized before accessed by the dev team.
I'm thinking the general steps should be:
- Automate a mysqldump on some sort of schedule from prod to dev
- Run a script on dev to clean/anonymize the data
- Automate a restore of the clean data, on a schedule, into dev
Based on these requirements, am I overlooking anything obvious that could impact the data in either server or the load/resources on the server?
已采纳解答
原帖链接 · Rick James · CC BY-SA 4.0
Plan A
Consider the following setup:
- Primary-Replica setup (ordinary replication)
- The Replica needs extra disk space. (Recommend 3x the size of the dataset.)
- The Replica has LVM already set up.
- On the Replica, take a snapshot -- this takes a minute or so, regardless of dataset size.
- Anonymize the data (etc) in the snapshot.
- Use the snapshot for your testing.
- The snapshot is separate from the Replica, which continues to receive writes.
- LVM depends on "Copy on write", so the snapshot starts out taking virtually no disk space, but grows in size over time. When finished with testing (or it is time to do a new snapshot), blow it away to recoup the disk space.
Plan A2 -- By having more than one Replica being used this way, multiple dev/test/build setups could be handled.
Plan B
- Set up Galera Cluster across 3 "nodes". All 3 will have the same data.
- When wanting to test, take one node out of the Cluster to use for dev.
- When finished testing, but it back into the Cluster; it will automatically resync. (Give it time to resync before using it again for dev.)
- Note that if anonymizing the data is too intensive, the resync will take a long time.
Plan B2 -- Plan B, but with 4 nodes; this gives you full automatic failover even when one node is being used by dev.
Plan B3 -- Plan B, but use a "garbd" to avoid needing a full 3rd node. (Failover is lost with this plan unless you have 4 nodes.)
Plan B4 -- With 5 nodes (one optionally a garbd), you could ping-pong two of the nodes for use by devs. One is busily anonymizing, while the other is being actively used. (I would not go beyond 5 nodes, for a variety or reasons.)
Notes
- The advice here is 'general', not specific to LMS.
- Pulling a dev server takes only minutes (plus anonymizing).
- Replicas (either regular or Galera) provide convenient techniques for taking backups.
摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。
