Learn How To Backup, Restore or Transfer MySQL/MariaDB Databases on Ubuntu 16.04
Table of Contents
Backing up is an important part of dealing with databases. Regardless whether you run a corporate site, or just host WordPress, it’s important to back up your databases. IT Web Services 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, backup 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 IT Web Services.
Need help?
Do you need help setting up this on your own service?
Please contact us and we’ll provide you the best possible quote!