If you are manually migrating your website or simply need to import certain SQL files into your database, this article will show you how to import multiple SQL files in mySQL. We will use both a graphical interface and MySQL command-line.
To begin with, it is strongly advised not to import a database onto a live website. Before attempting to import any SQL file, stop the web server and make a backup of your database.
Import database using phpMyAdmin
I’m assuming your host has already installed phpMyAdmin on your server. If you’re running a VPS and don’t have phpMyAdmin installed, I’ll go over how to install phpMyAdmin on Linux in detail in a separate article.
Most hosts impose severe file upload limits and impose a maximum execution time. If your server’s file upload limit is very low (2MB), and your database files are both many and large, the export process will always fail. phpMyAdmin, on the other hand, is intelligent enough to resume the import from where it left off. So, if you reach the maximum execution limit, reupload the files and phpMyAdmin will resume the import from where it left off. However, it is preferable to increase the limit through cPanel or by manually modifying php.ini.
Increase PHP file upload size on VPS
To increase PHP max upload limit, open php.ini and edit the following lines –
; https://php.net/cgi.check-shebang-line ;cgi.check_shebang_line=1 ;;;;;;;;;;;;;;;; ; File Uploads ; ;;;;;;;;;;;;;;;; ; Whether to allow HTTP file uploads. ; https://php.net/file-uploads file_uploads = On ; Temporary directory for HTTP uploaded files (will use system default if not ; specified). ; https://php.net/upload-tmp-dir ;upload_tmp_dir = ; Maximum allowed size for uploaded files. ; https://php.net/upload-max-filesize upload_max_filesize = 2M
The maximum upload file size is set to 2MB by default. Change it to the file size you require. Scroll down to
post_max_size. Increase the
post_max_size to the needed size if it is set to 8MB by default.
Scroll to the bottom and look for
max_execution_time. The default
max_execution_time is set to 30, which is too short for importing large database files. In one of my import processes, I had to set the
max_execution_time to 3600 seconds, or one hour. Increase this limit based on the size of the import.
Increase PHP file upload size on cPanel
cPanel is the popular control panel used by majority of hosting providers. cPanel easily allows to change the PHP
Log in to your cPanel account, and follow the below steps to modify PHP settings –
Click MultiPHP INI Editor from the cPanel dashboard.
Select the domain name where phpMyAdmin is hosted or select Home Directory to modify PHP settings for all domains. Now you should see all the PHP settings. Change
max_execution_time to 3600 (1 hour),
memory_limit to 512MB,
post_max_size to your preferred value.
After changing the settings, click Apply. Now phpMyAdmin is ready to import multiple SQL files.
Increase PHP file upload size on DirectAdmin
DirectAdmin is another popular hosting manager. DirectAdmin also provides an easier method to change PHP settings. From the dashboard, click PHP Settings.
Click the Add button and select file_uploads, change its value to On. Add one more setting, select
max_execution_time and set value to 1800 or 3600 (depends on import size). For larger SQL files, it may take more upto an hour or even more. Similarly, change
If you are using another web hosting panel, I recommend that you examine its settings or contact support to change the PHP settings on the server.
It is now time to begin the database import process. In the web browser, launch phpMyAdmin and navigate to Import. You can select a single file and then click Go. You can modify the settings based on the SQL file being imported. In most circumstances, the default settings would suffice.
But it will only import a single file. To import multiple SQL files, create a .zip of all sql files, rename the zip in the format –
filename.[format].[zip]. For example,
Depending on the number and size of the files, the import process may take some time. When the process is finished, the imported data will be visible under Databases.
Import database using MySQL command-line
It is highly adviced that you backup existing database before migrating or adding new data files to the existing database.
If you want to simplify and speed up the process, I recommend using the mysql command-line to import database files. It may appear complicated, but it is not. Once you’ve changed the PHP settings, all it takes is a single command to do the trick.
Delete old database and import new SQL files
The following command will replace all the existing data with the new data. Do not use this command if you want to import data to an existing database but when migrating an old database to a brand new server.
Transfer all the .sql files to the server. SSH into the server, cd into the directory where SQL files are stored, and use the following command to import all sql files.
sudo cat *.sql | mysql -u root -p database_name
Replace database_name with the actual database name on the server.
Keep old database and import new SQL files
The new SQL files will be imported using the command below. If there are any matching keys in the database, it will skip them and import only data that does not exist in the current database.
sudo cat *.sql|sed 's/^INSERT/INSERT IGNORE/'|mysql -u root -p database_name
That’s all there is to it. Database migration is a high-risk endeavour. There may be some server failures throughout the import process; but, if you have a complete database backup, you are safe. Before migrating the database to the production server, I recommend creating a clone or staging server and practising the processes on it.
If you have any issues with the above instructions, do let me know in the comment section below this article. Or, join our Discord Server.