Clinton Reeves / Posted 11.29.2011
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.
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';
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."
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.
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.
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:
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:
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 is a back-end developer at Q Digital Studio who lives and breathes code. He is Q Digital's resident problem-solver; if he can't fix it, he'll just create new code or a shiny new add-on. When he's not problem-solving at the studio, he's likely tinkering with an arduino project, or working on his house.