Database Backups

Database Backups

BBS provides dedicated plugins for backing up MySQL, PostgreSQL, and MongoDB databases. These plugins automatically dump databases before each backup operation, ensuring consistent, point-in-time database backups.

Overview

Database backup plugins work by:

1. Running a database dump (mysqldump, pg_dump, or mongodump) before Borg creates the backup archive
2. Saving the dump files to a specified directory on the client machine
3. Including those dump files in the regular Borg backup
4. Allowing one-click restoration through the BBS web interface

MySQL Backups (mysql_dump)

Enabling MySQL Backups

1. Navigate to the client detail page
2. Click the Plugins tab
3. Toggle MySQL Backup to enabled

Screenshot: Plugins tab showing MySQL Backup plugin toggle switch

Creating a MySQL Plugin Configuration

1. On the Plugins tab, click Add Configuration under MySQL Backup
2. Fill in the configuration details:
   • Configuration Name: A descriptive name (e.g., "Production MySQL", "WordPress DB")
   • Database Host: Hostname or IP address (default: localhost)
   • Port: MySQL port (default: 3306)
   • Username: MySQL user with appropriate privileges
   • Password: MySQL password
   • Databases: Comma-separated database names, or "all" for all databases
   • Dump Directory: Local path where dumps will be stored (e.g., /var/backups/mysql)
3. Click Save Configuration

Screenshot: MySQL plugin configuration form with all fields filled in

Configuration Fields Explained

Field	Description	Example
Database Host	MySQL server hostname or IP	localhost, db.example.com
Port	MySQL server port	3306
Username	MySQL user with SELECT, LOCK TABLES, SHOW VIEW privileges	backup_user
Password	MySQL password (encrypted in BBS)	••••••••
Databases	Comma-separated list or "all"	wordpress,joomla or all
Dump Directory	Local directory for dump files	/var/backups/mysql

Attaching MySQL Config to a Backup Plan

1. Go to the client detail page → Backup Plans tab
2. Create a new backup plan or edit an existing one
3. In the Plugins section, select your MySQL configuration from the dropdown
4. Ensure the backup plan includes the dump directory in its directory list
5. Save the backup plan

Screenshot: Backup plan editor showing plugin configuration dropdown

How MySQL Backups Work

When a backup job runs with a MySQL plugin attached:

1. Agent receives the backup job with plugin instructions
2. Before running borg create, the agent executes mysqldump:

   mysqldump -h HOST -P PORT -u USERNAME -pPASSWORD --single-transaction \
     --routines --triggers DATABASE > /dump/dir/DATABASE.sql

3. One .sql file is created per database
4. Borg then backs up the dump directory along with all other specified directories
5. The SQL dump files are included in the Borg archive

Restoring MySQL Databases

1. Navigate to client detail → Restore tab
2. Select a backup archive that contains database dumps (indicated by a database icon)
3. Click Restore Database
4. Fill in the restore form:
   • Select Databases: Choose which databases to restore
   • Target MySQL Server: Hostname/IP of the MySQL server (can be different from original)
   • Port: Target MySQL port
   • Username: MySQL user with CREATE DATABASE and INSERT privileges
   • Password: Target MySQL password
   • Rename Databases (optional): Rename databases during restore (e.g., prod_db → restored_db)
5. Click Restore

Screenshot: MySQL restore dialog with database selection and target credentials

The restore process:

• Extracts the requested SQL dump files from the Borg archive
• Safety backup (Replace mode): Before overwriting, the current database is dumped to dump_dir/dbname_pre_restore.sql.gz so you can recover if the restore goes wrong
• Connects to the target MySQL server
• Creates databases (or drops/recreates if they exist)
• Imports the SQL dump: mysql -h HOST -P PORT -u USER -pPASS DATABASE < dump.sql

PostgreSQL Backups (pg_dump)

Enabling PostgreSQL Backups

1. Navigate to the client detail page
2. Click the Plugins tab
3. Toggle PostgreSQL Backup to enabled

Screenshot: Plugins tab showing PostgreSQL Backup plugin toggle switch

Creating a PostgreSQL Plugin Configuration

1. On the Plugins tab, click Add Configuration under PostgreSQL Backup
2. Fill in the configuration details:
   • Configuration Name: A descriptive name (e.g., "Production PostgreSQL")
   • Database Host: Hostname or IP address (default: localhost)
   • Port: PostgreSQL port (default: 5432)
   • Username: PostgreSQL user with appropriate privileges
   • Password: PostgreSQL password
   • Databases: Comma-separated database names, or "all" for all databases
   • Dump Directory: Local path where dumps will be stored (e.g., /var/backups/postgresql)
3. Click Save Configuration

Screenshot: PostgreSQL plugin configuration form

Configuration Fields Explained

Field	Description	Example
Database Host	PostgreSQL server hostname or IP	localhost, pg.example.com
Port	PostgreSQL server port	5432
Username	PostgreSQL user	postgres
Password	PostgreSQL password (encrypted in BBS)	••••••••
Databases	Comma-separated list or "all"	app_db,analytics or all
Dump Directory	Local directory for dump files	/var/backups/postgresql

