Linux command: mysqldump

Every website that stores anything stores it in a database, and a database is the one part of a server you cannot copy by dragging a folder. The files sit in a format only the database engine understands, they change while you are reading them, and half of what matters lives in memory that has not been written to disk yet. So when you need a copy of a MySQL or MariaDB database, you do not copy files. You ask the server to write out a set of instructions that rebuilds the database from scratch. The program that asks is mysqldump.

2. Where the Name Comes From

The name is a plain compound, with no hidden joke in it:

mysqldump  =  MySQL  +  dump

The interesting half is dump. In computing a dump is a complete, unprocessed write-out of some internal state: a core dump, a memory dump, a hex dump. The word carries the promise that nothing was summarised and nothing was left out. That is exactly the promise this program makes about your tables.

It also sets the right expectation about the format. A dump is not a clever archive. It is the contents, written out in the most obvious way possible, which is why the output is readable SQL that you can open in a text editor and fix by hand at three in the morning.

MariaDB has since renamed the program to mariadb-dump, keeping the same structure with a different vendor prefix. See section 7.2.

4. Simple Use Cases

4.1 The Simplest Possible Dump

Name a database. The SQL goes to your terminal:

$ mysqldump -u root -p sitedb

The -u flag (short for user) names the account, and -p (short for password) makes the program prompt you for the password. Do this once, watch the SQL scroll past, and the tool stops being mysterious. Then redirect it into a file, which is how you will always use it:

$ mysqldump -u root -p sitedb > sitedb.sql

Note where the redirect sits. mysqldump writes to standard output, so the shell creates the file, not the program. That single fact explains most of the pipelines later in this article, and most of the traps in section 7.3.

4.2 Reading What Comes Out

Here is a real dump of a small table, with the header comments removed for space:

$ mysqldump -u root -p --skip-dump-date sitedb j6_content

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

