phpMyAdmin – How to Import a Database?
by Vincy. Last modified on February 24th, 2024.
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.
- Go to the “Import” tab in the
phpMyAdminand upload a file(SQL, CSV …) source that contains the database dumb. - Choose a database and drag and drop the import file to the phpMyAdmin interface.
How to import?
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,
- Foreign key checks.
- Partial import.
Click “Go” to complete the import.
Import CSV
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"
Import large SQL file
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
#path-to-mysql#– Path where the MySQL is. Example: /Applications/XAMPP/bin/mysql#database_name#– The target database where the import is going to happen.#path-of-the-sql-file#– The path of the source SQL to import. Example: /Users/vincy/Desktop/db_phppot_example.sql
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.
Features of the phpMyAdmin Import
The phpMyAdmin “Import” functionality provides several features.
- It allows the import of files in the following formats. The default format is SQL.
- CSV
- ESRI shape file
- MediaWiki table
- OpenDocument spreadsheet
- SQL
- XML
- It allows choosing character sets and SQL compatibility modes.
- It allows partial imports by allowing interruptions during the import of larger files.
Things to remember
When you import a database or table certain things to remember.
Database resource “Already exists” error
This error will occur if the importing file contains statements of existing resources.
Example:
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.
Access denied error
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.
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Comments to “phpMyAdmin – How to Import a Database?”
Leave a Reply
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.
Thank you
Welcome, James.