Sunday, June 30, 2019

From an empty box to MySQL custom replication in 3 minutes

Starting with version 1.32.0, dbdeployer has the ability of downloading a selection of MySQL tarballs from several sources.

This means that, when working in an empty box, you can populate it with database servers using

dbdeployer.

The “empty box” mentioned in the title is not really empty. It’s a Linux (or MacOS) host that is able to run a MySQL server. As such, it needs to have at least the prerequisites to run MySQL server (such as the libnuma and libaio packages), and a bash shell to run the scripts created by dbdeployer.

To try the thrill of an empty box that quickly becomes a working environment, we can use a docker image datacharmer/mysql-sb-base that I have created for this purpose.

$ docker pull datacharmer/mysql-sb-base
Using default tag: latest
latest: Pulling from datacharmer/mysql-sb-base
6b98dfc16071: Pull complete
4001a1209541: Pull complete
6319fc68c576: Pull complete
b24603670dc3: Pull complete
97f170c87c6f: Pull complete
b78c78fcfc94: Pull complete
379084573ce7: Pull complete
0afd193b699a: Pull complete
dfb4eecd399a: Pull complete
Digest: sha256:492c38b8662d393436141de5b3a9ad5b3994a7b095610b43896033fd740523ef
Status: Downloaded newer image for datacharmer/mysql-sb-base:latest

We can start a container from this image, and we won’t need anything else from the host computer.

$ docker run -ti --hostname dbtest datacharmer/mysql-sb-base bash
msandbox@dbtest:~$

The container runs as a regular user. Given that dbdeployer is designed specifically to run without root access (although it can run as root), this is the perfect scenario.

dbdeployer is already installed, but mysql is not.

msandbox@dbtest:~$ dbdeployer --version
dbdeployer version 1.34.0

msandbox@dbtest:~$ mysql
bash: mysql: command not found

Thus, we start getting our software from the locations that dbdeployer knows.

$ dbdeployer downloads list
Available tarballs
                          name                             OS     version   flavor     size   minimal
-------------------------------------------------------- ------- --------- -------- -------- ---------
 tidb-master-linux-amd64.tar.gz                           Linux     3.0.0   tidb      26 MB
 mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz               Linux    8.0.16   mysql    461 MB
 mysql-8.0.16-linux-x86_64-minimal.tar.xz                 Linux    8.0.16   mysql     44 MB   Y
 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz               Linux    5.7.26   mysql    645 MB
 mysql-5.6.44-linux-glibc2.12-x86_64.tar.gz               Linux    5.6.44   mysql    329 MB
 mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz               Linux    5.5.62   mysql    199 MB
 mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz               Linux    8.0.15   mysql    376 MB
 mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz               Linux    8.0.13   mysql    394 MB
 mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz               Linux    5.7.25   mysql    645 MB
 mysql-5.6.43-linux-glibc2.12-x86_64.tar.gz               Linux    5.6.43   mysql    329 MB
 mysql-5.5.61-linux-glibc2.12-x86_64.tar.gz               Linux    5.5.61   mysql    199 MB
 mysql-5.1.73-linux-x86_64-glibc23.tar.gz                 Linux    5.1.73   mysql    134 MB
 mysql-5.0.96.tar.xz                                      Linux    5.0.96   mysql    5.5 MB   Y
 mysql-5.1.72.tar.xz                                      Linux    5.1.72   mysql     10 MB   Y
 mysql-5.5.61.tar.xz                                      Linux    5.5.61   mysql    6.6 MB   Y
 mysql-5.5.62.tar.xz                                      Linux    5.5.62   mysql    6.6 MB   Y
 mysql-5.6.43.tar.xz                                      Linux    5.6.43   mysql    9.0 MB   Y
 mysql-5.6.44.tar.xz                                      Linux    5.6.44   mysql    9.1 MB   Y
 mysql-5.7.25.tar.xz                                      Linux    5.7.25   mysql     23 MB   Y
 mysql-5.7.26.tar.xz                                      Linux    5.7.26   mysql     23 MB   Y
 mysql-5.0.96-linux-x86_64-glibc23.tar.gz                 Linux    5.0.96   mysql    127 MB
 mysql-4.1.22.tar.xz                                      Linux    4.1.22   mysql    4.6 MB   Y
 mysql-cluster-gpl-7.6.10-linux-glibc2.12-x86_64.tar.gz   Linux    7.6.10   ndb      916 MB
 mysql-cluster-8.0.16-dmr-linux-glibc2.12-x86_64.tar.gz   Linux    8.0.16   ndb      1.1 GB

The above command shows all the tarballs that are available for the current operating system. You see that in addition to vanilla MySQL, there are also NDB and TiDB packages.

We start by getting the latest MySQL version using the command get-unpack that is available since version 1.33.0. This command downloads the tarball, compares the checksum, and unpacks it into the expected place.

$ dbdeployer downloads get-unpack mysql-8.0.16-linux-x86_64-minimal.tar.xz
Downloading mysql-8.0.16-linux-x86_64-minimal.tar.xz
....  44 MB
File /home/msandbox/mysql-8.0.16-linux-x86_64-minimal.tar.xz downloaded
Checksum matches
Unpacking tarball mysql-8.0.16-linux-x86_64-minimal.tar.xz to $HOME/opt/mysql/8.0.16
.........100.........200.219
Renaming directory /home/msandbox/opt/mysql/mysql-8.0.16-linux-x86_64-minimal to /home/msandbox/opt/mysql/8.0.16

The same operation for 5.7 gives us the second version available.

+ dbdeployer downloads get-unpack mysql-5.7.26.tar.xz
Downloading mysql-5.7.26.tar.xz
..  23 MB
File /home/msandbox/mysql-5.7.26.tar.xz downloaded
Checksum matches
Unpacking tarball mysql-5.7.26.tar.xz to $HOME/opt/mysql/5.7.26
.........99
Renaming directory /home/msandbox/opt/mysql/mysql-5.7.26 to /home/msandbox/opt/mysql/5.7.26

Now there are two versions that can be used for operations.

$ dbdeployer  versions
Basedir: /home/msandbox/opt/mysql
5.7.26  8.0.16

And we are going to deploy one sandbox from each version, because we want to put them in replication.

$ dbdeployer deploy single 5.7.26 --master
Creating directory /home/msandbox/sandboxes
Database installed in $HOME/sandboxes/msb_5_7_26
run 'dbdeployer usage single' for basic instructions'
. sandbox server started

$ dbdeployer deploy single 8.0.16 --master
Database installed in $HOME/sandboxes/msb_8_0_16
run 'dbdeployer usage single' for basic instructions'
.. sandbox server started


$ dbdeployer sandboxes --full-info
.------------.--------.---------.---------------.--------.-------.--------.
|    name    |  type  | version |     ports     | flavor | nodes | locked |
+------------+--------+---------+---------------+--------+-------+--------+
| msb_5_7_26 | single | 5.7.26  | [5726 ]       | mysql  |     0 |        |
| msb_8_0_16 | single | 8.0.16  | [8016 18016 ] | mysql  |     0 |        |
'------------'--------'---------'---------------'--------'-------'--------'

This are our active assets. The sandboxes are independent, but each sandbox has the ability of becoming the receiver of replication. In this case we want to replicate from version 5.7 to version 8.0, as it is always recommended to replicate from earlier to later version.

$ ~/sandboxes/msb_8_0_16/replicate_from msb_5_7_26
Connecting to /home/msandbox/sandboxes/msb_5_7_26
--------------
CHANGE MASTER TO master_host="127.0.0.1",
master_port=5726,
master_user="rsandbox",
master_password="rsandbox"
, master_log_file="mysql-bin.000001", master_log_pos=4089
--------------

--------------
start slave
--------------

              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4089
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 4089
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
                


Replication is active. We can now quickly check that it is working:

$ ~/sandboxes/msb_5_7_26/use -e 'create table test.t1(id int not null primary key, msg1 varchar(50), msg2 varchar(50)) default charset=utf8mb4'

$ ~/home/msandbox/sandboxes/msb_5_7_26/use -e 'insert into test.t1 values (1, @@version, @@server_uuid)'


