Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a main architecture with a MySql db, replication and backup, it's working fine. Now I also have a development server where I play with the code and I would like to use the data from the main db to play too, with reads and writes. How can I set up the replication to have some real data coming from the main db to the development db? Should I replicate permanently or only once a day to keep some sort of consistency during tests on dev db? Any idea / strategy is welcome.

Edit: ok so to clarify the kind of application I use is a web app development with Django. The idea is to test some new features on the development server. The need to write is important, a read only database won't be enough to run the tests thoroughly. For the moment the database takes a fairly reasonable amount of time to dump to another server (something like 10 minutes), but it's growing.

share|improve this question

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.

ETL Process

This approach makes a lot of sense if you want a subset of data, or you want to mask it somehow to protect the data from breach. You can transform sensitive data within the ETL scripts and either load directly into a development database, or create a dump like in the above approach, but you'll know that it's been cleaned already.

An ETL process can be nice if you want to run it hourly to, say, extract the last hour from production, do some cleanup/reorganization and import it into a development "master" database for export to development systems, etc.

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.

share|improve this answer
+1 for the first two ideas. The third is only good as long as the development code is doing read-only operations. I have seen too many clients butcher replication by mangling primary keys through their own writes ahead of replication. Those clients end up going back to your first idea. – RolandoMySQLDBA Apr 18 '12 at 14:50
Yeah, number 3 is good for only certain types of tests and is probably best used in conjunction with one of the first 2, otherwise you'll end up butchering the setup and some reason down the road and wishing you'd never done it. :) – Kyle Smith Apr 18 '12 at 14:55
Since I already have a daily backup on a replicated db I could implement your first option quite easily. And since the data is quite small for now it would work nice. But the second idea is interesting too, I'm not sure to understand though, could I just import the new data since last import? That would be awesome. – Bastian Apr 18 '12 at 16:50
Sure, but you have to know your data layout to make that work. It would require that removed records are somehow logged or marked as removed, or otherwise maked as "updated_at" so you know what data set to export. – Kyle Smith Apr 18 '12 at 17:23

it depends on the apps you are developing : a simple dump or a daily/weekly replication would do the trick unless you need to test how you app proceses fresh data from your production server, in this case, you will need a permanent replication.

share|improve this answer

All depends on how much data you want replicate. If its quite small db you can make daily dump and then recreate the db on your development database server. And you can do read and writes. Replication in scenario where you want do writes on slave will be quite tricky (im not sure but maybe imposible). You cant do writes on slave with master-slave model and in master-master any write will be replicated to other master

share|improve this answer

if you need read/write access, I suggest you to setup a cron job that would just copy database from your production to your development.

pros: you get write access

cons: all your work will get wiped out next time you run your cron job to refresh your dbs

if you need just read-only access, I suggest you to setup a replication using following guide: MySQL :: MySQL 5.0 Reference Manual :: 16 Replication

pro: always up to date

cons: can't write as that would break replication at some point.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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