How to Backup and Restore MySQL or MariaDB on Ubuntu 20.04

Estimated reading: 2 minutes 716 views

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

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

Exit the SQL console.

> exit;

Use mysqldump

-To back up more than one database at once, you can list them separately in your command after the –databases option. In this example, we will backup the databases greencloud

$ mysqldump -u root -p --databases greencloud> database_dump.sql

You can also make a backup of every MySQL or MariaDB database at once by specifying the –all-databases option:

$ mysqldump -u root -p --all-databases > database_dump.sql

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

3. How to restore a MySQL or MariaDB database backup

Your MySQL/MariaDB database backup is stored as a .sql file. Have this file handy and you can use the following command examples to restore a backup.
-This command will restore our database data to our mydata database from previous examples.

$ mysql -u root -p greencloud < database_dump.sql

-If your backup file contains multiple databases, you can select which ones to restore by using the –one-database flag in your command.

$ mysql --one-database greencloud < database_dump.sql

4. Conclusion

In this guide, we learned Linux commands to back up and restore MySQL or MariaDB databases. This included the backing up of multiple databases at once, or just a single database. You can use these commands to keep your MySQL data safe, and script regularly scheduled backups so you don’t always have to remember to run the commands.

Good Luck!