Students and new webmasters should learn how to export and import MySQL or MariaDB databases. For those managing a WordPress website and thinking of migrating to a new host or will be making changes to their sites, then learning to export and import these databases is a great skill to have.
One good habit to keep 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 a manual or automated task, 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 is going to 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 necessary rights. You should probably use the root user credentials
Step 1: Exporting your databases
MariaDB can be a drop-in replacement for MySQL and vice versa. This means, you can rip out MySQL database server and install 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 servers, 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 rights to perform the task
- -p => password prompt. will prompt you to type the root or user password
- database_name => is the name of the database on the server you want to export
- database_name.bak => is the backed-up database or exported database file.
So, the commands above dumps the database content into a file named database_name.bak. You can than take that backup file and store in a secure place. This is how one backs up or export databases from MySQL or MariaDB.
To validate that the backup file has the correct database content, run the commands below to view the database header.
head -n 5 database_name.bak
Step 2: Importing your databases
To import the exported database, you should first create a new and empty database. This new database will be used to store the content that was exported. Normally you will use the same name as the previous database if you’re exporting to a different host or database server. If you’re using the same host, then drop the current database after exporting it and create a new one.
To create new database, logon 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 commands above without problem, you will have successfully exported and restored a database from both MySQL or MariaDB servers.
Enjoy!
