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 upload_max_filesize, post_max_size, and max_execution_time. 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, upload_max_filesize and 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 post_max_size, upload_max_filesize, and max_memory settings. 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, wordpress.sql.zip. 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
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
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
Conclusion
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.
