(Partially) clone a huge DB for DEV purposes

Question

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.

Answer 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

Answer 2

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.