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.
The main difference between REPLACE
and INSERT IGNORE
is that REPLACE
deletes existing data, while INSERT IGNORE
leaves them untouched. An important but often forgotten side effect of the REPLACE
statement is that the replaced rows will have new AUTO_INCREMENT
values even if all other values are identical to the old ones. If foreign keys are not used to preserve cross-table data integrity, this could be a problem. If foreign keys are used, REPLACE
will be slower.
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
.
Remember that LOCK TABLES
and UNLOCK TABLES
implicitly commit the current transaction, thus some of the mentioned options are mutually exclusive. Using --no-autocommit
and --add-locks
together makes no sense, because table locks will make transactions useless. Whether we use this option or not, we usually want to use multiple-row INSERT
statements instead of one statement for each row. To do this, we can use the --extended-insert
option.
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';