Uploading Large SQL Files brought to you by META Q
Interior Ad 3 brought to you by The META Q

Uploading Large SQL Files

The other day while updating a client’s site, I needed to make a backup of the ExpressionEngine database.  Piece of cake, you’re probably thinking. I thought so too, and since I use phpMyAdmin, it was.

While the backup process was extremely easy, the resulting .sql backup file created a slight predicament.

See, the size of the .sql file was well over 20 megabytes. If you have ever tried to load a large .sql file through phpMyAdmin, you probably know what I am getting at here.  If you haven't tried to do this, I’m sure that eventually you will, and you should know that many (but not all) phpMyAdmin installations will not let you upload .sql dump files larger than 2 megabytes.

Say you find yourself in this very situation: you have a large .sql file but phpMyAdmin won’t let you load it up. You’re worried that there’s nothing you can do about it, right? The truth is, there is a lot you can do.

I’ll show you two easy ways to get around this setting in phpMyAdmin.

A word of caution before continuing:

Before you begin, please be aware that running some of these commands can wipe out a database completely.  If you consider yourself to be a novice when it comes to the MySQL command line or phpMyAdmin, please don't run anything on a database that you need. Create a test database to try things out first. Once you are confident that what you are about to do will work without ruining everything, then move on to the real system. 

The tools you will need:

  • Secure Shell (SSH) access (for remote database work)
  • Your MySQL username and password
  • The name of your database
  • A sql dump file to work with.

The graphical approach:

The first approach to this problem is what I call the "graphical approach" because it allows you to use  phpMyAdmin for the upload process.

To implement the graphical approach, you are actually going to modify phpMyAdmin ever so slightly.  These modifications will allow you to upload a .sql file of any size. How, you ask?  Read on dear readers.

First, you need to find the core files for your phpMyAdmin installation. If you are working on a local MySQL installation this should be easy to do (a search for “phpMyAdmin” should turn up some useful results). For those who are working on a remote server, please know that this could take some digging and possibly even a phone call to your host to find out where they are located. If you are in a rush and don't have the time to dig through the files on your server, I suggest you take a look at the "command line approach" (discussed next).

The most time-consuming aspect of this approach is finding the phpMyAdmin directory.  If you have found yours - configuration is a breeze from here on out.

Within your phpMyAdmin directory, you will want to locate the "config.inc.php" file.

Config File Location

Open "config.inc.php" and then locate the line of code that says:

  $cfg['UploadDir'] = ' ';

Once you have found this line of code, change it to:

  $cfg['UploadDir'] = './upload';

Config File Modifications

Now, save the file and close it. 

Go back to your phpMyAdmin directory (the same directory that holds the "config.inc.php" file you just modified) and create an empty folder called "upload."

Upload Directory Location

Once the upload folder has been created, place your .sql dump file into the folder. 

Go back to your phpMyAdmin Control Panel, select your database, navigate to the "Import" tab just like you would do to import any othe .sql file.  This time however, under the "File to Import" section you should see a dropdown box where you can select the file that you added to your "upload" directory.

phpMyAdmin View

Simply select the database in your "uploads" directory, and click "Go". 

phpMyAdmin will take care of the rest!

The command line approach:

While the graphical approach is quick and simple, the "command line" approach is even quicker and simpler. If you’re comfortable with command line, that is.

If you are working on a local machine, you will need to pull up a terminal window. If you are working on a remote server, in a terminal window you will need to connect via Secure Shell (SSH) to the server. 

Once you have your terminal open (and if necessary, are connected to your remote server), log in to mysql at the command line. To do this, at the command prompt, type the following:

mysql -u <enter_mysql_user_name> -p

The "-u" flag stands for "user name" and the "-p" flag stands for "password." Be sure to change <enter_mysql_user_name> to your user name. After typing in the command hit "enter" on your keyboard.  MySQL will request your password; go ahead and type it in and press enter again (Note: The field will remain empty as you enter the password. Although you can’t see the password, it is there. Don’t worry, this is a security feature).

At this point, if you entered your username and password correctly, you should see a greeting message from MySQL. It will be something to the effect that you’ve successfully entered the MySQL command line interface.

MySQL Login

Next, you will need to select the database that you want to work with. To do this, you will use the "use" command in MySQL.  In the terminal window, type in the following and press enter: 

use <database_name>;

Where <database_name> is the name of the database you would like to work with. If you entered the command correctly and selected an existing database, you should see a confirmation message that says, "Database changed."

Now that you are working with the proper database in mysql, it’s time to load your large .sql file.   Type in the following command and press enter:

source /path/to/sql/file/database.sql;

MySQL Run Commands

Where '/path/to/sql/file' is the direct path to your .sql dump file.

Depending on the size of your file and the speed of your system, this may take some time. While the command is running, it may output lots of confirmation messages to your terminal window. This is totally normal.

After it’s finished, you should receive a confirmation message letting you know the job is complete. At this point, in just three commands, you have just loaded your large .sql file into MySQL via the command line. You may congratulate yourself for a job well done.

Of course, these are only two (of what could be dozens of) ways to load a large .sql file into a MySQL database.

If you’ve figured out another way to load a large .sql file into a MySQL database, let us know! We may feature it on Meta Q.

Photo Credit: Rox SM


Clinton Reeves's avatar

Clinton Reeves

Back-end developer at Q Digital Studio

Clinton Reeves is a back-end developer at Q Digital Studio. He is a self-proclaimed nerd and happily embraces said nerdiness. He lives his life in a world of text (code code code), and even dreams in code. Clinton's day isn't complete without at least eight cups of coffee.


What others are saying

Brian

Backup Pro and Backup Pro(ish) for ExpressionEngine is a lifesaver for this. You can backup and restore your database right from the ExpressionEngine control panel, in just one click. http://mithra62.com/blog/cats/5/backup-proish

Clinton Reeves

Hi Brian,

You are absolutely correct here.  We’ve been working with Backup Pro lately and I really like it.

Hiso

is this working with wamp server ? Because i tried but still keep waiting waiting.. then show “Maximum execution time of 300 seconds ex…...”;

Clinton Reeves

Hi Hiso,

It sounds like you are running into problems with the execution time limit in phpMyAdmin.  Give this a try:

Search config.inc.php for $cfg[‘ExecTimeLimit’] - if you find this configuration setting, change it to: 

$cfg[‘ExecTimeLimit’] = 0;

If you don’t find $cfg[‘ExecTimeLimit’]  in config.inc.php, go ahead and add it in right below the $cfg[‘UploadDir’] configuration you changed above.

This is going to change the default script execution time limit in phpMyAdmin from 300 seconds to unlimited.

Terris Kremer

Clinton, my hero!

Thanks for this, you saved my day.

Steve Luiting

Thanks. It’s hard to find this information.

Bruce

Editing the config files worked like a charm. Thanks a lot.

Sunitha

Your article saved my website to loose.. Thanks


Speak your mind