Export and Import Databases in MySQL

The most difficult part of managing websites are its database and timely backing up and exporting and importing them for many use case. It become super difficult if you do it in terminals using command line interface (CLI) on Linux based machines.

To make things easier for you here in this tutorial, I am going to explain you – how to export and import databases in MySQL (works on MariaDB as well). Both are almost same, MySQL is the first one and the MariaDB is fork of the MySQL with different aim. But, if you want a dedicated instructions for MariaDB, check out this tutorial.

Exporting and importing databases in MySQL can be done easily using the command-line tools mysqldump (for export) and mysql (for import).

Here’s a step-by-step guide:

Exporting a MySQL Database.

Prerequisites:

I assume that you have an Linux machine running with all the necessary software installed like MySQL server, and know the root details.

1. Ensure you have access to the MySQL server.
2. Know the database name and user credentials.

Technically you need some time and working keyboard :)

Command:

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

• [username]: Replace with your MySQL username.
• [database_name]: The name of the database you want to export.
• [output_file.sql]: The file where the exported database will be saved.

Example:

mysqldump -u root -p mydatabase > mydatabase_backup.sql

Additional Options:

Export with structure only (no data):

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

Export with data only (no structure):

mysqldump -u [username] -p --no-create-info [database_name] > [output_file.sql]

Compress the output using gzip:

mysqldump -u [username] -p [database_name] | gzip > [output_file.sql.gz]

Importing a MySQL Database.

Prerequisites:

1. Ensure the target database exists or create it beforehand.
2. Have the .sql file from the export.

Command:

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

• [username]: Replace with your MySQL/MariaDB username.
• [database_name]: The name of the target database.
• [input_file.sql]: The file containing the exported database.

Example:

mysql -u root -p mydatabase < mydatabase_backup.sql

Additional Notes:

If the backup file is compressed (e.g., .sql.gz), decompress it before importing:

gunzip [input_file.sql.gz]

Creating the Database Before Importing.

If the target database does not exist, you can create it:

Log in to MySQL:

mysql -u root -p

Create the database:

CREATE DATABASE mydatabase;

exit

Exit and import the database:

mysql -u root -p mydatabase < mydatabase_backup.sql

Exporting and Importing All Databases.

Export All Databases:

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

Import All Databases:

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

Common Troubleshooting.

Permission Denied: Ensure the user has sufficient privileges on the database.

File Not Found: Verify the path to the .sql file is correct.

Character Set Issues: Specify the character set during export/import if necessary:

mysqldump --default-character-set=utf8mb4 -u [username] -p [database_name] > [output_file.sql]

mysql --default-character-set=utf8mb4 -u [username] -p [database_name] < [input_file.sql]

By following these steps, you can efficiently export and import databases in MySQL/MariaDB; eliminating the need of phpMyAdmin as an additional software.