MySQL is one of the popular open-source relational database management system used by developers, DBAs and businesses to manage and store data. In this article, we will learn the process of importing an SQL file into MySQL.
An SQL file contains a set of SQL commands that can be used to create tables, add data, and manipulate the database structure. This file can be used to create a backup of an existing database or to transfer data from one MySQL server to another.
We can import the SQL files using any of the following methods:
- MySQL command line interface
- mysqldump command line client utility
- GUI tool for MySQL
For detailed explanation, I have created a database named medical_store which stores the details of suppliers, medicines, purchases, and sales. The table database diagram is following. I have created it using the Database diagram feature of dbForge Studio for MySQL. I have exported a database to an SQL file using the mysqldump command.
The script to create tables:
CREATE TABLE suppliers (
supplier_id INT NOT NULL AUTO_INCREMENT,
supplier_name VARCHAR(100) NOT NULL,
contact_no VARCHAR(20) NOT NULL,
email VARCHAR(100) NOT NULL,
address VARCHAR(255) NOT NULL,
PRIMARY KEY (supplier_id)
)
CREATE TABLE medicines (
medicine_id INT NOT NULL AUTO_INCREMENT,
medicine_name VARCHAR(100) NOT NULL,
manufacturer VARCHAR(100) NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
expiry_date DATE NOT NULL,
PRIMARY KEY (medicine_id)
)
CREATE TABLE sales (
sale_id INT NOT NULL AUTO_INCREMENT,
medicine_id INT NOT NULL,
quantity INT NOT NULL,
sale_date DATE NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
total_price DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (sale_id)
)
ALTER TABLE sales
ADD CONSTRAINT sales_ibfk_1 FOREIGN KEY (medicine_id)
REFERENCES medicines(medicine_id);
CREATE TABLE purchases (
purchase_id INT NOT NULL AUTO_INCREMENT,
supplier_id INT NOT NULL,
medicine_id INT NOT NULL,
quantity INT NOT NULL,
purchase_date DATE NOT NULL,
unit_price DECIMAL(10, 2) NOT NULL,
total_price DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (purchase_id)
)
ALTER TABLE purchases
ADD CONSTRAINT purchases_ibfk_1 FOREIGN KEY (supplier_id)
REFERENCES suppliers(supplier_id);
ALTER TABLE purchases
ADD CONSTRAINT purchases_ibfk_2 FOREIGN KEY (medicine_id)
REFERENCES medicines(medicine_id);
Script to insert dummy data.
INSERT INTO suppliers VALUES
(1, ‘ABC Suppliers’, ‘555-1234’, ‘abc@example.com’, ‘123 Main St, Anytown USA’),
(2, ‘XYZ Suppliers’, ‘555-5678’, ‘xyz@example.com’, ‘456 Oak St, Anytown USA’);
INSERT INTO medicines VALUES
(1, ‘Aspirin’, ‘Bayer’, 100, 1.50, ‘2023-12-31’),
(2, ‘Paracetamol’, ‘GSK’, 50, 2.00, ‘2022-06-30’),
(3, ‘Amoxicillin’, ‘Pfizer’, 30, 5.00, ‘2024-09-30’);
INSERT INTO sales VALUES
(1, 1, 2, ‘2022-05-01’, 2.00, 4.00),
(2, 2, 3, ‘2022-06-01’, 3.00, 9.00),
(3, 3, 1, ‘2022-07-01’, 6.00, 6.00),
(4, 1, 5, ‘2022-08-01’, 1.50, 7.50);
INSERT INTO purchases VALUES
(1, 1, 1, 50, ‘2022-04-01’, 1.00, 50.00),
(2, 2, 2, 100, ‘2022-05-01’, 1.00, 5.00);
First, let us learn how to import SQL files using MySQL command line utility.
Also Read: RDBMS: What Is A Relational Database, And What Are The Advantages
Table of Contents
Import SQL file using command line utility
In the first example, we will see how to import the SQL file of the medical_store database in the dev_medical_store database. First, we must create a database named dev_medical_store by executing the following query.
CREATE DATABASE dev_medical_store;
To start importing the SQL file, you need to open the MySQL command-line tool. This tool allows you to enter SQL commands directly into the MySQL server.
You can open the MySQL command-line tool by opening a terminal or command prompt window and entering the following command:
mysql -u username -p
Replace the username with the username you use to access the MySQL server, specify the database name in <database_name>, and hit enter. In context of this article, the command will be as follows:
mysql -u root -p dev_medical_store
You will be prompted to enter the password. Once you enter the appropriate password, you will be connected to MySQL Server. You can use the SOURCE command to import the SQL file in dev_medical_store. It allows you to import the SQL file into the connected database. In our case, the command looks like below:
mysql> SOURCE D:\MySQLImportScripts\medical_store_20230509.sql
The SQL file will start importing. The import process may take some time, depending on the SQL file size and the commands’ complexity.
Once the import process is complete, you should see a message indicating that the SQL file was successfully imported.
After importing the SQL file, you can verify that the data has been successfully imported by querying the database. You can use the following MySQL query to retrieve data from the database and check that it matches the data in the SQL file.
SELECT
TABLE_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘dev_medical_store’
As you can see, the SQL file is successfully imported into the dev_medical_store database. Let us see another method.
Method 1: Use mysqldump client utility
The mysqldump is a command-line utility program that creates backup copies of MySQL databases. It generates a set of SQL queries that can be used to drop and create the database objects and insert data on a new MySQL server or to restore the database to its original state in case of data loss or corruption.
You can import a SQL file created using mysqldump into a MySQL database using the mysql command-line tool.
Here are the steps to do so:
- Open a command prompt or MySQL terminal window on your computer.
- Navigate to the directory where your SQL file is located.
Type the following command to import the SQL file into your MySQL database:
mysql -u username -p database_name < file_name.sql
Replace username with the username of a MySQL user with sufficient privileges to import the SQL file, database_name with the name of the MySQL database where you want to import the SQL file, and file_name.sql with the name and location of the SQL file that you want to import.
For example, if your MySQL username is root, the name of your MySQL database is uat_medical_store, and the name of your SQL file is uat_medical_store_20230509.sql, you would use the following command:
mysql -u root -p uat_school_management < school_management.sql
Press Enter to execute the command. You will be prompted to specify the appropriate the password for the MySQL user specified in the command. Enter the password and press Enter.
C:\Users\nisar>mysql -u root -p uat_medical_store< D:\MySQLImportScripts\medical_store_20230509.sql
Wait for the import process to complete. Depending on the size of the SQL file, this may take several minutes.
Once the import process is complete, your MySQL database will contain the tables and data defined in the SQL file. Let us run a MySQL query to view the list of tables created in uat_medical_store.
SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘uat_medical_store’;
Output
As you can see, the tables are created in the uat_medical_store database. You can refer official MySQL document to learn more about the mysqldump command.
Complementing the information about the methods of importing SQL files, we could also mention the option – opening / executing the SQL file in the editor. But it is important to consider that this method requires the appropriate quality of the SQL text from the file, the presence of all instruction descriptions and the appropriate user qualifications. The method may also have resource limitations – in the case of large files, the import may not be fully or partially executed. Due to these and other difficulties, this method is rarely used.
Method 2: dbForge Studio for MySQL – Import/Export Tool
The dbForge Studio for MySQL is a feature-rich GUI tool that helps to develop, architect, and administrate a MySQL database. The dbForge Studio has a Restore database feature that helps import SQL files in a MySQL database. This feature is also available in the free Express edition of dbForge Studio. I am going to explain the step-by-step method to import SQL files using the Restore database feature.
First, open dbForge Studio and Select the Data Pump tab. Here you can see the Restore database option.
When you select the Restore Database, an interactive wizard named Database Restore Wizard starts. The first screen is the Database Script file. Here you can specify the following set of information.
- Connection: Specify the connection name. The connection name is a connection string that is used to connect to a local or remote MySQL Server. If you have created it, select the name from the drop-down box. If you haven’t configured the connection, select Manage. In our demo, we have already created a connection named localhost; hence I have selected localhost.
- Database: Specify the database name in which you want to import the SQL file. If your script contains the database name, then select “As in script.” Select the desired database name if you want to import data into the existing database. We are doing a fresh import to the prod_medical_store database; therefore, I have selected “As in script.”
- SQL file name: Specify the location of the SQL File.
- SQL file encoding: Specify the desired encoding of the SQL file.
Once all parameters are set, click restore to begin the import process of the SQL file.
Once the import process completes, execute, and run a query to get details of tables.
use prod_medical_store;
SELECT
TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘prod_medical_store’;
Query output
As you can see, the tables have been created, and the data is imported correctly.
Summary
Importing an SQL file into MySQL is an easy process that can be accomplished using the MySQL command-line tool. By following the steps explained in this article, you can easily import data from an SQL file into your MySQL database. You can also use the dbForge Studio for MySQL, which includes not only restore database features but other features as well which can be used to manage the MySQL database more efficiently.
Also Read: Cloud Databases: How To Protect Them From Cyber Attacks
