---
title: "MySQL"
description: "Logical dumps of a MySQL or MariaDB database, local or cloud."
url: "https://saved.sh/docs/backups/sources/mysql"
---

Runs `mysqldump` and stores the SQL it produces. Available as both a local and a cloud
backup.

The dump is taken with `--single-transaction --quick --routines --events --triggers`, which
is the set that makes a nightly backup safe to run against production.

## What is taken [#what-is-taken]

A **logical** dump of one database: `CREATE` statements and `INSERT`s, as plain SQL.

| Included                           | Not included                                           |
| ---------------------------------- | ------------------------------------------------------ |
| Tables, data, indexes, constraints | Other databases on the server                          |
| Views, routines, triggers, events  | Users and grants (they are server-wide)                |
|                                    | Binary logs, so no point-in-time recovery between runs |

Plain SQL restores anywhere with a `mysql` client, which is the point. It also means the
artifact is highly compressible: unlike Postgres custom format, this one is worth
[compressing](/docs/backups/compression).

## Consistency [#consistency]

`--single-transaction` takes a consistent snapshot **without locking the whole database**.
That is the difference between a backup you can run in production and one you cannot.

<Callout type="warn">
  It only works for **transactional tables**, which in practice means InnoDB. MyISAM tables
  are not covered by the snapshot and can be dumped mid-write, producing a file that restores
  without complaint and is subtly inconsistent. If you still have MyISAM tables, back them up
  from a replica or during a quiet window.
</Callout>

DDL running during the dump can also break consistency, because `mysqldump` cannot include
schema changes in its transaction. Avoid migrating while the backup window is open.

## Dump the replica, not the source [#dump-the-replica-not-the-source]

**If you have a replica, point the backup at it.** A dump is a long read holding a snapshot
open, and on the machine serving production that means competing for I/O and keeping InnoDB's
undo log growing for the whole run. Moving it to a replica costs nothing when one already
exists, and it removes the DDL and MyISAM caveats above from your production window.

Two things to set up deliberately when you do.

**Your RPO now includes replication lag.** The dump is as old as the replica was when it
started. `Seconds_Behind_Source` in `SHOW REPLICA STATUS` is that number, and it is highest
exactly when the source is under the load that made you want a backup.

**A stopped replica backs up stale data quietly.** Replication breaks, nothing alerts, and
every dump afterwards is a faithful copy of a database frozen at the moment it broke. The runs
succeed and the sizes look right.

<Callout type="warn" title="Check freshness, not just success">
  A stalled replica does not produce small backups, it produces *identical* ones. Watch for a
  dump whose size stops changing, not only one that shrinks. `SHOW REPLICA STATUS` is the
  authoritative check: `Replica_IO_Running` and `Replica_SQL_Running` should both be `Yes`.
</Callout>

<Callout type="warn">
  **The dump carries GTID state on a GTID-enabled server, and there is no field to turn that
  off.** `mysqldump` writes `SET @@GLOBAL.GTID_PURGED` by default, and a dump carrying it
  refuses to load into a topology that already has GTIDs. Strip that line before restoring, or
  take the dump through a [`script` source](/docs/backups/sources/script) with
  `--set-gtid-purged=OFF` so you control every flag.
</Callout>

## Fields [#fields]

Local, in the worker's `config.yaml` under `source:`:

| Key                    | Required | Notes                                                              |
| ---------------------- | -------- | ------------------------------------------------------------------ |
| `database`             | Yes      |                                                                    |
| `host`, `port`, `user` | No       |                                                                    |
| `password`             | No       | Passed as `MYSQL_PWD`, never on the command line                   |
| `ssl_mode`             | No       | Passed through as `--ssl-mode`, e.g. `REQUIRED`, `VERIFY_IDENTITY` |
| `tables`               | No       | A list. Restrict the dump to these tables                          |
| `exclude_tables`       | No       | A list. Becomes `--ignore-table=<db>.<table>` per entry            |
| `no_data`              | No       | `true` dumps schema only                                           |

Cloud, on the backup definition:

| Half   | Fields                                                                    |
| ------ | ------------------------------------------------------------------------- |
| Public | `host`, `port`, `database`, `user`, `tables`, `exclude_tables`, `no_data` |
| Secret | `password`, `ssl_mode`                                                    |

```yaml title="config.yaml"
backups:
  "<backup-uuid>":
    source_type: mysql
    source:
      database: app
      host: db.internal
      port: 3306
      user: backup
      password: "<password>"
      exclude_tables: [sessions, audit_log]
```

<Callout type="warn">
  **`tables` and `exclude_tables` cannot both be set.** They express opposite intentions, so
  the worker refuses the config at startup rather than picking one. Choose the shorter list.
</Callout>

## A least-privileged user [#a-least-privileged-user]

```sql
CREATE USER 'backup'@'%' IDENTIFIED BY '...';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON app.* TO 'backup'@'%';
```

Each grant maps to a flag the dump uses:

| Grant         | Needed for                                                    |
| ------------- | ------------------------------------------------------------- |
| `SELECT`      | The data                                                      |
| `SHOW VIEW`   | View definitions                                              |
| `TRIGGER`     | `--triggers`                                                  |
| `EVENT`       | `--events`                                                    |
| `LOCK TABLES` | Non-transactional tables, and some server versions regardless |

`--routines` needs read access to stored programs, which on some versions means
`SELECT` on `mysql.proc` or the `PROCESS` privilege. If the dump fails naming routines, that
grant is the cause.

## Passwords [#passwords]

The password is passed through the environment, never on the command line. Anything in argv
is readable by every other process on the host, which is exactly the leak a backup user
should not introduce.

## MariaDB [#mariadb]

MariaDB works and is dumped by the same tool. On recent MariaDB the binary is `mariadb-dump`
with `mysqldump` as a symlink; if your host has dropped the symlink, point the worker at it
explicitly:

```yaml title="config.yaml"
tools:
  mysqldump: /usr/bin/mariadb-dump
```

## Sizing [#sizing]

Plain SQL is verbose: an artifact can approach or exceed the database's on-disk size before
compression, and shrink dramatically after it. Turn compression on for this source.

## Restoring [#restoring]

```bash
mysql -u root -e 'CREATE DATABASE scratch'
sctl restore mysql <artifact-id> --database scratch --user root
```

Create the target database first. The dump contains table definitions, not a `CREATE
DATABASE`.

<Callout type="warn">
  `--password` on the command line is visible in your shell history and in the process list of
  the machine you run it on. For a restore into a local scratch database prefer socket auth or
  a `~/.my.cnf`, and keep the flag for throwaway hosts.
</Callout>