The mysqldump program is included with MariaDB and works well as a simple backup tool.
Let's get started by following the ensuing steps:
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
If it completes successfully,
mysqldumpwill place a line similar to the following command at the end of the output file:-- Dump completed on <date> <time>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.
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.
The mysqldump program has many options. We will discuss some of the most useful ones here.
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.
