phone

Importing Large Files in Mysql using Source Command

dump import large mysql files

Using Shell Mysql Source Command to Import Large Databases

Never tried to upload or import a large file to Mysql? Well, I recently faced the dilemma of importing a large WordPress database, over a 100 MB in size and some serious difficulty.

Mysql is initially set up to process 1 MB through import, so i increased this via shell to 120 MB but it still did not work

I tried the bigdump script to no avail. Then, i came across johnon explanation of how to import large files in mysql using Mysql SOURCE Command but he provided no detailed information.

So, I played around in shell and found out the solution. Here it is the solution using XAMPP or WAMPP as the server

1. Create an empty database in phpMyadmin. Give it the same name as the sql file you are trying to upload. You could also create a database in shell by typing: mysql: CREATE DATABASE databasename;

2. Move your sql file to the bin file in your Xampp Mysql directory

3. Log on into shell: mysql -u root -p. It will then ask you for your root password. Type it in

4. Let Mysql know which file you intend to use

- mysql> USE database;. If you fail to use the USE command you can end up with this error: “error 1046 (3D0000): no database selected. If your database is large this error can freeze the screen

5. Dump the sql data file with this code: SOURCE c:/xampp/mysql/bin/file.sql

If your file is really big it could take over 30 minutes, be patience and your sql database will be imported

If Your Hosting Company is Godaddy

In the event you are using a hosting company like Godaddy or 1and1 or bluehost, upload the sql file to your cgi-bin or bin directory via ftp, through dreamweaver or any other ftp program.

Then create a database name in phpMyadmin for your new uploaded sql file

Type in mysql> USE new database name in shell. If you get an error, type is: SHOW DATABASES to ensure that you have the right name

Then enter this in shell: mysql> SOURCE /home/yourwebsitehome/anyotherhomefile/cgi_bin/file.sql;

Your sql file should load into the new database

Changing the Size of File Mysql Can Upload

Another less used method is to change the max_allowed_packet for mysql files. Go to your phpMyAdmin and click on variables and in the list of variables you will see max_allowed_packet set to 1 or 2M.

To change the max_allowed_packet size log on into shell and type in: mysql> nano /etc/my.cnf. Change the max_allowed_packet to about 100M. You may also want to increase the “max upload file” in the php.ini file.

So with these methods you should know how to import large sql data files into mysql myadmin

Grow Your Business. Call: 256-361-9080

2 Comments

  1. Gayatri says:

    Thank you – this is exactly what I was looking for. I had a 2.5GB dump file to import.
    The only thing I did different is instead of USE file.sql I did USE [new database name]

    Thank you once again.

  2. The Real Deal says:

    I am glad the sql source file command help you. This, in my opinion is the fastest way to upload large sql files

Leave a Reply

You must be logged in to post a comment.