8

I have an assignment to build a development virtual machine template.

I need to add to the server MySQL databases from each one my company's products so that new developers will have the ability to develop for each one of the products.

Most databases are smaller than 1GB.

But I have a few databases which are huge (around 160G) - but I'm limited in the size of the template I'm trying to create, and it's not reasonable to add there a DB of 160GB.

Therefore, I'm trying to find the right method to extract, let's say, last month's values from this huge database and add them to the server so the developer will be able to "feel" what it's like to work on this database.

Is it possible to do such a thing and how is it done? Thanks!

Edit:

Unfortunately, there is no option for a separate main DB server which will hold all development databases, also there's no requirement to update the data regularly, I just need to present the same data as in production servers (at a random time period) as a sandbox environment for new developers.

Improve this question

3 Answers 3

Reset to default
8

If I understood correctly you are planning to make a separate DB copy for each development environment.

While this might be feasible with small databases, it won't work that well with big DBs. So unless you have a really good reason to setup a separate DB for each environment it might be better to consider having a single copy of development database and setting all development environments to use it.

This approach will allow you to refresh development DB with the latest data periodically and if someone messes it up you can just refresh it again.

Also imagine the situation where your developers start to work on some new project which requires new tables to be created. If you have a single copy of development DB you (or devs) will need to create those tables and fill them with test data just once. Now imagine devs realize that initial table structure is not optimal and needs to be changed. Again this will need to be done on a single DB a opposed to possibly tens of environments.

This is the approach I saw being used for big projects time and time again and most of the time it works pretty well.

Improve this answer
5
  • 2
    In a shop where I work, every developer has their own copy of the database so nobody steps on other people toes. This worked out very well for us. We have scripts to rebuild the database from scratch and populate it with the test data required for the development. Single database has often been problematic with people working on some database areas breaking it for other people. And then everyone halts while a big db is being restored. So having a single database is NOT a superior solution. (TBH neither is. It is situational.)
    – Andrew Savinykh
    Jul 27, 2014 at 21:41
  • Agreed. Just out of curiosity, how big are your DBs? I can't see how this could work with a DB of 160GB in size.
    – grekasius
    Jul 28, 2014 at 10:06
  • Of course the dev databases would be intentionally small with only the subset of data required to test whatever the developers are working on. For purposes such as performance testing a separate, (bigger) instance would be used.
    – Andrew Savinykh
    Jul 29, 2014 at 9:40
  • I am not required to update the data regularly, just to place it once in this template.
    – Itai Ganot
    Jul 30, 2014 at 5:15
  • Have a look if you can create a small sample data set from your current DB. There is no straightforward answer here unfortunately. What you need to do depends on what data you have and what you choose to put in that dev DB.
    – grekasius
    Aug 1, 2014 at 8:14
4

This is extremely dependent on the kind of data in the database. In some cases, it might be as easy as 

select * from table where date > ....

while in other cases, it's impossible to separate this because of the structure of the data. In the end, it will likely be a mix and very hard to get right.

Improve this answer
1
  • 2
    Specifically, some databases may have newer entries (say, <1 month) that reference records from older entries (say, >1 month). The way you deal with them depends entirely on how these references are set up, and is infeasible for all but the most basic.
    – Bob
    Jul 27, 2014 at 11:40
0

We had recently the situation a customer wanted to extract the last 30 days of a database. If ALL tables have the same attribute where you can define the datetime you can do a

mysqldump --where=' datetimefield > "2014-06-28"'

but he wanted to mix different tables with old and new data. So this was no solution for him but it might be one for you?

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .