I need to create copy/replication of our databases for the development environment.
I'm not sure how to do it. DBs are updating many times during a day; not only new records added but also old records are updated.
How should I implement it?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
The "right answer" is highly application dependant, but here are a few strategies I've seen used before.
Daily Backup / Restore-on-demand
This works well if you have a small data set, and the need to be able to have various developers use the backups ad-hoc. The idea is basically a mysqldump on a replication target system and some scripts to do a MySQL import.
Binary Log Replication
This method probably won't work for you, as you specifically want to read and write to the development database. Sometimes people will use replication to run read-only regression testing against, but modifying the database will result in failed replication and/or inconsistent data.
In my experience, a regular backup or dump, plus an on-demand restore gives your Developers the most flexibility, being able to refresh their databases as and when they please.
However, under GDPR, the use of Live data in non-Live systems really ought to be covered by your organisation's Privacy Notice. Check with your Data Protection Officer (or equivalent) or your legal department about whether you're even allowed to use Live data in non-Live systems.
If so; great.
If not then you have [a lot of] work to do in "masking" that Live data before it gets anywhere near a Developer, so as to close off that vector for Data Breaches. That will probably involve dumping/restoring the database to a "staging" area, then masking the data somehow and then dumping the resulting database for [safe] use by Developers.