How to Install and Create MySQL Sandboxes with DBdeployer on an Ubuntu 18.04 VPS or Dedicated Server

Introduction

As a developer or a database administrator, you may find yourself working with different database versions. You may also need to deploy a database with minimal features for testing purposes quickly. Problems faced include having to deal with different environments which have different Service Level Agreements (SLA’s), data definition languages not supporting replication strategies, different schemas, etc. DBdeployer, as a successor of the MySQL sandbox, is a tool used to deploy multiple MySQL sandboxes on the same MySQL hosted by a MySQL hosting service. It has many significant advantages including the following:

Easy handling of environment specific data
Staged rollouts may be easily managed by using DBdeployer

Before You Start

A VPS or Dedicated server running Ubuntu 18.04 Server
A non-root user configured with sudo privileges.
Git version control software (Optional)

Steps

Update your system packages

$ sudo apt update && sudo apt upgrade

Download MySQL serverversion 8.0.12.

$ wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz

In order to work with MySQL server pack, you need to convert the downloaded file to a tar.gz format.

$ tar -xf mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz
$ tar -zcvf mysql-8.0.12-linux-glibc2.12-x86_64.tar.gz mysql-8.0.12-linux-glibc2.12-x86_64/

Download DBdeployer package

Download the current stable binary executable version of DBdeployer which is 1.12.3, extract its package and move it to your local binary directory in your Dedicated Server.

$ VERSION=1.12.3
$ OS=linux
$ origin=https://github.com/datacharmer/dbdeployer/releases/download/$VERSION
$ wget $origin/dbdeployer-$VERSION.$OS.tar.gz
$ tar -xzf dbdeployer-$VERSION.$OS.tar.gz
$ chmod +x dbdeployer-$VERSION.$OS
$ sudo mv dbdeployer-$VERSION.$OS /usr/local/bin/dbdeployer

To verify your installation, just type dbdeployer on your terminal

$ dbdeployer

dbdeployer makes MySQL server installation an easy task.
Runs single, multiple, and replicated sandboxes.

Usage:
  dbdeployer [command]

Available Commands:
  admin       sandbox management tasks
  defaults    tasks related to dbdeployer defaults
  delete      delete an installed sandbox
  deploy      deploy sandboxes
  global      Runs a given command in every sandbox
  help        Help about any command
  sandboxes   List installed sandboxes
  unpack      unpack a tarball into the binarydirectory
  usage       Shows usageof installed sandboxes
  versions    List available versions

Flags:
      --config string           configuration file (default "/home/paulodera/.dbdeployer/config.json")
  -h, --help                    help for dbdeployer
      --sandbox-binary string   Binary repository (default "/home/paulodera/opt/mysql")
      --sandbox-home string     Sandbox deployment directory (default "/home/paulodera/sandboxes")
      --version                 version for dbdeployer

Use"dbdeployer [command] --help"for more information about a command.

You now have to extract the MySQL server tarball you downloaded to the~/opt/mysqldirectory. You can use dbdeployer’s unpack command to ensure it is unpacked in the right directory.

$ mkdir ~/opt
$ mkdir opt/mysql
$ dbdeployer unpack ~/mysql-8.0.12-linux-glibc2.12-x86_64.tar.gz

Unpacking tarball mysql-8.0.12-linux-glibc2.12-x86_64.tar.gz to $HOME/opt/mysql/8.0.12
.........100.........200........288
Renaming directory /home/linuxuser/opt/mysql/mysql-8.0.12-linux-glibc2.12-x86_64 to /home/linuxuser/opt/mysql/8.0.12

Deploy Sandboxes

You can now start deploying MySQL sandboxes of different topologies. For easier deployments, a “cookbook” was developed which contains bash scripts for easier deployment, showing available sandboxes and deleting them too. All you need to do is run the particular script for the deployment topology desired or if you need to view the deployed sandboxes or delete them.

$ git clone https://github.com/datacharmer/dbdeployer.git

The cookbook scripts are contained in the dbdeployer/cookbook directory upon cloning the repository. You can create a single sandbox using the following script while adding an argument of the MySQL version installed on your PC

$ cd dbdeployer/cookbook

Special Note: Sometimes you may notice the sandbox will not start. You can solve this problem by installing the libaio-dev andnumactllibraries.

$ sudo apt install libaio-dev && sudo apt install numactl
$ ./single.sh 8.0.12

You can check the status of your newly deployed sandbox over at the sandboxes directory

$ cd ~/sandboxes/msb_8_0_12
$ ./status

Msb_8_0_12on

The above response shows the sandbox is active. To log into it, in your specific sandbox directory, use the following command.

$ ./use -u root

Welcome to the MySQL monitor.  Commands endwith ; or g.
Your MySQL connection id is 11
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type'c'toclear the currentinput statement.

mysql [localhost] {root} ((none)) >

Conclusion

You have successfully installed DBdeployer on your VPS. You can go ahead and create other sandboxes with different topologies such as multi-primary replication, all master replication etc.. You may also opt to use the command line to deploy the sandboxes instead of using the cookbook scripts. The good thing with DBdeployer is that it is fast and you do not need to make a lot of post configurations on your sandboxes before you can use them.