MySQL Large database export and re-build in dev environment

Question

I have a production database cluster (innodb if it matters) that has to be regularly backed up and imported into a development environment.

Currently I am using a basic mysqldump to generate a *.sql file that is transferred into the dev environment and imported.

The problem is the file size is now around 3.9Gb and takes over an hour to import. There has to be a faster way to do this as expanding the innodb cluster only takes a matter of minutes and accomplishes a full database mirror.

I tried using the mysqldump --tab=dir method but that exports all the tables essentially in alphabetical order so when trying to import the table *.sql files it fails on foreign keys.

Is there a way to get mysqldump to properly export tables and data separately so that it can be imported?

Is there a better way to handle this data transfer so that it does not take >1hr to import into our dev environment?

Answer 1

RomeoNinov has already suggested one way async replication - but this will break if you start allowing updates on the dev environment.

You might be able to save time by only doing the mysqldump infrequently then manually copying in the production binlogs from the production side to apply roll-forwards, e.g. instead of applying daily mysqldumps, you do this once per week, then on the dev side, delete the current database, import the (potentially stale) export then apply the binlogs from production.

Part of the problem with your current mechanism is that it runs as a single thread on both the source and destination nodes. If you have multiple databases then you could run each one in a separate thread.

The quickest mechanism to create a fresh copy is by using snapshots - you might not currently have a storage substrate which supports this (such as a SAN) but you can add snapraid or DRBD without too much disruption (it might be advisable to apply this to a new replica in the production environment as the source).

A less sophisticated version of this is to just copy the database files.

Both copying files and snapshots really require the source DBMS to be shutdown when the image is taken. MySQL's crash recovery works very well but it's not perfect.

so when trying to import the table *.sql files it fails on foreign keys.

Hmmm I think you are conflating 2 issues here.

The import will fail if you simply try to import a consistent backup of the data sequentially for each table without wrapping it in SET FOREIGN_KEY_CHECKS=0; ... SET FOREIGN_KEY_CHECKS=1 (mysqlbackup should do this automatically if you tell it to backup a database).

If you already are doing this and still seeing problems, then you need to look at your transaction isolation.