09.10.2024

Import and Export Databases in MySQL using Command Line

Managing database backups and migrations is an essential task for any MySQL database administrator. Using the command line, you can easily export databases to backup files or import them into a new environment. This guide covers how to import and export databases in MySQL using the command line.

Prerequisites

Before starting, ensure that:

MySQL is installed and running on your server or local machine.
You have access to a terminal or command line.
You have the necessary MySQL credentials, including the username and password.

Exporting Databases with mysqldump

The mysqldump utility is the standard tool for exporting databases in MySQL. It creates a .sql file that contains the SQL statements needed to recreate the database.

1. Export a Single Database

To export a single database, use the following syntax:

mysqldump -u [username] -p [database_name] > [filename].sql

-u [username]: The MySQL username.
-p: Prompts for the MySQL password.
[database_name]: The name of the database you want to export.
[filename].sql: The name of the output file that will contain the database backup.

Example:

mysqldump -u root -p mydatabase > mydatabase_backup.sql

This command will prompt for the password of the root user and create a file named mydatabase_backup.sql containing all the data and structure of mydatabase.

2. Export Multiple Databases

To export multiple databases, use the –databases flag followed by the names of the databases:

mysqldump -u [username] -p --databases [database1] [database2] > [filename].sql

Example:

mysqldump -u root -p --databases db1 db2 > multiple_databases_backup.sql

This command will export both db1 and db2 into multiple_databases_backup.sql.

3. Export All Databases

To export all databases on your MySQL server, use the –all-databases flag:

mysqldump -u [username] -p --all-databases > all_databases_backup.sql

Example:

mysqldump -u root -p --all-databases > all_databases_backup.sql

This creates a full backup of all databases into all_databases_backup.sql.

4. Export Only Table Structure (No Data)

If you only need the structure of the tables without the data, add the –no-data flag:

mysqldump -u [username] -p --no-data [database_name] > structure_only.sql

Example:

mysqldump -u root -p --no-data mydatabase > structure_only.sql

5. Export Specific Tables

To export specific tables from a database, list them after the database name:

mysqldump -u [username] -p [database_name] [table1] [table2] > [filename].sql

Example:

mysqldump -u root -p mydatabase table1 table2 > tables_backup.sql

Importing Databases with mysql

To import a database from an SQL file, use the mysql command.

1. Import a Single Database

To import an SQL file into a MySQL database, use the following command:

mysql -u [username] -p [database_name] < [filename].sql

-u [username]: The MySQL username.
-p: Prompts for the MySQL password.
[database_name]: The name of the database where the data will be imported.
[filename].sql: The name of the .sql file to import.

Example:

mysql -u root -p mydatabase < mydatabase_backup.sql

This command will prompt for the password of the root user and import the contents of mydatabase_backup.sql into mydatabase.

2. Import into a New Database

If you want to import into a new database, you first need to create the database and then import the .sql file.

Step 1: Create a new database.

mysql -u [username] -p -e "CREATE DATABASE [new_database_name];"

Step 2: Import the .sql file into the new database.

mysql -u [username] -p [new_database_name] < [filename].sql

Example:

mysql -u root -p -e "CREATE DATABASE newdatabase;"
 mysql -u root -p newdatabase < mydatabase_backup.sql

3. Import All Databases

If you backed up all databases using –all-databases, you can import them back using:

mysql -u [username] -p < all_databases_backup.sql

Example:

mysql -u root -p < all_databases_backup.sql

This command will restore all databases contained in the all_databases_backup.sql file.

Tips for Using mysqldump and mysql

Use Compression: To save disk space and transfer time, you can compress the backup file using gzip:
mysqldump -u root -p mydatabase | gzip > mydatabase_backup.sql.gz
And to import it:
gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase
Verify Database Before Import: Make sure the database you are importing into is empty or contains compatible data to avoid conflicts.
Check Character Set: If you are dealing with non-ASCII characters, ensure that you export and import with the correct character set using the –default-character-set option:
mysqldump -u root -p --default-character-set=utf8 mydatabase > mydatabase_backup.sql
Error Handling: If you encounter errors during import, check for common issues like missing databases or table structures and ensure that your .sql file is complete.

Summary

Exporting and importing MySQL databases using the command line is straightforward with mysqldump and mysql. These tools allow for flexibility in managing backups, migrating databases between servers, and creating structure-only or data-specific exports. By understanding the commands and their options, you can ensure smooth database operations and maintain data integrity during transfers.