DROP TABLE IF EXISTS `j6_content`;
CREATE TABLE `j6_content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `alias` varchar(255) NOT NULL,
  `state` tinyint(4) NOT NULL DEFAULT 1,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

LOCK TABLES `j6_content` WRITE;
/*!40000 ALTER TABLE `j6_content` DISABLE KEYS */;
INSERT INTO `j6_content` VALUES
(1,'Welcome','welcome',1,'2026-08-01 10:00:00'),
(2,'About us','about-us',1,'2026-08-02 11:30:00'),
(3,'Draft post','draft-post',0,'2026-08-03 09:15:00');
/*!40000 ALTER TABLE `j6_content` ENABLE KEYS */;
UNLOCK TABLES;

Five things are worth pointing out, because they answer questions people ask about dumps constantly.

• The /*!40101 ... */ comments are executable. This is MySQL's version comment syntax. A normal SQL parser sees a comment and skips it; a MySQL server at version 4.01.01 or newer strips the marker and runs the statement inside. It is how one dump file stays compatible with servers old and new.
• DROP TABLE IF EXISTS comes first. A restore is destructive by design: it replaces the table rather than merging into it. See section 9.
• The rows are batched. One INSERT carries many VALUES lists. That is --extended-insert, on by default, and it makes restores far faster than one statement per row.
• TIME_ZONE='+00:00' is set at the top. Your TIMESTAMP columns are dumped in UTC so they survive a move to a server in another time zone. This is --tz-utc, also on by default.
• FOREIGN_KEY_CHECKS=0 is set at the top too. Tables are written out in the order the server lists them, which is not dependency order, so a child row can easily be inserted before the parent it points at exists. Switching the checks off for the length of the restore is what makes that work. They are restored to their old value at the end, and the constraints themselves are still there in the CREATE TABLE statements - only the checking was paused. Section 6.3 shows the related trick that mysqldump uses for views.

4.3 Restoring: There Is No mysqlrestore

People look for the matching restore command and do not find one. There is none, and there does not need to be one. The dump is a script of SQL statements, so you restore it by feeding it to the ordinary client:

$ mysql -u root -p sitedb < sitedb.sql

The database itself must already exist, because a plain dump of one database contains no CREATE DATABASE. Create it first:

$ mysql -u root -p -e "CREATE DATABASE sitedb CHARACTER SET utf8mb4"
$ mysql -u root -p sitedb < sitedb.sql

Notice that the target name is on the restore command line. That is what makes it trivial to restore a dump into a differently named database, which is exactly how you clone a live site into a staging copy.

4.4 One Table, Several Tables, Several Databases

The arguments after the database name are table names:

$ mysqldump -u root -p sitedb j6_content              # one table
$ mysqldump -u root -p sitedb j6_content j6_users     # two tables
$ mysqldump -u root -p --databases sitedb shopdb      # two whole databases
$ mysqldump -u root -p --all-databases                # everything on the server

The --databases flag (-B) changes the meaning of every argument: they all become database names, and the output gains a CREATE DATABASE and a USE statement for each one. That is a real difference in behaviour, not just in syntax:

So use the plain form when you might want to restore elsewhere, and --databases when you want a faithful, self-contained rebuild.

4.5 Connecting to the Right Server

The connection flags are the same as for the mysql client:

$ mysqldump -h db.example.com -P 3306 -u backup -p sitedb > sitedb.sql

-h is the host, -P (capital P) is the port, and -p (lower case) is the password. Mixing up the two p flags is a rite of passage. One more subtlety: on Linux the host localhost means "use the local Unix socket", while 127.0.0.1 means "use TCP to this machine". They are not the same thing, and when a dump fails with a socket error, switching to 127.0.0.1 is the first thing to try.

6. Advanced Use Cases

6.1 The Defaults You Never Set: --opt

New users often copy a long command full of flags without knowing that most of them are already on. The umbrella option --opt is enabled by default and means eight things at once:

So mysqldump --opt sitedb is identical to mysqldump sitedb. You disable individual pieces with the --skip- prefix, or all of them with --skip-opt.

One pair is easy to confuse, and the names really are that close:

• --lock-tables locks tables on the server, during the dump. This is the one you turn off with --single-transaction.
• --add-locks writes LOCK TABLES statements into the output file, to speed up the eventual restore. It has no effect on the live server at all, and --single-transaction leaves it switched on.

6.2 Making a Dump Readable: --compact

When you want to read or diff a dump rather than restore it, the boilerplate gets in the way. --compact strips it:

$ mysqldump -u root -p --compact --no-data sitedb j6_content

CREATE TABLE `j6_content` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `alias` varchar(255) NOT NULL,
  `state` tinyint(4) NOT NULL DEFAULT 1,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

It switches off comments, the drop statements, the locks, the key handling, and the charset lines. That makes it excellent for reading and unsuitable as a backup. Do not use it for the dump you actually rely on.

6.3 The Parts That Are Not Tables

A database is more than tables, and mysqldump does not treat all of the extras alike. This catches people out badly, because the dump succeeds and looks complete:

The asymmetry is not obvious and there is no warning. A database with stored procedures, dumped without --routines, produces a file that restores cleanly and is missing them:

$ mysqldump -u root -p --no-data sitedb | grep -c PROCEDURE
0
$ mysqldump -u root -p --no-data --routines sitedb | grep -c PROCEDURE
1

If there is any chance the database has routines or events, dump with both flags. They cost nothing when there is nothing to dump.

Views are included without asking, but they are written in a way that looks like a bug the first time you read a dump. Every view appears twice. Once near its alphabetical place among the tables, as a placeholder made of dummy columns:

--
-- Temporary table structure for view `v_published`
--
DROP TABLE IF EXISTS `v_published`;
/*!50001 DROP VIEW IF EXISTS `v_published`*/;
/*!50001 CREATE VIEW `v_published` AS SELECT
 1 AS `id`,
  1 AS `title` */;

And then again at the very end of the file, as the real thing:

--
-- Final view structure for view `v_published`
--
/*!50001 DROP VIEW IF EXISTS `v_published`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_published` AS select `j6_content`.`id` AS `id`,
    `j6_content`.`title` AS `title` from `j6_content` */;

This is restore order again, the same problem the foreign-key switch solves. A view selects from tables that may not exist yet at that point in the file, and another view may select from this view. The placeholder gives anything that refers to the view something valid to bind to while the tables are still being created, and the real definition replaces it once they all exist. Both halves are needed, so do not tidy those SELECT 1 AS ... blocks out of a dump you intend to restore.

6.4 Binary Data: --hex-blob

By default, BLOB and BINARY columns are written as escaped string literals. Most of the time this survives the round trip, but binary bytes inside a quoted string are fragile: an unlucky byte sequence, a charset conversion during the restore, or an editor that "fixes" line endings can corrupt them silently. --hex-blob writes them as 0x hexadecimal literals instead, which cannot be misinterpreted:

