user99999 user99999 - 1 year ago 52
MySQL Question

Importing big database from file - check table size when the table is locked

I have a sql file created with mysqldump that has about 30GB, I have to import it to database now. The problem is that on bigger tables it takes a lot of time (like above 2 hours yet) to insert everything, and I have no way of checking what's going on other than

full process list

I can't use count on such tables, or anything like that, because the whole table is locked. Is there a way to check how much data was inserted, and how much is left to insert?

Answer Source

You could try first loading a subset of the data, say 1000 rows, and measure how long it takes:

head -n 1001 yourdump.sql > yourdump_first1000.sql

The dump file yourdump_first1000.sql should contain the first 1000 rows. You can see how long it takes to load, and compare that against the size of the file. From this, perhaps you can extrapolate how long 30GB would take.