dbdeployer cookbook - usability by example

When I designed dbdeployer, I wanted to eliminate most of the issues that the old MySQL-Sandbox had:

• dependencies during installation
• mistaken tarballs
• clarity of syntax
• features (un)awareness.

Dependencies during installation did go away right from the start, as the dbdeployer executable is ready to be used without additional components. The only dependency is to have a host that can run MySQL. There is little dbdeployer can do about detecting whether or not your system can run MySQL. It depends on which version and flavor of MySQL you are running. It should not be a big deal as I assume that anyone in need of dbdeployer has already the necessary knowledge about MySQL prerequisites. This is not always the case, but the issue goes beyond dbdeployer’s scope.

Mistaken tarballs are a common problem for users who have never seen a binaries tarball. Here dbdeployer can help, up to a point, to guide the user. It recognizes most cases where you are trying to use a source tarball or the wrong operating system tarball. It still does not recognize when you try to run a sandbox for a Linux 64bits out of a 32bit tarball, but to be honest I haven’t tried to solve this problem yet. There are still cases where users are a great risk of picking the wrong tarball (Percona Server download page is a minefield and the one for MariaDB is not picnic either) but I feel that I have given dbdeployer users a big help on this count.

Clarity of syntax is probably the biggest issue with the previous tool. It’s a consequence of the tool being developed over several years, slowly adapting to changing circumstances. I made dbdeployer clearer from the beginning, when I decided to avoid piling up many actions as the consequence of the same command. In dbdeployer, you need to unpack the tarball explicitly before running a deployment, and this gives dbdeployer users the visibility on the architecture that eluded many MySQL-Sandbox users. The architecture of dbdeployer is such that adding new features, commands, and options is easy and fits within an easily detectable paradigm. Thus, the operations are easier to spot and use.

Features awareness is still a problem. There is a lengthy description of everything dbdeployer can do, but, as often happens with even the best tools, users don’t read manuals.

The biggest obstacle about reading manuals is that dbdeployer executable is installed without any need to take the README file along. If you need it, you should go online and read it, and given that dbdeployer is built to be used mostly without internet connection, there are cases when you want to know how to do something, but you can’t get the manual right away.

There is the tool integrated help (dbdeployer [command] -h), which gives you a lot of information, but this tells you how to do something that you know already exists, not what you can do in general.

To help on this count, I added a collection of samples (the cookbook) that were initially released in a directory of the GitHub project, but then the sample scripts suffered of the same invisibility that plagues the README file. There is one more problem: when you tried using the generic cookbook scripts (now removed) you had to use the same environment as I did when defining them, or they would fail.

The current solution is to include cookbook files right within the tool, using templates (same as for regular sandbox scripts) with the double advantage that the scripts are available anywhere the dbdeployer executable is, and the scripts are adapted to the environment, since dbdeployer knows how to search for available binaries and can suggest the best parameters for the scripts.

Introducing dbdeployer cookbook

The command dbdeployer cookbook (with aliases recipes or samples) has three subcommands:

• list displays a list of available samples.
• show displays the contents of a recipe.
• create (with alias make) will build the recipe script.

We should try the list first:

$ dbdeployer cookbook list
.----------------------------------.-------------------------------------.--------------------------------------------------------------------.--------.
|              recipe              |             script name             |                            description                             | needed |
|                                  |                                     |                                                                    | flavor |
+----------------------------------+-------------------------------------+--------------------------------------------------------------------+--------+
| all-masters                      | all-masters-deployment.sh           | Creation of an all-masters replication sandbox                     | mysql  |
| delete                           | delete-sandboxes.sh                 | Delete all deployed sandboxes                                      |        |
| fan-in                           | fan-in-deployment.sh                | Creation of a fan-in (many masters, one slave) replication sandbox | mysql  |
| group-multi                      | group-multi-primary-deployment.sh   | Creation of a multi-primary group replication sandbox              | mysql  |
| group-single                     | group-single-primary-deployment.sh  | Creation of a single-primary group replication sandbox             | mysql  |
| master-slave                     | master-slave-deployment.sh          | Creation of a master/slave replication sandbox                     |        |
| ndb                              | ndb-deployment.sh                   | Shows deployment with ndb                                          | ndb    |
| prerequisites                    | prerequisites.sh                    | Shows dbdeployer prerequisites and how to make them                |        |
| pxc                              | pxc-deployment.sh                   | Shows deployment with pxc                                          | pxc    |
| remote                           | remote.sh                           | Shows how to get a remote MySQL tarball                            |        |
| replication-restart              | repl-operations-restart.sh          | Show how to restart sandboxes with custom options                  |        |
| replication-operations           | repl-operations.sh                  | Show how to run operations in a replication sandbox                |        |
| replication_between_groups       | replication-between-groups.sh       | Shows how to run replication between two group replications        | mysql  |
| replication_between_master_slave | replication-between-master-slave.sh | Shows how to run replication between two master/slave replications |        |
| replication_between_ndb          | replication-between-ndb.sh          | Shows how to run replication between two NDB clusters              | ndb    |
| show                             | show-sandboxes.sh                   | Show deployed sandboxes                                            |        |
| single                           | single-deployment.sh                | Creation of a single sandbox                                       |        |
| single-reinstall                 | single-reinstall.sh                 | Re-installs a single sandbox                                       |        |
| tidb                             | tidb-deployment.sh                  | Shows deployment and some operations with TiDB                     | tidb   |
| upgrade                          | upgrade.sh                          | Shows a complete upgrade example from 5.5 to 8.0                   | mysql  |
'----------------------------------'-------------------------------------'--------------------------------------------------------------------'--------'

The recipe that seems to be the simplest one is single. We can try to see what is in there:

$ dbdeployer  cookbook show single
#!/bin/bash

[...]
# Generated by dbdeployer 1.26.0 using template single on Fri Mar 29 12:27:53 UTC 2019
cd $(dirname $0)
source cookbook_include.sh

version=$1
[ -z "$version" ] && version=NOTFOUND_mysql
check_version $version

if [ -n "$(dbdeployer sandboxes | grep 'single\s*'$version)" ]
then
    echo "single version $version is already installed"
else
    header "Deploying a single sandbox for version $version"
    run dbdeployer deploy single $version
fi

What looks odd is the line that says NOTFOUND. If we try creating that script and then running it, it won’t work, and rightfully so.

There is a recipe named prerequisites that could probably help us.

$ dbdeployer cookbook create prerequisites
recipes/prerequisites.sh created

So, now, we have a starting point. Let’s run it:

$ ./recipes/prerequisites.sh

# ****************************************************************************
# Creating Sandbox binary directory ($HOME/opt/mysql)
# ****************************************************************************

## HOW TO GET  binaries for dbdeployer
# FOR REGULAR MYSQL
# run the commands:

1. dbdeployer remote list
2. dbdeployer remote get mysql-5.7.25
3. dbdeployer unpack mysql-5.7.25.tar.xz

4. dbdeployer versions

# FOR MySQL forks, MySQL Cluster, PXC:

# 1. Get the binaries from the maker download pages
# 2. run the command
 dbdeployer unpack FlavorName-X.X.XX-OS.tar.gz  --prefix=FlavorName
3. dbdeployer versions

The first thing we see is that the sandbox binary directory was created, and then we see a series of steps to fill it in.

Let’s try:

$ dbdeployer remote list
Files available in https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata/available.json
5.5 -> [mysql-5.5.61 mysql-5.5.62]
5.6 -> [mysql-5.6.41 mysql-5.6.43]
5.7 -> [mysql-5.7.24 mysql-5.7.25]
8.0 -> [mysql-8.0.13 mysql-8.0.15]
4.1 -> [mysql-4.1.22]
5.0 -> [mysql-5.0.15 mysql-5.0.96]
5.1 -> [mysql-5.1.72]

$ dbdeployer remote get mysql-5.7.25
File /home/msandbox/mysql-5.7.25.tar.xz downloaded

$ dbdeployer unpack mysql-5.7.25.tar.xz
Unpacking tarball mysql-5.7.25.tar.xz to $HOME/opt/mysql/5.7.25
[...]
Renaming directory $HOME/opt/mysql/mysql-5.7.25 to $HOME/opt/mysql/5.7.25

$ dbdeployer versions
Basedir: /home/msandbox/opt/mysql
5.7.25

If we repeat the show command now, we get a different result:

$ dbdeployer  cookbook show single
#!/bin/bash
[...]
# Generated by dbdeployer 1.26.0 using template single on Fri Mar 29 12:37:26 UTC 2019
cd $(dirname $0)
source cookbook_include.sh

version=$1
[ -z "$version" ] && version=5.7.25
check_version $version

if [ -n "$(dbdeployer sandboxes | grep 'single\s*'$version)" ]
then
    echo "single version $version is already installed"
else
    header "Deploying a single sandbox for version $version"
    run dbdeployer deploy single $version
fi

There! instead of the NOTFOUND we saw before, it now shows the version that we just downloaded. If we repeat the same procedure (remote list, remote get, unpack) for MySQL 8.0.15, we would see 8.0.15 as the recommended version.

Now we can create the single recipe. Or even better, since we want to try several ones, we can create all of them.

$ dbdeployer  cookbook create all
recipes/replication-between-master-slave.sh created
recipes/single-reinstall.sh created
recipes/fan-in-deployment.sh created
recipes/group-multi-primary-deployment.sh created
recipes/repl-operations.sh created
recipes/tidb-deployment.sh created
recipes/remote.sh created
recipes/upgrade.sh created
recipes/ndb-deployment.sh created
recipes/cookbook_include.sh created
recipes/master-slave-deployment.sh created
recipes/prerequisites.sh created
recipes/replication-between-groups.sh created
recipes/replication-between-ndb.sh created
recipes/pxc-deployment.sh created
recipes/single-deployment.sh created
recipes/show-sandboxes.sh created
recipes/delete-sandboxes.sh created
recipes/all-masters-deployment.sh created
recipes/group-single-primary-deployment.sh created
recipes/repl-operations-restart.sh created

Now it’s time to try one:

msandbox@505969e46289:~$ ./recipes/single-deployment.sh

# ****************************************************************************
# Deploying a single sandbox for version 5.7.25
# ****************************************************************************
+ dbdeployer deploy single 5.7.25
Creating directory /home/msandbox/sandboxes
Database installed in $HOME/sandboxes/msb_5_7_25
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
0

Looking at the list, we see a single-reinstall recipe. If we run it, we will get a mini tutorial on how to use a single sandbox:

$ ./recipes/single-deployment.sh

# ****************************************************************************
# Deploying a single sandbox for version 5.7.25
# ****************************************************************************
+ dbdeployer deploy single 5.7.25
Creating directory $HOME/sandboxes
Database installed in $HOME/sandboxes/msb_5_7_25
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
0
msandbox@505969e46289:~$ ./recipes/show-sandboxes.sh
+ dbdeployer sandboxes --full-info
.------------.--------.---------.---------.--------.-------.--------.
|    name    |  type  | version |  ports  | flavor | nodes | locked |
+------------+--------+---------+---------+--------+-------+--------+
| msb_5_7_25 | single | 5.7.25  | [5725 ] | mysql  |     0 |        |
'------------'--------'---------'---------'--------'-------'--------'
0
msandbox@505969e46289:~$ ./recipes/single-reinstall.sh

# ****************************************************************************
# Deploying the same sandbox again, with different parameters
# We need to use --force, as we are overwriting an existing sandbox
# Incidentally, the new deployment will run a query before and after the grants
# ****************************************************************************
+ dbdeployer deploy single 5.7.25 '--pre-grants-sql=select host, user from mysql.user' '--post-grants-sql=select host, user from mysql.user' --force
Overwriting directory $HOME/sandboxes/msb_5_7_25
stop $HOME/sandboxes/msb_5_7_25
Database installed in $HOME/sandboxes/msb_5_7_25
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| 127.%     | msandbox      |
| 127.%     | msandbox_ro   |
| 127.%     | msandbox_rw   |
| 127.%     | rsandbox      |
| localhost | msandbox      |
| localhost | msandbox_ro   |
| localhost | msandbox_rw   |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+

# ****************************************************************************
# Deploying the same sandbox with a different directory.
# No --force is necessary, as dbdeployer will choose a different port
# ****************************************************************************
+ dbdeployer deploy single 5.7.25 --sandbox-directory=msb_5_7_25_new
Database installed in $HOME/sandboxes/msb_5_7_25_new
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
0

+ dbdeployer sandboxes --header
            name               type    version    ports
---------------------------- -------- --------- ---------
 msb_5_7_25               :   single   5.7.25    [5725 ]
 msb_5_7_25_new           :   single   5.7.25    [5726 ]
0

