Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a large (~5GB) .sql file I am attempting to restore via SSH, it seems to quit in the middle of the restore process.

When I come back hours later after issuing the command (the file has 7 tables), there are only 5 tables and nothing is being processed.

Is there a log file I can view to see why it quit half-way through? Is there a configuration option I am probably missing? Any ideas would be greatly appreciated. Thanks!

The command I am using:

mysql -u username -p database_name < file.sql
share|improve this question
1  
Please edit your question with the technical details of how you are restoring (command-line args etc). –  Phil Aug 15 '12 at 16:26

2 Answers 2

up vote 3 down vote accepted

Either the problem is with MySQL connections being killed silently, in which case please set:

[mysqld]
net_read_timeout = 7200
net_write_timeout = 7200

Defaults are extremely low (30 seconds). These params set the number of seconds waiting upon a command before aborting a connection.

Or the problem is as you suggest with your SSH. In which case use screen. If you don't know about screen, you are missing on a major tool. In very brief, it splits your shell, and allows you to close the terminal without breaking ongoing processes. It allows you to re-connect at another time or place, share your connection with others, and more.

share|improve this answer

You should be able to login to mysql and run the following command

mysql> SHOW PROCESSLIST;

mysqld could be busy rebuilding indexes for you table. There is no way to know otherwise.

Here is another aspect to think about: If you have TEXT or BLOB data and you have the default setting of max_allowed_packet, that could potentially kill a mass INSERT in progress

RECOMMENDATION

Increase max_allowed_packet to 256M in /etc/my.cnf and restart mysql

UPDATE 2012-08-15 15:14 EDT

If all your data is MyISAM, you should also increase the bulk_insert_buffer_size to 256M

Now, my recommendation is to add these to /etc/my.cnf

[mysqld]
max_allowed_packet=256M
bulk_insert_buffer_size=256M

and restart mysql

share|improve this answer
2  
I think I found my problem- I wasn't aware once your SSH connection closes, the command will stop running. I managed to find the command 'nohup' and it seems to be working so far. I'll update with an answer if that was the problem. –  Blenderer Aug 15 '12 at 19:34

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.