We create a table in 5.7, taking care of using a character set that agrees with 8.0 defaults (we could also use utf8, but this is the one that presents less potential problems. We fill the table with server specific information (its version and UUID).


Now we can check that the slave is working


$ ~/sandboxes/msb_8_0_16/use -e 'SHOW SLAVE STATUS\G' | grep 'Running\|Master_\|Log_'
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 5726
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4636
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 868
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 4636
              Relay_Log_Space: 1072
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
Master_SSL_Verify_Server_Cert: No
             Master_Server_Id: 5726
                  Master_UUID: 00005726-0000-0000-0000-000000005726
             Master_Info_File: mysql.slave_master_info
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Master_TLS_Version:
       Master_public_key_path:

And finally we retrieve from the 8.0 slave the data that was created in 5.7

$ ~/sandboxes/msb_8_0_16/use -e 'show tables from test'
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+

$  ~/sandboxes/msb_8_0_16/use -e 'select * from test.t1'
+----+------------+--------------------------------------+
| id | msg1       | msg2                                 |
+----+------------+--------------------------------------+
|  1 | 5.7.26-log | 00005726-0000-0000-0000-000000005726 |
+----+------------+--------------------------------------+

QED.

Tuesday, April 02, 2019

dbdeployer cookbook - Advanced techniques

In the previous post about the dbdeployer recipes we saw the basics of using the cookbook command and the simpler tutorials that the recipes offer.

Here we will see some more advanced techniques, and more demanding examples.


We saw that the recipe for a single deployment would get a NOTFOUND when no versions were available, or the highest MySQL version when one was found.

$ dbdeployer cookbook  show single | grep version=
version=$1
[ -z "$version" ] && version=8.0.16

But what if we want the latest Percona Server or MariaDB for this recipe? One solution would be to run the script with an argument, but we can ask dbdeployer to find the most recent version for a given flavor and use it in our recipe:

$ dbdeployer cookbook  show single --flavor=percona | grep version=
version=$1
[ -z "$version" ] && version=ps8.0.15

$ dbdeployer cookbook  show single --flavor=pxc | grep version=
version=$1
[ -z "$version" ] && version=pxc5.7.25

$ dbdeployer cookbook  show single --flavor=mariadb | grep version=
version=$1
[ -z "$version" ] && version=ma10.4.3

This works for all the recipes that don’t require a given flavor. When one is indicated (see dbdeployer cookbook list) you can override it using --flavor, but do that at your own risk. Running the ndb recipe using pxc flavor won’t produce anything usable.


Replication between sandboxes

When I proposed dbdeployer support for NDB, the immediate reaction was that this was good to test cluster-to-cluster replication. Although I did plenty of such topologies in one of my previous jobs, I had limited experience replicating between single or composite sandboxes. Thus, I started thinking about how to do it. In the old MySQL-Sandbox, I had an option --slaveof that allowed a single sandbox to replicate from an existing one. I did not implement the same thing in dbdeployer, because that solution looked limited, and only useful in a few scenarios.

I wanted something more dynamic, and initially I thought of creating a grandiose scheme, involving custom templates and user-defined fillers. While I may end up doing that some day, I quickly realized that it was overkill for this purpose, and that the sandboxes had already all the information needed to replicate from and to every other sandbox. I just had to expose the data in such a way that it can be used to plug one sandbox to the other.

Now every sandbox has a script named replicate_from, and a companion script called metadata. Using a combination of the two (in fact, replicate_from on the would-be replica calls metadata from the donor) we can quickly define the replication command needed for most situations.


Replication between single sandboxes

Before we tackle the most complex one, let’s demonstrate that the system works with a simple case.

There is a recipe named replication_between_single that creates a file named, aptly, ./recipes/replication-between-single.sh.

If you run it, you will see something similar to the following:

$ ./recipes/replication-between-single.sh  5.7.25
+ dbdeployer deploy single 5.7.25 --master --gtid --sandbox-directory=msb_5_7_25_1 --port-as-server-id
Database installed in $HOME/sandboxes/msb_5_7_25_1
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
0
+ dbdeployer deploy single 5.7.25 --master --gtid --sandbox-directory=msb_5_7_25_2 --port-as-server-id
Database installed in $HOME/sandboxes/msb_5_7_25_2
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
0
+ dbdeployer sandboxes --full-info
.--------------.--------.---------.---------------.--------.-------.--------.
|     name     |  type  | version |     ports     | flavor | nodes | locked |
+--------------+--------+---------+---------------+--------+-------+--------+
| msb_5_7_25_1 | single | 5.7.25  | [5725 ]       | mysql  |     0 |        |
| msb_5_7_25_2 | single | 5.7.25  | [5726 ]       | mysql  |     0 |        |
'--------------'--------'---------'---------------'--------'-------'--------'
0
+ $HOME/sandboxes/msb_5_7_25_1/replicate_from msb_5_7_25_2
Connecting to $HOME/sandboxes/msb_5_7_25_2
--------------
CHANGE MASTER TO master_host="127.0.0.1",
master_port=5726,
master_user="rsandbox",
master_password="rsandbox"
, master_log_file="mysql-bin.000001", master_log_pos=4089
--------------

--------------
start slave
--------------

              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4089
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 4089
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 00005725-0000-0000-0000-000000005725:1-16
                Auto_Position: 0
0
# Inserting data in msb_5_7_25_2
+ $HOME/sandboxes/msb_5_7_25_2/use -e 'create table if not exists test.t1 (id int not null primary key, server_id int )'
+ $HOME/sandboxes/msb_5_7_25_2/use -e 'insert into test.t1 values (1, @@server_id)'
# Retrieving data from msb_5_7_25_1
+ $HOME/sandboxes/msb_5_7_25_1/use -e 'select *, @@port from test.t1'
+----+-----------+--------+
| id | server_id | @@port |
+----+-----------+--------+
|  1 |      5726 |   5725 |
+----+-----------+--------+

The script deploys two sandboxes of the chosen version, using different directory names (dbdeployer takes care of choosing a free port) and then starts replication between the two using $SANDBOX1/replicate_from $SANDBOX2. Then a quick test shows that the data created in a sandbox can be retrieved in the other.


Replication between group replication clusters

The method used to replicate between two group replications is similar to the one seen for single sandboxes. The script replicate_from on the group top directory delegates the replication task to its first node, which points to the second group.

$ ./recipes/replication-between-groups.sh  5.7.25
+ dbdeployer deploy replication 5.7.25 --topology=group --concurrent --port-as-server-id --sandbox-directory=group_5_7_25_1
[...]
+ dbdeployer deploy replication 5.7.25 --topology=group --concurrent --port-as-server-id --sandbox-directory=group_5_7_25_2
[...]
+ dbdeployer sandboxes --full-info
.----------------.---------------------.---------.----------------------------------------.--------.-------.--------.
|      name      |        type         | version |                 ports                  | flavor | nodes | locked |
+----------------+---------------------+---------+----------------------------------------+--------+-------+--------+
| group_5_7_25_1 | group-multi-primary | 5.7.25  | [20226 20351 20227 20352 20228 20353 ] | mysql  |     3 |        |
| group_5_7_25_2 | group-multi-primary | 5.7.25  | [20229 20354 20230 20355 20231 20356 ] | mysql  |     3 |        |
'----------------'---------------------'---------'----------------------------------------'--------'-------'--------'
0
+ $HOME/sandboxes/group_5_7_25_1/replicate_from group_5_7_25_2
Connecting to $HOME/sandboxes/group_5_7_25_2/node1
--------------
CHANGE MASTER TO master_host="127.0.0.1",
master_port=20229,
master_user="rsandbox",
master_password="rsandbox"
, master_log_file="mysql-bin.000001", master_log_pos=1082
--------------

--------------
start slave
--------------

              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1082
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          Exec_Master_Log_Pos: 1082
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 00020225-bbbb-cccc-dddd-eeeeeeeeeeee:1-3
                Auto_Position: 0
0
# Inserting data in group_5_7_25_2 node1
+ $HOME/sandboxes/group_5_7_25_2/n1 -e 'create table if not exists test.t1 (id int not null primary key, server_id int )'
+ $HOME/sandboxes/group_5_7_25_2/n1 -e 'insert into test.t1 values (1, @@server_id)'
# Retrieving data from one of group_5_7_25_1 nodes
# At this point, the data was replicated twice
+ $HOME/sandboxes/group_5_7_25_1/n2 -e 'select *, @@port from test.t1'
+----+-----------+--------+
| id | server_id | @@port |
+----+-----------+--------+
|  1 |     20229 |  20227 |
+----+-----------+--------+

The interesting thing about this recipe is that the sandboxes are created using the option --port-as-server-id. While it was used also in the replication between single sandboxes as an excess of caution, in this recipe, and in all the recipes involving compound sandboxes, it is a necessity, as the replication would fail if primary and replica servers have the same server_id.

All the work is done by the replicate_from script, which knows how to check whether the target is a single sandbox or a composite one, and where to find the primary server.

Using a similar method, we can run more recipes on the same tune.


Replication between different things

I won’t reproduce the output of all recipes here. I will just mention what every recipe needs to prepare to ensure a positive outcome.

  • Replication between NDB clusters. Nothing special here, except making sure to use a MySQL Cluster tarball. If you don’t dbdeployer will detect it and refuse the installation. For the rest, it’s like replication between groups.
  • Replication between master/slave. This is a bit trickier, because the replication data comes to a master, and if we want to propagate to its slaves we need to activate log-slave-update. The recipe shows how to do it.
  • Replication between group and master/slave. In addition to the trick mentioned in the previous recipe, we need to make sure that the master/slave deployment is using GTID.
  • Replication between master/slave and group. See the previous one.
  • Replication between group and single (and vice versa). We just need to make sure the single sandbox has GTID enabled.

Replication between different versions

This is a simple recipe that comes from a feature request. All you need to do is make sure that the version on the master is lower than the one on the slaves. The recipe script replication-multi-versions.sh, looks for tarballs of 5.6, 5.7, and 8.0, but you can start it using three versions that you’d like. For example:

./recipes/replication-multi-versions.sh 5.7.23 5.7.24 5.7.25

The first version will be used as the master.


Circular replication

I didn’t want to do this, as I consider ring replication to be weak and difficult to handle. I stated that much in the feature request and in the list of dbdeployer features. But then I saw that with the latest enhancements it was so easy, that I had to at least make a recipe for it. And then you have it. recipes/circular-replication.sh does what it promises, but the burden of maintenance is still on the user’s shoulders. I suggest looking at it, and then forgetting it.


Upgrade from MySQL 5.5 to 8.0 (through 5.6 and 5.7)

This is one of the most advanced recipes. To enjoy it, you need to have expanded tarballs from 5.5, 5.6, 5.7, and 8.0.

Provided that you do, running this script will do the following:

  1. deploy MySQL 5.5
  2. Create a table upgrade_log and insert some data.
  3. deploy MySQL 5.6
  4. run mysql_upgrade (through dbdeployer)
  5. Add data to the log table
  6. deploy MySQL 5.7
  7. run mysql_upgrade again
  8. add data to the log table
  9. deploy MySQL 8.0
  10. run mysql_upgrade for the last time
  11. Show the data from the table

Here’s a full transcript of the operation. It’s interesting to see how the upgrade procedure has changed from older versions to current ones.


$ ./recipes/upgrade.sh

# ****************************************************************************
# Upgrading from 5.5.53 to 5.6.41
# ****************************************************************************
+ dbdeployer deploy single 5.5.53 --master
Database installed in $HOME/sandboxes/msb_5_5_53
run 'dbdeployer usage single' for basic instructions'
.. sandbox server started
0
+ dbdeployer deploy single 5.6.41 --master
Database installed in $HOME/sandboxes/msb_5_6_41
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
0
+ $HOME/sandboxes/msb_5_5_53/use -e 'CREATE TABLE IF NOT EXISTS test.upgrade_log(id int not null auto_increment primary key, server_id int, vers varchar(50), urole varchar(20), ts timestamp)'
+ $HOME/sandboxes/msb_5_5_53/use -e 'INSERT INTO test.upgrade_log (server_id, vers, urole) VALUES (@@server_id, @@version, '\''original'\'')'
+ dbdeployer admin upgrade msb_5_5_53 msb_5_6_41
stop $HOME/sandboxes/msb_5_5_53
stop $HOME/sandboxes/msb_5_6_41
Data directory msb_5_5_53/data moved to msb_5_6_41/data
. sandbox server started
Looking for 'mysql' as: $HOME/opt/mysql/5.6.41/bin/mysql
Looking for 'mysqlcheck' as: $HOME/opt/mysql/5.6.41/bin/mysqlcheck
Running 'mysqlcheck' with connection arguments: '--port=5641' '--socket=/var/folders/rz/cn7hvgzd1dl5y23l378dsf_c0000gn/T/mysql_sandbox5641.sock'
Running 'mysqlcheck' with connection arguments: '--port=5641' '--socket=/var/folders/rz/cn7hvgzd1dl5y23l378dsf_c0000gn/T/mysql_sandbox5641.sock'
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Running 'mysqlcheck' with connection arguments: '--port=5641' '--socket=/var/folders/rz/cn7hvgzd1dl5y23l378dsf_c0000gn/T/mysql_sandbox5641.sock'
Running 'mysqlcheck' with connection arguments: '--port=5641' '--socket=/var/folders/rz/cn7hvgzd1dl5y23l378dsf_c0000gn/T/mysql_sandbox5641.sock'
test.upgrade_log                                   OK
OK

