How to Backup and Restore MySQL or MariaDB on Ubuntu 20.04

Introduction

This tutorial describes the steps to properly backup a MySQL/MariaDB server on Ubuntu 20.04 LTS. Backing up databases is one of the most important tasks in production; a human error or a simple distraction could cause big issues, which are not easy to fix if there are no backups.

Prerequisites

This guide has been tested on a new Vultr Ubuntu 20.04 LTS cloud server instance.

• A fully updated Ubuntu 20.04 LTS server
• A non-root sudo user
• A working MySQL/MariaDB instance

Backup

Choose the Databases to Backup

Log in with a user that has the permissions to see all the databases (default: root) and check the database list.
If it is needed to use a password to login into the account, add -p to the command below.

$ mysql -u USERNAME

After the connection is established, run the command below to list all the databases.

> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| test_data          |
| important_db       |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
5 rows in set (0.012 sec)

Exit the SQL console.

> exit;

Use mysqldump

If you use a password to log in to the account, add -p to the command below. If you need to save all the databases, you can use the shortcut --all-databases instead of --databases names.

$ mysqldump -u USERNAME --databases test_data important_db > database_dump.sql

A file called "database_dump.sql" is created and contains all the data to rebuild the selected databases.

Secure the Backup

If the data in the database contains sensitive data may be a good idea to encrypt it before saving it or moving it between servers.
Execute the first command below and then type in the password; it won't be shown on your screen.

$ openssl enc -aes-256-cbc -pbkdf2 -in database_dump.sql -out database_dump.sql.enc
  enter aes-256-cbc encryption password:
  Verifying - enter aes-256-cbc encryption password:
  
$ rm database_dump.sql

To decrypt the backup in the new server, use the command below.

$ openssl enc -d -aes-256-cbc -pbkdf2 -in database_dump.sql.enc -out database_dump.sql
  enter aes-256-cbc encryption password:

Moving the Backup

Sometimes the .sql may be too large, and the transfer process through a residential internet connection may require too much time. Instead, it is possible to use tools like rsync to share the data directly between servers, taking advantage of the network speed of Vultr instances.

$ rsync -a ./database_dump.sql user@192.0.2.1:/tmp/

• Replace user@ with your username on the remote server.
• Replace 192.0.2.1 with the remote server's IP address.
• Replace /tmp/ with the directory location on the remote server.

Restore the Database

To restore a database dump use the mysql utility.

$ mysql -u USERNAME < database_dump.sql

If you need a password to log in to the account, add -p to the command below before the minus sign.