Why does the dump take an extremely long time to import and how can I speed it up?

Question

MySQL

I'm importing locally a dump that weighs only 4 GB, it's already the 5th hour in MySQL Workbench. The work is worth it (

I have a 1 terabyte HDD, of course it would be faster with an SSD. Ubuntu 20.04 OS, 8GB of RAM.

Is it possible to somehow speed up the process without changing the type of drive? Please tell me how to solve it.

Answer 1

You can do something like this (they say mysql itself can also do it):
- bite (put aside) from the dump everything related to keys, indexes, constraints
- fill in the rest
- apply the bitten
at least this will allow in the process of filling the tables "not to distract" the subd for a permanent index updates, consistency checks, etc.

Answer 2

Try to create a mysqldump dump with options --opt --quickthen | gzip > dump.sql.gz
You can install the pv utility and then you will see the speed of receiving data and the amount of data transferred.
mysqldump ... | pv | gzip > file
it is desirable to redirect to a separate drive.

Answer 3

Without changing the disk and dump type, all that remains is to configure my.cnf.
And to check how much is left - two options, the first one is simpler and "easier" for the system:
- see how much space the database takes on your disk
- in MySQL - see how many tables and how many records are in large tables.

Answer 4

Yes, shove it into memory and it will be fast, as options to turn off indexes if this is the case, the data themselves of such a volume are quickly imported