# ****************************************************************************
# Removing the second sandbox
# ****************************************************************************
+ dbdeployer delete msb_5_7_25_new
List of deployed sandboxes:
$HOME/sandboxes/msb_5_7_25_new
Running $HOME/sandboxes/msb_5_7_25_new/stop
stop $HOME/sandboxes/msb_5_7_25_new
Running rm -rf $HOME/sandboxes/msb_5_7_25_new
Directory $HOME/sandboxes/msb_5_7_25_new deleted
0

This script teaches us the basics of starting and restarting a sandbox, with useful twists as running an SQL command before granting privileges.

There is a similar tutorial for replication operations, but we’ll have a look at a slightly different one.

$ ./recipes/master-slave-deployment.sh
+ dbdeployer deploy replication 5.7.25 --concurrent
$HOME/sandboxes/rsandbox_5_7_25/initialize_slaves
initializing slave 1
initializing slave 2
Replication directory installed in $HOME/sandboxes/rsandbox_5_7_25
run 'dbdeployer usage multiple' for basic instructions'

$ ./recipes/repl-operations.sh

# ****************************************************************************
# Running a simple command with the master in the sandbox.
# Notice the usage of the '-e', as if we were using the 'mysql' client
# ****************************************************************************
+ $HOME/sandboxes/rsandbox_5_7_25/m -e 'SHOW MASTER STATUS'
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     4089 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

# ****************************************************************************
# Creating a table in the master
# ****************************************************************************
+ $HOME/sandboxes/rsandbox_5_7_25/m -e 'DROP TABLE IF EXISTS test.t1'
+ $HOME/sandboxes/rsandbox_5_7_25/m -e 'CREATE TABLE test.t1(id int not null primary key)'

# ****************************************************************************
# Inserting 3 lines into the new table
# ****************************************************************************
+ $HOME/sandboxes/rsandbox_5_7_25/m -e 'INSERT INTO test.t1 VALUES(1)'
+ $HOME/sandboxes/rsandbox_5_7_25/m -e 'INSERT INTO test.t1 VALUES(2)'
+ $HOME/sandboxes/rsandbox_5_7_25/m -e 'INSERT INTO test.t1 VALUES(3)'

# ****************************************************************************
# Getting the table contents from one slave
# ****************************************************************************
+ $HOME/sandboxes/rsandbox_5_7_25/s1 -e 'SELECT * FROM test.t1'
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

# ****************************************************************************
# Getting the table count from all nodes (NOTE: no '-e' is needed)
# $HOME/sandboxes/rsandbox_5_7_25/use_all 'SELECT COUNT(*) FROM test.t1'
# master
COUNT(*)
3
# server: 1
COUNT(*)
3
# server: 2
COUNT(*)
3

# ****************************************************************************
# Checking the status of all slaves
# ****************************************************************************
+ $HOME/sandboxes/rsandbox_5_7_25/check_slaves
master
port 19226 - server_id 100
             File: mysql-bin.000001
         Position: 5213
Executed_Gtid_Set:
slave1
port 19227 - server_id 200
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 5213
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 5213
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
slave2
port 19228 - server_id 300
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 5213
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 5213
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
0

# ****************************************************************************
# Running a multiple query in all slaves
# ****************************************************************************
+ $HOME/sandboxes/rsandbox_5_7_25/use_all_slaves 'STOP SLAVE; SET GLOBAL slave_parallel_workers=3; START SLAVE;show processlist '
# server: 1
Id User Host db Command Time State Info
11 msandbox localhost NULL Query 0 starting show processlist
12 system user  NULL Connect 0 Checking master version NULL
13 system user  NULL Connect 0 System lock NULL
14 system user  NULL Connect 0 Waiting for an event from Coordinator NULL
15 system user  NULL Connect 0 Waiting for an event from Coordinator NULL
16 system user  NULL Connect 0 Waiting for an event from Coordinator NULL
# server: 2
Id User Host db Command Time State Info
10 msandbox localhost NULL Query 0 starting show processlist
11 system user  NULL Connect 0 Checking master version NULL
12 system user  NULL Connect 0 System lock NULL
13 system user  NULL Connect 0 Waiting for an event from Coordinator NULL
14 system user  NULL Connect 0 Waiting for an event from Coordinator NULL
15 system user  NULL Connect 0 Waiting for an event from Coordinator NULL

By studying the commands mentioned in these samples, you will become proficient in dbdeployer components, allowing you to use it for advanced testing operations.