The data directory from msb_5_6_41/data is preserved in msb_5_6_41/data-msb_5_6_41
The data directory from msb_5_5_53/data is now used in msb_5_6_41/data
msb_5_5_53 is not operational and can be deleted
+ dbdeployer delete msb_5_5_53
List of deployed sandboxes:
$HOME/sandboxes/msb_5_5_53
Running $HOME/sandboxes/msb_5_5_53/stop
Running rm -rf $HOME/sandboxes/msb_5_5_53
Directory $HOME/sandboxes/msb_5_5_53 deleted
+ $HOME/sandboxes/msb_5_6_41/use -e 'INSERT INTO test.upgrade_log (server_id, vers, urole) VALUES (@@server_id, @@version, '\''upgraded'\'')'
+ $HOME/sandboxes/msb_5_6_41/use -e 'SELECT * FROM test.upgrade_log'
+----+-----------+------------+----------+---------------------+
| id | server_id | vers       | urole    | ts                  |
+----+-----------+------------+----------+---------------------+
|  1 |      5553 | 5.5.53-log | original | 2019-04-01 20:27:38 |
|  2 |      5641 | 5.6.41-log | upgraded | 2019-04-01 20:27:46 |
+----+-----------+------------+----------+---------------------+

# ****************************************************************************
# The upgraded database is now upgrading from 5.6.41 to 5.7.25
# ****************************************************************************
+ dbdeployer deploy single 5.7.25 --master
Database installed in $HOME/sandboxes/msb_5_7_25
run 'dbdeployer usage single' for basic instructions'
. sandbox server started
0
+ $HOME/sandboxes/msb_5_6_41/use -e 'CREATE TABLE IF NOT EXISTS test.upgrade_log(id int not null auto_increment primary key, server_id int, vers varchar(50), urole varchar(20), ts timestamp)'
+ $HOME/sandboxes/msb_5_6_41/use -e 'INSERT INTO test.upgrade_log (server_id, vers, urole) VALUES (@@server_id, @@version, '\''original'\'')'
+ dbdeployer admin upgrade msb_5_6_41 msb_5_7_25
stop $HOME/sandboxes/msb_5_6_41
stop $HOME/sandboxes/msb_5_7_25
Data directory msb_5_6_41/data moved to msb_5_7_25/data
.. sandbox server started
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Upgrading the sys schema.
Checking databases.
sys.sys_config                                     OK
test.upgrade_log
error    : Table rebuild required. Please do "ALTER TABLE `upgrade_log` FORCE" or dump/reload to fix it!

