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


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

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
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
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="",
, 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
                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_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_Pos: 0
           Master_SSL_Allowed: No
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

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 |