MySQL / MariaDB

The MySQL / MariaDB integration performs logical backups of MySQL and MariaDB databases using mysqldump and mariadb-dump. These backups produce standard SQL files that are human-readable and can be restored without Plakar if needed.

mysql:// and mysql+mariadb:// are independent connectors: each always uses its own set of binaries regardless of the server at the other end.

Typical use cases

• Scheduled logical backups of application databases with cross-version portability.
• Full-cluster dumps including stored procedures, events, and triggers.
• MariaDB-specific backups that require mariadb-dump semantics.
• Cloud SQL backups via the Cloud SQL Auth Proxy.

Installation

The MySQL / MariaDB integration is distributed as a Plakar package.

$ plakar pkg add mysql

$ plakar pkg list

$ plakar pkg build mysql

$ plakar pkg add ./mysql_v1.0.0_linux_amd64.ptar

To list, upgrade, or remove the package, see the managing packages guide.

Logical backup - mysql://

How it works

The connector invokes mysqldump to produce a single SQL file per database, or a combined all.sql when no database is specified. Every snapshot follows the same layout:

• /manifest.json: cluster metadata captured before the dump (see Snapshot manifest).
• /<dbname>.sql: single-database dump when a database is specified.
• /all.sql: full-server dump when no database is specified (--all-databases).

Pros

• Portable across MySQL major versions.
• Human-readable SQL that can be restored without Plakar.
• Supports selective restore of individual databases.
• No server downtime required.

Cons

• Restore time scales with data volume.
• Does not capture user accounts or grants for single-database backups.
• MyISAM tables cannot be backed up consistently without locking.

Prerequisites

The following tools must be available in $PATH on the machine running Plakar (provided by the mysql-client package, or the MySQL distribution):

• mysqldump: for backup
• mysql: for restore

The backup user requires the following privileges:

-- Single database (with single_transaction=true, default)
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT ON mydb.* TO 'backup'@'%';
GRANT PROCESS ON *.* TO 'backup'@'%';

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

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

Source connector

flowchart LR
subgraph Source["MySQL Server"]
  DB["Databases"]
end

subgraph Plakar["Plakar"]
  Connector["mysqldump"]
  Transform["Encrypt & deduplicate"]

  Connector --> Transform
end

Store["Kloset Store"]

DB --> Connector
Transform --> Store

# Back up a single database
$ plakar source add mydb mysql://dbuser:secret@db.example.com/mydb
$ plakar at /var/backups backup "@mydb"

# Back up all databases
$ plakar source add alldb mysql://root:secret@db.example.com
$ plakar at /var/backups backup "@alldb"

# Schema only
$ plakar source add mydb mysql://dbuser:secret@db.example.com/mydb \
  no_data=true
$ plakar at /var/backups backup "@mydb"

Source options

Destination connector

flowchart LR
Store["Kloset Store"]

subgraph Plakar["Plakar"]
  Transform["Decrypt & reconstruct"]
  Connector["mysql"]

  Transform --> Connector
end

subgraph Destination["MySQL Server"]
  DB["Databases"]
end

Store --> Transform
Connector --> DB

# Restore into an existing database
$ plakar destination add mydbdst mysql://dbuser:secret@target.example.com/mydb
$ plakar at /var/backups restore -to "@mydbdst" <snapshot_id>

# Create the database and restore
$ 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
$ plakar destination add mydbdst mysql://root:secret@target.example.com
$ plakar at /var/backups restore -to "@mydbdst" <snapshot_id>

Destination options

Logical backup - mysql+mariadb://

mysql+mariadb:// works identically to mysql:// but invokes mariadb-dump for backup and mariadb for restore. This connector is used when targeting a MariaDB server to avoid binary-compatibility issues.

# Back up a single MariaDB database
$ plakar source add mydb mysql+mariadb://dbuser:secret@db.example.com/mydb
$ plakar at /var/backups backup "@mydb"

# Back up all MariaDB databases
$ plakar source add alldb mysql+mariadb://root:secret@db.example.com
$ plakar at /var/backups backup "@alldb"

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

mysql+mariadb:// supports the same source and destination options as mysql://, with the following differences:

The column_statistics and set_gtid_purged options are MySQL-only and have no effect on mysql+mariadb://.

Google Cloud SQL - mysql+gcsql://

mysql+gcsql:// targets MySQL databases hosted on Google Cloud SQL. It uses the Cloud SQL Auth Proxy to establish an encrypted connection, making the experience equivalent to connecting to a local MySQL server.

The connector supports the same source and destination options as mysql://. The Cloud SQL Auth Proxy must be running and accessible on the configured host and port before Plakar connects.

# Assuming the proxy is running on localhost:3306
$ plakar source add mygcs mysql+gcsql://dbuser:secret@127.0.0.1:3306/mydb
$ plakar at /var/backups backup "@mygcs"

Snapshot manifest

Every snapshot produced by this integration includes a /manifest.json record written before the dump data. It captures the server state at the time of backup.

Metadata collection is best-effort: if a query fails, the affected field is omitted and the backup continues normally.

Considerations

MySQL vs MariaDB binaries

Always use binaries that match your server flavor. On Debian and Ubuntu, apt install default-mysql-client installs MariaDB binaries by default. MariaDB’s mysqldump produces dumps that fail to restore against MySQL 8 for all-databases backups. Use mysql_bin_dir to point to the correct binaries when both flavors are installed on the same machine.

InnoDB and MyISAM

single_transaction (enabled by default) produces a consistent InnoDB snapshot without locking tables. For databases that contain MyISAM tables, this option does not prevent locks on those tables. Disable single_transaction to use --lock-all-tables instead, accepting write locks for the duration of the dump.

GTIDs

When the server has GTIDs enabled (gtid_mode=ON), mysqldump includes SET @@GLOBAL.GTID_PURGED statements. Restoring to a server that already has GTID history will fail. Options:

• Set set_gtid_purged=OFF on the source to omit GTID information from the dump.
• Run RESET MASTER on the target server before restoring.

User and grant migration

Single-database backups do not include user accounts or grants. To migrate users:

• Use an all-databases backup, which includes the mysql system database.
• Export grants manually with a tool like pt-show-grants (Percona Toolkit).
• Recreate user accounts manually on the target server.

Known limitations

• Physical backup not supported: Only logical dumps via mysqldump / mariadb-dump. For physical backups, stop the server and use Plakar’s filesystem backup on the data directory directly.
• No per-table filtering: The entire database (or all databases) is dumped.
• No restore-time data/schema filtering: The full dump is always piped to the client CLI.

See also

• Logical backups with SQL dumps
• Physical backups
• MySQL integration GitHub
• mysqldump reference
• mariadb-dump reference