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 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 => 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. 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 dump the database content into a file named database_name.bak. You can then take that backup file and store it 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.
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 a new database, logon to the database server by running the commands below: Then run the commands below to create a new database. After that, exit the database server and run the commands below to import the exported database into the new one created above.
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 and MariaDB servers. Enjoy!