Attaching PostgreSQL Config to a Backup Plan

Follow the same process as MySQL:

1. Create or edit a backup plan
2. Select your PostgreSQL configuration in the Plugins section
3. Ensure the dump directory is included in the backup directories
4. Save the plan

How PostgreSQL Backups Work

When a backup job runs with a PostgreSQL plugin attached:

1. Before running borg create, the agent executes pg_dump:

   PGPASSWORD=PASSWORD pg_dump -h HOST -p PORT -U USERNAME \
     -Fc -f /dump/dir/DATABASE.dump DATABASE

2. One .dump file is created per database (using custom format with -Fc)
3. Borg backs up the dump directory
4. The dump files are included in the archive

Restoring PostgreSQL Databases

1. Navigate to client detail → Restore tab
2. Select an archive with PostgreSQL dumps (database icon indicator)
3. Click Restore Database
4. Fill in the restore form:
   • Select Databases: Choose which databases to restore
   • Target PostgreSQL Server: Hostname/IP
   • Port: Target PostgreSQL port
   • Username: PostgreSQL user with CREATEDB privileges
   • Password: Target password
   • Rename Databases (optional): Rename during restore
5. Click Restore

Screenshot: PostgreSQL restore dialog

The restore process:

• Extracts the requested dump files from the archive
• Safety backup (Replace mode): Before overwriting, the current database is dumped to dump_dir/dbname_pre_restore.sql.gz so you can recover if the restore goes wrong
• Connects to the target PostgreSQL server
• Creates databases if needed
• Restores using pg_restore:

  PGPASSWORD=PASSWORD pg_restore -h HOST -p PORT -U USERNAME \
    -d DATABASE /path/to/dump.dump

MongoDB Backups (mongo_dump)

Enabling MongoDB Backups

1. Navigate to the client detail page
2. Click the Plugins tab
3. Toggle MongoDB Backup to enabled

Creating a MongoDB Plugin Configuration

1. On the Plugins tab, click Add Configuration under MongoDB Backup
2. Fill in the configuration details:
   • Configuration Name: A descriptive name (e.g., "Production MongoDB")
   • MongoDB Host: Hostname or IP (default: 127.0.0.1)
   • Port: MongoDB port (default: 27017)
   • Username: MongoDB user (leave empty if auth is not enabled)
   • Password: MongoDB password (leave empty if auth is not enabled)
   • Authentication Database: The auth database, usually admin
   • Databases: * for all databases, or comma-separated list
   • Dump Directory: Local path for dumps (default: /home/bbs/mongodump)
   • Compress: Enable gzip compression (recommended)
   • Exclude Databases: Databases to skip when using * (default: admin, config, local)
3. Click Save Configuration

Configuration Fields Explained

Field	Description	Example
MongoDB Host	Server hostname or IP	127.0.0.1, mongo.example.com
Port	MongoDB server port	27017
Username	MongoDB user (optional)	bbs_backup
Password	MongoDB password (encrypted in BBS)	••••••••
Authentication Database	Auth DB for credentials	admin
Databases	Comma-separated list or * for all	myapp,analytics or *
Dump Directory	Local directory for dump files	/home/bbs/mongodump
Compress	Use gzip compression	Enabled (recommended)
Exclude Databases	DBs to skip with *	admin,config,local
Extra mongodump Options	Additional CLI flags	--oplog (for replica sets)

Attaching MongoDB Config to a Backup Plan

Follow the same process as MySQL/PostgreSQL:

1. Create or edit a backup plan
2. Select your MongoDB configuration in the Plugins section
3. The dump directory is automatically included in backup directories
4. Save the plan

How MongoDB Backups Work

When a backup job runs with a MongoDB plugin attached:

1. Before running borg create, the agent lists databases via mongosh
2. For each database, the agent runs mongodump:

   mongodump --host=HOST --port=PORT --username=USER --password=PASS \
     --authenticationDatabase=admin --db=DATABASE --out=/dump/dir --gzip

3. One directory per database is created (e.g., /dump/dir/myapp/)
4. Borg backs up the dump directory
5. After backup completes, dump files are cleaned up (if enabled)

Restoring MongoDB Databases

1. Navigate to client detail > Restore tab
2. Select a backup archive that contains MongoDB dumps (database icon indicator)
3. Click Restore Database
4. Select the MongoDB connection config from the dropdown
5. Choose which databases to restore
6. Select restore mode:
   • Replace: Drops and restores the database (a safety backup is created automatically)
   • Rename: Restores to a new database name
7. Click Restore

The restore process:

• Extracts the requested database directories from the Borg archive
• Safety backup (Replace mode): Before dropping and restoring, the current database is dumped to dump_dir/_pre_restore/dbname/ so you can recover if the restore goes wrong
• Restores using mongorestore:

  mongorestore --host=HOST --port=PORT --username=USER --password=PASS \
    --authenticationDatabase=admin --db=DATABASE --drop --gzip /dump/dir/DATABASE

