The `mysqldump --databases` Footgun: How to Not Silently Overwrite Your Live Database

Every experienced database operator has a story about a mysqldump restore going sideways. The version of that story we live-tested last month is worth writing down, because the failure mode is silent, the fix is one flag, and the same footgun sits waiting in every MariaDB and MySQL installation on the planet. This post covers exactly what mysqldump --databases does to a dump file, how it can silently overwrite a live production database when you think you’re restoring into a scratch database, and the flags and workflows that make dumps safe to inspect and restore under any circumstance. If you run backups on a schedule (you should) and occasionally poke at those backups (you will), this is worth the eight minutes.

What Happened, In One Sentence

We ran zcat old_backup.sql.gz | mariadb -uroot mautic_oldinspect intending to load a nightly backup into a scratch database for inspection, and it silently overwrote the live mautic database instead.

The rest of this post explains why.

What mysqldump --databases Adds to a Dump

If you generate a MariaDB or MySQL dump with the plain form:

mysqldump -uroot -p mautic > mautic.sql

You get a dump file that contains only the DDL for the tables and the INSERT statements for the rows. When you restore it, you pass the target database on the client command line and MariaDB routes every insert to that target:

mariadb -uroot -p mautic_oldinspect < mautic.sql

Now try the same thing with the --databases flag:

mysqldump -uroot -p --databases mautic > mautic.sql

The dump file now has three additional statements silently added at the top:

DROP DATABASE IF EXISTS `mautic`;
CREATE DATABASE `mautic` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `mautic`;

This is documented behaviour, and for the intended use case (portable full-database backups where the target database is created fresh at restore time) it makes sense. What it does silently is more consequential than what it does openly.

How the USE Statement Redirects Every Insert

MariaDB and MySQL clients don’t have a persistent “target database” concept in the way that mongoimport --db or psql -d do. When you run mariadb -uroot mautic_oldinspect, you’re setting the default database for the session — a fallback for statements that don’t specify one. Any statement in the input stream that explicitly names or switches the database overrides that default.

USE mautic; is exactly such a statement. The moment the client parses it, the session’s active database changes to mautic, regardless of what you passed on the command line. Every INSERT statement that follows lands in mautic. Every CREATE TABLE lands in mautic. Every DROP TABLE IF EXISTS at the top of a table’s section lands in mautic.

Combined with the DROP DATABASE IF EXISTS mautic; two lines earlier, the sequence looks like this from the live database’s point of view:

1. Your production mautic database is dropped (all tables, all rows, all indexes — gone).
2. A new empty mautic database is created.
3. Every table from the dump is recreated inside the new mautic database.
4. Every row from the dump is inserted.

The mautic_oldinspect database you named on the command line? Never touched. It sits there empty while everything you thought you were loading into it flows into mautic.

This is what makes the failure mode silent. The restore command returns success. The mautic_oldinspect database exists (you may have CREATE DATABASEd it before the restore). No error message fires. Only if you check the live mautic database do you notice that its state now matches the backup you thought you were inspecting.

The Real-World Consequences

In our case the outcome was survivable — the “live” database was itself a fresh installation from a migration project, and the “backup” being loaded actually contained the older schema and data we still wanted. We were lucky. The class of consequences this footgun enables is anything but:

• Silent data loss: You inspect a nightly backup, don’t realise it overwrote production, and the next day’s automated backup captures the older-state data. Depending on how many backup generations you retain, the true production state may already be irrecoverable.
• Silent schema regression: The backup is from before a schema migration, and the restore takes production back to the older schema. Application code stops matching the schema, endpoints start returning 500s, and it takes hours to work out that the schema change was undone (not that new code was misdeployed).
• Cross-tenant contamination: In a multi-tenant deployment where staging and production share a MariaDB instance under different database names, a staging restore can obliterate production if the dump was made with --databases naming the production database.
• Compliance and audit failure: If your database is subject to GDPR, PCI-DSS, or Australian Privacy Act obligations, an accidental restore of an old backup effectively resurrects rows that had been legitimately deleted for compliance reasons. That’s a notifiable breach in some jurisdictions.

None of these fire an error. All of them look like a successful restore.

How to Safely Inspect Any Dump

Assume any dump handed to you was made with --databases until proven otherwise. Here are three ways to inspect one without risk.