$ mysqldump --single-transaction --hex-blob -u root -p sitedb > sitedb.sql

The file gets bigger. If the database stores images, PDFs, or serialised binary data, add the flag anyway.

6.5 Large Values and max_allowed_packet

A row has to cross between server and client in a single packet, and both ends cap how big that packet may be. When one value is larger than the cap, the dump stops - with an error message that sends people hunting in entirely the wrong place:

$ mysqldump --max-allowed-packet=1M -u root -p sitedb big > big.sql
mysqldump: Error 2013: Lost connection to server during query
           when dumping table `big` at row: 0
$ echo $?
3

Nothing was lost and the network is fine. The row simply did not fit. The client reports a lost connection because that is genuinely what it sees when the server refuses an oversized packet, and administrators lose afternoons to it every year. The clue is at row: 0 on a table you know holds large values.

The client default is generous rather than unlimited: 16 MB on MySQL 8.0, 24 MB on MariaDB. Raise it when a table stores images, PDFs, or long TEXT:

$ mysqldump --single-transaction --hex-blob --max-allowed-packet=512M \
    -u root -p sitedb > sitedb.sql

Then remember the other end. A dump that needed a raised limit to be created needs the same room to be restored, so the target server's own max_allowed_packet has to be large enough as well. Otherwise the backup writes perfectly and refuses to load, which you discover on the day you need it.

6.6 Tab-Separated Output: --tab

The -T / --tab option writes two files per table into a directory: a .sql file with the definition and a .txt file with the raw rows.

$ mysqldump -u root -p --tab=/var/lib/mysql-files/out sitedb j6_content
$ ls /var/lib/mysql-files/out/
j6_content.sql  j6_content.txt

$ cat /var/lib/mysql-files/out/j6_content.txt
1	Welcome	welcome	1	2026-08-01 10:00:00
2	About us	about-us	1	2026-08-02 11:30:00
3	Draft post	draft-post	0	2026-08-03 09:15:00

Loading that back with LOAD DATA INFILE is much faster than replaying INSERT statements, and the .txt files feed straight into other tools. But there are two hard restrictions, and they are why most people never use it: mysqldump must run on the same machine as the server, because the server writes the data files itself, and the server needs write permission on the target directory (usually the one named by secure_file_priv). It is a local bulk-transfer tool, not a backup method.

6.7 Replication Coordinates

To build a replica from a dump, the dump has to record the exact binary log position it was taken at. Modern MySQL calls this --source-data:

$ mysqldump --single-transaction --source-data=2 \
    -u root -p --all-databases > full.sql

A value of 1 writes an executable CHANGE MASTER statement; 2 writes it as a comment, which is what you want when you are only taking a backup and do not want a restore to reconfigure replication. Combined with --single-transaction the global read lock is held only briefly at the start rather than for the whole dump.

The older names still work but now print a deprecation notice. The mapping is:

On a server with GTIDs enabled, also think about --set-gtid-purged. Its default is AUTO, which adds a SET @@GLOBAL.GTID_PURGED statement to the dump. That is right for building a replica and wrong for restoring a copy of the site into a staging server, where it will refuse to load or corrupt the target's GTID state. For a plain backup, use --set-gtid-purged=OFF.

6.8 Keeping the Password Off the Command Line

Writing -pSecret123 puts the password in your shell history and in the process list, where any user on the machine can read it with ps. MySQL's client warns you about it every single time:

$ mysqldump -u root -pSecret123 sitedb > sitedb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

MariaDB's client does not print that warning, which makes the habit easier to keep and no less dangerous. For scripts, put the credentials in a file that only the owner can read:

$ cat ~/.my.cnf
[client]
user = backup
password = Secret123

$ chmod 600 ~/.my.cnf
$ mysqldump --single-transaction sitedb > sitedb.sql   # no credentials needed

Better still for a cron job, keep it in its own file and point at it explicitly, so you are not depending on which user the job happens to run as:

$ mysqldump --defaults-extra-file=/etc/backup/db.cnf \
    --single-transaction sitedb > sitedb.sql

That flag must come first on the command line. MySQL also offers mysql_config_editor, which stores credentials obfuscated in ~/.mylogin.cnf for use with --login-path. Obfuscated is not encrypted - it stops a shoulder-surfer, not an attacker with the file.

