1

What is a way to import a 40GB+ .sql file into a MySQL database, that works?

I tried big dump. I tried loading the .sql into workbench and executing.

None of the methods I tried so far, have worked.

I was wondering what is the best way or even a-way (that works) of importing such a large database .sql into mysql? Note that splitting the SQL file into chunks may not work, as some tables are larger than 5GB.

I appreciate any suggestions.

nobody
  • 19,814
  • 17
  • 56
  • 77
AnchovyLegend
  • 12,139
  • 38
  • 147
  • 231
  • start mysql console client `use` your database and insert `\. dump.sql` – Rufinus Mar 18 '14 at 14:30
  • try to import it with the mysql commandline tools – Jenson Mar 18 '14 at 14:31
  • Use `mysql` cli, like `mysql -uUSER -p DATABASE < dump.sql > result.txt` (you'll be prompted to type password). It won't be extremely faster, but will do dump restore in background. File `result.txt` will contain any errors or other result messages – Alma Do Mar 18 '14 at 14:32
  • Both and I tried the method above and it starts out well but then I get a notification after some time, saying: `Error 2006 (HY000): MySQL Server has gone away`. – AnchovyLegend Mar 18 '14 at 15:12
  • How was this dump file created? Is there any possibility to regenerate it in a different way, in particular splitting huge tables into individual INSERT statements? – Mike Lischke Mar 19 '14 at 07:47
  • 1
    try this for the import http://stackoverflow.com/a/12425526/1251601 – Jenson Mar 19 '14 at 09:53
  • @Mike dump file was created using a mysql dump from the command line – AnchovyLegend Mar 19 '14 at 12:33
  • Increasing the package size is an obvious improvement, but if you have a single package of 5GB then you are doomed. I doubt you can set such a large value for the server. – Mike Lischke Mar 19 '14 at 15:24
  • Thanks everyone, this was all very helpful. I was able to resolve this issue by combining your suggestions, hints and clues with some additional research from other posts. Posted the solution I ended up using. – AnchovyLegend Mar 20 '14 at 15:14

2 Answers2

1

I imported a big dump file like this: mysql -u username -p database_name < your_dump.sql

Jenson
  • 625
  • 3
  • 16
0

Step 1: Make changes to the configuration file: my.cnf (located in C:\ProgramData directory in Windows if running MySQL V5.6, Note: this directory is hidden by default, you must enable visibility of hidden files in Folder Options).

max_allowed_packet=2000M

Step 2: Restart MySQL service

Step 3: Use the following dump command:

mysql -hhostname -uusername -ppassword db_name
< C:\sql_input_path\db.sql > C:\error_log_output_path\error.txt
AnchovyLegend
  • 12,139
  • 38
  • 147
  • 231