Repairing tables
`test`.`upgrade_log`
Running  : ALTER TABLE `test`.`upgrade_log` FORCE
status   : OK
Upgrade process completed successfully.
Checking if update is needed.

The data directory from msb_5_7_25/data is preserved in msb_5_7_25/data-msb_5_7_25
The data directory from msb_5_6_41/data is now used in msb_5_7_25/data
msb_5_6_41 is not operational and can be deleted
+ dbdeployer delete msb_5_6_41
List of deployed sandboxes:
$HOME/sandboxes/msb_5_6_41
Running $HOME/sandboxes/msb_5_6_41/stop
Running rm -rf $HOME/sandboxes/msb_5_6_41
Directory $HOME/sandboxes/msb_5_6_41 deleted
+ $HOME/sandboxes/msb_5_7_25/use -e 'INSERT INTO test.upgrade_log (server_id, vers, urole) VALUES (@@server_id, @@version, '\''upgraded'\'')'
+ $HOME/sandboxes/msb_5_7_25/use -e 'SELECT * FROM test.upgrade_log'
+----+-----------+------------+----------+---------------------+
| id | server_id | vers       | urole    | ts                  |
+----+-----------+------------+----------+---------------------+
|  1 |      5553 | 5.5.53-log | original | 2019-04-01 20:27:38 |
|  2 |      5641 | 5.6.41-log | upgraded | 2019-04-01 20:27:46 |
|  3 |      5641 | 5.6.41-log | original | 2019-04-01 20:27:51 |
|  4 |      5725 | 5.7.25-log | upgraded | 2019-04-01 20:28:01 |
+----+-----------+------------+----------+---------------------+

# ****************************************************************************
# The further upgraded database is now upgrading from 5.7.25 to 8.0.15
# ****************************************************************************
+ dbdeployer deploy single 8.0.15 --master
Database installed in $HOME/sandboxes/msb_8_0_15
run 'dbdeployer usage single' for basic instructions'
.. sandbox server started
0
+ $HOME/sandboxes/msb_5_7_25/use -e 'CREATE TABLE IF NOT EXISTS test.upgrade_log(id int not null auto_increment primary key, server_id int, vers varchar(50), urole varchar(20), ts timestamp)'
+ $HOME/sandboxes/msb_5_7_25/use -e 'INSERT INTO test.upgrade_log (server_id, vers, urole) VALUES (@@server_id, @@version, '\''original'\'')'
+ dbdeployer admin upgrade msb_5_7_25 msb_8_0_15
stop $HOME/sandboxes/msb_5_7_25
Attempting normal termination --- kill -15 10357
stop $HOME/sandboxes/msb_8_0_15
Data directory msb_5_7_25/data moved to msb_8_0_15/data
... sandbox server started
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.columns_priv                                 OK
mysql.component                                    OK
mysql.db                                           OK
mysql.default_roles                                OK
mysql.engine_cost                                  OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.global_grants                                OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.password_history                             OK
mysql.plugin                                       OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.role_edges                                   OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
sys.sys_config                                     OK
test.upgrade_log                                   OK
Upgrade process completed successfully.
Checking if update is needed.

The data directory from msb_8_0_15/data is preserved in msb_8_0_15/data-msb_8_0_15
The data directory from msb_5_7_25/data is now used in msb_8_0_15/data
msb_5_7_25 is not operational and can be deleted
+ dbdeployer delete msb_5_7_25
List of deployed sandboxes:
$HOME/sandboxes/msb_5_7_25
Running $HOME/sandboxes/msb_5_7_25/stop
Running rm -rf $HOME/sandboxes/msb_5_7_25
Directory $HOME/sandboxes/msb_5_7_25 deleted
+ $HOME/sandboxes/msb_8_0_15/use -e 'INSERT INTO test.upgrade_log (server_id, vers, urole) VALUES (@@server_id, @@version, '\''upgraded'\'')'
+ $HOME/sandboxes/msb_8_0_15/use -e 'SELECT * FROM test.upgrade_log'
+----+-----------+------------+----------+---------------------+
| id | server_id | vers       | urole    | ts                  |
+----+-----------+------------+----------+---------------------+
|  1 |      5553 | 5.5.53-log | original | 2019-04-01 20:27:38 |
|  2 |      5641 | 5.6.41-log | upgraded | 2019-04-01 20:27:46 |
|  3 |      5641 | 5.6.41-log | original | 2019-04-01 20:27:51 |
|  4 |      5725 | 5.7.25-log | upgraded | 2019-04-01 20:28:01 |
|  5 |      5725 | 5.7.25-log | original | 2019-04-01 20:28:07 |
|  6 |      8015 | 8.0.15     | upgraded | 2019-04-01 20:28:20 |
+----+-----------+------------+----------+---------------------+

What else can we do?

The replication recipes seen so far use the same principles. The method used in these recipes doesn’t work for all-masters and fan-in replication, because mixing named channels and nameless ones is not allowed. Also, there are things that don’t respond to replication commands at all, like TiDB. But it should be easy to enhance the current scripts (or to add some more specialized ones) that will include also these exceptions. Given the recent wave of collaboration, I expect it will happen relatively soon.

Friday, March 29, 2019

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.

Tuesday, March 19, 2019

dbdeployer community: Part 3 - MySQL Cluster (NDB)

I remember wanting to create MySQL Cluster sandboxes several years ago. By then, however, MySQL-Sandbox technology was not flexible enough to allow an easy inclusion, and the cluster software itself was not as easy to install as it is today. Thus, I kept postponing the implementation, until I started working with dbdeployer.