6.9 A Backup User With Only the Rights It Needs

A backup job does not need root. Give it exactly the privileges a dump uses:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS
  ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

Each grant maps to something the tool does: SELECT to read rows, SHOW VIEW to reproduce views, TRIGGER for triggers, LOCK TABLES for the default locking, EVENT for --events, and PROCESS because MySQL 8.0 needs it to read tablespace information. If you never dump events, drop EVENT. If PROCESS is refused by your host, add --no-tablespaces to the command and you no longer need it.

6.10 Running It Against a Docker Container

Most local development now happens in containers, and that adds one wrinkle worth understanding properly. The database server runs inside the container, while your shell, your redirect, and the dump file all live outside it. Every problem in this subsection comes from that one boundary.

Dumping is the easy direction. Name the service exactly as it appears in your docker-compose.yml and redirect as usual:

$ docker compose exec -T db mysqldump --single-transaction \
    -u root -p sitedb > sitedb.sql

Restoring is where people lose an evening:

$ docker compose exec db mysql -u root -p sitedb < sitedb.sql
the input device is not a TTY

Nothing is restored. The fix is the -T flag, and the reason is worth understanding rather than memorising. By default docker compose exec allocates a pseudo-terminal, as though you had typed the command interactively. A terminal is not a pipe: it does not carry a file on standard input. -T means "no terminal", which lets the redirect through.

Plain docker exec has the same problem spelled the opposite way. It allocates no terminal, but it also does not attach standard input unless you ask for it with -i:

$ docker exec CONTAINER mysql -u root -p sitedb < dump.sql     # does nothing
$ docker exec -i CONTAINER mysql -u root -p sitedb < dump.sql  # correct

The first form is by far the more dangerous of the two, because it prints no error at all. The command returns, the database is untouched, and nothing anywhere tells you the restore did not happen.

You will find plenty of advice saying you must add -T to the dump as well, or the file comes back with Windows line endings. On Compose v2 that is no longer true: it allocates a terminal only when the output is really going to a terminal, so a redirect or a pipe is already safe. For the interactive client I keep a small wrapper that tests [ -t 0 ] and adds -T only when standard input is not a terminal. The same command then gives me a real SQL prompt when I want one, and swallows a dump file when I feed it one.

There is one more container-specific trap, and it is the naming problem from section 7.2 with considerably sharper teeth. The client inside the container is whichever one the image ships, not the one on your laptop. Point a compose file at mariadb:11.4 and the familiar name has gone:

$ docker compose exec -T db mysqldump -u root -p sitedb > sitedb.sql
$ echo $?
127

Now look at the backup that command just produced:

$ ls -l sitedb.sql
-rw-r--r-- 1 peter peter 137 Aug 23 11:04 sitedb.sql

$ cat sitedb.sql
OCI runtime exec failed: exec failed: unable to start container process:
exec: "mysqldump": executable file not found in $PATH: unknown

Docker wrote its own error message into your dump file. The file exists, it is not empty, and its timestamp is today, so a backup check that only tests for a non-zero size accepts it without complaint. Use mariadb-dump for MariaDB 11 images, and read section 7.3 again: this is the empty-backup problem wearing a different hat.

Finally, credentials. A compose stack normally keeps them in its .env file already, so there is no reason to type a password on the command line and hand it to your shell history:

$ docker compose exec -T db mysqldump --single-transaction \
    -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" > sitedb.sql

Be honest about what that buys you. It keeps the password out of your history and out of the script, but the password is still visible in the process list inside the container. On a local development stack that is a fair trade. On a server, use an option file as in section 6.8.

8. Best Practices

