Copying DBs for development

MySQL & MariaDB Other MySQL® Questions
1

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?

2

Xtrabackup&innobackupex is just what you need, have a look at http://www.percona.com/doc/percona-xtrabackup/how-tos.html#r ecipes-ibk and this (for partial backups).

Also look at the “expand table import” feature in Percona Xtradb.

3

I looked at the innobackupex documentation. I’m not reading anywhere that suggests it can do what I need. innobackex’s restore is “–copy-back”, which is just going to put it back with the same name. I need multiple copies. There’s a section on exporting and importing individual tables, but that’s only for XtraDB, and from what I understand (admittedly limited), that still won’t allow you to put them into new databases, since innodb stores the tablespace name inside the .idb files (which you’re copying from the other server).