mysqldump: Logical Backups

mysqldump is the standard tool for logical backups. It connects to the server and writes a text file of SQL statements (CREATE TABLE, INSERT…) that rebuilds your data. It ships with MySQL, works on every platform, and its output is readable and portable.

The examples below were run for real on a MySQL 8.4.11 server, with a small shop database that has two tables (customers, orders), a view and a stored function.

The basic command

1

mysqldump -u backup -p --single-transaction --routines --triggers --events --set-gtid-purged=OFF --databases shop > shop_2026-10-05.sql

-p asks for the password (do not type it in the command). The file is written to shop_2026-10-05.sql. There is no output on success. Put the date in the file name.

The options that matter

What the file contains

The start of the file lists the versions and sets safe session settings:

1
2
3
4
5
6
7
8
9
10
11
12

-- MySQL dump 10.13  Distrib 8.4.11, for Win64 (x86_64)
--
-- Host: 127.0.0.1    Database: shop
-- ------------------------------------------------------
-- Server version	8.4.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

The /!40101 ... / comments are version-conditional: servers at version 4.01.01 or higher run them, others skip them. The settings switch off unique and foreign key checks while loading, which makes the restore faster and lets tables load in any order. Then, for each table, the structure and the data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `shop` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `shop`;

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `email` varchar(80) NOT NULL,
  `city` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

LOCK TABLES `customers` WRITE;
/*!40000 ALTER TABLE `customers` DISABLE KEYS */;
INSERT INTO `customers` VALUES (1,'Maya','maya@example.com','Pune'),(2,'Rohan','rohan@example.com','Delhi'),(3,'Isha','isha@example.com','Goa'),(4,'Kabir','kabir@example.com','Pune'),(5,'Zoya','zoya@example.com',NULL);
/*!40000 ALTER TABLE `customers` ENABLE KEYS */;
UNLOCK TABLES;

Notice that:

• DROP TABLE IF EXISTS comes first, so restoring over an existing database replaces its tables. Be careful where you restore.
• The rows are in one multi-row INSERT per table (grouped to a maximum packet size), which loads much faster than one statement per row.
• Views are written first as a temporary stand-in, and the real definition goes at the end of the file, so that the view can refer to tables created later.
• Routines are dumped with DELIMITER ;;. That is a client command, so the file must be read with the mysql client (Restoring).

1
2
3
4
5
6
7
8
9

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `order_count`(cid INT) RETURNS int
    READS SQL DATA
RETURN (SELECT COUNT(*) FROM orders WHERE customer_id = cid) ;;
DELIMITER ;
...
/*!50001 VIEW `paid_orders` AS select `orders`.`id` AS `id`,`orders`.`customer_id` AS `customer_id`,`orders`.`total` AS `total` from `orders` where (`orders`.`status` = 'paid') */;
...
-- Dump completed on 2026-10-05 20:47:57

The last line, -- Dump completed, matters. If it is missing, the dump stopped half way. Check for it in your backup script.

The DEFINER=root@localhost is kept. If you restore on a server where that account does not exist, the view or function may fail when used (Views).

Dumping parts of a database

1
2
3
4
5
6
7
8
9
10
11

# one table (or several)
mysqldump -u backup -p --single-transaction shop customers orders > shop_tables.sql

# structure only, no rows
mysqldump -u backup -p --no-data --databases shop > shop_schema.sql

# rows only, no CREATE TABLE
mysqldump -u backup -p --no-create-info --skip-triggers shop customers > customers_data.sql

# only some rows
mysqldump -u backup -p --no-create-info --skip-triggers --where="city='Pune'" shop customers > customers_pune.sql

The last command, run for real, produced the matching rows only:

1

INSERT INTO `customers` VALUES (1,'Maya','maya@example.com','Pune'),(4,'Kabir','kabir@example.com','Pune');

Compression and speed

Dump files are text and compress very well. Compress while writing, so that the uncompressed file never lands on disk:

1

mysqldump -u backup -p --single-transaction --routines --triggers --events --databases shop | gzip > shop_2026-10-05.sql.gz

For many large databases, dump each database in its own process, in parallel. For a very large single database (hundreds of GB), a physical backup is faster (Physical Backups).

–single-transaction and MyISAM

--single-transaction gives a consistent snapshot only for InnoDB. A MyISAM table is not transactional, so a dump that includes one can be inconsistent while the application writes. Convert MyISAM tables to InnoDB (Converting Engines), or use --lock-all-tables (which blocks writes during the dump) for those.

Another caveat: DDL during the dump. A --single-transaction dump does not hold a metadata lock the whole time. If someone runs ALTER TABLE or DROP TABLE while it runs, the dump can fail or be wrong. Do not deploy schema changes during the backup window.

Common errors

Each of these messages is the real output of a MySQL 8.4 server.

mysqlpump and other tools

mysqlpump (a parallel dump tool) is deprecated and is not shipped in the MySQL 8.4 and 9.7 packages used for this course, so do not build new scripts on it. Alternatives for faster logical dumps are MySQL Shell (util.dumpInstance(), util.loadDump()) and mydumper.

A minimal backup script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

#!/bin/sh
set -eu
DEST=/backups/mysql
STAMP=$(date +%F_%H%M)
mkdir -p "$DEST"

mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
  --single-transaction --routines --triggers --events --set-gtid-purged=OFF \
  --source-data=2 --flush-logs --databases shop | gzip > "$DEST/shop_$STAMP.sql.gz"

# the dump must end with this marker, or it was cut short
gunzip -c "$DEST/shop_$STAMP.sql.gz" | tail -n 1 | grep -q '^-- Dump completed' || { echo "BACKUP INCOMPLETE" >&2; exit 1; }

# keep 14 days
find "$DEST" -name 'shop_*.sql.gz' -mtime +14 -delete

/etc/mysql/backup.cnf holds [client], user=backup and password=... with permissions 600. set -eu stops the script on an error, and the tail check catches a truncated dump. Add an upload to off-site storage and a message to your monitoring system (Backup Strategy).