Dokku: Creating and linking a MariaDB database

After deploying our PHP application we will create a MariaDB database and link it to our application in Dokku v0.4.5 running on DigitalOcean.

MariaDB is a drop-in replacement of MySQL, meaning if you have an application that is using MySQL you can export the database and import it into MariaDB and everything will still work.

The instructions in this article will be for the MariaDB plugin but they are basically the same for the other database plugins.

Create the database

Start by installing the plugin:

dokku plugin:install https://github.com/dokku/dokku-mariadb.git mariadb

Now create a database:

dokku mariadb:create db_name

Link the database service to the application:

dokku mariadb:link db_name app_name

Connect to the database in your application

When linking a database to an application an environment variable is created named DATABASE_URL. This variable contains the necessary information to connect to the database.

The variable is in the following format:

service://user:pass@host:port/db_name

To connect to the database in PHP you can extract the information from this environment variable without having to store sensitive information in your repository:

$url = getenv('DATABASE_URL');
$components = parse_url($url);

if ($components) {
 $host = $components['host'];
 $username = $components['user'];
 $password = $components['pass'];
 $dbname = substr($components['path'], 1);
 $port = $components['port'];
}

Now you can use those variables to connect to the database.

Importing an existing database

If you need to import an existing database you can do so with the following command:

dokku mariadb:import db_name < file_name

You can use SFTP to transfer the file from your local machine.

For example I use FileZilla in Windows and in the Site Manager I just need to:

Select the SFTP protocol
Set the logon type to "Key file"
Set "root" as the user
Select the private key file

Backup a database

In a similar way you can create a dump of the database using the command:

dokku mariadb:export db_name > file_name

Create a scheduled task

You can create a simple bash script to manage the backups of your application.

This script was adapted from dommmel:

#! /bin/bash

# directory to save backups in, must be rwx
BASE_DIR="/var/backups/$1"
YMD=$(date "+%Y-%m-%d")
DIR="$BASE_DIR/$YMD"
mkdir -p $DIR
cd $DIR

# make database backup
dokku mariadb:export $1 | gzip -9 > "$DIR/$1.sql.gz"

# delete backup files older than 31 days
OLD=$(find $BASE_DIR -type d -mtime +31)
if [ -n "$OLD" ] ; then
        echo deleting old backup files: $OLD
        echo $OLD | xargs rm -rfv
fi

This will create a gzipped dump of the database to a folder with the database name and delete files older than 31 days. You need to pass the name of the database as an argument.

Make the script executable:

chmod +x /path/to/script

Run it to make sure it is working:

/path/to/script db_name

Create a cron task

Now you need to create a cron task:

crontab -e

This will open the user's crontab for editing. If it asks you to choose an editor, pick one.

Now add a line at the end to call your backup script:

0 0 * * 0 /path/to/script db_name

This will schedule the script to be executed every Sunday at midnight. Read more information on the cron format for how to customize the schedule.

Conclusion

Now pat yourself in the back. You should be all set.