Importing larger SQL files into MySQL

I have a 400 MB 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 configuration of 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

Is this the right way to import such a large SQL file using WAMP → import?

If so, did I make the right changes to the configuration files? What are the maximum values allowed for the above variable?

195042 次浏览
小开
最佳答案

You can import large files this command line way:

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

You can make the import command from any SQL query browser using the following command:

source C:\path\to\file\dump.sql

Run the above code in the query window ... and wait a while :)

This is more or less equal to the previously stated command line version. The main difference is that it is executed from within MySQL instead. For those using non-standard encodings, this is also safer than using the command line version because of less risk of encoding failures.

小开

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.

小开

The question is a few months old but for other people looking --

A simpler way to import a large file is to make a sub directory 'upload' in your folder c:/wamp/apps/phpmyadmin3.5.2 and edit this line in the config.inc.php file in the same directory to include the folder name $cfg['UploadDir'] = 'upload';

Then place the incoming .sql file in the folder /upload.

Working from inside the phpmyadmin console, go to the new database and import. You will now see an additional option to upload files from that folder. Chose the correct file and be a little patient. It works.

If you still get a time out error try adding $cfg['ExecTimeLimit'] = 0; to the same config.inc.php file.

I have had difficulty importing an .sql file where the user name was root and the password differed from my the root password on my new server. I simply took off the password before I exported the .sql file and the import worked smoothly.

小开

my.ini

max_allowed_packet = 800M
read_buffer_size = 2014K

PHP.ini

max_input_time = 20000
memory_limit = 128M
post_max_size=128M
小开

On item 1.16 of phpMyAdmin they say:

1.16 I cannot upload big dump files (memory, HTTP or timeout problems).

Starting with version 2.7.0, the import engine has been re–written and these problems should not occur. If possible, upgrade your phpMyAdmin to the latest version to take advantage of the new import features.

The first things to check (or ask your host provider to check) are the values of upload_max_filesize, memory_limit and post_max_size in the php.ini configuration file. All of these three settings limit the maximum size of data that can be submitted and handled by PHP. One user also said that post_max_size and memory_limit need to be larger than upload_max_filesize. There exist several workarounds if your upload is too big or your hosting provider is unwilling to change the settings:

Look at the $cfg['UploadDir'] feature. This allows one to upload a file to the server via scp, ftp, or your favorite file transfer method. PhpMyAdmin is then able to import the files from the temporary directory. More information is available in the Configuration of this document.

Using a utility (such as BigDump) to split the files before uploading. We cannot support this or any third party applications, but are aware of users having success with it.

If you have shell (command line) access, use MySQL to import the files directly. You can do this by issuing the “source” command from within MySQL:

source filename.sql;
小开

Use this from mysql command window:

mysql> use db_name;
mysql> source backup-file.sql;
小开

I believe the easiest way is to upload the file using MYSQL command line.

using the command from the terminal to access MySQL command line and run source

    mysql --host=hostname -uuser -ppassword
source filename.sql

or directly from the terminal

   mysql --host=hostname -uuser -ppassword < filename.sql

at the prompt.

小开

We have experienced the same issue when moving the sql server in-house.

A good solution that we ended up using is splitting the sql file into chunks. There are several ways to do that. Use

http://www.ozerov.de/bigdump/ seems good (but never used it)

http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/ used it and it was very useful to get structure out of the mess and you can take it from there.

Hope this helps :)

小开

Three things you have to do, if you are doing it locally:

In php.ini or php.cfg of your PHP installation

post_max_size=500M


upload_max_filesize=500M


memory_limit=900M

Or set other values. Restart Apache.

Or

Use the PHP big dump tool. It’s best ever I have seen. It’s free and open source.

小开

If you are using the source command on Windows remember to use f:/myfolder/mysubfolder/file.sql and not f:\myfolder\mysubfolder\file.sql

小开

Had a similar problem, but in Windows. I was trying to figure out how to open a large MySQL SQL file in Windows, and these are the steps I had to take:

  • Go to the download website (http://dev.mysql.com/downloads/).

  • Download the MySQL Community Server and install it (select the developer or full install, so it will install client and server tools).

  • Open the MySQL command-line client from the Start menu.

  • Enter your password used in install.

  • In the prompt, mysql>, enter:

      CREATE DATABASE database_name;


USE database_name;


SOURCE myfile.sql

That should import your large file.

小开

The simplest solution is MySQL Workbench. Just copy the .sql file text to the query window of MySQL Workbench and just execute it. All renaming things will done by it.


提交答案