• Always use --single-transaction on a live InnoDB site. Without it you are locking the database against your own visitors for the length of the backup.
• Add --routines --events unless you have checked the database has neither. They are not included by default and their absence is silent.
• Add --hex-blob if any column stores binary data, and raise --max-allowed-packet on both the dump and the restore when rows are large.
• List the definers before you delete a database account. A view or routine left pointing at a missing account breaks every dump of that database from then on.
• Start every backup script with set -euo pipefail, then verify both the exit code and the file size. A backup script that cannot fail loudly is worse than no script.
• Write to a temporary name and rename on success, so a broken run never replaces a good backup.
• Keep credentials out of the command line - use --defaults-extra-file with a chmod 600 file, and pass it as the first argument.
• Use a dedicated backup account with SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, not root.
• Compress in the pipe with gzip or zstd. SQL text compresses several times over and you avoid writing the large file at all.
• Detect the program name in scripts, so a MariaDB upgrade that removes the mysqldump symlink does not silently break the job.
• Move the dump off the server. A backup stored next to the database protects you against nothing except your own mistakes, and it is a file an attacker would love to find.
• Restore it somewhere, on a schedule. An untested dump is a guess. Restoring into a scratch database takes ten minutes and is the only thing that proves the backup works.
• Remember what is not in it: users and grants live in the mysql system database, not in your site's database, and they are not in a single-database dump.
• Read the documentation with man mysqldump, or mysqldump --help for the flags plus a full list of every default value.

10. Summary

mysqldump looks like a one-line utility and behaves like one, but almost everything that goes wrong with database backups goes wrong in the space around it: the shell, the pipe, the schedule, and the restore nobody tried.

• It makes a logical backup: plain SQL text that rebuilds your database anywhere, on any version, readable and editable. That portability is why it is still the default tool after thirty years.
• It writes to standard output. The shell makes the file, which is why redirection, pipes, and compression work so naturally - and why a failed dump still leaves a file behind.
• There is no restore command. You feed the dump back with mysql < file.sql, into a database that must already exist.
• --single-transaction is the flag that matters on a live site: an InnoDB snapshot instead of a table lock, so the backup does not take the site down with it.
• Defaults do most of the work through --opt, but --routines and --events are not among them, and their absence is silent.
• Cut the dump down with --where, --ignore-table, --no-data, and the two-pass pattern that keeps a session table's structure without its rows.
• Objects remember who made them. A DEFINER pointing at an account that no longer exists lets the restore finish silently and fails when the object is used - and stops the source database dumping at all.
• The exit code is honest but the pipeline is not. Without set -o pipefail, a completely failed backup reports success. Check the status, check the size, and rename into place only on success.
• On MariaDB the program is mariadb-dump, the mysqldump symlink disappeared in 11.4, and dumps start with a sandbox line that protects you from a hostile dump file.
• Know where it stops: xtrabackup or mariabackup for large databases, MySQL Shell for parallel logical dumps, mysqlbinlog for point-in-time recovery, snapshots for whole-server rollback.
• When in doubt, type man mysqldump.

This is the quick reference worth keeping:

mysqldump -u U -p DB > db.sql            dump one database to a file
mysql -u U -p DB < db.sql                restore it (database must exist)
mysqldump --single-transaction ...       consistent dump without locking (InnoDB)
mysqldump ... | gzip > db.sql.gz         compress in the pipe
gunzip < db.sql.gz | mysql -u U -p DB    restore a compressed dump
mysqldump -u U -p DB tbl1 tbl2           only these tables
mysqldump -u U -p --databases DB1 DB2    several databases, with CREATE DATABASE
mysqldump -u U -p --all-databases        the whole server
mysqldump --no-data DB > schema.sql      structure only (-d)
mysqldump --no-create-info DB > data.sql rows only (-t)
mysqldump --where="state=1" DB tbl       only matching rows (-w)
mysqldump --ignore-table=DB.tbl DB       skip a table (needs DB.tbl)
mysqldump --routines --events DB         include procedures and events
mysqldump --hex-blob DB                  safe binary columns
mysqldump --compact --no-data DB         readable schema, for diffing only
mysqldump --skip-column-statistics ...   8.0 client against a 5.7 server
mysqldump --set-gtid-purged=OFF ...      backup on a GTID server
mysqldump --defaults-extra-file=f.cnf    credentials from a file (must be first)
mysqldump --max-allowed-packet=512M ...  tables holding large TEXT or BLOB values
docker compose exec -T db mysqldump ...  dump from a container (-T for restores)
docker exec -i CONTAINER mysql db < f    restore into a plain docker container

set -o pipefail                          or a failed dump reports success
exit 0 ok | 2 connect or definer | 3 truncated | 6 no such table

A database backup is the cheapest insurance a website has, and it is almost always set up once and never looked at again. The uncomfortable part is that a backup routine and a broken backup routine look exactly the same from the outside, right up to the day you need one. If you want the database behind your site backed up, tested, and restorable by someone who has done the restore before, that is exactly the kind of quiet work I enjoy helping with.