I have a 1.4GB .gz backup file(which is 8GB unzipped) I have to import to the server but it isnt working whatever method I have tried.

1st I tried Workbench, after 2 days and still no progress I canceled, and after some research I found out console was the better option with big files.

On Console I tried importing directly from compressed .gz however that gave me ascii\0 error so I unzipped and tried uploading the file directly. This time it started very fast with some occasional spikes (like some rows gets 0.8 secs but the next row 11.76 secs). But this time it got stuck at the last second. I tried this step with 4 different OS and hardware setups.(Vista,7 server 08 and ubuntu also I tried with dedicated hardware also)

Since the file is big I cant just open it from notepad++ and I read that might corrupt the file also.

I cant get a new backup as the data came from outside source.

The database has 15 coloumns, myISAM engine.

As I can't seem to find anything else I would like some help as to what might be causing this issue.

Thank you very much.

  • You have lots of indexes? And a tiny key_buffer_size? – Rick James Apr 3 '16 at 17:21
  • If you want to open the file (good idea to eyeball your data), use vi (works on both Windows and Linux! – Vérace Apr 3 '16 at 17:44
  • @RickJames, my key_buffer_size is: 2344M with a 8GB RAM machine. I used percona config wizard. – kharon Apr 3 '16 at 18:16
  • Do you know on what it is getting stuck? show processlist what's the output... Maybe you are getting stuck on repair by keycache because of small tmp dir – CodeBird Apr 3 '16 at 21:40
  • @CodeBird, This is the output; '1 | root | localhost:50828 | maindb | Query | 1572 | Repair with keycache | /*!40000 ALTER TABLE main ENABLE KEYS / | | 3 | root | localhost:51396 | NULL | Query | 1324 | Waiting for table meta data lock | SELECT COUNT() FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHE MA='maindb' AND TABLE_TYPE='B | | 4 | root | localhost:51455 | NULL | Query | 0 | init ' – kharon Apr 3 '16 at 21:55

As I said in my comments above your issue comes from a small tmp dir. There are many answers on stackoverflow concerning this. Here is 1


And here's how to change mysql tmp dir temporarily without editing my.cnf. Make sure you create a tmp dir somewhere with enough available space.


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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