The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another database server (not necessarily MariaDB or MySQL). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.
If you are doing a backup on the server and your tables all are MyISAM tables, consider using mysqlhotcopy instead because it can accomplish faster backups and faster restores.
mysqldump dumps triggers along with tables, as these are part of the table definition. However, stored procedures, views, and events are not, and need extra parameters to be recreated explicitly (for example, —routines and —events). Procedures and functions are however also part of the system tables (for example mysql.proc).
mysqldump supports the enhancements for START TRANSACTION WITH CONSISTENT SNAPSHOT.
From MariaDB 10.4.6, mariadb-dump is a symlink to mysqldump.
Performance
mysqldump doesn’t usually consume much CPU resources on modern hardware as by default it uses a single thread. This method is good for a heavily loaded server.
Disk input/outputs per second (IOPS), can however increase for multiple reasons. When you back-up on the same device as the database, this produces unnecessary random IOPS. The dump is done sequentially, on a per table basis, causing a full-table scan and many buffer page misses on tables that are not fully cached in memory.
It’s recommended that you back-up from a network location to remove disk IOPS on the database server, but it is vital to use a separate network card to keep network bandwidth available for regular traffic.
Although mysqldump will by default preserve your resources for regular spindle disks and low-core hardware, this doesn’t mean that concurrent dumps cannot benefit from hardware architecture like SAN, flash storage, low write workload. The back-up time would benefit from a tool such as MyDumper.
Usage
There are three general ways to invoke mysqldump:
mysqldump does not dump the INFORMATION_SCHEMA database by default. MariaDB dumps the INFORMATION_SCHEMA if you name it explicitly on the command line, although currently you must also use the —skip-lock-tables option.
To see a list of the options your version of mysqldump supports, execute mysqldump —help.
Row by Row vs. Buffering
mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the —quick option (or —opt, which enables —quick). The —opt option (and hence —quick) is enabled by default, so to enable memory buffering, use —skip-quick.
Mysqldump in MariaDB 10. 3 and Higher
mysqldump in MariaDB 10.3 includes logic to cater for the mysql.transaction_registry table. mysqldump from an earlier MariaDB release cannot be used on MariaDB 10.3 and beyond.
Mysqldump and Old Versions of MySQL
If you are using a recent version of mysqldump to generate a dump to be reloaded into a very old MySQL server, you should not use the —opt or —extended-insert option. Use —skip-opt instead.
Options
Some mysqldump options are shorthand for groups of other options:
When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, —disable-keys —lock-tables —skip-opt would not have the intended effect; it is the same as —skip-opt by itself.
Special Characters in Option Values
Some options, like —lines-terminated-by, accept a string. The string can be quoted, if necessary. For example, on Unix systems this is the option to enclose fields within double quotes:
Option Files
In addition to reading options from the command-line, mysqldump can also read options from option files. If an unknown option is provided to mysqldump in an option file, then it is ignored.
Option Groups
XML output from mysqldump includes the XML namespace, as shown here :
Restoring
To restore a backup created with mysqldump, use the mysql client to import the dump, for example:
mysql db_name < backup-file.sql
Examples
A common use of mysqldump is for making a backup of an entire database:
You can load the dump file back into the server like this:
Or like this:
mysqldump is also very useful for populating databases by copying data from one MariaDB server to another:
It is possible to dump several databases with one command:
To dump all databases, use the —all-databases option:
For InnoDB tables, mysqldump provides a way of making an online backup:
This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the MariaDB server may get stalled until those statements finish. After that, the dump becomes lock free and does not disturb reads and writes on the tables. If the update statements that the MariaDB server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates.
For point-in-time recovery (also known as “roll-forward,” when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log or at least know the binary log coordinates to which the dump corresponds:
The —master-data and —single-transaction options can be used simultaneously, which provides a convenient way to make an online backup suitable for use prior to point-in-time recovery if tables are stored using the InnoDB storage engine.
See Also
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.
This chapter provides a brief overview of the MySQL command-line
programs provided by Oracle Corporation. It also discusses the
general syntax for specifying options when you run these programs.
Most programs have options that are specific to their own operation,
but the option syntax is similar for all of them. Finally, the
chapter provides more detailed descriptions of individual programs,
including which options they recognize.
1 Overview of MySQL Programs
There are many different programs in a MySQL installation. This
section provides a brief overview of them. Later sections provide
a more detailed description of each one, with the exception of NDB
Cluster programs. Each program’s description indicates its
invocation syntax and the options that it supports.
Section 21.4, “NDB Cluster Programs”, describes programs
specific to NDB Cluster.
Most MySQL distributions include all of these programs, except for
those programs that are platform-specific. ( For example, the
server startup scripts are not used on Windows.) The exception is
that RPM distributions are more specialized. There is one RPM for
the server, another for client programs, and so forth. If you
appear to be missing one or more programs, see
Chapter 2, Installing and Upgrading MySQL, for information on types of
distributions and what they contain. It may be that you have a
distribution that does not include all programs and you need to
install an additional package.
Each MySQL program takes many different options. Most programs
provide a —help option that you can use to get a
description of the program’s different options. For example, try
.
You can override default option values for MySQL programs by
specifying options on the command line or in an option file. See
Section 4.2, “Using MySQL Programs”, for general information on
invoking programs and specifying program options.
The MySQL server, , is the main program
that does most of the work in a MySQL installation. The server is
accompanied by several related scripts that assist you in starting
and stopping the server:
Several programs perform setup operations during MySQL
installation or upgrading:
MySQL client programs that connect to the MySQL server:
MySQL program-development utilities:
For a full list of environment variables used by MySQL programs,
see Section 4.9, “Environment Variables”.
