0

I want to setup a dev and test environment for website that has a huge MySQL database. Since this DB is so big, it is quite inconvenient to clone it 1-on-1, because it simply takes too long and uses too much storage.

My ideas was to only dump / copy certain records, matching some SQL. For example, only the last 1000 users and last 1000 products in the DB, assuming we only need this much data for testing and development.

My questions are:

  1. How to best go about this, keeping in mind the DB is live at the time of the dumping (referential integrity), and keep it from going offline
  2. The data is complex and has many joins, making insert into ... select ... statements a bit complex, but still possible.

My ideas was to use mysqldump to set up only the structure (no data), then use a long list of insert into ... select ... statements to fill the tables 1 by 1.

Only issues I am uncertain of is the referential integrity and processing the tables in the right order, and maybe there is a better way?

The DEV environment is my personal laptop, so not a server with huge disks.

1

Answers originally left in comments

A test environment should probably have a full copy and could be populated form the backups (and it double checks they are working right). – danblack

My ideas was to use mysqldump to set up only the structure (no data), then use a long list of insert into … select ... statements to fill the tables 1 by 1.

Use SELECT .. INTO OUTFILE and LOAD DATA INFILE instead of building SELECTs. Plus processing the tables in the right order of course. – Akina

1

LVM (Logical Volume Management)

With that, you can take a snapshot of a disk drive "instantly". This give you a full test database.

I would recommend stopping MySQL, do the snapshot, then restart. The snapshot, itself, takes seconds, regardless of how big the disk drive is. (cf "Copy On Write" technology)

Unformatuely, LVM needs to be set up when you first get started; I don't think it can be added to a running disk.

Note that this is also a nifty way to take a backup. Take the snapshot, then (at your leisure) copy the snapshot to a backup device, then release the snapshot.

  • This is an excellent solution, assuming the environments share a disk. Not so much for moving the data to a laptop for development. – Wouter Feb 12 at 13:05
  • @Wouter - The approach minimizes downtime to take the dump. As you say, it does not speed up the load. – Rick James Feb 12 at 15:40

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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