Our system is a pretty straightforward LAMP stack, but our database has grown to a few hundred gigabytes. Our developers need access to clean copies of the database to work on without stepping on each other’s toes or affecting the live data. This means I need to create multiple instances of the database for them on a fairly regular basis.
I’ve been loading them from mysqldumps, but this process is taking longer and longer over time. Other options I’ve looked at seem to take it as read that you’re keeping the same database name and tablespace on restore.
The main problem seems to be that it’s re-indexing the entire database when it loads (either throughout or re-indexing at the end), since mysqldump doesn’t preserve indexes.
Processes that involve copying the .ibd files themselves don’t work, since innodb apparently stores tablespace data in there aswell, and they’ll get upset if you try and move them. (Unless you hexedit them by hand, which is just impractical)
Is there a better way that someone could point me toward?