MySQL: Ways to run mysqldump
This text exists mainly so that I paste the URL into the
#mysql channel in Libera IRC.
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 firstname.lastname@example.org: $ ssh email@example.com ... $ mysql --show-warnings --whatever-options < somescript.sql
mysql with an input redirect, you may also use the command line client and the
$ mysql --show-warnings --whatever-options mysql> source somescript.sql
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
$ mysqldump ... --single-transaction --source-data=2 \ > --routines --triggers --events \ > --no-data \ > --databases dbname > mydump.sql
How do we get there?
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.
--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
--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
mysqldump will not dump all non-table objects in the database.
You need to pass
--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
--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
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:
- Generate a
DROP TABLEstatement before each
CREATE TABLEstatement in the dump.
- Enclose the
INSERTstatements loading data into the table with
UNLOCK TABLESstatements. 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.
- 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.
- For each table, enclose the
ENABLE KEYSstatements. For MyISAM tables, this improves data load speed, and index quality. For InnoDB, it does nothing, but is also not harmful.
INSERTstatements generated will use extended insert syntax. This will generate very long lines in the dump (matching whatever is defined as
max-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, large
max-allowed-packet. Depending on what you want to do with the dump, it may be useful to
--skip-extended-insertto turn this off.
- 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-transactionis used. So we are okay with it.
- 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,
mysqldumpwill be using a lot of memory, and will be very unhappy. Do not disable
- Generate a
SET NAMESstatement 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:
You get an SQL file with no
CREATE TABLE and friends.
You would still need
--events to get all the code.
$ mysqldump ... --single-transaction --source-data=2 \ > --trigger --routines --events \ > --no-data
Dumping all databases, or just one database
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.
--all-databases is being used, each database in the dump will be created with a
CREATE DATABASE statement.
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
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.
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
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
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.
--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.
--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
--tables will not work.