MySQL backups: mysqldump on cron, S3 offload, restore test

A MySQL backup that you can rely on is three separate jobs, not one. The first is a copy taken on a schedule, the second is a copy that lives somewhere other than the database, and the third is a regular, boring proof that the second one can be turned back into a working database. On RE:NODE the first can be done in the panel with a schedule that creates a backup. The second and third are a mysqldump run from cron on a machine of your own, an upload to S3 storage, and a script that loads last night's file into a throwaway MySQL and checks it. This post builds all three, in the order they should run, with the timings that stop them tripping over each other.

If you only want the flags of mysqldump itself, mysqldump backup and restore explains each one, and database dumps to S3 on a schedule covers the general pipe-to-S3 pattern for three engines. This post is narrower: one MySQL database, one schedule, and a restore test that you will actually keep running.

The plan: three copies, three schedules#

Each layer answers a different failure, and each runs at a different time so they never compete for the same database.

The panel backup is the fast one. The RE:NODE panel keeps backups in slots on every plan, creates them on demand or on a schedule, restores them with a button, lets you download them and lets you lock one against rotation. They are stored off the machine they protect. What we do not publish is how long a slot is kept beyond rotation, a size limit, or where the off-machine copies physically sit, so treat the slot as a convenient first line and not as your only copy. The number of slots depends on the tier; for MySQL it runs from one to four, so check the plan you bought. Deleting a server deletes its backups, locked ones included, which is the single strongest argument for the second layer.

The mysqldump file is the portable one. It is plain SQL, so you can load it into a different host, a newer MySQL, or a laptop. The restore test is the one people skip, and the one that decides whether the other two count.

A sensible clock for a database that is quietest in the early morning: the panel backup at 03:00, the dump at 03:30, the upload straight after it, and the restore test once a week on Sunday at 06:00. The gaps matter less than the ordering. A panel backup and a dump are both readers, so they can overlap, but a long dump holds a snapshot open and you do not want two of those at once on a small plan.

Where the dump job runs#

mysqldump is a client. It connects to the database over the network like any application and needs no shell on the database machine, which is lucky, because a database plan is not a place to install your own tools. On RE:NODE a MySQL server gives you a generated root password, an application database and an application user, reached on the plan's host and port. We do not document a shell on the database server itself, so assume mysqldump runs elsewhere and plan for that.

The options for the runner, in order of preference:

• A small VDS or any Linux machine you already run, with cron and the MySQL client tools installed. This is the normal answer.
• The application server, if it is a machine on which you can install packages and edit a crontab. Be careful with this one: an app server that is deleted takes the backup job with it.
• Your own computer or a home server, which has the virtue of being a different place from both the database and the bucket.

The panel's Schedules tab can run a console command, a backup or a power action on a server. That is exactly right for the backup task and for restarts. A console command is typed into the server's console, which is not a general shell, so do not expect it to run mysqldump against the database. Use the panel schedule for what the panel does, and cron on your runner for the dump.

The client version should match the server. This post assumes MySQL 8.4 LTS, so install the 8.4 client tools on the runner. A mysqldump from a much older release can fail on options that a current server knows, and a dump taken by a MariaDB client against MySQL is something to avoid for backups. Check what you have:

$ mysqldump --version$ mysql --version

Step one: a schedule for panel backups#

Open the server in the panel, go to Schedules, and create a schedule. A schedule has a cron expression and an ordered list of tasks, each of which can wait a given delay after the one before it. For a database the useful shape is a single task that creates a backup. If you have never written a cron expression, cron expressions explained takes five minutes to read.

A daily backup at 03:00 is:

0 3 * * *

The five fields are minute, hour, day of month, month and day of week. Check which time zone the Schedules tab uses before you pick the hour, because the panel's clock and your runner's clock are not necessarily the same one. We do not state a zone for schedules, so look at how the tab presents the next run time, and make one test schedule if the display is unclear.

Then think about slots. With one to four slots on a MySQL plan, a daily backup rotates through them quickly. Two habits help:

1. Keep a second, weekly schedule only if your plan has enough slots to spare, and treat the slots as a short window of recent states rather than as history.
2. Lock a backup you want to keep, for example the one taken just before a risky migration. Locked backups are excluded from rotation, so remember that they also take up a slot until you unlock them.

Whether a backup is reliable is not something you learn from the panel. After the first scheduled run, open Backups and confirm that the entry appeared at the time you expected, with a plausible size. The guides page walks through the Schedules tab, and the backups section covers the Backups tab.

Step two: a backup user for mysqldump#

Do not run the cron job as root. Root's password is the key to the whole server, and a cron environment is a poor place to keep it. The application user that RE:NODE generates for your database is a better choice because it can only touch that database. If you are able to create a dedicated account, give it only what a dump needs. For one database, that is:

CREATE USER 'backup'@'%' IDENTIFIED BY RANDOM PASSWORD;GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON appdb.* TO 'backup'@'%';

IDENTIFIED BY RANDOM PASSWORD prints a generated password once; copy it straight into the credentials file below. Whether you can create extra users on your plan is a question for your own account in the panel, so check before you rely on it; if you cannot, the generated application user is enough for dumping its own database. If --routines fails with a privilege error, MySQL users and privileges explains the SHOW_ROUTINE privilege that fixes it.

Put the credentials in a file so that they never appear in the process list or in your shell history:

[client]host = db.example.netport = 30412user = backuppassword = paste-the-generated-password-here

Replace the host and port with the ones shown for your database in the panel. Then lock the file down:

$ sudo mkdir -p /etc/backup$ sudo chown root:root /etc/backup && sudo chmod 700 /etc/backup$ sudo chmod 600 /etc/backup/my.cnf

If the job runs as an ordinary user, own the directory by that user instead. The point is that nobody else on the runner can read it. Test the connection before writing any script:

$ mysql --defaults-extra-file=/etc/backup/my.cnf -e 'SELECT VERSION(), NOW();'

Step three: the dump script#

The pipeline mysqldump | gzip > file has a well-known failure: if mysqldump dies halfway, the compressor still finishes cleanly and leaves a small, valid-looking file. The script below avoids that with pipefail, writes to a temporary name, and then proves the file is complete before it takes its final name. A complete mysqldump file ends with a comment line beginning -- Dump completed, and that line is absent from a truncated one, which makes it a cheap and honest completeness check.

#!/usr/bin/env bashset -euo pipefailDB=appdbWORK=/var/backups/mysqlBUCKET=s3://db-dumps/mysql/$DBPROFILE=storeKEEP_LOCAL_DAYS=3STAMP=$(date -u +%F_%H%M)NAME="$DB-$STAMP.sql.zst"mkdir -p "$WORK"cd "$WORK"mysqldump --defaults-extra-file=/etc/backup/my.cnf \  --single-transaction --routines --events --triggers --hex-blob \  --set-gtid-purged=OFF --no-tablespaces "$DB" \  | zstd -q -T0 > "$NAME.part"zstd -t -q "$NAME.part"zstd -dc "$NAME.part" | tail -n 1 | grep -q '^-- Dump completed'mv "$NAME.part" "$NAME"sha256sum "$NAME" > "$NAME.sha256"aws s3 cp "$NAME" "$BUCKET/$NAME" --profile "$PROFILE" --only-show-errorsaws s3 cp "$NAME.sha256" "$BUCKET/$NAME.sha256" --profile "$PROFILE" --only-show-errorsfind "$WORK" -name "$DB-*.sql.zst*" -mtime +"$KEEP_LOCAL_DAYS" -deleteecho "ok $NAME $(stat -c %s "$NAME") bytes"

Walk through what it does, because each line is there for a reason:

• set -euo pipefail stops on the first error, on unset variables, and on a failure in any stage of a pipe. Without pipefail the exit status of the dump pipeline would be the compressor's.
• --single-transaction reads every InnoDB table from one consistent snapshot without blocking writers. --routines --events --triggers put stored procedures, scheduled events and triggers in the file. --hex-blob writes binary columns safely. --set-gtid-purged=OFF and --no-tablespaces avoid the two errors that most often stop a dump taken by an ordinary user.
• zstd -t tests the integrity of the compressed file. The next line decompresses it and checks the last line, which proves the dump itself ended normally and not merely that the compression was intact.
• The .part name means a half-written file is never mistaken for a backup. Only a file that passed both checks gets renamed.
• The checksum is uploaded next to the dump. The restore test uses it to detect a corrupted download or an upload that went wrong.
• Local copies are deleted after three days. The bucket keeps the long history; the runner only needs the last few for a fast restore.

Install the tools and make it executable. The package names below are for Debian and Ubuntu:

$ sudo apt install mysql-client zstd awscli$ sudo chmod 755 /usr/local/bin/mysql-backup.sh$ sudo /usr/local/bin/mysql-backup.sh

Run it by hand once and read the output before you schedule it. Note the size of the file and write down how long it took. Both numbers are your baseline.

Step four: S3 storage as the off-site copy#

The upload uses the AWS CLI because it ships on every platform and RE:NODE's S3 storage works with it. Each S3 server comes with an access key and secret key generated for it and a first bucket already created, addressed path-style with any region name. The endpoint speaks plain HTTP on its port, and HTTPS goes through the plan's proxy slot. That has a practical consequence for a database dump: the file crosses the network, so either use the HTTPS address or encrypt the file before it leaves the runner. A dump contains every user, every hash and every address in your application.

Configure a profile once on the runner. The endpoint below is a placeholder for the address your plan shows:

[profile store]region = us-east-1endpoint_url = https://s3.example.comrequest_checksum_calculation = when_requiredresponse_checksum_validation = when_requireds3 =  addressing_style = path

[store]aws_access_key_id = YOUR_ACCESS_KEYaws_secret_access_key = YOUR_SECRET_KEY

Check it with a listing. The first call fails loudly if the endpoint, the keys or the addressing style are wrong:

$ aws s3 ls s3://db-dumps/ --profile store

S3 storage with the AWS CLI covers the endpoint and checksum settings in more detail. If you prefer rclone, rclone with S3 storage has the equivalent remote.

For encryption, the lowest-effort option is to put age between the compressor and the file: change the dump line to end with | zstd -q -T0 | age -r YOUR_PUBLIC_KEY > "$NAME.part" and adjust the name and checks accordingly, which means the completeness check has to decrypt first, using a private key that you should not leave on the runner. That tension is why many people choose HTTPS and a locked-down bucket for a first version and add encryption later. Whichever you choose, keep the decryption key somewhere that survives the loss of the runner.

An S3 storage plan holds one copy on NVMe in one location, not replicated. It is a second home for your dumps, not a third. If the data matters, the 3-2-1 idea in S3 storage sizing and the 3-2-1 rule applies: a panel backup, a dump in the bucket, and an occasional download to somewhere else entirely. Size the bucket from your policy. Thirty daily dumps of a 300 MB compressed file is about 9 GB, which fits a 10 GB plan with little spare, so measure your own first file before choosing.

Step five: schedule it with cron#

Cron is the scheduler on the runner. Edit the crontab of the user that owns the credentials:

$ crontab -e

MAILTO=you@example.com30 3 * * * flock -n /tmp/mysql-backup.lock /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Replace the address in MAILTO with your own; cron mails any output of a job that is not redirected, though here the log file takes it. 30 3 * * * is 03:30 every day in the runner's time zone, which you can check with timedatectl. flock -n refuses to start a second copy if the previous run is still going, which on a growing database is how two overlapping dumps happen. The log needs to exist and be writable by the job's user:

$ sudo touch /var/log/mysql-backup.log$ sudo chown "$USER" /var/log/mysql-backup.log

Cron runs with a minimal PATH, so a script that works in your shell and fails from cron nearly always cannot find aws or zstd. Either run command -v aws zstd mysqldump and put the directories in a PATH= line at the top of the crontab, or use full paths in the script. Check the log the morning after the first run, not a week later.

A job that stops running looks exactly like a job that works, so add an alarm for silence. Many free monitoring services give you a URL; the script pings it only at the end of a successful run, and you are alerted when the ping is missing. Add one line as the last command of the script:

curl -fsS -m 10 --retry 3 "https://hc.example.com/ping/your-uuid" > /dev/null

Also keep an eye on the size line the script logs. A dump that suddenly drops to a few kilobytes has failed in a way that exit codes did not catch, for instance because the application user lost its privileges.

Step six: the restore test#

A backup nobody has restored is a hypothesis. The restore test turns it into a fact, and it is worth automating because the version you do manually only happens once. The idea is simple: pull the newest dump from the bucket, check its checksum, load it into a scratch MySQL of the same version that exists only for the test, run a few queries that only succeed on real data, print how long it took, and delete everything.

Docker is the easiest scratch server, and it keeps the test away from your real database. Pin the same version as the server, here 8.4:

#!/usr/bin/env bashset -euo pipefailDB=appdbBUCKET=s3://db-dumps/mysql/$DBPROFILE=storeCTR=restore-test-$$EXPECT_TABLES=12WORK=$(mktemp -d)cleanup() { docker rm -f "$CTR" > /dev/null 2>&1 || true; rm -rf "$WORK"; }trap cleanup EXITcd "$WORK"LATEST=$(aws s3 ls "$BUCKET/" --profile "$PROFILE" \  | awk '{print $4}' | grep '\.sql\.zst$' | sort | tail -n 1)[ -n "$LATEST" ] || { echo "no dump found"; exit 1; }aws s3 cp "$BUCKET/$LATEST" . --profile "$PROFILE" --only-show-errorsaws s3 cp "$BUCKET/$LATEST.sha256" . --profile "$PROFILE" --only-show-errorssha256sum -c "$LATEST.sha256"docker run -d --name "$CTR" -e MYSQL_ROOT_PASSWORD=scratch \  -e MYSQL_DATABASE="$DB" mysql:8.4 > /dev/nulluntil docker exec "$CTR" mysql -h127.0.0.1 -uroot -pscratch -e 'SELECT 1' \    > /dev/null 2>&1; do sleep 2; doneSTART=$(date +%s)zstd -dc "$LATEST" | docker exec -i "$CTR" mysql -uroot -pscratch "$DB"END=$(date +%s)TABLES=$(docker exec "$CTR" mysql -uroot -pscratch -N -e \  "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='$DB'")echo "tables: $TABLES (expected at least $EXPECT_TABLES)"[ "$TABLES" -ge "$EXPECT_TABLES" ]docker exec "$CTR" mysql -uroot -pscratch -N "$DB" -e \  "SELECT COUNT(*) AS orders, MAX(created_at) AS newest FROM orders"echo "restored $LATEST in $((END - START)) s"

Several details matter here. The official MySQL image starts a temporary server while it initialises and then restarts it. That temporary server does not listen on TCP, which is why the readiness loop connects with -h127.0.0.1 and only passes once the real server is up; a plain mysqladmin ping over the socket can succeed too early and the import then hits a restart. EXPECT_TABLES is a number you set by hand, the count of tables your database should have, and the test fails if the restored copy has fewer. The final query is the one to adapt: use any table and a timestamp column your application writes on every change, so that you can see at a glance that the newest row is from yesterday and not from last month.

The trap removes the container and the temporary directory whether the test passes or fails, so a failure leaves nothing behind. The throwaway root password scratch is harmless because the container publishes no ports.

Run it by hand the first time and read what it says. Then put it in cron, weekly, outside the dump window:

0 6 * * 0 /usr/local/bin/mysql-restore-test.sh >> /var/log/mysql-restore-test.log 2>&1

The number worth keeping from the first run is the restore time. A dump of a few hundred megabytes loads in minutes on a laptop, but loading rebuilds every index, and a restore is often several times slower than the dump was. When the number is larger than the downtime you can accept, the answer is not a faster script; it is parallel dump and load with MySQL Shell, described in mysqldump backup and restore, or smaller databases. Test a backup restore: a 30-minute drill turns the same idea into a team routine.

Restoring for real#

When the day comes, you have two routes and the panel decides which is quicker. For a mistake in the last few days, the slot backup is the first thing to try: restore it with the button on the Backups tab. Before you press it, remember that a restore replaces the current state, so if the database still holds changes you want, take a fresh backup first or download the slot you are about to restore over.

When the slot is gone, or the server is gone, use the dump. Create the database on the new server, then load the file straight from the bucket:

$ aws s3 cp s3://db-dumps/mysql/appdb/appdb-2026-10-23_0330.sql.zst - --profile store \    | zstd -dc \    | mysql --defaults-extra-file=/etc/backup/my.cnf appdb

Restore into an empty database. A dump drops and recreates the tables it contains, but leaves other tables alone, so loading "over" a live database gives you a mixture. If the application should not see a half-loaded database, restore into a new database name and switch the connection string once it is complete. If you are moving to a different host altogether, migrating MySQL to a new host covers users, definers and the cut-over.

What goes wrong#

The failures cluster around a handful of causes, and most are quiet.

• The job never ran. Cron's PATH is minimal, the crontab belongs to a different user, or the machine was rebuilt. The missing ping from the monitoring service is how you find out within a day. Without it, you find out at restore time.
• Access denied for tablespaces. Since MySQL 8.0.21, dumping tablespace information needs the global PROCESS privilege. The script passes --no-tablespaces, which is what an application dump wants.
• The dump is tiny. The user can connect but can see nothing, or the database name is wrong. mysqldump can exit successfully on an empty database. Keep the size line in the log and compare it with yesterday's.
• `Dump completed` is missing. The connection dropped mid-dump, usually over a long run on an unreliable link. The script refuses to promote that file, and you see an error in the log and no new object in the bucket.
• The upload fails with a checksum or addressing error. The two checksum settings in the profile and addressing_style = path are the usual fix for S3-compatible endpoints.
• The restore is wrong in a way no check catches. Row counts match and the newest timestamp is right, but a stored procedure is missing. That is --routines and --events missing from the dump command, and why the script has them.
• The dump overlaps a migration. --single-transaction does not protect against schema changes in the middle of a dump. Do not deploy migrations between 03:25 and the time the dump normally finishes.

If the database is small, the whole routine uses a few hundred megabytes of storage and a few minutes of runner time each night. If it is large, the restore time is where you will spend your thinking, and the right moment to learn that is during a Sunday test, not during an outage.

FAQ#

Can I run mysqldump from the panel's Schedules tab?

Not as a way to dump the database. A schedule runs a console command, a backup or a power action on the server, and a console command goes to the server's console rather than to a general shell. Use the schedule for backups and restarts, and run mysqldump from cron on a separate machine.

How many panel backups should I keep?

As many as your plan's slots allow, treated as a short window of recent states. A MySQL plan has between one and four slots depending on the tier. Lock the one you cannot afford to lose before a risky change, and keep the long history as dumps in S3.

How often should I run the restore test?

Weekly is a good default for a database that matters, and after any change to the dump script, the MySQL version or the credentials. The test costs a few minutes of a spare machine, and the first failure it catches pays for every run you did without incident.

Should I encrypt the dumps?

Yes if the file crosses the network without HTTPS or if the bucket keys might leak, because a dump holds every user and hash in the database. The S3 endpoint speaks plain HTTP on its port, so use the HTTPS address behind the plan's proxy slot, or encrypt with a tool such as age before upload, and store the key apart from the runner.

What if my database is too big for a single mysqldump file?

Past roughly 20 to 50 GB, a single-threaded dump and, above all, a single-threaded restore get slow. MySQL Shell's dump and load utilities work in parallel and are the usual next step. Measure your restore time first, because it is the number that decides whether you need to change anything.