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.

4 comments:

William Anthony said...

Hello,
I need your help. I used mysqlsandbox 3.0.47 on my development box, and then one day, it cannot boot anymore because of my mistake. So I backup my sandboxes directory and installed a clean fresh OS.
When I need to download mysqlsandbox, I just found out that mysqlsandbox was already changed to dbdeployer. So my question is how can I convert mysqlsandbox databases to dbdeployer databases?
Thanks before for your help.

Giuseppe Maxia said...

MySQL-Sandbox databases are compatible with dbdeployer, which can recognize them and to a limited extent use them (it can list, start, stop, lock, unlock, and delete them) but due to the old sandboxes lack of metadata, dbdeployer is limited in what it can do.

The best thing is migrating data from the old sandboxes and re-deploying them using dbdeployer.
MySQL sandboxes were designed to be temporary for testing, and dbdeployer follows the same principle. If you can start an old sandbox, you can use the sandbox script './my sqldump > dump.sql' and then delete the sandbox and create it again.

William Anthony said...

Hello again,
Thanks for your answer.

I have one more question, I look into sandboxes log (msandbox.err) and found out this line:

"[Warning] Could not increase number of max_open_files to more than 1024 (request: 4186)"

From what I googled, the solution is to modify systemd mysql service configuration, locate at /lib/systemd/system/mysql.service. So what I need to do for mysql instance from dbdeploy, as it's not using the systemd services.

Thanks.

Giuseppe Maxia said...

Please report issues (with a way of reproducing them) at https://github.com/datacharmer/dbdeployer/issues