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

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.