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 environments and production systems. The only requirement 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 containing 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 statements), 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 permissions to access the database and perform read and write operations. For production systems, it is strongly recommended to create a full backup of the target database before performing an import.