How to import and export MySQL/MariaDB database

If you want to back up or restore a MySQL or MariaDB database, command-line tools offer a simple and reliable solution. Importing and exporting are done directly via the console and are suitable for both local testing en­vir­on­ments and pro­duc­tion systems. The only re­quire­ment is that the database file is in a valid SQL format.

How to import MySQL database

Open a terminal and log in to the MySQL client using the following command:

mysql -u root -p

The system will prompt you to enter the password for the MySQL root user. Once logged in, create the new, empty database:

CREATE DATABASE my_new_db;

Replace my_new_db with your desired database name, then exit the MySQL client using:

quit;

In the terminal, navigate to the directory where your SQL dump file is located. Then run the following import command:

mysql -u root -p my_new_db < my_db_dump.sql

Replace my_new_db with the name of your target database and my_db_dump.sql with the filename of your SQL file.

How to export MySQL database

To back up a database, use the following command:

mysqldump -u root -p my_db > my_db_dump.sql

Replace my_db with the name of your database. The system will create a file con­tain­ing all tables and data. If you want to back up only the table structure without any content, use the following command:

mysqldump -u root -p --no-data my_db > structure.sql

If you want to export only the data (without CREATE TABLE state­ments), use:

mysqldump -u root -p --no-create-info my_db > data_only.sql

How to import MariaDB database

Importing into MariaDB works almost the same as with MySQL, but you’ll use the mariadb command instead of mysql.

Open a terminal and log in to the MariaDB client:

mariadb -u root -p

Next, enter the password and create the new database within the client:

CREATE DATABASE my_new_db;

Exit the session with:

quit;

In the terminal, run the following command to load the data into the database:

mariadb -u root -p my_new_db < my_db_dump.sql

Make sure to enter the correct file name and database name here as well.

How to export MariaDB database

Use the following command to create a backup of the database:

mariadb-dump -u root -p my_db > my_db_dump.sql

If you only want to back up the structure of the database:

mariadb-dump -u root -p --no-data my_db > structure.sql

To export only the data without the table structure, enter:

mariadb-dump -u root -p --no-create-info my_db > data_only.sql

Make sure you have the necessary per­mis­sions to access the database and perform read and write op­er­a­tions. For pro­duc­tion systems, it is strongly re­com­men­ded to create a full backup of the target database before per­form­ing an import.