How do i import a dump 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.

Looking for simple service to manage your MySQL backups?
→ Try SimpleBackups for free

Table of Contents

Import an SQL file using Command Line

Using XAMPP (or skip to Command line import)

1. Open XAMPP.
2. Launch Apache Server and MySQL Database.
3. Create a database via phpMyAdmin.
4. Copy the SQL file of your choice to the xampp/mysql/bin/ directory.
5. Open Command Prompt.
6. Go to xampp/mysql/bin/.

Command line MySQL import

1. Type: mysql -u username -p database_name < file.sql
2. The username refers to your MySQL username.
3. database_name refers to the database you want to import.
4. file.sql is your file name.
5. If you've assigned a password, type it now and press Enter.

Verify your data

1. Open phpMyAdmin or any MySQL client and select your database to ensure that the tables have imported properly.

Import a SQL file using mysqldump

1. To import a .sql file with mysqldump, use the mysqlimport command and use the following flags and syntax $ mysqlimport -u magazine_admin -p magazines_production ~/backup/database/magazines.sql
2. -u and -p are needed for authentication, and is then followed by the name of the database you want to import into.
3. You'll need to specify the path to your SQL dump file that will contain your import data: ~/backup/database/magazines.sql
4. You won't need to use > or < for importing, but you will need them for exporting in the next guide.
5. This will prompt a password request.
6. Your file will be automatically imported.

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.

Export an SQL file using mysqldump

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.

Introduction

MySQL is a popular Linux-based database program. As a database, MySQL is a versatile application. It can be used for something as simple as a product database, or as complex as a WordPress website.

This tutorial will walk you through how to export a MySQL database and import it from a dump file in MySQL.

Prerequisites

• A system with MySQL installed and configured
• An existing database (a test or empty database will work)
• A root user account and password for the MySQL database
• A terminal window or command-line
• (optional) the phpMyAdmin utility

Option 1: Use mysqldump Tool

Exporting a database puts it in a dump file that can be transferred to another system.

The MySQL database application includes a command mysqldump to create a dump file of your database. This file can be used as a backup or copied to another system.

1. To export your MySQL database to a dump file, enter the following in a terminal window:

mysqldump –u username –p db_name > dump_file.sql

Replace username with the actual username for the MySQL database, and db_name with the name of the database. You can rename dump_file.sql to anything you’d like but keep the .sql extension.

2. Confirm the export by entering:

head –n 5 dump_file.sql

The system should display the first five lines of the dump file you have just created. You should recognize the database name that’s listed.

Note: The dump file is saved to the working directory you run it from. This is typically the home directory for the system’s current user account.

Option 2: Use phpMyAdmin

PhpMyAdmin is a handy utility for managing web servers. It includes powerful tools for graphically managing files.

To export your MySQL database using phpMyAdmin:

1. Log in to cPanel
2. In the Databases section, select phpMyAdmin

1. In the column on the left, choose the database to export
2. Once it loads, click the tab on top to export
3. The export method should be Quick
4. The format should be SQL
5. Click Go, then use the Save File dialog to save a copy of the exported database.

How to Import MySQL Database

Option 1: Import MySQL Using mysqldump Tool

Importing a database requires setting up a blank database first.

1. To create a blank database, launch the MySQL shell by entering:

mysql –u root –p

2. Enter the root password when prompted. The command prompt should change to show that you’re working in MySQL.

3. Next, create a new database by entering the following:

CREATE DATABASE new_db_name;

The system should return with Query OK, 1 row affected (0.00 sec). Note: The command won’t run if the semicolon isn’t entered at the end of the command.

4. Exit the MySQL shell by pressing Ctrl-D. The command prompt should return to normal.

5. Enter the following command to import the dump file:

mysql –u username –p new_db_name < dump_file.sql

You’ll only see a response if there are errors. A successful import won’t display any comments on the screen.

6. To check the database, log back into the MySQL shell:

mysql –u root –p

7. To load the database, enter:

USE new_db_name

8. Display the contents of the database by typing:

SHOW TABLES;

Option 2: Importing with phpMyAdmin

Step 1: Create New MySQL Database and Assign User

Before you can import a database using phpMyAdmin, you’ll need to create a blank database first:

1. Log into cPanel
2. Under Databases navigate to MySQL Databases
3. Enter the name of the new database in the Create New Database field, then select Create Database

1. Once it finishes, click Go Back, then click Add User to Database
2. Select the correct user in the User box, select the new database in the Database list box, then Add
3. Select All Privileges (unless you have a reason or policy that specifies account privileges)
4. Click Make Changes

Step 2: Import MySQL Database with phpMyAdmin

To import a dump file into the new database using the phpMyAdmin web interface:

1. Log into cPanel
2. In the Databases section, select phpMyAdmin

1. In the left column, select the newly-created database as seen in the image below:

1. Click the Import tab (next to the Export tab)
2. Next to File to Import, select Browse, then choose the file you downloaded from the export operation and select Go

Note: These commands can all be run with a root database user account. If you already have another user account set up, you can use it instead. Before you begin, make sure that the account has the correct privileges. Or, you may add a new MySQL user account if you prefer.

Conclusion

In reading this guide, you now know two ways to export and import a MySQL database.

One uses a command line to create a local dump file, which you can copy or transfer to a new system to import. The other uses a graphical utility to generate the dump file.

Also, check out our article on how to easily export from MySQL database table to CSV and how to import CSV into MySQL table.

How import SQL dump MySQL?

How do I import a dump?

How do I import a dump into MySQL workbench?

What is dump file in MySQL?