MongoDB User Setup

For backups (run in mongosh):

use admin
db.createUser({
  user: "bbs_backup",
  pwd: "strong_password",
  roles: [
    { role: "backup", db: "admin" },
    { role: "readAnyDatabase", db: "admin" }
  ]
})

For restores (additional roles):

db.grantRolesToUser("bbs_backup", [
  { role: "restore", db: "admin" },
  { role: "readWriteAnyDatabase", db: "admin" }
])

Authentication is optional. If your MongoDB instance does not require auth, leave the username and password fields empty.

Prerequisites

The following must be installed on the client machine:

• mongodump and mongorestore (part of MongoDB Database Tools)
• mongosh or mongo shell (for database discovery and connectivity testing)

Testing Plugin Configurations

Before running your first backup, test your database plugin configuration:

1. On the Plugins tab, find your saved configuration
2. Click the Test button next to the configuration
3. BBS queues a plugin_test job
4. Navigate to Queue to monitor the test
5. Review the test results:
   • Success: Plugin is configured correctly, dumps were created
   • Failure: Check the error log for connection issues, permission problems, or incorrect credentials

Screenshot: Plugin configuration card with Test button highlighted

Common test failure reasons:

• Connection refused: Database server not reachable, firewall blocking connection
• Access denied: Incorrect username/password, or user lacks necessary privileges
• Directory not writable: Dump directory doesn't exist or agent user lacks write permissions
• Database not found: Specified database doesn't exist on the server

Best Practices

For MySQL Backups

• Use a dedicated backup user with minimal privileges:

  CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password';
  GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';
  FLUSH PRIVILEGES;

• Use --single-transaction for InnoDB tables (automatically included)
• Ensure the dump directory has sufficient space (estimate database size)
• Consider backing up all databases to capture mysql.user and system tables

For PostgreSQL Backups

• Use a dedicated backup role:

  CREATE ROLE backup WITH LOGIN PASSWORD 'password';
  GRANT CONNECT ON DATABASE your_db TO backup;
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;

• The custom format (-Fc) provides compression and selective restore capabilities
• Ensure pg_dump and pg_restore are installed on the client machine

For MongoDB Backups

• Use a dedicated backup user with minimal roles (backup + readAnyDatabase)
• Use 127.0.0.1 instead of localhost to avoid IPv6 connection issues
• Enable gzip compression to reduce dump size
• For replica sets, consider adding --oplog in Extra Options for point-in-time consistency
• The default exclusions (admin, config, local) are correct for most setups
• Ensure MongoDB Database Tools are installed (mongodump, mongorestore, mongosh)

General Best Practices

• Named Configurations: Create reusable named configs for each database server. Share configs across multiple backup plans.
• Dump Directory: Use a dedicated directory outside web roots (e.g., /var/backups/mysql, not /var/www/dumps)
• Encryption: BBS encrypts all database passwords using the APP_KEY
• Test Regularly: Run test jobs after changing credentials or database structure
• Monitor Space: Database dumps can be large. Monitor disk space on both client and backup storage.
• Retention: Borg's deduplication handles dump file changes efficiently, but old dumps are only removed when archives are pruned

Troubleshooting

MySQL Issues

Problem	Solution
Access denied for user	Verify username/password, check MySQL user permissions
Can't connect to MySQL server	Check host/port, ensure MySQL is running, verify firewall rules
Unknown database	Verify database names are correct, check for typos
Dump directory not found	Create the directory on the client, ensure agent user has write access
Lock wait timeout	Reduce concurrent connections, consider --skip-lock-tables for non-InnoDB

PostgreSQL Issues

Problem	Solution
password authentication failed	Verify username/password, check pg_hba.conf settings
could not connect to server	Check host/port, ensure PostgreSQL is running, verify firewall
database does not exist	Verify database names, check spelling
permission denied	Grant SELECT on all tables in schema to backup user
pg_dump: command not found	Install postgresql-client package on the agent machine

MongoDB Issues

Problem	Solution
mongodump: command not found	Install MongoDB Database Tools on the client machine
mongosh: command not found	Install mongosh (or legacy mongo shell) for database discovery
Authentication failed	Verify username/password and authenticationDatabase (usually admin)
Connection refused	Check host/port, ensure MongoDB is running, verify firewall rules
No databases found	Check that the user has listDatabases privilege
IPv6 connection issues	Use 127.0.0.1 instead of localhost

Restore Issues

Problem	Solution
Restore fails with "database exists"	Drop the database first, or enable force/overwrite option
Access denied during restore	Target user needs CREATE DATABASE and INSERT privileges
Syntax errors during restore	Version mismatch between dump and target server
Restore incomplete	Check target disk space, review restore job logs

Related Documentation

• Plugins — Overview of all BBS plugin types
• Queue-and-Jobs — Monitoring backup and restore jobs
• Troubleshooting — General BBS troubleshooting guide