The mysqldump command is the most used tool to perform hot logical backups. It is included in all MariaDB distributions and is located in the bin directory.

Usually, mysqldump is used to create a dump file: it connects to MariaDB, reads the data we want to back up, and creates a file containing the SQL statements that are required to recreate the same data. It has several options that allow us to control which data is included in the backup and modify the SQL statements that are written in the file. The dump file can also be manually edited to fit particular needs. The generated SQL statements use executable comments (mentioned in Chapter 1, Understanding the Essentials of MariaDB) so that it is possible to restore the dump in an older version of MariaDB, in a MySQL installation, or possibly even on different DBMSs.

For all these reasons, mysqldump is an amazingly flexible program. This explains why mysqldump is the preferred tool for taking logical backups. It is also used in other situations, such as copying a database or a table from one server to another, or to generate a dump file that is run during an application's installation.

The drawback of dump files is that they take a lot of space. Not only do logical backups represent the data in an uncompressed way, but a dump file can even put that data inside SQL statements. However, mysqldump is also able to create textual backups, as explained in the next section.

The mysqldump command supports three syntaxes depending on whether we want to back up all databases in the server, a limited set of databases, or a set or tables within a database, shown as follows:

mysqldump [other_options] --all-databases > file_name
mysqldump [other_options] --databases db1 db2 … > file_name
mysqldump [other_options] db_name table1 table2 … > file_name

In the third case, no USE command is included in the dump file. The reason is that the user probably wants to recreate the dumped tables into another database.

Even while dumping multiple databases, it is possible to exclude some tables. To ignore one table, we can add an option like this:

--ignore-table=db_name.table_name

To ignore multiple tables, we must repeat the option multiple times. Specifying a comma-separated list is not correct. For example:

--ignore-table=db_name.table_one --ignore-table=db_name.table_two …

The options that tell mysqldump how to connect to the server are standard. They are the same as the mysql command-line client and all other client programs distributed with MariaDB.

By default, the dump files include a CREATE DATABASE statement. To skip it, we can use the --no-create-db option. Usually, we want to have a DROP DATABASE before CREATE DATABASE. So, if a damaged database exists, it is replaced with complete, correct data. We can use –add-drop-database. Both the table's definition and data are included in the dump by default. However, it is possible to exclude table definitions with --no-create-info or table data with –no-data. The table options in CREATE TABLE are not standard (they only work on MariaDB and mostly on MySQL) and are only included if --table-options is specified.

Sometimes, we do not want a dump to destroy and replace existing databases, because they contain some tables that we want to preserve. Even in that case, we probably want the dumped tables to completely replace the existing ones (for example, because at least one of them is damaged). To do this we can use the --add-drop-table option, which adds a DROP TABLE statement before each CREATE TABLE. As an alternative, we may want the data to be inserted with the REPLACE statement instead of INSERT: this way, the dumped data will replace the existing data but, if the table also contains rows that are not in the dump file, those rows will be preserved. This can be done using the --replace option. Or, we can use --insert-ignore, which turns INSERT statements into INSERT IGNORE. This is useful if we want dumped data to be inserted only if they do not exist in the table.

Stored programs (triggers, routines, functions, and events) are not included by default. However, a complete logical backup should include them. Also, they usually do not take much space. To dump stored programs, we can use the --triggers, --routines, and --events options.

Usually, we want the dump to consist of a single transaction. This guarantees data integrity across tables. If we specify the --single-transaction option, mysqldump begins a transaction in the REPEATABLE READ isolation level before starting reading data and issues COMMIT after the dumping process. The --no-autocommit option surrounds each table's inserts in the dump file with SET autocommit=0; and COMMIT. This makes the restore faster, but while a table is populated, other tables can be modified by other sessions.

When dumping non-transactional tables, --single-transaction does not guarantee data integrity. Thus, in such cases, we will use an option that locks the tables. The --lock-all-tables option acquires a global read lock on all databases. This completely blocks all the write operations on the server until the end of the dumping process but is the only way to guarantee consistency across several databases if non-transactional tables are used. However, often we only need to guarantee data integrity on a per-database basis. In this case, we can use the --lock-tables option, which locks one database at a time. The --add-locks option adds LOCK TABLES before each table's inserts and UNLOCK TABLES after each table's inserts. The --disable-keys option makes the restoring of MyISAM tables faster using ALTER TABLE … DISABLE KEYS.

By default, mysqldump reads all the rows from the server into a buffer and writes them together into the dump file. While this is performance optimization, when dumping large amounts of data, it may require too much memory. To avoid bufferizing the rows, the --quick option can be specified.

The following is an example of the beginning of a typical dump file:

-- MySQL dump 10.14  Distrib 10.0.8-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version    10.0.8-MariaDB-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Each database dump begins with lines similar to the following:

--
-- Current Database: 'flexviews'
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ 'flexviews' /*!40100 DEFAULT CHARACTER SET latin1 */;

USE 'flexviews';

Restoring a dump file is simple. Basically, we just need to execute the statements contained in it. There are many ways to do this; for example, if you have a small dump file, you could even copy its content and paste it into your favorite GUI. However, the most practical way is invoking the mysql command-line client using the file as input. The following syntax works on all systems, including Windows:

mysql [options] < file_name

If we already have a mysql instance open, we can use the SOURCE client command:

SOURCE 'file_path'path'path';