How to Import and Export MySQL or MariaDB Database

Students and new web admins should learn to export and import MySQL or MariaDB databases. Learning to export and import these databases is an excellent skill for those managing a WordPress website and considering migrating to a new host or changing their sites.

One good habit while managing your MySQL or MariaDB-based website is to always perform a backup before making changes that may break your site.

Backing up can be manual or automated, but always take a good backup of your site’s content before making changes.

Manually backing up MySQL or MariaDB can also be done by exporting the databases.

This brief tutorial will show you how to export or import databases from these servers.

Prerequisites

Before exporting or importing databases from the database server, you must have access and the necessary rights. You should probably use the root user credentials.

Exporting your databases

MariaDB can be a drop-in replacement for MySQL and vice versa. This means you can rip out the MySQL database server and install the MariaDB database server or the other way around, and your applications will still function and probably won’t know the difference.

So, the same commands work on both servers.

To export your databases from either server, run the commands below

mysqldump -u root -p database_name > database_name.bak

• mysqldump => is the command to create or dump the database content into a file
• -u root => (username) is the username with the rights to perform the task
• -p => password prompt. It will prompt you to type the root or user password
• database_name => is the database name on the server you want to export
• database_name.bak => is the backed-up database or exported database file.

So, the commands above dump the database content into a file named database_name.bak. You can then take that backup file and store it securely. This is how one backs up or exports databases from MySQL or MariaDB.

Run the commands below to view the database header to validate that the backup file has the correct content.

head -n 5 database_name.bak

Importing your databases

First, You should create a new and empty database to import the exported one. This new database will be used to store the content that was exported. Usually, you will use the same name as the previous database if you’re exporting to a different host or database server. However, if you’re using the same host, drop the current database after exporting it and create a new one.

To create a new database, log in to the database server by running the commands below:

mysql -u root -p

Then, run the commands below to create a new database.

CREATE DATABASE database_name;

After that, exit the database server and run the commands below to import the exported database into the new one created above.

mysql -u root -p database_name < database_name.bak

• mysql => command use the import the database
• -u root => user account used to import the database
• -p => prompt to type the user account password
• database_name => the database name on the new database server
• database_name.bak => the exported database from the previous host

If you run all the above commands without a problem, you will have successfully exported and restored a database from MySQL and MariaDB servers.

Enjoy!