Approach 1: Read the Top of the File

The single fastest check is to look at what the dump does before it does anything else:

zcat mautic_backup.sql.gz | head -30

If you see DROP DATABASE, CREATE DATABASE, or USE <name>; in that output, the dump was made with --databases. Do not pipe it into a client on the same MariaDB instance that holds the live database with that name, ever, under any circumstances.

Approach 2: Strip the Embedded Statements

If you have to load the dump on the same MariaDB instance, strip the three embedded statements first:

zcat mautic_backup.sql.gz \
  | sed -e '/^DROP DATABASE/d' \
        -e '/^CREATE DATABASE/d' \
        -e '/^USE /d' \
  | mariadb -uroot mautic_oldinspect

The sed filter removes only the three statement types that redirect targets. Every actual DDL and DML statement passes through untouched. Test this on a small dump first to confirm the sed patterns match your dump’s exact formatting — some mysqldump versions wrap them in /*!40000 ... */ version-guarded comments that make the pattern subtly different.

Approach 3: Restore to a Separate MariaDB Instance

The safest option is to not share an instance with the live database at all. Spin up a throwaway MariaDB container on a non-standard port:

docker run --rm -d \
  --name inspect \
  -e MARIADB_ROOT_PASSWORD=inspect \
  -p 3399:3306 \
  mariadb:10.11

zcat mautic_backup.sql.gz | mariadb -h127.0.0.1 -P3399 -uroot -pinspect

docker exec -it inspect mariadb -uroot -pinspect mautic

Now the USE mautic; in the dump lands in a completely isolated MariaDB server that has no relationship to production. When you’re done, docker rm -f inspect erases the entire inspection environment. This is the workflow we now use for every dump inspection, without exception.

The Flags That Make Dumps Safe by Default

Fixing the input side of the pipeline is a permanent solution. Two flags remove the embedded statements from dumps you produce yourself:

mysqldump -uroot -p \
  --single-transaction \
  --no-create-db \
  mautic > mautic.sql

--no-create-db suppresses the CREATE DATABASE and USE statements. --single-transaction gives you a consistent snapshot on InnoDB tables without table-level locks — worth having on every dump regardless of the topic of this post.

The dump you get is still a full backup — all tables, all rows, all indexes. It just doesn’t decide for the restore client where the data lands. When you restore it, the target database is whatever you pass on the mariadb command line, and nothing in the dump file overrides that. This is how nearly every safe backup pipeline is configured; if your team’s dump script uses --databases, change it.

What This Means for Your Backup Strategy

Three concrete recommendations for anyone running self-hosted MariaDB or MySQL, especially in a small-team environment where the person restoring the backup is often the same person who created it and shortcuts creep in:

1. Standardise your dump command. Pick one form of mysqldump invocation and use it in every automated backup script. If that form is --databases-based (there are legitimate reasons — portable full-database backups where you want the target to be created fresh), document it explicitly and add a “never restore this on the source instance” comment in the script itself.
2. Never restore on the source instance. Even if your dumps are safe, force the habit of restoring backups on isolated instances only. The muscle memory of “backups restore on scratch containers, not the production host” catches many failure modes beyond this one — corrupted dumps, wrong-database-name typos, and interrupted restores.
3. Test-restore your backups regularly. A backup you’ve never restored is untested code in a critical path. Once a quarter, restore your production backup on a scratch instance and diff a sample of tables against the source. You’ll catch missing tables, permission drift, and dump-flag drift while it’s still cheap to fix.

For anyone who hits this footgun the way we did, the fix is one UPDATE-style rollback plan if you’re lucky and a full restore from a different backup generation if you’re not. It’s cheaper to make sure the situation never occurs in the first place. That’s what the --no-create-db and --single-transaction flags exist for.

Related Reading

• Mautic 5.2.9 to 7.1.2 on Docker: The Migration Path That Actually Works — the war story that surfaced this footgun.
• Hacked WordPress Site Cleanup with Sucuri, Wordfence, and MalCare — the operational discipline of never restoring straight to production applies just as strongly to incident recovery.
• Security Alert: Mautic CSV Injection (CVE-2018-8092) — another silent-failure story where the surface looked clean and the underlying issue was two layers deep.