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.
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
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 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.