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.

Welcome Tungsten Replicator 2.1.0!

Overview

First off, the important news. Tungsten Replicator 2.1.0 was released today.
You can download it and give it a try right now.

Second, I would say that I am quite surprised at how much we have done in this release. The previous release (2.0.7) was in February, which is just a few months ago, and yet it looks like ages when I see the list of improvements, new features and bug fixes in the Release Notes. I did not realized it until I ran my last batch of checks to test the upgrade from the previous release, which I hadn’t run for quite a long time. It’s like when you see a son growing in front of your eyes day by day, and you don’t realize he’s grown a full foot until a distant relative comes visit you. The same happened to me here. I looked at the ./cookbook directory in 2.0.7, and I saw just a handful of commands (most of them now deprecated), and then at 2.1.0, which has about 30 new commands, all nicely categorized and advertised in the embedded documentation. If you are starting today with Tungsten Replicator 2.1.0, you can run

./cookbook/readme

and

./cookbook/help

Upgrade

If you were using Tungsten Replicator before, you need to know how to upgrade. If, by any unfortunate chance, you were not using the Cookbook recipes to run the installation, the method for installing is the following:

• unpack the tarball in a staging directory
• For each node in your deployment:
  
  • stop the replicator
  • run
    

    ./tools/update –release-directory=$PATH_TO_DEPLOYED_TUNGSTEN –host=$NODE

• If your node has more than one service, restart the replicator

If you are using the cookbook, you can run an upgrade using

./cookbook/upgrade

This command will ask for your current topology and then show all the commands that you should run to perform the upgrade, including adapt the cookbook scripts to use the new deployment.

So, What’s New:

The list of goodies is long. All the gory details are in the Release Notes. Here I would like to mention the ones that have impressed me more.

Oracle Extractor Is Open Source

Up to the previous release, you could extract from MySQL and appley to Oracle, all using open source tools. If you wanted to extract from Oracle, you needed a commercial license. Now all the replication layer is completely open source. You can replicate from and to Oracle using Tungsten Replicator 2.1.0 under the terms of the GPL v2. However, you will still have to buy database licenses from Oracle!

Installation and Administration

There is a long list of utilities released inside the ./cookbook directory, which will help you install and maintain the cluster with a few strokes. See References #2 and #3 below. The thing that you should try right away is:

 # edit ./cookbook/COMMON_NODES.sh
 # edit ./cookbook/USER_VALUES.sh
 ./cookbook/validate_cluster

This will tell you if your servers are ready for deployment, without actually deploying anything.

Documentation!

We have hired a stellar professional writer (my former colleague at MySQL AB, well known book writer MC Brown) and the result is that our well intentional but rather unfocused documentation is now shaping up nicely. Among all the things that got explained, Tungsten Replicator has its own getting started section.

Metadata!

Tungsten replication tools now give information using JSON. Here’s a list of commands to try:

trepctl status -json
trepctl services -json -full
trepctl properties | less
thl list -headers -high 100 [-json]

For example:

$ trepctl services -json
[
{
"appliedLatency": "0.81",
"state": "ONLINE",
"role": "slave",
"appliedLastSeqno": "1",
"started": "true",
"serviceType": "local",
"serviceName": "cookbook"
} 
]

$ trepctl properties -filter replicator.service.comments
{
"replicator.service.comments": "false"
}

More Tools

My colleague Linas Virbalas has made the team (and several customers) happy when he created two new tools:

• ddlscan, a Utility to Help Analyze and Migrate Database Schemas
• the rename filter A supercharged filter that can rename mostly any object in a relational database, from schema down to columns.

Linas coded also the above mentioned JSON-based improvements.

MongoDB Installation

It was improved and tested better. It’s a pleasure top see how data from a relational database flow into a rival NoSQL repository as if they belong there! See reference #4 below.

More to Come

What’s listed here is what we have tested and documented. But software development is not a linear process. There is much more boiling in the cauldron, ready to be mixed into the soup of release 2.1.1.

We’re working hard at making filters better. You will see soon the long awaited documentation for them, and a simplified interface.

Another thing that I have tested and worked surprisingly well is the creation of Change Data Capture for MySQL. This is a feature that is usually asked for by Oracle users, but I tried it for MySQL and it allowed me to create shadow tables with the audit trace of their changes. I will write about that as soon as we smooth a few rough edges.

Scripting! This going to be huge. Much of it is already available in the source, but not fully documented or integrated yet. The thing that you will see soon in the open is a series of Ruby libraries (the same used by the very sophisticated Tungsten installation tools) that is exposed for general usage by testers and tool creators. While the main focus of this library is aimed at the commercial tools, there is a significant portion of work that needs to end up in the replicator, and as a result its usability will increase.

What else? I may have forgot something important amid all the excitement. If so, I will amend in my next articles. Happy hacking!

References

1. Tungsten Replicator documentation
2. Installing and Administering Tungsten Replicator - Part 1 - basics
3. Installing and administering Tungsten Replicator - Part 2 : advanced
4. Getting started with replication from MySQL to MongoDB

Tungsten Replicator 2.0.4 released: usability and power

It has been a bumpy ride, with dozens of issues opened and resolved, but we finally feel that Tungsten Replicator 2.0.4 is ready for prime time. There have been quite a lot of changes. Most notably, the replicator is much faster, especially when it comes to parallel replication, and it is much easier to install, thanks to its new integrated installer, which can validate all the requirements to install the replicator, and suggest remedies when the requirements aren't met. This new installer is so good, in fact, that calling it installer is an insult. It is a legitimate cluster builder, able to install a full fledged cluster from a central location.

Probably equally important, we have caught up with the documentation, and now you can install several replication topologies following detailed instructions from the docs. You will find both HTML and PDF guides, with the steps to install straight master/slave systems, or direct slave takeover, or bi-directional replication. The binaries are available in the project's Downloads page. Later on, you will find the most updated (and possibly less bug-infested) binaries in our build server list. The Release_Notes list all the issues that have been closed since we released 2.0.3. The advanced users will especially appreciate an innovation introduced in the installer, which now allows users to define one or more of --property=key=value. Using this option wisely, you can now customize the replication properties straight at the start. What used to require several commands and a restart of the replicator right after the installation, now flows smoothly and quickly with one single command. With this release, Tungsten Replicator is closer to become a tool for mass consumption. The old installation method (which we have deprecated and renamed, to discourage anyone from using it) required time, constant attention, and it was unforgiving. The new one will let you make your mistakes freely. If something is amiss anywhere in all the servers where you are installing, it won't install and it will tell you what went wrong. This is probably my favorite feature, because it allows Tungsten to be used by less experienced users. Now it's up to the users. We have no illusion that the product is bug free, and we want to hear from users who try it and report on Issues.

World map, shaped by MySQL downloads

A few years ago, during the MySQL Conference opening keynote, two world maps of MySQL downloads were displayed. With the lights down, they made an impression.
Oddly enough, to the best of my knowledge, the downloads map has not been drawn again since then. I asked my friend and colleague Markus Popp, and he provided the data from the downloads logs, leaving the implementation to me.
A first attempt with Google Maps API produced a chart that is nice to see for a single country or town, but hardly pleasant for the entire world.

Then, I abandoned the easy path, and looked at CPAN for inspiration, and immediately found something that could solve my problem. Using GD::Map, I quickly created a world map, and after a few minutes of fiddling with the innards, I manage to plot a map that looked like what I wanted.
.
To get the red dots on a black background I simply used Gimp, and soon I had the results I was looking for.