Making backups with mysqldump

The mysqldump program is included with MariaDB and works well as a simple backup tool.

Getting ready

Create a backup user by following the instructions in the Creating a backup user recipe.

How to do it…

Let's get started by following the ensuing steps:

1. To make a complete backup of all the data to a file named my-backup.sql, run the following command:

   mysqldump --user=backupuser -p \
     --all-databases > my-backup.sql
   

2. If it completes successfully, mysqldump will place a line similar to the following command at the end of the output file:

   -- Dump completed on <date> <time>
   

3. If a dump fails, an error message will be printed to the screen and the data in the backup file will end right before the error took place. Checking both the error message and the end of the backup file can give us important clues to figure out the failure.

How it works...

The mysqldump program generates backups in SQL formatted text. These backups can then be restored to the same MariaDB install, to a different MariaDB server, or because they are in SQL format, to a different database altogether.

Depending on the sizes of the databases in our database server, and whether we choose to backup all of the databases or just one or two, the backup file created by mysqldump could potentially be very large. We need to keep this in mind when using this program.

There's more...

The mysqldump program has many options. We will discuss some of the most useful ones here.

--add-drop-database

The --add-drop-database option causes mysqldump to add SQL commands to the backup output to drop a given database and then recreate it prior to restoring the data. This helps us to prevent duplicate data from being written to the database.

--add-drop-table

Similar to the previous option, the --add-drop-table option causes mysqldump to add SQL commands to the backup output in order to drop the tables prior to recreating them and inserting data.

--add-locks

The --add-locks option surrounds the table output of the backup with LOCK TABLES and UNLOCK TABLES statements. When restoring from a backup, locking the tables speeds up the restore.