How to Backup, Restore or Transfer MySQL/MariaDB Databases on Ubuntu 16.04

Backing up is an important part of dealing with databases. Regardless of whether you run a corporate site or just host WordPress, it’s important to back up your databases. AleForge offers free snapshots, which you can use, but it’s very easy to restore a database.

This tutorial assumes that you have MySQL or MariaDB installed on Ubuntu, although this will work with most Linux distributions. This tutorial will also touch on how to migrate from MySQL to MariaDB, as most of the steps are the same.

You do not need to use sudo in front of the commands, but it makes everything easier as you don’t need the database password.

Backing Up

The easiest way to back up a MySQL or MariaDB database is by using the mysqldump dump command. If you are not sure of the name of your database, use the following.

echo "SHOW DATABASES;" | sudo mysql

You will get a list of your databases. Be sure you know which one you want, and then just run the following command to backup into a .sql file.

sudo mysqldump example_database > $(date +"%F").sql

The above command will backup the database example_database into a file with the date that ends with .sql . You can change the filename to whatever you’d like, although keeping the filename as the date will be helpful if you accidentally delete an important row or column. Use date --help to learn about other ways to style the date.

Restoring

Restoring the database is even easier than backup it up. All you need to do is find the .sql file you’ve created, and run the following.

sudo mysql example_database < filename.sql

MySQL will restore the database from the file.

Migrating to MariaDB from MySQL

First, back up each of your databases.

sudo mysqldump example_database > example_database.sql

Then, install MariaDB, which will replace MySQL, and restore the databases you had by running the following for each database.

sudo mysql example_database < example_database.sql

It is recommended that you take a snapshot before doing something that can break your database. Snapshots are free on AleForge.