How to Import an SQL File in MySQL

How to Import an SQL File in MySQL

Have you just started learning how to work with SQL files in MySQL? You could be perplexed as to how to use this program to import files. Fortunately, importing and exporting files with MySQL is extremely simple. Database import and export is a typical process in software development. Data dumps may be used to backup and recover your information. They are also useful for migrating data to a new server or development environment.

Follow the steps below to learn how to use MySQL to import SQL files.

Using XAMPP

XAMPP stands for cross-platform, Apache, MySQL, PHP, and Perl, It also enables you to create a WordPress site offline on your computer's local web server. This straightforward and lightweight solution is cross-platform, which means it can be used on Windows, Linux, and Mac. You may use XAMPP to import a file by simply following these steps:

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

Command line MySQL import

• 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.
• If you've assigned a password, type it now and press Enter.

If you want to dump all databases in MySQL rather than just one, use the same command but add the parameter --all-databases instead of selecting a specific database.

Instead of:

$ mysqldump -u username -p database_name > data-dump.sql

You could do:

$ mysqldump -u username -p --all-databases > alldatabases.sql

Here we use the --all-databases flag to dump all databases instead of specifying a particular one.

Take note that the output will be a single dump file, alldatabases.sql that includes all the databases you have access to, and not a single file per database.

If you were successful in importing your file, you can check it by opening phpMyAdmin or any MySQL client and choosing your database to ensure that the tables were successfully imported.

‍

Import a SQL file using mysqldump

• To import a .sql file with mysqldump, use the mysqlimport command and use the following flags and syntax

$ mysqlimport -u book_admin -p books_production ~/backup/database/bookss.sql

• -u and -p is 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/books.sql
• You won't need to use > or < for importing, but you will need them for exporting in the next guide.
• This will prompt a password request.
• Your file will be automatically imported.

After running this command, you may be prompted to provide the password for the MySQL user you used. Please be cautious when utilizing an existing database with records, since this command will overwrite your existing database and result in the loss of your records.

Exporting a MySQL Database Using mysqldump

The console program mysqldump exports databases to SQL text files. This facilitates the transfer and relocation of databases. You will need the name of your database as well as credentials for an account with at least complete read-only access to the database.

Use mysqldump to export your database:

mysqldump -u username -p database_name > data-dump.sql

• username is the username you can log in to the database with
• database_name is the name of the database to export
• data-dump.sql is the file in the current directory that stores the output.

The command will produce no visual output, but you can inspect the contents of data-dump.sql to check if it’s a legitimate SQL dump file.

Run the following command:

head -n 5 data-dump.sql

The top of the file should look similar to this, showing a MySQL dump for a database named database_name.

SQL dump fragment
-- MySQL dump 10.13  Distrib 5.7.16, for Linux (x86_64)
--
-- Host: localhost    Database: database_name
-- ------------------------------------------------------
-- Server version       5.7.16-0ubuntu0.16.04.1

If any errors occur during the export process, mysqldump will print them to the screen.

‍

How can you automate your MySQL backups?

The above steps outline how to import SQL files in MySQL and can act as a wonderful tool for managing your databases and ensuring that you have copies of all your data. If you want to manage your databases more easily, automated services can go a long way and Ottomatik can easily back up your databases in a jiffy! Ottomatik automates MySQL backups such that backup files are safely sent offline to the cloud for storage. Try Ottomatik for more hassle-free and automated database management for your projects.