How to Import an SQL File in MySQL

Have you just begun to learn how to work with SQL files using MySQL?
Maybe you feel a bit lost on how to import files with this tool?
Luckily, importing and exporting files via MySQL is actually quite simple.

Learn how to use MySQL to import SQL files by following the step-by-step guide below.

Table of Contents

Import an SQL file using XAMPP and PHPMyAdmin

XAMPP is bundled with MySQL and phpMyAdmin, which makes it easy to import and export databases.
To import an SQL file using XAMPP, follow these steps:

1. Open XAMPP.
2. Launch Apache Server and MySQL Database.
3. Create a database via phpMyAdmin.
4. Select your database and click on the Import tab.
5. Select the SQL file you want to import and click on "open"

Note that you can use the command line MySQL import method with XAMPP if you prefer.

Import an SQL File using XAMPP and MySQL CLI

Certainly, here are the steps to import an SQL file in XAMPP using the MySQL command-line client (mysql CLI):

1. Ensure XAMPP is Running:
   Start XAMPP and confirm that the Apache and MySQL modules are running. You can check this in the XAMPP Control Panel.

2. Open a Command Prompt or Terminal:
   Open a command prompt or terminal window on your computer. You can find the command prompt in Windows, or use Terminal in macOS and Linux.

3. Navigate to the MySQL Bin Directory:
   In your command prompt or terminal, navigate to the MySQL bin directory in XAMPP. The path may vary depending on your XAMPP installation. For a typical installation, you can use a command like this (but make sure to verify the path):

   cd C:\xampp\mysql\bin   # For Windows

4. Use the MySQL CLI to Import the SQL File:
   Run the following command to import your SQL file into MySQL. Replace the placeholders with your specific information:

   mysql -u username -p database_name < path_to_mysql_file.sql

   username: Your MySQL username (often "root" by default).
   database_name: The name of the target database where you want to import the SQL file.
   path_to_mysql_file.sql: The complete path to the SQL dump file you want to import.

5. Enter the MySQL Password:
   After executing the command, you'll be prompted to enter your MySQL password. Type it and press Enter.

6. Import Completion:
   The SQL file will be imported into your MySQL database. Once the process is complete, you should see a confirmation message.

That's it! You've successfully imported an SQL file into your XAMPP MySQL database using the MySQL command-line client (mysql CLI).

Import an SQL file using WAMP and MySQL CLI

If you're working with the offline WAMP server, follow these steps:

1. Launch the WAMP server.

2. Click the WAMP icon in the taskbar.

3. Look for the "MySQL" link under "Default DBMS: MySQL" in the displayed menu.

4. Find your MySQL version on the right.

5. Open the command prompt and execute this command to import the SQL file into your database:

   C:\wamp64\bin\mysql\mysql8.1.00\bin\mysql -u username -p db_name < mysql_backup.sql

   Remember to make the following substitutions:

   • Replace mysql8.1.00 with your specific MySQL version.
   • Change username to your database login username.
   • Replace db_name with the name of your target database for the SQL import.
   • Substituting mysql_backup.sql with the full path to the SQL dump file you intend to import.

Import an SQL file using command line MySQL CLI

In order to import an SQL file using the MySQL command-line client (mysql CLI), you'll need the destination database to be created first. You can do this by using the CREATE DATABASE command.

Step 1: Create the destination database

mysql -u root -pYOUR_PASSWORD -e "CREATE DATABASE destination_db

Step 2: Import the SQL file into the database you've just created.

mysql -u root -pYOUR_PASSWORD destination_db < db_backup.sql

Import a mysql dump file using mysqlimport

⚠️ You cannot use mysqlimport to import an SQL dump file`. You can only use it to import data from external files, such as CSV files.

mysqlimport is a command-line utility in MySQL that is used for efficiently importing data from various text-based file formats, such as CSV (Comma-Separated Values), TSV (Tab-Separated Values), and other delimited or fixed-length formats, into MySQL tables. This tool allows you to populate database tables with data from external files, making it a valuable tool for data migration, data loading, and bulk data insertion tasks.

Example: Import data from a CSV file:

mysqlimport --fields-terminated-by=, --columns=id,name,date destination_db people.csv

While mysqlimport is a powerful tool for importing data into MySQL, it's important to note that it primarily focuses on data loading and does not handle other SQL operations, such as database schema modifications or data manipulation. It's commonly used in scenarios where the goal is to efficiently populate MySQL tables with large amounts of data from external files, such as data migration, data warehousing, and data integration tasks.

Notes:

• After entering this command, you may be asked to enter the password for the MySQL user that you used.
• Please be careful when using an existing database that has records as this command will overwrite your existing database and end up losing your records.

How to export a MySQL database to a file?

We have written an extensive guide on how to backup your database using mysqldump, but here's a quick summary:

1. To export a MySQL database to a test file, start by using the mysqldump command.
2. Log in to MySQL.
3. Enter the mysqldump command using the following flags and options: $ mysqldump -u my_username -p database_name > output_file_path
4. The -u flag specifies the MySQL username.
5. The -p flag specifies a password prompt associated with the above username.
6. database_name is the name of the database you want to export.
7. The > symbol is a Unix directive for STDOUT, which will make it possible for Unix commands to output the subsequent results of the output command to another location. These locations are usually file paths.
8. Be sure to input the completely qualified path and its filename to your output file path, so that your file will be placed exactly where you want it to be.
9. Once the command is executed, you'll be prompted to enter your password. This will then create your exported backup file with a .sql extension.

How to automate your MySQL backups?

Making MySQL backups and restoring a MySQL dump (like addressed in this article) is not a complicated task but comes a moment when you'll want to automate it in a way where you can trust your data is secure 100% of the time.

When you have to manage multiple backups, on multiple servers and want a solution you can trust with orchestrating it all in an optimized way, make sure to check out what we do at SimpleBackups.
SimpleBackups automates MySQL backups to securely send backup files offsite to the cloud for storage.