mysqldump flags for legacy MySQL: the .my.cnf cheatsheet

It's a Friday at 19:40 and we're about to take a 14 GB dump of a Magento 1.9 store before its host force-migrates the PHP version on Monday. The database has BLOBs in core_config_data, utf8mb4 customer notes with the occasional 4-byte character, and binlogs configured for GTID. If any of those three things is true for your dump, the default mysqldump invocation will quietly burn you on restore.

The cheatsheet below is the .my.cnf and the mysqldump command we drop on the box before any legacy MySQL export. Then the three flags we added after restores that failed at 02:00. Copy the file, edit the credentials block, and run.

The .my.cnf we drop in before any dump

We keep this file at ~/.my.cnf and chmod 600 it. It scopes credentials per-tool, so mysql, mysqldump, and mysqladmin all read the right thing without leaking the password into shell history or ps output.

[client]
host                     = 127.0.0.1
port                     = 3306
user                     = backup
password                 = "long-random-string-here"
default-character-set    = utf8mb4

[mysql]
prompt                   = "\\u@\\h [\\d]> "
no-auto-rehash

[mysqldump]
single-transaction
quick
skip-lock-tables
routines
events
triggers
hex-blob
default-character-set    = utf8mb4
max-allowed-packet       = 512M
net-buffer-length        = 16384
column-statistics        = 0
set-gtid-purged          = OFF
order-by-primary

Two things to verify before you run anything. First, chmod 600 ~/.my.cnf so the file isn't world-readable; without it MySQL will warn but still read it, and on shared boxes you've just leaked credentials. Second, that backup user. We give it SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, and PROCESS and nothing else. The official mysqldump reference documents the minimum grants if you want to argue any of them down further.

The mysqldump command that goes with it

With the option file in place, the command shrinks to the bits that actually change per-dump: which database, where the file goes, and whether you want a gzip pipe.

mysqldump \
  --databases magento_prod \
  | gzip --rsyncable \
  > /backups/magento_prod-$(date +%Y%m%d-%H%M).sql.gz

A few notes on the shape of that line:

• --databases (plural) emits a CREATE DATABASE and USE at the top of the dump, which means the restore script does not have to know the target database name. Worth it.
• gzip --rsyncable makes the gzipped file diffable for backup tools like rsnapshot. The compression ratio is fractionally worse and the wall-clock is identical.
• The single-transaction flag from the option file gives InnoDB a consistent snapshot without locking writes. On a busy WooCommerce store that is the difference between a four-second hiccup and a ninety-second outage. The manual page on consistent reads explains the mechanism.

Three flags that have saved a restore

The defaults are fine for a dump you take and restore on the same server, same version, same character set, same day. They are not fine when you are moving a legacy site between hosts, which is most of the work. These three are the ones that have caught us.

hex-blob

Without --hex-blob, BLOBs and BINARY columns are written as escaped strings. The dump opens in your editor, looks plausible, and on restore the bytes have been silently re-encoded through the connection charset. We hit this on a WordPress site where wp_options held a serialized PHP object containing image bytes; the restore read clean but every option that touched binary data unserialized to false. With --hex-blob each binary value is written as 0xDEADBEEF… and survives whichever character set the restoring client decides to use.

set-gtid-purged=OFF

Managed MySQL (RDS, Aurora, Cloud SQL) ships with GTIDs on. By default mysqldump emits a SET @@GLOBAL.GTID_PURGED='...' at the top of the file. Restore that into a fresh dev box and you get:

ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only
be set when @@GLOBAL.GTID_EXECUTED is empty.

--set-gtid-purged=OFF strips the statement. The dump still restores correctly; you just don't get replication coordinates you weren't going to use on the dev box anyway.

column-statistics=0

A MySQL 8 client dumping a 5.7 server tries to read from information_schema.COLUMN_STATISTICS, which doesn't exist on 5.7. You get:

mysqldump: Couldn't execute 'SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ...)
FROM information_schema.COLUMN_STATISTICS ...': Unknown table
'COLUMN_STATISTICS' in information_schema (1109)

Half a terabyte through, after midnight. --column-statistics=0 skips the lookup. We set it on every legacy dump because the cost of having it on when you don't need it is zero, and the cost of not having it when you do is the dump.

What to verify after the dump finishes

Three quick checks. If any of them fails the dump is suspect and you re-run before signing off.

# 1. File ends with the expected sentinel
zcat backup.sql.gz | tail -1
# -- Dump completed on 2026-06-11  ...

# 2. Row count looks sane for a known table
zcat backup.sql.gz | grep -c "^INSERT INTO \`wp_posts\`"

# 3. Character set survived the round-trip
zcat backup.sql.gz | grep -m1 "DEFAULT CHARSET"
# DEFAULT CHARSET=utf8mb4

The "Dump completed" footer is the only built-in integrity check mysqldump provides. It is not a hash, but its absence is a reliable signal that the process died before flushing the tail.

How this fits into a working session

When we built Pier we ran into this exact sequence on every legacy site we touched: drop the .my.cnf, run the dump, eyeball the footer, restore on staging, find the one flag we forgot. The way we handled it inside the MySQL editor was to bake the option file into the docking flow, so the flags above are always set and the resulting .sql.gz lands in version history next to the file changes from the same session.

If you do nothing else today, put the .my.cnf block above into ~/.my.cnf on whichever box you run dumps from, chmod 600 it, and re-run a dump you already trust. You'll see the file shrink in command length and grow in what it survives.