2

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?

Improve this question
4
  • What about master->slave replica (from prod to dev)? Commented Feb 24 at 18:06
  • Depending on your usecase my first consideration would be: do you even need a full & current production dataset in your development environment? Can't you do with a smaller, limited and fixed set of test data that matches the input for your automated tests, rather than "polluting" test results with changes only due to a large, growing and changing live data set? - Note also that many developers relax security controls for development/testing purposes or use a development stack that by default doesn't apply any access controls or security monitoring which may cause (undetected) data breaches Commented Feb 25 at 11:24
  • @RomeoNinov I have thought about this and we use this for our backup/snapshots, however dev needs to be able to diverge from production. Commented Mar 2 at 4:31
  • @HBruijn how would a partial snapshot be generated? Due to the complexity of the table layout and data dependencies a simple data after date type of method would not work. Commented Mar 2 at 4:35

1 Answer 1

Reset to default
-2

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.

Improve this answer
6
  • 3
    It is better to use built-in replication tools than DRBD. DRBD is just unreliable from my experience. Almost every DB has replication mechanism, which can be used. Commented Feb 27 at 17:23
  • YOU CANNOT REPLICATE TO A DATABASE YOU WILL UPDATE INDEPENDENTLY Commented Feb 27 at 18:00
  • 1
    Of course you can! SQL Server AGs, Oracle RAC, SAP HANA et cetera. Google these guys when you'll get some spare time. Commented Mar 1 at 12:45
  • 1
    You clearly have no idea what you're talking about! Follow the link and educate yourself a bit... MySQL Master-Slave configuration example, other guys are similar. hevodata.com/learn/mysql-master-slave-replication Commented Mar 9 at 17:45
  • 1
    Try it yourself: Delete a record on the replica, update the record on the primary, replication breaks. (there are several other scenarios). Commented Mar 10 at 8:42

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.