# Logical backups with SQL dumps

The Plakar MySQL integration uses `mysqldump` (MySQL) or `mariadb-dump` (MariaDB) to produce logical backups. These are standard SQL files that are portable, human-readable, and restorable without Plakar if needed.

Two URI schemes map to independent sets of binaries:

| Protocol          | Target                     | Dump tool      | Restore tool   |
|-------------------|---------------------------|----------------|----------------|
| `mysql://`        | MySQL 5.7 / 8.x          | `mysqldump`    | `mysql`        |
| `mysql+mariadb://`| MariaDB 10.x / 11.x      | `mariadb-dump` | `mariadb`      |

For a deeper understanding of logical backups and MySQL backup strategies, refer to the [official MySQL documentation on mysqldump](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html).

## Requirements

- A running MySQL or MariaDB server.
- A database user with sufficient privileges (see [Required privileges](/content/docs/main/guides/mysql/sqldump/#required-privileges/index.html)).
- `mysqldump` and `mysql` in `$PATH` for MySQL, or `mariadb-dump` and `mariadb` for MariaDB.

### Install the package

```bash
$ plakar pkg add mysql
```

## What gets stored in a snapshot

| File               | Description                                                   |
|--------------------|---------------------------------------------------------------|
| `/manifest.json`   | Server metadata: version, configuration, databases, tables, routines, triggers, events. |
| `/<database>.sql`  | Single-database dump (when `database` is specified).         |
| `/all.sql`         | Full-server dump (when `database` is omitted).              |

## Back up a single database

```bash
# MySQL
$ plakar source add mydb mysql://dbuser:secret@db.example.com/mydb
$ plakar at /var/backups backup @mydb

# MariaDB
$ plakar source add mydb mysql+mariadb://dbuser:secret@db.example.com/mydb
$ plakar at /var/backups backup @mydb
```

## Back up all databases

Omit the database name from the URI to use `--all-databases`:

```bash
# MySQL
$ plakar source add alldb mysql://root:secret@db.example.com
$ plakar at /var/backups backup @alldb

# MariaDB
$ plakar source add alldb mysql+mariadb://root:secret@db.example.com
$ plakar at /var/backups backup @alldb
```

## Restore a single database

The target database must already exist:

```bash
$ plakar destination add mydbdst mysql://dbuser:secret@target.example.com/mydb
$ plakar at /var/backups restore -to @mydbdst <snapshot_id>
```

To have Plakar create the database automatically, set `create_db=true`:

```bash
$ plakar destination add mydbdst mysql://dbuser:secret@target.example.com/mydb \
  create_db=true
$ plakar at /var/backups restore -to @mydbdst <snapshot_id>
```

## Restore all databases

```bash
$ plakar destination add mydbdst mysql://root:secret@target.example.com
$ plakar at /var/backups restore -to @mydbdst <snapshot_id>
```

## List snapshots

```bash
$ plakar at /var/backups ls
```

## Source options

| Option            | Default                | Description                                        |
|-------------------|-----------------------|----------------------------------------------------|
| `location`        | —                     | Connection URI: `mysql://[user[:password]@]host[:port][/database]` |
| `host`            | `127.0.0.1`          | Server hostname. Overrides the URI host.           |
| `port`            | `3306`                | Server port. Overrides the URI port.                |
| `username`        | —                     | Username. Overrides the URI user.                   |
| `password`        | —                     | Password. Overrides the URI password. Passed via `MYSQL_PWD`, never on the command line. |
| `database`        | —                     | Database to back up. Overrides the URI path. If omitted, all databases are backed up. |
| `single_transaction`| `true`              | Use `--single-transaction` for a lock-free InnoDB snapshot. |
| `routines`        | `true`                | Include stored procedures and functions.            |
| `events`          | `true`                | Include event scheduler events.                     |
| `triggers`        | `true`                | Include triggers.                                   |
| `no_data`         | `false`               | Dump schema only, no data.                         |
| `no_create_info`  | `false`              | Dump data only, no schema.                          |
| `no_tablespaces`  | `true`                | Suppress tablespace statements.                     |
| `hex_blob`        | `false`               | Encode BINARY/BLOB columns as hex.                 |
| `ssl_mode`        | —                     | TLS mode: `disabled`, `preferred`, `required`, `verify_ca`, `verify_identity`. |
| `ssl_cert`        | —                     | Path to the client SSL certificate (PEM).          |
| `ssl_key`         | —                     | Path to the client SSL private key (PEM).         |
| `ssl_ca`          | —                     | Path to the CA certificate (PEM).                  |
| `mysql_bin_dir`   | —                     | Directory containing MySQL binaries. MySQL only.   |
| `column_statistics`| `true`               | Query `COLUMN_STATISTICS`. Set to `false` when using mysqldump 8.0 against MySQL 5.7. MySQL only. |
| `set_gtid_purged` | `AUTO`                | GTID mode: `AUTO`, `ON`, or `OFF`. MySQL only.    |
| `mariadb_bin_dir` | —                     | Directory containing MariaDB binaries. MariaDB only. |

## Destination options

| Option            | Default               | Description                                        |
|-------------------|-----------------------|----------------------------------------------------|
| `location`        | —                     | Connection URI: `mysql://[user[:password]@]host[:port][/database]` |
| `host`            | `127.0.0.1`          | Server hostname. Overrides the URI host.           |
| `port`            | `3306`                | Server port. Overrides the URI port.                |
| `username`        | —                     | Username. Overrides the URI user.                   |
| `password`        | —                     | Password. Overrides the URI password.               |
| `database`        | —                     | Target database. Inferred from the dump filename if omitted. |
| `create_db`       | `false`               | Issue `CREATE DATABASE IF NOT EXISTS` before restoring. |
| `force`           | `false`               | Continue on SQL errors during restore.             |
| `ssl_mode`        | —                     | TLS mode (same values as source).                  |
| `ssl_cert`        | —                     | Path to the client SSL certificate (PEM).          |
| `ssl_key`         | —                     | Path to the client SSL private key (PEM).          |
| `ssl_ca`          | —                     | Path to the CA certificate (PEM).                  |
| `mysql_bin_dir`   | —                     | Directory containing the `mysql` binary. MySQL only. |
| `mariadb_bin_dir` | —                     | Directory containing the `mariadb` binary. MariaDB only. |

## Required privileges

### Single database backup

```sql
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT ON mydb.* TO 'backup'@'%';
GRANT PROCESS ON *.* TO 'backup'@'%';
```

With `single_transaction=true` (default), `LOCK TABLES` is not required for InnoDB tables.

### All-databases backup

```sql
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, RELOAD ON *.* TO 'backup'@'%';
GRANT PROCESS ON *.* TO 'backup'@'%';
```

## Considerations

### MySQL vs MariaDB binaries

Always use binaries that match your server. On Debian and Ubuntu, `apt install default-mysql-client` installs MariaDB’s `mysqldump` by default. MariaDB’s `mysqldump` is not compatible with MySQL 8 for all-databases backups and will produce dumps that fail to restore.

Verify you have the correct binary:

```bash
$ mysqldump --version
# MySQL:   mysqldump  Ver 8.x Distrib 8.x, for Linux (x86_64)
# MariaDB: mysqldump from 11.x.x-MariaDB ...
```

If both are installed, point the integration to the correct directory using `mysql_bin_dir`.

### InnoDB and MyISAM

`single_transaction` (enabled by default) produces a consistent InnoDB snapshot without locking tables. For databases with MyISAM tables, `single_transaction` does not prevent locks on those tables. If you need a consistent backup across MyISAM tables, set `single_transaction=false` to use `--lock-all-tables` instead, accepting write locks during the dump.

### GTIDs

When the server has GTIDs enabled, the dump includes `SET @@GLOBAL.GTID_PURGED` statements that will cause restore to fail on a server that already has GTID history. Set `set_gtid_purged=OFF` on the source to omit GTID information, or run `RESET MASTER` on the target before restoring.

### User and grant migration

Single-database backups do not include user accounts or grants. To migrate users, use an all-databases backup, export grants manually with a tool like `pt-show-grants`, or recreate accounts manually on the target.

### Compression

Do not enable compression at the dump level. Plakar deduplicates and compresses data automatically. Pre-compressed dumps reduce deduplication effectiveness across snapshots.

### Kloset store location

The examples above use `/var/backups` as the Kloset store. Any supported store backend can be used instead. See [Create a Kloset store](/content/docs/main/guides/create-kloset-repository/index.html) for details.