In this tutorial, we are going to learn how to import MySQL database using phpMyAdmin. There are two ways to do the import via this PHP application.
Open the phpMyAdmin application and create a connection by logging in with the host, user and password. Then, follow the below steps to import a database.
1) Choose the “Database” link and create a new or select an existing database. In a previous tutorial, we have seen the possible options of creating a database using phpMyAdmin.
2) Choose the file in .sql (or other phpMyAdmin-supported) format.
3) [optional] Choose char-set, SQL compatibility modes and other options like,
Click “Go” to complete the import.
If you have the SQL dumb in a form of a CSV file, the phpMyAdmin allows that format to import.
Change the format to the CSV from the default format. The SQL is the default format that the phpMyAdmin populates under the “Format” section.
It is suitable to import a database containing a single table. If the import file contains multiple tables then the import will merge all into one.
It creates auto-generated columns like COL1, COL2… and stores the other comma-separated values as data.
Note the following CSV format to import a database table.
"id","question","answer" "1"," What are the widely used array functions in PHP?","Answer1" "2","How to redirect using PHP?","Answer2" "3"," Differentiate PHP size() and count():","Answer3" "4","What is PHP?","Answer4" "5","What is php.ini?","Answer5"
Note the maximum file size allowed to upload via the phpMyAdmin application. It is near the “Choose File” option on the Import page.
If the import file is too large, then it interrupts to skip the number of queries during the import.
It is better to process import via Terminal if the import file exceeds the allowed limit. It will prevent the data inconsistency that may occur because of the partial import.
Note the below Terminal command to process importing larger SQL files.
#path-to-mysql#mysql -u root -p #database_name# < #path-of-the-sql-file#
Replace the following variable in the above command
The command line execution is also used to connect the remote server. It is in case of facing restrictions to access a remote MySQL server via phpMyAdmin.
The phpMyAdmin “Import” functionality provides several features.
When you import a database or table certain things to remember.
This error will occur if the importing file contains statements of existing resources.
If the importing file has the query to create an existing table, then phpMyAdmin will show this error.
So, it is important to clean up the existing state before importing a database to avoid this problem.
If the users have no permission to import or create databases/tables, then it will return this error.
If the user can import and can’t create tables, the file must contain allowed queries only.
Note: If you are importing via remote access, give the right credentials to connect. Make sure about the user access privileges to import or related operations.