How to Backup and Restore MySQL or MariaDB on Ubuntu 20.04
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.
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.
-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
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.