MySQL: Ways to run mysqldump

This text exists mainly so that I paste the URL into the #mysql
channel in Libera IRC.
The mysqldump
tools allows you to convert a MySQL database server or individual schemas back to SQL.
You are left with a script that is supposed to be loadable into a target server and gives you back the full database, including all objects in it.
You can read that SQL as a script into an empty server to create a new instance, or process it with different tools for different purposes. So in general, a workflow can look like this:
$ mysqldump --options --more-options and parameters > somescript.sql
$ scp somescript.sql somewhere@else.com:
$ ssh somewhere@else.com
...
$ mysql --show-warnings --whatever-options < somescript.sql
Instead of mysql
with an input redirect, you may also use the command line client and the source
command:
$ mysql --show-warnings --whatever-options
mysql> source somescript.sql
TL;DR
To get everything, run
$ mysqldump ... --single-transaction --source-data=2 \
> --routines --triggers --events \
> --all-databases |
> gzip -9 > mydump.sql.gz
To get one database, run
$ mysqldump ... --single-transaction --source-data=2 \
> --routines --triggers --events \
> --databases dbname |
> gzip -9 > mydump.sql.gz
To get only the schema, use --no-data
.
For example:
$ mysqldump ... --single-transaction --source-data=2 \
> --routines --triggers --events \
> --no-data \
> --databases dbname > mydump.sql
How do we get there?
Setting expectations
SQL scripts are not a compact data representation, and often the mysqldump
of a server is larger than $DATADIR
of that instance.
The script usually compresses very well.
So it is useful to run gzip
or similar on it.
SQL scripts are a representation of the data in the instance in textual form without indexes. Reading the script into a new server not only requires parsing the SQL into server internal data structures. It also requires that indexes are being rebuilt, and that is a process that consumes a lot of CPU and memory.
Most people would suggest that you do not ship data larger than 10 GB in the form of a mysqldump
, because loading it will take too long.
As a rule of thumb that holds well, but it really is a function of memory available for index building, and the size of the largest index in the dump.
mysqldump
is single threaded. It profits from faster CPUs, but not from more cores.
Loading a dump is also single threaded.
The defaults of mysqldump
are somewhat sane, if you only want to dump tables.
For code in the database, additional options are required that are not part of the standard configuration.
Dumping everything, atomically
We are assuming here that all tables are InnoDB, without exception. If in 2023 you still are using non-transactional tables, you are beyond help anyway.
Dumping all data in the database takes time. While running the dump, the data you are trying to dump may change due to other processes making changes.
Specifically, with the default isolation level, each single table will maintain a consistent read view while being dumped.
It will therefore not change for the mysqldump
while it is being dumped.
But between tables changes may happen.
The option --single-transaction
can be used to run the entire mysqldump
in a single transaction, providing you with a consistent dump of the database.
A consistent dump of a database is associated with exactly one binlog position.
In traditional replication, and for bookkeeping purposes, it is useful to take note of this binlog position.
So the option --source-data=2
(previously --master-data=2
) should also be used to record this binlog position in the dump.
This will result in a comment to be added at the top of the dump file looking like this:
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=157;
So far, our mysqldump
command to dump everything should look like this:
$ mysqldump ... --single-transaction --source-data=2
Dumping code in the database
By default, mysqldump
will not dump all non-table objects in the database.
You need to pass --routines
, --trigger
and --events
to get all the code from the database as well.
On newer versions of MySQL, --trigger
is enabled by default, so they are dumped.
On older versions, you would have to manually mention it on the command line.
Even on the most recent versions of MySQL, stored functions, stored procedures and events are not dumped automatically.
You would have to mention --routines
and --events
for them to be dumped.
On older versions of MySQL that was not a problem, if the mysql.*
schema was part of the dump.
Inside it were tables in there that contained functions, procedures and even definitions.
Starting with MySQL 8, these tables are no longer used, because the definitions are now stored in hidden data dictionary tables.
Using these options is now mandatory, if you want this code to be part of the dump.
So in order to also dump code in the database, our command becomes now
$ mysqldump ... --single-transaction --source-data=2 --trigger --routines --events
--opt
is a set of options enabled by default
The mysqldump
runs with --opt
enabled by default.
You could run with --skip-opt
to prevent this, but this is not helpful.
--opt
is a shorthand for the following collection of options:
--add-drop-table
- Generate a
DROP TABLE
statement before eachCREATE TABLE
statement in the dump. --add-locks
- Enclose the
INSERT
statements loading data into the table withLOCK TABLES
andUNLOCK TABLES
statements. This used to make data load faster for MyISAM tables, but should not be necessary for InnoDB tables. It is not harmful, especially not for initial data loads, so do not worry about it. --create-options
- Preserve all MySQL specific create options for all tables. This is very much needed in almost all use-cases. It may be disabled for data transport from MySQL to other database systems.
--disable-keys
- For each table, enclose the
INSERT
statements inDISABLE KEYS
andENABLE KEYS
statements. For MyISAM tables, this improves data load speed, and index quality. For InnoDB, it does nothing, but is also not harmful. --extended-insert
- The
INSERT
statements generated will use extended insert syntax. This will generate very long lines in the dump (matching whatever is defined asmax-allowed-packet
). This will minimize round trips, and make the load faster. It will make grepping and editing the dump very hard. It may create problems loading the data, if the source server has a modified, largemax-allowed-packet
. Depending on what you want to do with the dump, it may be useful to--skip-extended-insert
to turn this off. --lock-tables
- This will lock the local tables before dumping them.
This is important when using MyISAM tables, and is overridden, if tables are InnoDB, and
--single-transaction
is used. So we are okay with it. --quick
- This dumps the tables using
mysql_use_result()
, so the result is streamed using very little memory in the client. If you run with--skip-quick
, full tables will be downloaded into the client before then can be dumped. If these tables are many gigabytes in size,mysqldump
will be using a lot of memory, and will be very unhappy. Do not disable--quick
. --set-charset
- Generate a
SET NAMES
statement as needed to preserve character encoding. This should be always on, in order to preserve character encoding correctly.
In general, you should be fine with --opt
and there is no need to change anything.
In some cases, you want to run with --skip-extended-insert
, in order to search and manually edit the data in the dump easier.
The dump will load somewhat slower, and will be somewhat larger in this case.
Dumping only the schema
If you want to dump only the schema, and not the data, there is an option for that: --no-data
.
You get an SQL file with no INSERT
, only CREATE TABLE
and friends.
You would still need --routines
, --triggers
and --events
to get all the code.
We get
$ mysqldump ... --single-transaction --source-data=2 \
> --trigger --routines --events \
> --no-data
Dumping all databases, or just one database
–all-databases
You need to tell mysqldump
what to dump.
This can be --all-databases
, which will do exactly that, including the mysql
database with all the grants, the help, the timezone data and so on.
If --all-databases
is being used, each database in the dump will be created with a CREATE DATABASE
statement.
A matching DROP DATABASE
statement is not being generated.
That is less of a problem than one might assume, because the generated CREATE DATABASE
statement is protected by an IF NOT EXISTS
clause.
Still, it may be useful to DROP DATABASE IF EXISTS
before creating a new one, in order to get rid of tables that should not be there.
To achieve this, you could add the option --add-drop-database
.
But: If you do that together with --all-databases
, then a DROP DATABASE ...;
statement will put in front of each CREATE DATABASE
statement, including the mysql
system database:
/*!40000 DROP DATABASE IF EXISTS `mysql`*/;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `mysql`;
In MySQL 8, the mysql
schema cannot be dropped anymore, so you need to edit that out manually to make the dump useful.
Why mysqldump
does not special case this schema nobody knows:
it is known broken, and documented as broken in the manual page.
But nobody cared enough to put this single && (!my_strcasecmp(charset_info, ..., "mysql"))
into
the codebase
.
We end up running
$ mysqldump ... --single-transaction --source-data=2 \
> --routines --triggers --events \
> --all-databases --add-drop-database > mydump.sql
A single database
Instead of dumping all database, maybe we want just one database, or even a single table from a single database.
If you run mysqldump databasename
to dump a single database like this:
$ mysqldump ... --single-transaction --source-data=2 \
> --routines --triggers --events \
> databasename > mydump.sql
no CREATE DATABASE
statement is generated.
You will just get a sequence of CREATE TABLE
statement.
This can easily be imported into a differently named database.
Also, adding --add-drop-database
to this does nothing.
If you run mysqldump --databases databasename
to dump a single database instead,
$ mysqldump ... --single-transaction --source-data=2 \
> --routines --triggers --events \
> --databases databasename > mydump.sql
this does generate a leading CREATE DATABASE
statement.
That is, because the --databases
option accepts a space separated list of databases to dump.
Here, using --add-drop-database
will do what is expected from it.
To dump a single table, use
$ mysqldump ... --single-transaction --source-data=2 \
> --routines --triggers --events \
> --databases databasename --tables tablename > mydump.sql
Alternately mixing --databases
and --tables
will not work.