Being able to export or import MariaDB and MySQL databases is a good skill to have when managing a website or blog. This brief tutorial shows students and new users how to easily export or import MariaDB or MySQL databases on Ubuntu 17.04 | 17.10.
You can use this tip to backup or restore your databases in cases where they’re corrupted after a change or to migrate to a different server. This should be straightforward and works on both systems.
In order to do this, you must have root access to the database server and be comfortable running commands on the Linux command terminal. When everything is in place and you’re ready to export / import the databases, continue below.
Step 1: Exporting MariaDB / MySQL Databases
These two database servers are basically the same. MariaDB was fork out of MySQL and it’s a drop-in replacement for MySQL on Linux systems. So, the same commands almost always work on both systems. Both systems also use MySQL Dump command to export database content into a file. The file can then be moved during migration or to restore the servers.
The commands below can be used to export a database named mydatabase into a file called mydatabase.sql using the root credential.
sudo mysqldump -u root -p mydatabase > mydatabase.sql
- root is used log in to the database
- mydatabse is the name of the database that will be exported
- mydatabase.sql is the file in the current directory that the output will be saved to
When you run the commands above, you should be prompted to enter the root password. When you do that, all the content in the mydatabase will be exported into a file called mydatabase.sql. Now you can take the exported file anywhere.
Step 2: Importing MariaDB / MySQL Databases
Now that you’ve exported the database, the commands below can be used to import the database. To do that, logon to the new database server and create a blank database. To logon, run the commands below.
sudo mysql -u root -p
You should be prompted to enter the root password.
After logging on, run the commands below to create a blank database called mynewdatabase.
CREATE DATABASE mynewdatabase;
Exit after running the above command.
Now to import the database you exported earlier, run the commands below.
sudo mysql -u root -p mynewdatabase < mydatabase.sql
You should be prompted for the root password. If everything works, the exported database content should be stored in the new database created above.
That’s it!
You’ve successfully exported and imported MariaDB / MySQL database content.
Enjoy!
You may also like the post below:
