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.
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
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;
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.
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:
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 [email protected]:/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.
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.