DEV Community

loading...

Mini Guide: How to Import a SQL File in MySQL

simplebackupsio profile image SimpleBackups Originally published at simplebackups.io ・2 min read

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.

Import a SQL file using Command Line

  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/.
  7. Type: mysql -u username -p database_name < file.sql
    • The username refers to your MySQL username.
    • database_name refers to the database you want to import.
    • file.sql is your file name.
  8. If you've assigned a password, type it now and press Enter.
  9. Open phpMyAdmin 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
    • -u and -p are needed for authentication, and is then followed by the name of the database you want to import into.
    • You'll need to specify the path to your SQL dump file that will contain your import data: ~/backup/database/magazines.sql
    • You won't need to use > or < for importing, but you will need them for exporting in the next guide.
  2. This will prompt a password request.
  3. Your file will be automatically imported.

Export a 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
    • The -u flag specifies the MySQL username.
    • The -p flag specifies a password prompt associated with the above username.
    • database_name is the name of the database you want to export.
    • 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.
  4. 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.
  5. 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.

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.

Automate MySQL Backups with SimpleBackups

SimpleBackups is a database and website backup automation tool. SimpleBackups automates MySQL backups by using MySQLDump to securely send backup files offsite to the cloud for storage. When you need to ensure your MySQL backups are secure, you can trust SimpleBackups to take care of it for you.

Discussion (0)

pic
Editor guide