Secure PXC backups: pull from a separate server, restrict SSH and test restores

A backup is useful only if it survives the incident that destroys the production system.

Keeping database backups on the database node itself protects against a few mistakes, but it does not protect against filesystem loss, host compromise, ransomware, operator error or loss of the entire production environment.

For important databases I prefer a pull model from a separate backup server.

Production PXC node                 Backup server
-------------------                 -------------
MySQL / PXC       <--- restricted --- SSH key
backup command                         |
                                       +--> backup storage
                                       +--> retention
                                       +--> verification
                                       +--> monitoring

The backup server initiates the job and retrieves the backup. Production does not receive broad credentials that can modify or delete the backup repository.

Why pull instead of push?

A traditional push design gives the production server credentials for the backup destination:

production -> backup server

That is convenient, but a compromised production host may then have a path into the backup infrastructure.

With a pull design:

backup server -> production

the backup system receives narrowly scoped access to production instead. That access should be restricted to exactly one backup operation.

This is not magic isolation: the backup server is still a sensitive system. But it creates a much better security boundary than sharing general-purpose SSH access in both directions.

Do not give the backup key a normal shell

One useful OpenSSH control is a forced command in authorized_keys.

For example, on the database host:

from="172.16.23.58",restrict,command="sudo /usr/local/sbin/pxc-backup-stream" ssh-ed25519 AAAA... backup@backup-host

Three parts are important:

from="172.16.23.58"
restrict
command="sudo /usr/local/sbin/pxc-backup-stream"

from= limits where that key is accepted from. restrict disables several SSH features that a backup key does not need. command= ignores the command requested by the client and executes the predefined backup wrapper instead.

So stealing this private key should not automatically provide an interactive shell on the database server.

The exact restrictions must be tested with the SSH/OpenSSH version in use and with the backup transport you choose.

Keep sudo narrow too

A forced SSH command is only half of the boundary if it immediately calls unrestricted sudo.

The backup account should be allowed to execute only the specific root-owned wrapper required for the backup. Do not give it generic sudo ALL access.

Conceptually:

backup user
    |
    +-- SSH key accepted only from backup server
    |
    +-- forced command only
    |
    +-- sudo only /usr/local/sbin/pxc-backup-stream

The wrapper itself should be owned by root and not writable by the backup account.

Stream instead of staging huge files on production

For large databases, writing a complete temporary backup to the production filesystem can create unnecessary capacity risk.

A streaming workflow can send the backup directly to the dedicated backup host:

PXC node
   |
   | backup stream over SSH
   v
backup server
   |
   +--> dated backup directory / object

The exact implementation can use Percona XtraBackup or another tool appropriate to the database version. Compression and encryption can be added in the pipeline, but CPU cost and recovery complexity should be measured rather than assumed.

A separate server is not enough by itself

The backup repository should have its own retention and access policy. Ideally, production credentials cannot delete historical copies.

Depending on the recovery requirements, I want multiple failure boundaries:

production database
        |
        v
separate backup server
        |
        +--> local retention
        |
        +--> additional off-host/off-site copy

For higher-value systems, immutable or otherwise deletion-resistant storage is worth considering as another layer.

Monitor backup freshness externally

The database host should not be the only place that knows whether its backup succeeded.

The monitoring system should alert on the absence of a recent successful backup. Useful signals include:

last successful backup timestamp
age of newest backup
backup size
backup duration
exit status
repository free space
verification / restore-test result

This monitoring should also live outside the production database cluster. If the whole cluster disappears, you still need the alerting and backup systems to be alive.

Test restore, not just backup creation

A green backup job proves that a process exited successfully. It does not prove that the database can be recovered.

Periodically restore a backup into an isolated environment and validate it. Depending on the system this can include:

prepare/apply backup successfully
start MySQL
check expected databases and tables
run consistency checks
verify critical row/table counts
record restore duration

That exercise also gives you something many teams do not know until an incident: a realistic recovery time.

Protect the backup server as production infrastructure

A backup server contains a historical copy of valuable data. Treat it accordingly:

• no unnecessary public services;
• firewall access to known management and database addresses;
• separate administrative credentials;
• encrypted transport;
• monitored storage capacity and filesystem health;
• controlled retention and deletion;
• security updates;
• logs for backup and administrative access.

A backup server should not become an easier way to steal the database than the database server itself.

The operational rule

A production backup design should survive more than rm on one database node.

A useful baseline is:

separate backup host
+ pull model
+ source-restricted SSH key
+ forced command
+ minimal sudo
+ retention outside production
+ external backup-age alert
+ periodic restore test

The important question is not “did the backup command run?”

It is “can we recover the service after production is gone?”

For backup architecture, recovery validation and database engineering, see Backup & Recovery → or contact 0ROOT →.