Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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) ?

share|improve this question
    
Which storage engine are you importing the table into? I strongly discourage using MyISAM. If its InnoDB there are a couple of settings you could set in the my.cnf to make the import fast and get past the 10GB barrier you are hitting. Also are there any errors in the mysql error log? Are you sure your not running out of disk space? –  Levi Apr 27 '12 at 2:23
    
I checked this morning and it is InnoDB. What specific changes would you suggest making in the my.cnf? –  lumberjacked Apr 27 '12 at 12:44
add comment

3 Answers 3

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).

share|improve this answer
add comment

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).

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.