I included the skeleton of support for MySQL Cluster since the beginning (by keeping a range of ports dedicated for this technology, but I didn’t do anything until June 2018, when I made public my intentions to add support for NDB in dbdeployer with issue #20 (Add support for MySQL Cluster)). The issue had just a bare idea, but I needed help from someone, as my expertise with NDB was limited, and outdated.

Help came in November, when Daniël van Eeden started giving me bits of information on how to put together a cluster sandbox. I still resisted forcing my hand at the implementation, because by then I had realised that my method of checking the database server version to know whether it supported a given feature was inadequate to support anything other than vanilla MySQL or fully complaint forks.

The game changer was the cooperation with TiDB that opened the way for supporting Percona XtraDB Cluster. Even though these technologies are way different from MySQL Cluster, they forced me to improve dbdeployer’s code, making it more flexible, easier to enhance.

When I finally decided to start working on NDB, it took me only a few days to implement it, because I had all the pieces ready for this technology to become part of dbdeployer.

Following Däniel’s instructions, I had a prototype working, which I submitted to #dbdeployer channel on MySQL community slack. In that channel, I got help again from Däniel van Eeden, and then Frédéric Descamps summoned two more experts (Ted Wennmark and Bernd Ocklin), who gave me feedback, advice, and some quick lessons on how the cluster should work, which allowed me to publish a release (dbdeployer 1.23.0) this past week-end.

The implementation has some peculiarities for both users of dbdeployer and MySQL Cluster. For the ones used to dbdeployer, the biggest change is that we are deploying two entities, of which the main one is an NDB cluster, with its own directories and processes, while the MySQL servers are just the visible part of the cluster, but are, in fact, only cluster clients. Still, the cluster works smoothly in dbdeployer paradigm: the cluster is deployed (like Group replication or PXC) as a replication topology, and as such we can run the standard replication test and expect to get the same result that we would see when checking another multi-source deployment.

For people used to NDB, though, seeing NDB as “replication” feels odd, because the cluster is seeing as a distinct entity, and replication is when we transfer data between two clusters. If we were developing a dedicated tool for NDB clusters, this is probably what we would have done, but since we want dbdeployer integration, we must play by the general rules of the tool, where “single” is a stand-alone server instance, and we can’t have dbdeployer deploy single --topology=ndb, because single instance don’t have a topology, which is a property of a group of entities. Therefore, the price to pay for dbdeployer support accepting to see a MySQL cluster deployment as replication.

Now that we have covered all the philosophical angle, it’s time to show an example. Unlike PXC, which is requires Linux, MySQL Cluster can also run on MacOS, which makes my testing much easier.

The first step to run a cluster in dbdeployer is to download a tarball from dev.mysql.com/downloads/cluster, and then expand it in our usual directory ($HOME/opt/mysql):

$ dbdeployer unpack --prefix=ndb --flavor=ndb \  
    ~/Downloads/mysql-cluster-gpl-7.6.9-macos10.14-x86_64.tar.gz  
Unpacking tarball $HOME/Downloads/mysql-cluster-gpl-7.6.9-macos10.14-x86_64.tar.gz to $HOME/opt/mysql/ndb7.6.9  
[...]  
Renaming directory $HOME/opt/mysql/mysql-cluster-gpl-7.6.9-macos10.14-x86_64 to $HOME/opt/mysql/ndb7.6.9

We can repeat the same operation for MySQL Cluster 8.0.14, and in the end we will two expanded tarballs named ndb7.6.9 and ndb8.0.14. With this we can install a few clusters in the same host:

$ dbdeployer deploy replication ndb7.6 --topology=ndb --concurrent  
# ndb7.6 => ndb7.6.9  
$HOME/sandboxes/ndb_msb_ndb7_6_9/initialize_nodes  
MySQL Cluster Management Server mysql-5.7.25 ndb-7.6.9  
2019-03-18 23:47:15 [ndbd] INFO     -- Angel connected to 'localhost:20900'  
2019-03-18 23:47:16 [ndbd] INFO     -- Angel allocated nodeid: 2  
2019-03-18 23:47:16 [ndbd] INFO     -- Angel connected to 'localhost:20900'  
2019-03-18 23:47:16 [ndbd] INFO     -- Angel allocated nodeid: 3  
executing 'start' on node 1  
................ sandbox server started  
executing 'start' on node 2  
.. sandbox server started  
executing 'start' on node 3  
.. sandbox server started  
NDB cluster directory installed in $HOME/sandboxes/ndb_msb_ndb7_6_9  
run 'dbdeployer usage multiple' for basic instructions'


$ dbdeployer deploy replication ndb8.0 --topology=ndb --concurrent  
# ndb8.0 => ndb8.0.14  
$HOME/sandboxes/ndb_msb_ndb8_0_14/initialize_nodes  
MySQL Cluster Management Server mysql-8.0.14 ndb-8.0.14-dmr  
2019-03-18 23:45:53 [ndbd] INFO     -- Angel connected to 'localhost:21400'  
2019-03-18 23:45:53 [ndbd] INFO     -- Angel allocated nodeid: 2  
2019-03-18 23:45:53 [ndbd] INFO     -- Angel connected to 'localhost:21400'  
2019-03-18 23:45:53 [ndbd] INFO     -- Angel allocated nodeid: 3  
executing 'start' on node 1  
........ sandbox server started  
executing 'start' on node 2  
... sandbox server started  
executing 'start' on node 3  
.. sandbox server started  
NDB cluster directory installed in $HOME/sandboxes/ndb_msb_ndb8_0_14  
run 'dbdeployer usage multiple' for basic instructions'

If we look at the sandbox directories, we will see a few more subdirectories than we usually have with other topologies. For example:

 ndb_conf   # cluster configuration  
 ndbnode1   # management node (1)  
 ndbnode2   # data node (2)  
 ndbnode3   # data node (3)  
 node1      # MySQL node 1  
 node2      # MySQL node 2  
 node3      # MySQL node 3

The clusters are well framed into dbdeployer’s architecture, and they respond to standard commands like any other sandbox:

$ dbdeployer sandboxes --full-info  
.-------------------.------.-----------.----------------------------------------------.--------.-------.--------.  
|       name        | type |  version  |                    ports                     | flavor | nodes | locked |  
+-------------------+------+-----------+----------------------------------------------+--------+-------+--------+  
| ndb_msb_ndb7_6_9  | ndb  | ndb7.6.9  | [20900 27510 27511 27512 ]                   | ndb    |     3 |        |  
| ndb_msb_ndb8_0_14 | ndb  | ndb8.0.14 | [21400 28415 38415 28416 38416 28417 38417 ] | ndb    |     3 |        |  
'-------------------'------'-----------'----------------------------------------------'--------'-------'--------'

$ dbdeployer global status  
# Running "status_all" on ndb_msb_ndb7_6_9  
MULTIPLE  /Users/gmax/sandboxes/ndb_msb_ndb7_6_9  
node1 : node1 on  -  port   27510 (27510)  
node2 : node2 on  -  port   27511 (27511)  
node3 : node3 on  -  port   27512 (27512)

# Running "status_all" on ndb_msb_ndb8_0_14  
MULTIPLE  /Users/gmax/sandboxes/ndb_msb_ndb8_0_14  
node1 : node1 on  -  port   28415 (28415)  
node2 : node2 on  -  port   28416 (28416)  
node3 : node3 on  -  port   28417 (28417)

$ dbdeployer global test-replication  
# Running "test_replication" on ndb_msb_ndb7_6_9  
# master 1  
# master 2  
# master 3  
# slave 1  
ok - '3' == '3' - Slaves received tables from all masters  
# slave 2  
ok - '3' == '3' - Slaves received tables from all masters  
# slave 3  
ok - '3' == '3' - Slaves received tables from all masters  
# pass: 3  
# fail: 0

# Running "test_replication" on ndb_msb_ndb8_0_14  
# master 1  
# master 2  
# master 3  
# slave 1  
ok - '3' == '3' - Slaves received tables from all masters  
# slave 2  
ok - '3' == '3' - Slaves received tables from all masters  
# slave 3  
ok - '3' == '3' - Slaves received tables from all masters  
# pass: 3  
# fail: 0

Like other topologies, also the NDB cluster has a script that shows the status of the nodes:

$ ~/sandboxes/ndb_msb_ndb7_6_9/check_nodes  
+---------+-----------+---------------+--------+---------+-------------+-------------------+  
| node_id | node_type | node_hostname | uptime | status  | start_phase | config_generation |  
+---------+-----------+---------------+--------+---------+-------------+-------------------+  
|       2 | NDB       | localhost     |     58 | STARTED |           0 |                 1 |  
|       3 | NDB       | localhost     |     58 | STARTED |           0 |                 1 |  
|       1 | MGM       | localhost     |   NULL | NULL    |        NULL |              NULL |  
|       4 | API       | localhost     |   NULL | NULL    |        NULL |              NULL |  
|       5 | API       | localhost     |   NULL | NULL    |        NULL |              NULL |  
|       6 | API       | localhost     |   NULL | NULL    |        NULL |              NULL |  
|       7 | API       | localhost     |   NULL | NULL    |        NULL |              NULL |  
+---------+-----------+---------------+--------+---------+-------------+-------------------+  
Connected to Management Server at: localhost:20900  
Cluster Configuration  
---------------------  
[ndbd(NDB)] 2 node(s)  
id=2    @127.0.0.1  (mysql-5.7.25 ndb-7.6.9, Nodegroup: 0, *)  
id=3    @127.0.0.1  (mysql-5.7.25 ndb-7.6.9, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)  
id=1    @127.0.0.1  (mysql-5.7.25 ndb-7.6.9)

[mysqld(API)]   4 node(s)  
id=4    @127.0.0.1  (mysql-5.7.25 ndb-7.6.9)  
id=5    @127.0.0.1  (mysql-5.7.25 ndb-7.6.9)  
id=6    @127.0.0.1  (mysql-5.7.25 ndb-7.6.9)  
id=7 (not connected, accepting connect from localhost)

It is possible that we will need more iterations to make the deployment more robust. When testing it, keep in mind that this deployment is only for testing, and it won’t probably have all the performance that you may find in a well deployed production cluster. Still, compared to other topologies, the replication tests performed faster than I expected.

Friday, March 08, 2019

dbdeployer community - Part 2: Percona XtraDB Cluster

This was not on the radar. I have never been proficient in Galera clusters and related technologies, and thus I hadn’t given much thought to Percona Xtradb Cluster (PXC), until Alkin approached me at FOSDEM, and proposed to extend dbdeployer features to support PXC. He mentioned that many support engineers at Percona use dbdeployer) on a daily basis and that the addition of PXC would be welcome.

