Export and Import Databases in MariaDB

Managing databases is a crucial skill for anyone working with MariaDB. Exporting and importing databases allow you to back up your data, migrate databases between servers, or share them with other developers. This tutorial will guide you step-by-step through the process of exporting and importing databases in MariaDB.

In earlier tutorial, we seen how to export and import databases from MySQL server. Same would work for MariaDB as well, but for future, here is a dedicated tutorial.

Exporting and importing databases in MariaDB can be done easily using the terminal and command-line tools mariadb-dump (for export) and mariadb (for import).

Here’s a step-by-step guide:

Exporting a MariaDB Database.

Prerequisites:

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

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

Technically you need some time and working keyboard :)

Command:

mariadb-dump -u [username] -p [database_name] > [output_file.sql]

• [username]: Replace with your MariaDB 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:

mariadb-dump -u root -p mydatabase > mydatabase_backup.sql

Additional Options:

Export with structure only (no data):

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

Export with data only (no structure):

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

Compress the output using gzip:

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

Importing a MariaDB Database.

Prerequisites:

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

Command:

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

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

Example:

mariadb -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 MariaDB:

mariadb -u root -p

Create the database:

CREATE DATABASE mydatabase;

exit

Exit and import the database:

mariadb -u root -p mydatabase < mydatabase_backup.sql

Exporting and Importing All Databases.

Export All Databases:

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

Import All Databases:

mariadb -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:

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

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

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