Back Up and Restore MariaDB via SSH Tunnel

Need to back up a MariaDB database on Sliplane? This guide shows you how to securely create and restore backups using an SSH tunnel. This method keeps your database private while giving you full access without exposing it publicly.

What You'll Learn

In this guide, you'll:

• Set up an SSH tunnel service in Sliplane
• Create backups using mysqldump over the tunnel
• Restore databases using mysql
• Handle different backup formats and restore options

Note: If your database is already public and your IP is whitelisted, you can connect directly without an SSH tunnel.

Prerequisites

Before you start, make sure you have:

• MariaDB client tools installed: mysqldump, mysql
• Your Sliplane MariaDB database credentials
• A Sliplane project with a running MariaDB service

Step 1: Create an SSH Tunnel Service

First, create a dedicated SSH tunnel service in your Sliplane project:

1. In your Sliplane dashboard, go to your project
2. Click Add Service → Presets → SSH Tunnel
3. Use these settings:
   • Image: ghcr.io/sliplane/docker-ssh:latest
   • Environment Variables:
     • HOST=0.0.0.0
     • PORT=2222
     • ROOT_PASSWORD=choose a strong password
4. Deploy the service and note its public domain (e.g., ssh-tunnel-123.sliplane.app)

Step 2: Establish the SSH Tunnel

Open a terminal and create a local tunnel to your Sliplane database:

ssh -p 2222 \
  [email protected] \
  -L 3306:mariadb-service-name.internal:3306 \
  -N

Replace mariadb-service-name.internal with your MariaDB service's internal hostname. Keep this terminal window open during the backup/restore process.

Step 3: Test the Connection

Verify that the tunnel works before creating backups:

mysql -h 127.0.0.1 -P 3306 -u your-user -p'your-password' \
-e 'SELECT version();'

If you see the MariaDB version, your tunnel is working correctly.

Step 4: Create a Backup

Now that your SSH tunnel is working, it's time to create a backup. MariaDB offers multiple backup options through mysqldump, each with different use cases and advantages.

Full Database Backup (Recommended)

This format creates a complete SQL backup of your database:

mysqldump -h 127.0.0.1 -P 3306 \
  -u your-user -p'your-password' \
  --single-transaction \
  --routines \
  --triggers \
  --all-databases \
  > backup-$(date +%F).sql

Specific Database Backup

Backup only a specific database:

mysqldump -h 127.0.0.1 -P 3306 \
  -u your-user -p'your-password' \
  --single-transaction \
  --routines \
  --triggers \
  your-database \
  > backup-$(date +%F).sql

Compressed Backup

Create a compressed backup:

mysqldump -h 127.0.0.1 -P 3306 \
  -u your-user -p'your-password' \
  --single-transaction \
  --routines \
  --triggers \
  your-database \
  | gzip > backup-$(date +%F).sql.gz

Specific Tables Backup

Backup only specific tables:

mysqldump -h 127.0.0.1 -P 3306 \
  -u your-user -p'your-password' \
  your-database \
  table1 table2 \
  > backup-$(date +%F).sql

Step 5: Restore the Backup

With your backup file ready, you can now restore it to your MariaDB database. The restore process depends on your backup format and whether you're restoring to an existing database or creating a new one.

Restore Full Database

From SQL file:

mysql -h 127.0.0.1 -P 3306 \
  -u your-user -p'your-password' \
  < backup-YYYY-MM-DD.sql

Restore Compressed Backup

From gzipped file:

gunzip < backup-YYYY-MM-DD.sql.gz | \
mysql -h 127.0.0.1 -P 3306 \
  -u your-user -p'your-password'

Restore to Specific Database

mysql -h 127.0.0.1 -P 3306 \
  -u your-user -p'your-password' \
  target-database \
  < backup-YYYY-MM-DD.sql

Create Database During Restore

mysql -h 127.0.0.1 -P 3306 \
  -u your-user -p'your-password' \
  -e "CREATE DATABASE IF NOT EXISTS target_database;"

mysql -h 127.0.0.1 -P 3306 \
  -u your-user -p'your-password' \
  target_database \
  < backup-YYYY-MM-DD.sql

This method gives you secure, reliable access to your MariaDB database on Sliplane for backups and restores while keeping your infrastructure secure.