Importing larger sql files into MySQL

Question

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- gt MySQL- gt my ini      max allowed packet   800M     read buffer size   2014K   Under WAMP- gt PHP- gt 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

User · Answer

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 :)

User · Answer

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

User · Answer

I really like the BigDump to do it  It s a very simple PHP file that you edit and send with your huge file through SSH or FTP  Run and wait  It s very easy to configure character encoding  comes UTF-8 by default

User · Answer

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

User · Answer

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 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

User · Answer

my ini  max allowed packet   800M read buffer size   2014K   PHP ini  max input time   20000 memory limit   128M post max size 128M

User · Answer

Simplest solution is mySql workBench just copy the  sql file text to query window of mysql-workbenck and just execute it  All renaming things will done by it

User · Answer

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  lt  filename sql   at the prompt  I found this link How to upload big sql dump files  memory  HTTP or timeout problems  in MYSQL  it give the outline of what to do to upload a large SQL  It helped me when I got face with a client 196mb sql dump  Something the PHPMySql couldn t handle

User · Answer

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

User · Answer

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     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  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  lt  redirection operator    Option 1  from the directory where your backup file is    mysql -u username -p -h hostname databasename  lt  backupfile sql   Option 2  from the directory where your backup file is    mysql -u username -p -h hostname  enter your password   gt  use databasename   gt  source backupfile sql   Obviously  both of these options require that you have a backup file that is SQL

User · Answer

Use this from mysql command window   mysql gt  use db name  mysql gt  source backup-file sql

User · Answer

You can import large files this command line way   mysql -h yourhostname -u username -p databasename  lt  yoursqlfile sql

User · Answer

3 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 php big dump tool its best ever i have seen  its free and opensource  http   www ozerov de bigdump

User · Answer

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

[mysql] Importing larger sql files into MySQL

Examples related to mysql