A Python script for compressed MySQL backups

A reliable database backup does not need to be a large platform or a complicated orchestration system. For a small Linux server, a short Python program can call mysqldump, compress the output, apply a retention policy, and record enough information to diagnose failures. That is the approach I used when I wrote a Python script to automatically backup MySQL databases with compression.

The goal was a portable utility that could run on a VPS, a home server, or a development machine without adding another service to maintain. The resulting archive was a compressed SQL dump, named with the database and timestamp, and stored outside the live application directory.

Australian deployments add a few practical details. A Sydney or Melbourne server may use daylight saving time while a Brisbane or Perth machine does not, and cron jobs often run in the server’s local timezone. I also wanted the script to suit Australian businesses that keep customer data with a local hosting provider and need a clear, auditable backup process.

Defining the backup workflow

The script follows a simple sequence: load configuration, create a timestamped destination, run mysqldump, pipe the SQL stream through gzip, verify the command status, and remove archives older than the retention period. Keeping these operations separate makes the program easier to test and troubleshoot.

I chose mysqldump because it is widely available with MySQL and MariaDB installations. It produces a logical backup that can be restored on another compatible server, which is useful when moving between a local workstation and a VPS in Sydney, Melbourne, or another Australian data centre.

The backup name includes the database name and UTC timestamp, such as shop_2025-04-18T021500Z.sql.gz. UTC avoids ambiguity when daylight saving changes in New South Wales or Victoria, while Queensland and Western Australia remain on their standard local time.

Keeping credentials out of the command line

Putting a password directly in a shell command is risky because it may appear in process listings, shell history, monitoring output, or error reports. Instead, the script reads a MySQL client configuration file, commonly ~/.my.cnf, with restrictive permissions.

A minimal configuration looks like this:

[client]
user=backup_user
password=replace_this_value
host=127.0.0.1

The file should be owned by the account running the backup and set to mode 0600. The MySQL account itself should have the smallest practical set of permissions, usually access to the selected databases and the privileges required by mysqldump, rather than full administrative access.

Streaming SQL directly into gzip

There is no need to create a large uncompressed dump before compressing it. Python’s subprocess.Popen can connect the standard output of mysqldump to a gzip process. This keeps disk usage lower and means a database several gigabytes in size does not require another several gigabytes of temporary space.

The core operation is conceptually like this:

dump = subprocess.Popen(
    ["mysqldump", "--single-transaction", "--routines",
     "--events", database],
    stdout=subprocess.PIPE,
    stderr=subprocess.PIPE,
)

with gzip.open(output_path, "wb") as compressed:
    shutil.copyfileobj(dump.stdout, compressed)

error = dump.stderr.read()
status = dump.wait()
if status != 0:
    output_path.unlink(missing_ok=True)
    raise RuntimeError(error.decode().strip())

The --single-transaction option is appropriate for InnoDB tables because it creates a consistent snapshot without holding ordinary table locks for the entire export. Tables using other storage engines need separate consideration. Stored routines and events are included explicitly because a database restore can be incomplete without them.

Making failed backups visible

A backup that quietly fails is worse than no backup because it creates false confidence. The script writes start time, database name, output path, archive size, and exit status to a log file. Standard error from mysqldump is retained and included in the failure message.

I also write the archive to a temporary filename ending in .part. Once the dump exits successfully and gzip closes cleanly, the script renames it to its final .sql.gz name. Monitoring tools can then treat .part files as evidence of interrupted jobs rather than valid backups.

For a small business in Adelaide or Hobart, a plain log checked by cron email may be enough. A larger operation can forward the same events to systemd-journald, Nagios, or another monitoring system. The important point is that a failed export produces a visible signal.

Applying retention without deleting too much

Retention keeps the backup directory useful and prevents an inexpensive VPS from running out of disk space. My script accepts a number of days, lists files matching the expected database prefix, and removes only archives older than that period.

The filename pattern matters. A broad command such as rm *.gz could delete unrelated compressed files, while a precise pattern such as shop-*.sql.gz limits the cleanup scope. I also avoid deleting the newest archive, even when its timestamp appears old because of a clock problem.

Retention should match the business need. Seven daily copies may suit a test application, while production data might need daily and weekly generations stored separately. Australian organisations covered by the Privacy Act 1988 should also consider whether old customer data in backups is still required and how securely it is retained.

Scheduling the job on Linux

A cron entry can run the script overnight:

17 2 * * * /usr/bin/python3 /opt/dbbackup/backup.py >> /var/log/dbbackup.log 2>&1

I prefer a non-round minute because many servers start scheduled work at exactly 2:00 am. The script uses an absolute Python path and an absolute configuration path, since cron provides a much smaller environment than an interactive shell.

Timezone settings deserve deliberate testing. A 2:17 am job on a Sydney host may shift relative to UTC when daylight saving begins, while a Brisbane host will not shift at all. Running the schedule in UTC, or documenting the host timezone clearly, prevents confusion when an operator in Perth expects the archive at a different local hour.

Testing restoration rather than just creation

A compressed dump is useful only if it can be restored. I test the process against a disposable MySQL instance by creating a new database, importing the decompressed SQL stream, and checking row counts, tables, routines, and events. The restore test runs separately from the production database so it cannot overwrite live data.

The restore command is straightforward:

gzip -dc shop-2025-04-18T021500Z.sql.gz | mysql restored_shop

I also check that the archive is readable, has a sensible non-zero size, and contains expected markers such as CREATE TABLE. Those checks cannot prove that every row is correct, but they catch missing files, truncated compression, and empty exports.

For teams using Australian cloud regions, I document where the backup is stored, who can access it, and how long transfers take over the office connection or NBN service. A local copy protects against a failed application server; a second location protects against storage loss or a compromised host.

Practical safeguards for daily operation

After the first working version, I kept the script deliberately conservative. It uses a lock file to prevent overlapping runs, creates the destination directory with controlled permissions, and exits with a non-zero status whenever the dump, compression, or cleanup step fails.

The following checks form a useful operating baseline:

• Use a dedicated MySQL backup account with limited privileges.
• Store credentials in a 0600 client configuration file.
• Write archives to a separate filesystem or backup volume.
• Use UTC timestamps and document the server’s local timezone.
• Retain several generations instead of overwriting one file.
• Test a full restore at a scheduled interval.
• Encrypt archives before sending them to off-site storage.

This design remains small enough to inspect in one sitting, yet it handles the parts that usually cause trouble: secrets, streaming compression, partial files, retention, scheduling, and restoration. It is a practical foundation for a Linux backup utility that can grow as the database and operational requirements grow.