SxMDBSync 1.2: synchronise MariaDB and MySQL databases over SSH

SxMDBSync

SxMDBSync is a GPL-licensed Bash tool that synchronises one or more MariaDB or MySQL databases between two servers by streaming the dump directly through an SSH connection.

It is designed for controlled migrations and synchronisation jobs, not for setting up permanent replication. No large intermediate SQL file is required: data is exported, transported and imported in the same stream.

Antesync and retrosync

The two methods describe the transfer direction relative to the machine on which the command is run:

– antesync sends local databases to the remote server;
– retrosync retrieves remote databases onto the local machine.

Send a local database:

./sxmdbsync.sh \
  --method antesync \
  --user root \
  --host 192.0.2.10 \
  --database production

Retrieve a remote database:

./sxmdbsync.sh \
  --method retrosync \
  --user root \
  --host 192.0.2.10 \
  --database production

The default SSH port is 65022. It can be changed with --port. The --ssh-option option can be repeated to add ssh -o options.

Selection and renaming

--database can be repeated to synchronise several databases. The SOURCE:TARGET form changes the name on arrival:

./sxmdbsync.sh -m retrosync -u root -H 192.0.2.10 \
  --database production:staging \
  --database reporting

--all-user-databases selects every non-system database on the source side. The information_schema, performance_schema, mysql and sys databases are excluded.

Warning: the target is reset by default

By default, SxMDBSync drops and recreates every target database before importing it. This behaviour is deliberate: a true synchronisation must also remove tables from the target when they no longer exist in the source. The script verifies that the database is absent after DROP DATABASE, then present after CREATE DATABASE, before sending any data.

This operation therefore destroys the previous contents of the target database. Check the antesync or retrosync direction, server, source and target names, and your backups before running the command.

The --no-reset-target option disables that reset and imports into the existing database, or creates it if missing. This is useful when the SQL account cannot recreate the schema, but this mode no longer guarantees a strict mirror.

Always start with a dry run

--dry-run, or -n, displays the operation plan without performing any drop, creation or import:

./sxmdbsync.sh \
  --method antesync \
  --user deploy \
  --host db.example.net \
  --database production:production \
  --dry-run

With --all-user-databases, a dry run must still query the source to establish the real list of affected databases. It should not be mistaken for a purely offline validation.

Credentials and secret files

SxMDBSync can use local socket authentication when run by root and ~/.my.cnf for an unprivileged user. The same principle applies on the remote machine according to the SSH user.

For explicit configuration, preferably use:

– --local-db-cnf and --remote-db-cnf for MariaDB/MySQL configuration files;
– --local-db-pass-file and --remote-db-pass-file for files containing only the password.

Password files must be regular files, not symbolic links, owned by the current user, and inaccessible to the group and other users. The legacy --local-db-pass and --remote-db-pass options still exist, but put the secret in visible process arguments: do not use them for normal automation.

Safeguards

Version 1.2 validates database names, users, host and port; rejects duplicate target mappings; protects execution with a lock; checks sensitive files; detects the available MariaDB or MySQL clients; and stops as soon as a critical step fails.

The log is written to ~/sxmdbsync.log by default. The SXMDBSYNC_LOG variable changes its location.

Resources

Script: sxmdbsync.sh

—
Christophe Casalegno (find all my links, projects and social networks at all.bo)