MariaDB – Backing up databases

There are 3 levels of mysql database backups you can do:

  • Backup the contents of a single database
  • Backup multiple databases
  • Backup the entire mysql server

when creating database backups, the resulting backup files are actrually really big sql files that will repopulate a database. In all cases we create database backups using the mysqldump utility.

Backup the contents of a single database

This is done by running the command:

$ mysqldump -u root -p'rootpassword' -h mariadb-server.example.com online_shop > online_shop_backup.sql

This file will populate a database’s content. It won’t create the database itself. This is handy if you want to create a clone of a production db in a dev environment. It also means that when restoring this backup file, you first need to manually create an empty database for this backup file to populate.

Backup multiple databases

To backup multiple databases we do:

$ mysqldump -u root -p'rootpassword' -h mariadb-server.example.com --databases online_shop mysql > multiple_databases_backup.sql

Notice we used the ‘–databases’ option and listed out which databases we want backed up. This time, this backup file will restore the database itself as well as it’s content.

Backup mysql as a whole

Here’s the command for backing up all the databases into a single backup file:

$ mysqldump -u root -p'rootpassword' -h mariadb-server.example.com --all-databases > backup_everything.sql

Take the RHCSA Quiz

This article is part of our RHCSA Study guide (click on the yellow tab on the far left). By the end of this article you should be able to answer the following questions:


What are the three main types of database backups you can do?

– Backup the contents of a single database
– Backup multiple databases
– Backup the entire mysql server

What is the command to backup the contents of just one db called 'online_shop'?

$ mysqldump -u root -p’rootpassword’ online_shop > online_shop_backup.sql

What is the command to backup the contents of 2 dbs, called 'online_shop' and 'warehouse'?

$ mysqldump -u root -p’rootpassword’ –databases online_shop warehouse > multiple_databases_backup.sql

What is the command to backup everything?

$ mysqldump -u root -p’rootpassword’ –all-databases > backup_everything.sql