SQL Files can quickly become really large, sometimes up to a GB and more depending on your site. Knowing how to quickly import huge files is a real advantage and will save you a lot of time


Importing Files Via Linux Shell

Instead of relying on PHPMyAdmin you should really learn how to import files via the Linux shell. PHPMyAdmin is not made for importing huge files and is certainly not meant to be your all-in-one go to solution for administrating SQL databases. Sure, it can help you to manage your database, repair them and optimize them from time to time, but when it comes to importing nothing beats the Linux shell.

1. Open a Linux shell, usually via a SSH program called Putty if you are on Windows. Mac and Ubuntu users can search for “Terminal” and then connect to your server via SSH.

Putty can be downloaded here – after installing it you can connect with your username, password on port 22. If you have set up SSH keys, you will also need to provide them. (In general, you should change the SSH default port and add secure RSA SSH keys!)

2. Once you have established a connection to your server, we will also need the password for your database and the username administrating the database. Once you have everything sorted out, we can proceed.

1 Example: Creating a MySQL Database Backup – Via MySQLdump

Syntax:

mysqldump -f --max_allowed_packet=650M -u user_name --password=mycoolstring database_name > /home/user/database.sql

If your password includes some special characters, you may want to wrap quotes around it like:

--password="stringwith#%!"

Also make sure to paste the entire database name including the second name followed by an undescore, it usually looks like

database_name

Where does it store the database? Where you tell it to. The following is the equivalent of telling it to dump it in that location

> /my/hidden/location

2 Example: Restoring A Large Database File

When restoring a database backup all you have to do is to change mysqldump to mysql and change the “greater than” (>) sign into a “less than” sign (<).

mysql -f --max_allowed_packet=650M -u user_name --password="coolstring" database_name < /home/user/database.sql

When importing large database files it is crucial to add the following parameter and modify it accordingly:

--max_allowed_packet=650M

If your database file is larger than 650 MB, change this!

Importing Sql File Large Filesize.png

3 Restoring Database Backup via PHPMyAdmin

As mentioned before, I do not recommend this and only total beginners should follow the instructions if you run into any issues following the instructions above

1. Log into PHPMyAdmin

2. Log into your control panel or locate your php.ini file locally

3. If you are using WHM open “PHP Configuration”. Change the following values to be greater than the filesize of your backup. Local users need to change this in their php.ini:

memory_limit
post_max_size
upload_max_filesize

Example:

memory_limit = 500M
upload_max_filesize = 500M
post_max_size = 500M

Open the file config.inc.php and change the line $cfg[‘UploadDir’] = ‘upload’;. Change ‘upload’ to an actual directory

Example:

/home/user/databases/

Restart Apache to make sure PHP is using the latest php.ini and you’re good to go.

You should now be able to select files stored in “UploadDir” on the PHPMyAdmin tab “Import”.

I hope this cleared this up. Should you have any question on importing huge SQL files, post a comment.