Importing Large MySQL Files With phpMyAdmin

It may not be possible to import large SQL files using phpMyAdmin due to it’s uploading limits.
Sometimes this is related to your php.ini settings, but no always.

You can always use the old reliable method of importing sql files from the command line. Or upload to file to server and tell phpMyAdmin to look for that file specifically.

MySQL Command Line Option

Of course, we need to upload the file first. Below, the 1st line is to achieve this. After that we connect to our server via ssh, and then (on the 2nd line) import the sql file.

scp the_sql_file.sql root@myserver.com:/path/to/upload
mysql -u username -p -h localhost DATABASE-NAME < the_sql_file.sql

Here, the MySQL connection is established using a username and password. If you simple create a .my.cnf file in order to get automatic connections, you won’t need all these. Simply create a file to /root/.my.cnf (or any other user directory you want to)

[client]
user=root
pass=mysql_root_password

From now on, you don’t need to use the -u and -p arguments when using mysql, if this file exists at the user’s home directory you’re logged on as, than it will automatically pass them. It may seem insecure, but the /root folder is only accessed by the root anyway, even though it is not a good idea to keep passwords clear text, if your /root folder is compromised, you’re in big trouble anyway. And keep in mind that we usually have to keep MySQL passwords a cleartexts in scripts all the time.

Using phpMyAdmin to Import the SQL File

You can set a default folder for phpMyAdmin to check for uploaded files. This way, if you place a file into this folder, you can easily choose to import it. Find where your config.inc.php file is. If you don’t know where, try the locate command (if it is installed). Open the file and find the $cfg[‘UploadDir’] section. Update it as below

$cfg['UploadDir'] = 'imports';

Now, phpMyAdmin will check the “imports” folder directly. So we need a folder like that. On your terminal, create the folder.

mkdir /path/to/phpmyadmin/imports

Now upload your sql files here.

scp /path/to/the_sql_file.sql root@myserver.com:/path/to/phpmyadmin/imports

Change the owner and group of the imports folder. First check who owns the phpmyadmin folder. Let’s say it is called webapp,

chown -R webapp:webapp /path/to/phpmyadmin/imports

Ok. Now use your browser and access phpmyadmin, at the import db seciton you’ll see a dropdown menu that wasn’t there before. There you’ll see the files inside the “imports” folder. Even though they are larger than the phpmyadmin upload limits, they’ll get imported.