Scheduled Backups for MariaDB Databases
MariaDB is a community-developed fork of the MySQL relational database management system and is used at Opalstack to provide MySQL-compatible database services.
This document shows how to create scheduled backups of a MariaDB database on Opalstack.
The examples use mydb
as the database name, mydbuser
as the database user name, and mydbpassword
as the database user password - be sure to replace those values with the names of your own database, user, and password.
- 1
Log in to a SSH session on your Opalstack server.
- 2
Run the following commands to create the files and directories used by your scheduled MariaDB backups:
export DBNAME=mydb mkdir -m 700 -p ~/.local/bin mkdir -m 700 -p ~/.local/etc/mariadb_backups mkdir -m 700 -p ~/backups/mariadb touch ~/.local/etc/mariadb_backups/$DBNAME.cnf chmod 600 ~/.local/etc/mariadb_backups/$DBNAME.cnf touch ~/.local/bin/backup_$DBNAME chmod 700 ~/.local/bin/backup_$DBNAME
- 3
Edit the database credentials file named
~/.local/etc/mariadb_backups/mydb.cnf
and add the following lines:[client] password='mydbpassword'
- 4
Edit the script
~/.local/bin/backup_mydb
and add the following lines that will run your backup:#!/bin/bash export DBNAME=mydb export DBUSER=mydbuser /bin/mysqldump --defaults-file=$HOME/.local/etc/mariadb_backups/$DBNAME.cnf \ -u $DBUSER $DBNAME \ > $HOME/backups/mariadb/$DBNAME-$(date +\%Y\%m\%d\%H\%M).sql \ 2>> $HOME/backups/mariadb/$DBNAME.log
- 5
Run
crontab -e
and create a crontab entry to schedule the backup. The following example will schedule the backup to run daily at 3:30 AM UTC (server time):30 3 * * * $HOME/.local/bin/backup_mydb
Please see the following pages for general help with cron jobs:
Once the above steps are complete, your backup will run at the schedule time to create a backup of your MariaDB database in ~/backups/mariadb
on the server.