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.