Importing a database using MySQL from the terminal is a fundamental task for database administrators and developers. It allows for efficient data transfer and backup recovery. This guide will walk you through the process with technical explanations and examples, ensuring a solid understanding of the procedure.

Prerequisites

Before proceeding with importing a database, ensure you have:

1. MySQL Installed: Verify that MySQL is installed on your system by running mysql --version in your terminal.
2. Database File: A .sql file containing the database backup you intend to import.
3. Access Credentials: Ensure you have the necessary credentials (username and password) to access the MySQL server.
4. Database Ready: The target database should already exist, or you should have permissions to create one.

Steps to Import a Database

Step 1: Open the Terminal

Access your terminal or command-line interface that supports MySQL commands. This environment interacts directly with your MySQL server.

Step 2: Log into MySQL

Authenticate and log into the MySQL server using:

mysql -u [username] -p

Replace [username] with your MySQL username. After pressing enter, you'll be prompted to enter your password. Upon successful login, you'll enter the MySQL command prompt.

Step 3: Create or Select the Target Database

If the database already exists, you can skip this step. To create a new database, run:

CREATE DATABASE [database_name];

Replace [database_name] with the name you want for the new database. To use this database, execute:

USE [database_name];

Step 4: Import the SQL File

Exit the MySQL prompt by typing exit or pressing Ctrl + D. To import the .sql file, use the following command in the terminal:

mysql -u [username] -p [database_name] < /path/to/your/databasefile.sql

• Replace [username] with your MySQL username.
• Replace [database_name] with the name of the database you're importing into.
• Replace /path/to/your/databasefile.sql with the full path to your .sql file.

Example

To illustrate, if your username is admin, your database name is sampleDB, and your file path is /home/user/backup.sql, the command would look like:

mysql -u admin -p sampleDB < /home/user/backup.sql

When prompted, enter your password. The import process then begins, displaying progress in your terminal.

Common Issues and Troubleshooting

• Access Denied Error: Ensure you have typed your username and password correctly, and verify that your user has the correct privileges.
• Database Doesn't Exist: If you get this error, ensure you have created the database or have permissions to do so before the import.
• File Not Found: Double-check the path to your .sql file for correctness.

Advanced Options

Custom Port Number

If your MySQL server is running on a custom port, specify it using the -P flag:

mysql -u [username] -p -P [port_number] [database_name] < /path/to/your/databasefile.sql

Specifying a Host

When needing to connect to a remote host, include the -h flag with the host address:

mysql -u [username] -p -h [host_address] [database_name] < /path/to/your/databasefile.sql

Summary Table

Additional Tips

• Backup Regularly: Regular backups are key to disaster recovery. Export databases using the mysqldump command.
• Permissions Management: Regularly review and update MySQL user permissions to secure access.
• Data Validation: After import, verify data integrity by performing consistency checks and comparisons with source data.

By following the steps outlined in this guide, you can reliably import a MySQL database from the terminal, ensuring efficient database management and recovery procedures.