How long does it take to recover 20 GB in MySQL? (Aka Is something broken?)

Question

I am trying to create a dev copy of a database for MySQL production by loading one of the backups. How long should this be done if the uncompressed dump is ~ 20G?

This command works for something like 24h with 10% CPU load, and I wonder if it just slows down or if she / I am doing something wrong.

mysql -u root -p < it_mysql_dump.sql 

---

Listen to it on a muscular desktop machine with a lot of bars, but it can be reading and writing to the same hard drive. I think I'm using InnoDB.

Answer 1

Recovering MySQL dumps can take a long time. This is because it really restores all tables.

What you need to do to fix this depends on the engine, but in general

I would say do the following:

Zeroth Rule: Use only 64-bit OS.

• Make sure that you have enough physical plunger to install the largest single table in memory; include any overhead for the OS in this calculation (NB: On operating systems that use 4k pages, that is, all of them, page tables occupy most of the memory in systems with large memory - do not forget about this)
• Configure innodb_buffer_pool so that it is larger than the largest single table; or, if you are using MyISAM, configure key_buffer to be large enough to hold the indexes of the largest table.
• Be patient.

Now, if you still find that it was done slowly, your particular database may have a very complicated structure to recover.

Personally, I managed to rebuild the server with ~ 2Tb in <48 hours, but this was a special case.

Make sure your development system has production-level hardware if you intend to upload production data to it.

In particular, if you think that you can massage data in tables that do not fit into memory (or, at least, mainly in memory), forget about it.

---

If all this seems too big, remember that you can simply use the file system or an online LVM snapshot from InnoDB, and then just copy the files. With MyISAM, this is a bit more complicated, but still can be done.

Answer 2

Open another terminal, start mysql and count the rows in some tables in the dump ( SELECT COUNT(*) FROM table ). Compare with the source database. This will tell you progress.

I entered about 80 GB of data into MySQL over a network in about 14 hours. They were one insertion for each row (slow) with a small amount of overhead, inserting them on a server with fast disks.

24 hours is possible if the equipment is out of date, or your import is competing with something else for I / O and disk memory.

Answer 3

I just went through the experience of restoring a 51.8 GB database from a 36.8 Gb mysqldump file to create an imdb database. For me, a recovery that was not done over the network, but was made from a file on a native machine, took a little less than 4 hours.

The device is a quad-core server running Windows Server 2008. People are wondering if there is a way to track progress. In fact. You can see the recovery of database files by going to the program data directory and finding the MYSQL subdirectory, and then finding the subdirectory with the name of your database.

Files are gradually created in the directory, and you can observe their creation. Without a little comfort, when you have a problem with the production, and you are wondering if the restoration work freezes or takes a lot of time.