I could not follow up much during the conference, but we agreed on making a proof-of-concept in an indirect way: if several nodes of PXC can run in the same host using shell scripts, dbdeployer could reproduce that behavior.

A few weeks later, when dbdeployer had already been enhanced with flavors and capabilities, I got the script that can deploy several nodes in the same host. It’s a simplification of the ones used in Percona PXC tests, which got me started.

I followed a method similar to the one I used for MySQL Group replication. The technology is similar, although the MySQL Team used a different approach for the installation. The basic principle is that the cluster needs two ports per node: in addition to the regular MySQL port, there is a communication port (SST or Snapshot State Transfer port) that is needed to exchange cluster data. Using this information, and following the sample in the script, I could produce a prototype that surprisingly worked at the first try!

The cluster did deploy, and the replication test, which comes free of charge when you implement a replication-type sandbox using standard templates, worked flawlessly.

Then I hooked the deployment method into dbdeployer concurrency engine, which is able to deploy several nodes at once. Here I hit the first problem. In PXC, the nodes are not equal at startup. The first node needs to be initialised without other nodes addresses, and it becomes the reference for other nodes to join the cluster. If I provided complete references for all nodes (as I do for MySQL Group Replication,) it didn’t work.

After some talk with Percona engineers on Slack, I figured out that the nodes can be deployed together, and the second and third node will just wait for the first one to come online and then join. That worked in principle, or when I deployed sequentially, but not when they are deployed all at once. Fortunately, dbdeployer has several ways of enabling debugging output, and after a few unsuccessful attempts I got the reason: PXC initialisation happens using rsync on port 4444. When the nodes are started sequentially, the receiving node takes control of port 4444 without conflicts, gets the job done and releases the port. When we deploy all nodes at once, there is a race for the possession of the synchronisation port, and a random node will win it, leaving the others waiting forever.

Thus, I modified the installation to allocate a different rsync port for each node, and after that the concurrent installation worked as well.

The last obstacle was the discovery that there is yet another port (IST, or Incremental State Transfer port), which is always one number bigger than the SST port. Thus, if the SST port is, say, 5555, the IST port is set to 5556. This means that, unlike other dbdeployer clusters, I can’t set port numbers incrementally, but I need to set them with an interval. I did that, and the cluster came with a default allocation of four ports per node (MySQL, rsync, SST, IST). If we also enable MySQLX, which comes includes as PXC binaries are based on MySQL 5.7, we would set 5 ports per node, and a majestic 15 ports for a three-node cluster.

Anyway, the support for Percona XtraDB Cluster is available in dbdeployer 1.21.0. Let’s see a sample session to use the new functionality.

$ $ dbdeployer --version
dbdeployer version 1.21.0

$ dbdeployer unpack --prefix=pxc ~/downloads/Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl100.tar.gz  
[...]  
Renaming directory $HOME/opt/mysql/Percona-XtraDB-Cluster-5.7.25-rel28-31.35.1.Linux.x86_64.ssl100 to $HOME/opt/mysql/pxc5.7.25

Before trying the cluster, it would be a good practice to make sure that your system can deploy a single node.

$ dbdeployer deploy single pxc5.7.25  
Database installed in $HOME/sandboxes/msb_pxc5_7_25  
run 'dbdeployer usage single' for basic instructions'  
. sandbox server started

$ $HOME/sandboxes/msb_pxc5_7_25/test_sb  
[...]  
# Tests :    11  
# PASS  :    11  
# fail  :     0

$ dbdeployer delete msb_pxc5_7_25  
[...]

And now for the real test:

$ dbdeployer deploy replication --topology=pxc pxc5.7.25  
Installing and starting node 1  
. sandbox server started  
Installing and starting node 2  
...... sandbox server started  
Installing and starting node 3  
..... sandbox server started  
Replication directory installed in $HOME/sandboxes/pxc_msb_pxc5_7_25  
run 'dbdeployer usage multiple' for basic instructions'

We should now see all the allocated ports.

$ dbdeployer sandboxes --header  
            name                       type             version                                      ports  
---------------------------- ------------------------ ----------- ----------------------------------------------------------------------------  
 pxc_msb_pxc5_7_25        :   Percona-Xtradb-Cluster   pxc5.7.25   [26226 26352 26353 26364 26227 26354 26355 26365 26228 26356 26357 26366 ]

If we want more detail, we can look at the sandbox description file:

$ cat $HOME/sandboxes/pxc_msb_pxc5_7_25/sbdescription.json  
{
    "basedir": "$HOME/opt/mysql/pxc5.7.25",  
    "type": "Percona-Xtradb-Cluster",  
    "version": "pxc5.7.25",  
    "flavor": "pxc",  
    "port": [  
        26226,  
        26352,  
        26353,  
        26364,  
        26227,  
        26354,  
        26355,  
        26365,  
        26228,  
        26356,  
        26357,  
        26366  
    ],  
    "nodes": 3,  
    "node_num": 0,  
    "dbdeployer-version": "1.21.0",  
    "timestamp": "Thu Mar  7 17:20:03 CET 2019",  
    "command-line": "dbdeployer deploy replication --topology=pxc pxc5.7.25"  
 }

Now we can run the replication test. Given that we have a cluster where all nodes are masters, the test will create a table in each node, and read the result in each slave (again, each node):

$ $HOME/sandboxes/pxc_msb_pxc5_7_25/test_replication  
# master 1  
# master 2  
# master 3  
# slave 1  
ok - '3' == '3' - Slaves received tables from all masters  
# slave 2  
ok - '3' == '3' - Slaves received tables from all masters  
# slave 3  
ok - '3' == '3' - Slaves received tables from all masters  
# pass: 3  
# fail: 0

It’s a simple test, but it tells us that the cluster is fully functional.

Thanks to Alkin and other Percona engineers who have tested the prototype in real time.

Wednesday, March 06, 2019

dbdeployer community - Part 1: TiDB

After a conference, when I take stock of what I have learned, I usually realise that the best achievements are the result of interacting with other attendees during the breaks, rather than simply listening to the lectures. It might be because I follow closely the blogosphere and thus the lectures have few surprises in store for me, or perhaps because many geeks take the conference as an excuse to refresh dormant friendships, catch up with technical gossip, and ask their friends some questions that were too sensitive to be discussed over Twitter and have been waiting for a chance of an in-person meeting to see the light of the day.

I surely had some of such questions, and I took advantage of the conference to ask them. As it often happens, I got satisfactory responses, but the latest FOSDEM conference was different than usual, because I got the best experience from the questions that others did ask me.

As it turned out, others were waiting for a chance to discuss things over coffee or food, and I saw that my pet project (dbdeployer) is a lot more popular than I thought, and it is being used silently in several environments. It should not be surprising if you read several MySQL reports on bugs at bugs.mysql.com where it is common the usage of sandboxes to reproduce user issues. Anyway, I got some praise, some requests, a few ideas for improvements, advance notice of an incoming graphical interface, and a few concrete collaboration proposals.

One of such proposals came from Morgan Tocker, who suggested enhancing dbdeployer to support TiDB. At first, it seemed uninteresting, as TiDB is designed to be distributed, and installing just a component didn’t immediately look useful. However, Morgan pointed out that it could be used as a tool to test compatibility with existing applications, and as such it could gain much more value than I initially thought. We decided to try a quick hackathon to make a proof of concept.

It was a great pleasure to figure out, in just over one hour of close interaction, that dbdeployer design for flexibility was up to the task. We managed to make TiDB work with dbdeployer simply by exporting, editing, and re-loading a few templates.

The exercise showed strengths and limitations in both projects. We agreed that dbdeployer had to lose some assumptions (such as “I am working with a MySQL server”) and become able to recognise which flavor of MySQL-lookalike we are dealing with. At the same time, we noted that TiDB is not compatible when it comes to deployment and bootstrap: it is so simple and straightforward that its initialisation doesn’t fit in the complex operation that is a MySQL server warm-up.

Pleased with the initial success, we kept in touch and, after dbdeployer acquired the ability of telling one flavor from another, we put together the various pieces to make dbdeployer recognise and install TiDB. We found and fixed several bugs in both project, and finally released dbdeployer 1.19.0, which can use a TiDB server transparently.

What does transparently mean? It means that tests for TiDB deployment can run alongside tests for other MySQL servers, and the sandbox scripts (such as start, stop, use, status, and test_sb) work as expected and produce a compatible output. Thus, there is a TiDB test running together with another dozen MySQL versions.

Now, if you want, you can evaluate TiDB in your computer without installing the full stack. It won’t be as fast as the real thing: what is installed as a single node is a slower emulation of the real database, but it is enough to give you an idea of what queries you can and cannot run in TiDB, and perhaps try to see if your application could run on TiDB at all.

The collaboration with TiDB was especially useful because the changes needed to smooth the TiDB integration have made made dbdeployer better suited to add support for more not-quite-mysql servers, such as the one that we’ll see in the next post.

But before reaching that point, here’s an example of TiDB deployment on Linux:

$ wget https://download.pingcap.org/tidb-master-linux-amd64.tar.gz  
[...]  
2019-02-24 04:46:26 (2.26 MB/s) - 'tidb-master-linux-amd64.tar.gz' saved [16304317/16304317]

$ dbdeployer unpack tidb-master-linux-amd64.tar.gz --unpack-version=3.0.0 --prefix=tidb  
Unpacking tarball tidb-master-linux-amd64.tar.gz to $HOME/opt/mysql/tidb3.0.0  
1
Renaming directory /home/msandbox/opt/mysql/tidb-master-linux-amd64 to /home/msandbox/opt/mysql/tidb3.0.0

TiDB tarballs doesn't come with a client. We need to use one from MYSQL 5.7. Rather than downloading the huge tarball from MySQL site, we can get a smaller one from a GitHub repository, using dbdeployer itself (NB: this reduced tarball is only for Linux)


$ dbdeployer remote list  
Files available in https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata/available.json  
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]  
5.5 -> [mysql-5.5.61 mysql-5.5.62]  
5.6 -> [mysql-5.6.41 mysql-5.6.43]

$ 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  
[...]  
Renaming directory /home/msandbox/opt/mysql/mysql-5.7.25 to /home/msandbox/opt/mysql/5.7.25

Now we are ready to install TiDB:

$ dbdeployer deploy single tidb3.0.0 --client-from=5.7.25  
Creating directory /home/msandbox/sandboxes  
Database installed in $HOME/sandboxes/msb_tidb3_0_0  
run 'dbdeployer usage single' for basic instructions'  
. sandbox server started

Once installed, a TiDB sandbox behaves like a MySQL sandbox.

$ $HOME/sandboxes/msb_tidb3_0_0/use  
Welcome to the MySQL monitor.  Commands end with ; or \g.  
Your MySQL connection id is 2  
Server version: 5.7.10-TiDB-v3.0.0-beta-111-g266ff4b6f MySQL Community Server (Apache License 2.0)

Copyright (c) 2000, 2019, 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' to clear the current input statement.

mysql [localhost:3000] {msandbox} ((none)) >

Friday, April 20, 2018

MySQL adjustment bureau


When maintainng any piece of software, we usually deal with two kind of actions:

  • bug fixing,
  • new features.

bugs and features

A bug happens when there is an error in the software, which does not behave according to the documentation or the specifications. In short, it's a breech of contract between the software maintainer and the users. The promise, i.e. the software API that was published at every major version, is broken, and the software must be reconciled with the expectations and fixed, so that it behaves again as the documentation says. When we fix a bug in this way, we increment the revision number of the software version (e.g. 1.0.0 to 1.0.1. See semantic versioning).

New features, in turn, can be of two types:

  • backward compatible enhancements, which add value to the software without breaking the existing functionality. This is the kind of change that requires an increment of the minor indicator in the version (for example: 1.1.15 to 1.2.0.)
  • Incompatible changes that break the existing behavior and require users to change their workflow. This kind of change requires bumping up the major number in the version (as in 2.1.3 to 3.0.0.)

Not a bug, nor a feature, but an adjustment.

The above concepts seem simple enough: you either fix something that's broken or add new functionality.

However, when maintaining a tool that has the purpose of helping users to deal with another software (as it is the case of dbdeployer that helps users to deploy MySQL databases) there is yet another category of changes that don't fall into the standard categories: it's what happens when the software being helped (MySQL) changes its behavior, which would break the normal functioning of the helping tool, giving the maintainer a difficult choice:

  • shall I modify the tool's interface to adapt to the new behavior, breaking existing procedures?
  • or shall I adapt the tool's functioning behind the scenes to keep the interface unchanged?

My philosophy with dbdeployer (and MySQL-Sandbox before it) is to preserve the tool's interface, so that users don't have to change existing procedures. I call this kind of changes adjustments, because they are not bugs, as they are not a consequence of a coding error, and not a feature, as the intervention is not a conscious decision to add new functionality, but an emergency operation to preserve the status quo. You can think of this category as a capricious change in specifications, which so often happens to software developers, with the difference that the one changing the specs is not the user, but a third party who doesn't know, or care, about our goal of preserving the API integrity.

For example, from MySQL 8.0.3 to 8.0.4 there was a change in the default authentication plugin. Instead of mysql_native_password, MySQL 8.0.4 uses caching_sha2_password. The immediate side effect for MySQL-Sandbox and dbdeployer was that replication doesn't work out of the box. A possible solution would be to force the old authentication plugin, but this would not allow users to test the new one. Since the main reason to use a tool like dbdeployer is to experiment with new releases safely, I had to keep the default behavior. Thus, I left the default plugin in place, and changed the way the replication works. It's an ugly workaround actually, but allows users to see the new behavior without losing existing functionality.
To complete the adjustment, I added a new option --native-auth-plugin, which would deploy using the old mysql_native_password. In total, the adjustment consists of a behind-the-scenes change, almost undetectable by users, and a new option to keep using the familiar authentication if users want it.

From the point of view of semantic versioning, this kind of change is a backward-compatible modification of the API, which warrants an increase of the minor number of the version.

Another example: when MySQL went from 8.0.4 to 8.0.11, it introduced a deal breaker change: the X Plugin is now loaded by default. This is easy for users of MySQL as a document store, as they don't need to enable the plugin manually, but bad news for anyone else, as the server is opening a port and a socket that many users may not choose to open voluntarily. What's worse, when installing more sandboxes of version 8.0.11 in the same host (for example in replication), one will succeed in reserving the plugin port and socket, while the others will have the error log populated with surprising errors about a socket being already in use.

The solution is similar to the previous one. When dbdeployer detect MySQL 8.0.11 or newer, it adds options to customize the mysqlx plugin port and socket, thus allowing a frictionless deployment where the new functionality is available to the brave experimenters. At the same time, I added a new option (--disable-mysqlx) for the ones who really don't want an extra port and socket in their servers, not even for testing.

These adjustment are usually costly additions. While the added code is not that much, they require extra tests, which are often complex and require more time to write and execute them. The process to add an adjustment goes mostly like this:

  • I dedicate my morning walk to think about the fix. Sometimes the fix requires several walks, while I decide the less intrusive solution.
  • If the walk has been fruitful, writing the code requires just a few minutes. If I missed something, I iterate.
  • Then the more difficult part: writing meaningful tests that prove that the adjustment is correct and it doesn't introduce side effects in any MySQL version. And of course the option that reintroduces the old behavior must be tested too.
  • A positive side effect of this exercise is that often I realize that I was missing a test for an important behavior and then I write down that as well. The test suite included 6,000+ tests 1 month ago, and now it has almost doubled.

Tuesday, April 03, 2018

Test MySQL 8.0 right in your computer

MySQL 8.0 GA is right around the corner. I don't have precise information about its release, as I don't work at Oracle. If I did, I would probably know, but I couldn't tell when the release is scheduled to appear because of company policies. I can, however, speculate and infer, based of my experience with previous releases. My personal assessment is that the release will appear before 9:00am PT on April 24, 2018. The "before" can be anything from a few minutes to one week in advance.
Then, again, it may not happen at all if someone finds an atrocious bug that needs to be fixed asap.

