10 gig MySql database import stalls and doesn't finish

Question

I am trying to import a 10 gig database to my local installation of mysql. I'm using Ubuntu 11.10. I am using the command line to do it.

mysql -u root -p database < database.backup.sql

I left it running for 2 days and finally stopped it just to see whats going on and I only had some of the tables. I need a better way to do this, but I haven't found anything saying to do it differently than the command line.

Is there another way of doing this that doesn't time out the server (or whatever its doing) ?

Answer 1

log in to another mysql window and type, you'll see where it "stuck"...

SHOW PROCESSLIST;

using that information you'll be able to adjust your my.cnf and make that import go a lot quicker.

also take a look at your mysqld.log make sure everything is clean there whenever you start mysqld, this will also gives you an indication if something is wrong (corrupted InnoDB for an example).

Answer 2

If it's not all one table you might want to split into multiple files and load each one in a seperate client (it'll go faster as well as make isolating the error easier).

Answer 3

Depending on how the file is structured, as well as your database structure, engine type, etc., you may get a huge benefit from wrapping your import in a transaction (or several).

A specific web application comes into mind. The installer simply executed a .sql script, consisting of several thousand inserts. This took ~2 minutes to run. Once I wrapped that in a transaction, it executed in less than a second.