Importing larger sql files into MySQL

71

I have a 400MB large sql backup file. I'm trying to import that file into MySQL database using WAMP->import, but the import was unsuccessful due to many reasons such as upload file size is too large and so on. So, i've gone ahead and made a few changes to the config PHP and MySQL settings under WAMP. Changes made are

Under WAMP->MySQL->my.ini

    max_allowed_packet = 800M
    read_buffer_size = 2014K

 Under WAMP->PHP->PHP.ini

    max_input_time = 20000
    memory_limit = 128M

My question is, Is this the right way to import such a large sql files using WAMP->import? If so, did i make the right changes to the config files? What are the maximum values allowed for the above variable?

This question is tagged with mysql

~ Asked on 2013-03-07 17:57:32

The Best Answer is


149

You can import large files this command line way:

mysql -h yourhostname -u username -p databasename < yoursqlfile.sql

~ Answered on 2013-03-07 18:01:22


38

Since you state (in a clarification comment to another person's answer) that you are using MySQL Workbench, you could try using the "sql script" option there. This will avoid the need to use the commandline (although I agree with the other answer that it's good to climb up on that horse and learn to ride).

  1. In MySQL Workbench, go to File menu, then select "open script". This is probably going to take a long time and might even crash the app since it's not made to open things that are as big as what you describe.

  2. The better way is to use the commandline. Make sure you have MySQL client installed on your machine. This means the actual MySQL (not Workbench GUI or PhpMyAdmin or anything like that). Here is a link describing the command-line tool. Once you have that downloaded and installed, open a terminal window on your machine, and you have two choices for slurping data from your file system (such as in a backup file) up into your target database. One is to use 'source' command and the other is to use the < redirection operator.

Option 1: from the directory where your backup file is:

$mysql -u username -p -h hostname databasename < backupfile.sql

Option 2: from the directory where your backup file is:

$mysql -u username -p -h hostname
[enter your password]
> use databasename;
> source backupfile.sql

Obviously, both of these options require that you have a backup file that is SQL.

~ Answered on 2013-03-07 18:48:25


Most Viewed Questions: