Automatic Database Backups: mysqldump, pg_dump, cron

curl -sSL https://cp.lv/scripts/bekapy-bazy-dannyh.sh | sudo bash

wget -qO- https://cp.lv/scripts/bekapy-bazy-dannyh.sh | sudo bash

#!/usr/bin/env bash
# Creates a MySQL backup script, the first dump and a cron job
set -e

SCRIPT=/opt/scripts/mysql-backup.sh
BACKUP_DIR=/var/backups/mysql

# 1. Directories for the script and the dumps
sudo mkdir -p /opt/scripts "$BACKUP_DIR"

# 2. Create the backup script itself (quoted 'EOF': variables expand at run time)
sudo tee "$SCRIPT" > /dev/null << 'EOF'
#!/bin/bash
set -euo pipefail
DB_NAME="app_db"
DB_USER="backup_user"
BACKUP_DIR="/var/backups/mysql"
KEEP_DAYS=7
DATE=$(date +%F_%H-%M)
mkdir -p "$BACKUP_DIR"
mysqldump --single-transaction --quick --user="$DB_USER" \
  "$DB_NAME" | gzip > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete
EOF
sudo chmod +x "$SCRIPT"

# 3. Take the first backup right away
sudo "$SCRIPT"

# 4. Cron job: every day at 03:30
echo "30 3 * * * root $SCRIPT >> /var/log/db-backup.log 2>&1" | sudo tee /etc/cron.d/db-backup > /dev/null

echo "Done: script $SCRIPT created, first dump in $BACKUP_DIR, cron configured."

A database backup is what saves a project after a disk failure, an accidental DROP TABLE or a broken migration. No hosting provider takes your own responsibility for backups away: keep copies and test that they restore. Below are working scripts for MySQL/MariaDB and PostgreSQL, scheduled with cron.

What makes a backup reliable

• Regularity — on a schedule, with no manual steps.
• Rotation — keep the last N copies and delete the older ones.
• Off-server storage — copy the dumps to another server or to object storage, not only to the same disk.
• Restore testing — periodically restore a dump into a test database.

MySQL and MariaDB backups

The mysqldump utility creates a logical dump — an SQL file with all the data. Create /opt/scripts/mysql-backup.sh in an editor (nano, vim or mcedit):

sudo mkdir -p /opt/scripts
sudo nano /opt/scripts/mysql-backup.sh

Script contents:

#!/bin/bash
set -euo pipefail

DB_NAME="app_db"
DB_USER="backup_user"
BACKUP_DIR="/var/backups/mysql"
KEEP_DAYS=7
DATE=$(date +%F_%H-%M)

mkdir -p "$BACKUP_DIR"

mysqldump --single-transaction --quick --user="$DB_USER" \
  "$DB_NAME" | gzip > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"

# delete copies older than KEEP_DAYS days
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete

Or create the whole script with a single command — copy it and paste it into the console (the quotes around 'EOF' matter: the $ variables must stay inside the script instead of expanding now):

sudo tee /opt/scripts/mysql-backup.sh > /dev/null << 'EOF'
#!/bin/bash
set -euo pipefail

DB_NAME="app_db"
DB_USER="backup_user"
BACKUP_DIR="/var/backups/mysql"
KEEP_DAYS=7
DATE=$(date +%F_%H-%M)

mkdir -p "$BACKUP_DIR"

mysqldump --single-transaction --quick --user="$DB_USER" \
  "$DB_NAME" | gzip > "$BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz"

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete
EOF
sudo chmod +x /opt/scripts/mysql-backup.sh

The --single-transaction flag takes a consistent snapshot without locking tables (for InnoDB). To avoid storing the password in the script, create ~/.my.cnf with permissions 600. Open it in an editor:

nano ~/.my.cnf

File contents:

[client]
user=backup_user
password=StrongPassword123!

Or create the file with a single command — copy it and paste it into the console:

tee ~/.my.cnf > /dev/null << 'EOF'
[client]
user=backup_user
password=StrongPassword123!
EOF
chmod 600 ~/.my.cnf

Make the script executable:

chmod +x /opt/scripts/mysql-backup.sh

PostgreSQL backups

A similar script based on pg_dump (the custom format is handier for partial restores). Create /opt/scripts/postgres-backup.sh in an editor:

sudo nano /opt/scripts/postgres-backup.sh

Script contents:

#!/bin/bash
set -euo pipefail

DB_NAME="app_db"
BACKUP_DIR="/var/backups/postgres"
KEEP_DAYS=7
DATE=$(date +%F_%H-%M)

mkdir -p "$BACKUP_DIR"

pg_dump -Fc "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"
find "$BACKUP_DIR" -name "*.dump" -mtime +$KEEP_DAYS -delete

Or create the whole script with a single command (the quotes around 'EOF' keep the $ variables inside the script):

sudo tee /opt/scripts/postgres-backup.sh > /dev/null << 'EOF'
#!/bin/bash
set -euo pipefail

DB_NAME="app_db"
BACKUP_DIR="/var/backups/postgres"
KEEP_DAYS=7
DATE=$(date +%F_%H-%M)

mkdir -p "$BACKUP_DIR"

pg_dump -Fc "$DB_NAME" > "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"
find "$BACKUP_DIR" -name "*.dump" -mtime +$KEEP_DAYS -delete
EOF
sudo chmod +x /opt/scripts/postgres-backup.sh

The PostgreSQL password is kept in ~/.pgpass (permissions 600). Open it in an editor:

nano ~/.pgpass

File contents (the format is host:port:db:user:password):

localhost:5432:app_db:backup_user:StrongPassword123!

Or create the file with a single command:

tee ~/.pgpass > /dev/null << 'EOF'
localhost:5432:app_db:backup_user:StrongPassword123!
EOF
chmod 600 ~/.pgpass

The cron schedule

Open the crontab of the user the scripts run as:

crontab -e

Add the jobs — a daily backup at 3:30 at night:

30 3 * * * /opt/scripts/mysql-backup.sh >> /var/log/db-backup.log 2>&1
30 3 * * * /opt/scripts/postgres-backup.sh >> /var/log/db-backup.log 2>&1

Check that the job was saved: crontab -l.

Copying to another server

Add the transfer of the dump to a remote host over SSH at the end of the script:

rsync -az "$BACKUP_DIR"/ [email protected]:/remote/db-backups/

Restoring from a dump

MySQL:

gunzip < app_db_2026-04-17_03-30.sql.gz | mysql -u root -p app_db

PostgreSQL (custom format):

pg_restore -d app_db --clean app_db_2026-04-17_03-30.dump

FAQ

How often should I run backups? It depends on how much data you are willing to lose. For an active site, daily; for critical data, several times a day or continuous WAL archiving (PostgreSQL).

Does mysqldump lock the database while it runs? With the --single-transaction flag there is no locking for InnoDB tables — a consistent snapshot is taken instead. For MyISAM, locking is possible.

Where should the dumps be stored? Not only on the same disk. Copy them to a separate server or to object storage: a disk failure must not take out both the database and its backups.

How do I make sure a backup actually works? Restore the dump into a separate test database from time to time and check the data. An untested backup is not a backup.

Backups are your own responsibility, and we recommend keeping them at all times. You can host a database with its own backup schedule on the VPS hosting plans or on the dedicated database VPS.