Mini Guide: How to Import an SQL File in MySQL

SimpleBackups founder

Laurent Lemaire

Co-founder, SimpleBackups

November 21st, 2020

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.



Back to blog

Stop worrying about your backups.
Focus on building amazing things!

Free 7-day trial. No credit card required.

Have a question? Need help getting started?
Get in touch via chat or at [email protected]

Customer support with experts
Security & privacy first
Service that you'll love using