What is the fastest way to import a large mysql database backup?

Question

What is the fastest way to export / import mysql database using innodb tables?

I have a production database that I periodically need to upload to my development machine in order to debug client problems. The way we do it now is to load our regular database backups, which are generated using "mysql -B dbname" and then gzipped. Then we import them using "gunzip -c backup.gz | mysql -u root".

From what I can tell from reading "mysqldump --help", mysqldump starts wtih -opt by default, which seems to include a bunch of things that I can think of, which will make import faster, like disabling indexes and table import as one massive import statement.

Are there any better ways to do this or further optimizations that we should do?

Note. . I basically want to optimize the time it takes to download the database to my development machine (relatively recent macbook pro, with lots of bar). Backup time and network transfer time are currently not big problems.

Update:

To answer some of the questions posed in the answers:

• The layout of the production database changes up to a couple of times a week. We use rails, so it is relatively easy to run transfer scripts on outdated production data.

• We must place production data in the development environment potentially daily or hourly. It completely depends on what the developer is working on. We often have special problems with clients, which are the result of distributing data across several tables in db, which must be debugged in the development environment.

• I honestly don't know how long mysqldump lasts. Less than 2 hours since we are currently running it every 2 hours. However, this is not what we are trying to optimize, we want to optimize imports to the developer's workstation.

• We do not need a complete production database, but it is not entirely trivial to separate what we do and do not need (there are many tables with foreign key relationships). Most likely, we will have to go in the end, but we would like to avoid this a little longer if we can.

Answer 1

It depends on how you define "fastest."

According to Joel, development time is expensive. Mysqldump works and handles many cases that you had to handle on your own or spend time evaluating other products to see if they can handle them.

Relevant Questions:

How often does your production database schema change?

Note. . I mean adding, deleting or renaming tables, columns, views, etc., i.e. things that violate the actual code.

How often do you need to put production data in a development environment?

In my experience, not very often. I usually found that once a month is more than enough.

How long does mysqldump take?

If it is less than 8 hours, this can be done in one night as a cron job. The problem is resolved.

Do you need all the data?

Another way to optimize this is to simply get the appropriate subset of data. Of course, this requires creating a custom script to get a subset of the objects and all related related objects, but will give the fastest end result. The script also needs to be supported by schema changes, so this is a time-consuming approach that should be used as an absolute last resort. Product samples should be large enough to include a sufficiently wide selection of data and identify any potential performance problems.

Conclusion

Basically, just use mysqldump until you absolutely can. Time for another solution is time spent on development.

Answer 2

Consider using replication. This will allow you to update your copy in real time, and MySQL replication allows you to catch up, even if you need to disconnect the slave. You can also use a parallel instance of MySQL on your regular server, which replicates the data to a MyISAM table that supports online backup. MySQL allows this if the tables have the same definition.

Another option worth considering is XtraBackup from renowned MySQL Percona performance experts. This is an online backup solution for InnoDB. I did not look at it myself, so I will not vouch for stability or that this is even an acceptable solution to your problem.