Either way, users are keen on testing the new release in its current state of release candidate. Here I show a few methods that allow you to have a taste of the new goodies without waiting for the triumphal (keynote) announcement.


1. Docker containers

If you are a docker user, using a container to test MySQL is a no brainer. Unlike virtual machines or standalone servers, a docker container comes ready to use, with nothing to configure. All you need to do is pulling the right image. As with every docker images, you pull once and then use as many times as you need.

There are two reliable images that contain the latest MySQL. One is called mysql:8.0 and is tagged as official, which means that it is released by the Docker maintenance team. The other one, which is released by the MySQL team, is called mysql/mysql-server:8.0.

$ docker pull mysql:8.0
8.0: Pulling from library/mysql
Digest: sha256:7004063f8bd0c7bade8d1c526b9b8f5188c8288f411d76ee4ba83131e00c6f02
Status: Downloaded newer image for mysql:8.0

$ docker pull mysql/mysql-server:8.0
8.0: Pulling from mysql/mysql-server
Digest: sha256:e81d95f788adb04a4d2fa5f6f7e9283ca0f6360fb518efe65af5a7377a4ec282
Status: Downloaded newer image for mysql/mysql-server:8.0

The mysql image is based on Debian, while the original package, as you would expect, is based on Oracle Linux.

Let's see how to run MySQL in a container.

$ docker run --name official  -e MYSQL_ROOT_PASSWORD=secret -d mysql:8.0
60ec307578a139f5083ded07e94d737690d287b1b95093878675983a5cc40174

$ docker run --name original -e MYSQL_ROOT_PASSWORD=secret \
    -d mysql/mysql-server:8.0
0c93bb4a97ffa53232a69732d3ae45413a443e38fa43ad6fdc4057168cba42d2

With the above commands we get two containers, one for the official image and one for the original one.
We can't use them straight away, though. We need to wait for the servers to be ready. An easy method to verify the status of the server is looking at docker logs:

$ docker logs original --tail 1
2018-04-01T21:23:30.395461Z 0 [System] [MY-010931] /usr/sbin/mysqld: ready for connections. Version: '8.0.4-rc-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL).

$ docker logs original --tail 1
2018-04-01T21:23:30.395461Z 0 [System] [MY-010931] /usr/sbin/mysqld: ready for connections. Version: '8.0.4-rc-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL).

Here, after about 10 seconds, both containers are ready to use. We can now access the servers. One easy method is through docker exec

$ docker exec -ti original mysql -psecret
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.4-rc-log 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' to clear the current input statement.

mysql>

A similar command would allow us to access the other container.

If you want to try replication, more work is needed. In these articles you will find more details on Docker operations, and examples of advanced deployments:


2. Sandboxes

A sandboxed database is deployed in a non-dedicated box, with its configuration altered in such a way that it will run independently from other similar deployment and even from databases running in the main space.
The granddaddy of the sandbox deployer was MySQL-Sandbox, which has recently evolved into the more powerful and easier to use dbdeployer.
You can use MySQL-Sandbox to test a MySQL 8.0 tarball on MacOS

$ make_sandbox --export_binaries  mysql-8.0.4-rc-macos10.13-x86_64.tar.gz

This command unpacks the tarball into $HOME/opt/mysql and deploys the database in $HOME/sandboxes/msb_8_0_4.
Until recently, the same command would work on Linux without modifications. In MySQL 8.0.4, though, the tarball organization for Linux has changed. There are symbolic links for SSL libraries inside the ./bin directory. Those symlinks are not extracted by default, but only if you use the option --keep-directory-symlink when opening the tarball. MySQL-Sandbox doesn't do it, also because this option is not standard to every version of tar.

Thus, if you want to use the old MySQL-Sandbox, you need to run the extraction manually.

$ cd $HOME/opt/mysql
$ tar -xzf  --keep-directory-symlink /tmp/mysql-8.0.4-rc-linux-glibc2.12-x86_64.tar.gz
$ mv mysql-8.0.4-rc-linux-glibc2.12-x86_64 8.0.4
$ make_sandbox 8.0.4

I don't recommend the above procedure, for either Linux or MacOS. The main reason, in addition to the manual operations involved, is that MySQL-Sandbox is not going to be updated for the time being. Instead, you should use dbdeployer, which has all the main features of MySQL-Sandbox and a lot of new ones. Here's the equivalent procedure:

$ dbdeployer unpack /tmp/mysql-8.0.4-rc-linux-glibc2.12-x86_64.tar.gz
$ dbdeployer deploy single 8.0.4
Database installed in $HOME/sandboxes/msb_8_0_4
run 'dbdeployer usage single' for basic instructions'
. sandbox server started

dbdeployer uses a different method to initialize the database server, which at the same time makes the initialization more visible and avoids the problem of the phantom SSL libraries.

Note: Tarballs for recent MySQL versions are really big. MySQL 8.0.4 binaries expand to 1.9 GB. If storage is an issue, you should get the tarballs from a collection of minimised tarballs (Linux only) for most MySQL versions. For now, it's maintained by me, but I hope that the the MySQL team will release something similar.

Once you have deployed a sandbox with MySQL 8.0, using it is easy:

$ cd $HOME/sandboxes/msb_8_0_4
$ ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.4-rc-log 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' to clear the current input statement.

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

dbdeployer creates several shortcuts for the most common commands to use the database. ./use is the most common, and provides access to the MySQL client with all the necessary options needed to use it correctly. For more information on what is available, run

$ dbdeployer usage single

This functionality would be enough to decide for a sandbox as your preferred method for testing. However, it this is only a tiny portion of what you can do with dbdeployer in your own computer. With a single command, you can test master/slave replication, multi-primary group replication, single primary group replication, fan-in, and all-masters topologies.

You can try the following commands:

$ dbdeployer deploy single 8.0.4
$ dbdeployer deploy replication 8.0.4
$ dbdeployer deploy replication 8.0.4 --topology=group
$ dbdeployer deploy replication 8.0.4 --topology=group --single-primary
$ dbdeployer deploy replication 8.0.4 --topology=all-masters
$ dbdeployer deploy replication 8.0.4 --topology=fan-in

If you have enough RAM, all these deployments will survive in parallel.
In my desktop, I can run:

$ dbdeployer sandboxes --header
name                        type                    version  ports
----------------            -------                 -------  -----
all_masters_msb_8_0_4     : all-masters               8.0.4 [15001 15002 15003]
fan_in_msb_8_0_4          : fan-in                    8.0.4 [14001 14002 14003]
group_msb_8_0_4           : group-multi-primary       8.0.4 [20009 20134 20010 20135 20011 20136]
group_sp_msb_8_0_4        : group-single-primary      8.0.4 [21405 21530 21406 21531 21407 21532]
msb_8_0_4                 : single                    8.0.4 [8004]
rsandbox_8_0_4            : master-slave              8.0.4 [19009 19010 19011]

When MySQL 8.0.11 is released, you can replace "8.0.4" with "8.0.11" and get a similar result.

BTW, you have seen that deploying replication sandboxes may take a long time. You may try adding --concurrent to each command, and enjoy a notable speed increase.

What else can you do with the sandboxes you have just deployed? Plenty! For a complete list, have a look at the online documentation. But for the moment, you may try this:

$ dbdeployer global status
$ dbdeployer global test
$ dbdeployer global test-replication

3. Other methods

Besides the methods that I recommend, there are others that you could use, but I won't advise about them as there are more qualified ones for that.

  • Standalone server. If you have the luxury of having one or more standalone servers sitting in a lab, by all means go for it. Just follow the instructions about installing MySQL on your lucky server. Be advised, though, that depending on the method you choose and the version of your operating system, you may face compatibility issues (.rpm or .deb dependencies).
  • Virtual machines. VMs share with standalone servers the same ease of installation (and the same dependency issues), only a bit slower. They are convenient, as you can use them to test in conditions that more closely resemble production settings, and if you use a configuration server such as Puppet or Ansible, your task of testing the new version could be greatly simplified. The instructions for the virtual machines are the same seen for standalone servers.