MySQL
Logical dumps of a MySQL or MariaDB database, local or cloud.
View as MarkdownRuns 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
A logical dump of one database: CREATE statements and INSERTs, 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.
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.
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.
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
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.
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.
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 with
--set-gtid-purged=OFF so you control every flag.
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 |
backups:
"<backup-uuid>":
source_type: mysql
source:
database: app
host: db.internal
port: 3306
user: backup
password: "<password>"
exclude_tables: [sessions, audit_log]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.
A least-privileged user
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
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 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:
tools:
mysqldump: /usr/bin/mariadb-dumpSizing
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
mysql -u root -e 'CREATE DATABASE scratch'
sctl restore mysql <artifact-id> --database scratch --user rootCreate the target database first. The dump contains table definitions, not a CREATE DATABASE.
--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.