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
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.
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
Sep 01, 2011 : MySQL server has gone away obstructing import of large dumpsAug 14, 2012 : "Mysql has gone away" heavy multiple insertIncrease max_allowed_packet to 256M in /etc/my.cnf and restart mysql
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
