Logical backups with SQL dumps

This guide shows how to back up and restore MySQL and MariaDB databases using the mysql:// and mysql+mariadb:// connectors. Backups produce standard SQL files via mysqldump or mariadb-dump that are portable and can be restored without Plakar if needed.

For full reference on all options, snapshot layout, Google Cloud SQL, and MySQL-specific considerations, see the MySQL / MariaDB integration reference.

• A running MySQL or MariaDB server.
• A database user with sufficient privileges (see Required privileges in the integration reference).
• mysqldump and mysql in $PATH for MySQL, or mariadb-dump and mariadb for MariaDB.

Install the MySQL package:

$ plakar pkg add mysql

# 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

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

# 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

$ plakar at /var/backups ls

The target database must already exist:

$ 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:

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

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

Always use binaries that match your server. On Debian and Ubuntu, apt install default-mysql-client installs MariaDB’s mysqldump by default, which is not compatible with MySQL 8 for all-databases backups. Verify you have the correct binary:

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

Use mysql_bin_dir to point to the correct binary directory if both are installed.

The examples above use /var/backups as the Kloset store. Any supported store backend can be used instead. See Create a Kloset store for details.

• MySQL / MariaDB integration reference
• MySQL integration GitHub
• mysqldump documentation