Some time ago I've noted that one of the tools I use for testing various MySQL and MariaDB cases and to reproduce potential bugs,
MySQL-Sandbox, is not updated any more. It turned out that active development switched to its port in
Go called
dbdeployer. You can find detailed information about
dbdeployer and reasons behind developing it provided by its author,
Giuseppe Maxia,
here and there. See also
this post at Percona blog for some quick review of its main features. One of the points of
dbdeployer (and reasons to use Go) is that
it is built once (per platform supported) somewhere and then binaries
are downloaded from GitHub and used everywhere, without any problems
with dependencies etc.
I've added checking
dbdeployer to my long ToDo list, as I planned to use it (if not MySQL Sandbox) for some tests and posts related to resolving typical practical problems with MariaDB GTID-based replication. Yesterday I've allocated some time to finally try it and, as usual, I've started with building it from source (as for me databases-related software that I can not build from source on my test systems is not any attractive as something new to study and use).
I was immediately surprised by the lack of instructions on how to do this at GitHub, no Makefile of any kind etc. All I was able to find is build.sh script. Correction: just check
README.md on how to build it properly, as
Giuseppe Maxia explained in the comment.
 |
| Good, regular structure is important for deployment |
Fortunately this is not the first project written in Go that I try to build (or change somehow and then build). The first one was this
replication manager (that has proper
build instructions in docs). So, I though I knew what to do. I've installed missing
golang package on my netbook with Ubuntu 14.04 that I had at hand and tried the following typical steps:
openxs@ao756:~/go$ export GOPATH=$HOME/go
openxs@ao756:~/go$ echo $GOPATH
/home/openxs/go
openxs@ao756:~/go$ go get github.com/datacharmer/dbdeployer
# github.com/datacharmer/dbdeployer/common
src/github.com/datacharmer/dbdeployer/common/strutils.go:170: undefined: sort.Slice
...
That was a bit surprising, but quick Google search shown that this could be caused by outdated (pre-1.8) version of
golang package. So,
dbdeployer requires
golang 1.8 or newer and there was no such package for my good old Ubuntu (it has some 1.2.x only). One day I'll upgrade it, but so far I am OK with 14.04 for all other testing purposes, so I had to give up on the idea to build from source temporary.
Today during few free minutes I've retried on my good old desktop box with Fedora 27 (where I surely built some Go project(s) successfully):
[openxs@fc23 go]$ uname -a
Linux fc23 4.18.19-100.fc27.x86_64 #1 SMP Wed Nov 14 22:04:34 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
[openxs@fc23 ~]$ ls go
pkg src
[openxs@fc23 ~]$ echo $GOPATH
[openxs@fc23 ~]$ export GOPATH=$HOME/go
[openxs@fc23 ~]$ cd go
[openxs@fc23 go]$ go versiongo version go1.9.7 linux/amd64
This environment should work for build, so I've proceeded with:
[openxs@fc23 go]$ go get github.com/datacharmer/dbdeployer
[openxs@fc23 go]$ ls src/github.com/datacharmer/ jmoiron/ nsf/ tanji/
go-sql-driver/ mattn/ ogier/
[openxs@fc23 go]$ ls src/github.com/datacharmer/dbdeployer/
abbreviations/ compare/ docs/ mkreadme/ test/
.build/ concurrent/ .git/ rest/ unpack/
cmd/ cookbook/ .github/ sandbox/ vendor/
common/ defaults/ globals/ scripts/
Now let's try that
scripts/build.sh with
linux as a parameter, as it's a way to build Linux binaries based on what I found:
[openxs@fc23 go]$ MKDOCS=1 src/github.com/datacharmer/dbdeployer/scripts/build.sh linux
+ env GOOS=linux GOARCH=386 go build --tags docs -o dbdeployer-1.17.0-docs.linux .
+ env GOOS=linux GOARCH=amd64 go build -o sort_versions.linux sort_versions.go
/home/openxs/go/src/github.com/datacharmer/dbdeployer
-rwxrwxr-x. 1 openxs openxs 8.1M Jan 14 10:27 dbdeployer-1.17.0-docs.linux
-rw-rw-r--. 1 openxs openxs 3.0M Jan 14 10:27 dbdeployer-1.17.0-docs.linux.tar.gz
[openxs@fc23 go]$ ls
bin pkg src
[openxs@fc23 go]$ ls bin
dbdeployer
[openxs@fc23 go]$ bin/dbdeployer --version
dbdeployer version 1.17.0
Now we know how to build
dbdeployer from source, if needed. If some dependencies are missing you'll be informed and similar
go get ... command should allow to install it.
I was somewhat surprised to see MariaDB NOT mentioned at all in
README.md. It says:
"DBdeployer is a tool that deploys MySQL database servers easily."
while good old
MySQL-Sandbox also mentions MariaDB explicitly:
"This package is a sandbox for testing features under any version of
MySQL from 3.23 to 8.0 (and any version of MariaDB.)"
So, my idea was to double check that
dbdeployer is both MySQL-Sandbox compatible and MariaDB compatible (it is). I have several sandboxes already created in the past. I also have MariaDB 10.2.21 .tar.gz binaries that I want to use with
dbdeployer for further testing:
[openxs@fc23 go]$ ls ~/sandboxes/
clear_all rsandbox_mariadb-10_0_19 send_kill_all test_replication
plugin.conf rsandbox_mariadb-10_1_12 start_all use_all
restart_all rsandbox_mysql-8_0_12 status_all
rsandbox_8_0_12 sandbox_action stop_all
[openxs@fc23 go]$ ls ~/*.tar.gz
/home/openxs/galera-25.3.22-x86_64.tar.gz
/home/openxs/galera-25.3.24-x86_64.tar.gz
/home/openxs/galera-25.3.25-glibc_214-x86_64.tar.gz
/home/openxs/mariadb-10.2.12-linux-x86_64.tar.gz
/home/openxs/mariadb-10.2.21-linux-x86_64.tar.gz
With
dbdeployer one has to unpack
.tar.gz first with
dbdeployer unpack command. So, I tried it immediately:
[openxs@fc23 go]$ bin/dbdeployer unpack ~/mariadb-10.2.21-linux-x86_64.tar.gz
directory '/home/openxs/opt/mysql' not found
You should create it or provide an alternate base directory using --sandbox-binary
It seems the tool now wants to use
~/opt/mysql as a directory to unpack to, while MySQL_Sandbox silently used
~:
[openxs@fc23 go]$ ls ~ | grep 8.0
8.0.12
I made a lame try to force it to use ~, but failed for the reason I was too lazy to study:
[openxs@fc23 go]$ bin/dbdeployer --sandbox-binary=/home/openxs unpack /home/openxs/mariadb-10.2.21-linux-x86_64.tar.gzUnpacking tarball /home/openxs/mariadb-10.2.21-linux-x86_64.tar.gz to $HOME/10.2.21
.........100.........200....&tar.Header{Name:"mariadb-10.2.21-linux-x86_64/mysql-test/mysql-test-run", Mode:511, Uid:1021, Gid:1004, Size:0, ModTime:time.Time{wall:0x0, ext:63681810892, loc:(*time.Location)(0xa47aa0)}, Typeflag:0x32, Linkname:"./mysql-test-run.pl", Uname:"dbart", Gname:"my", Devmajor:0, Devminor:0, AccessTime:time.Time{wall:0x0, ext:0, loc:(*time.Location)(nil)}, ChangeTime:time.Time{wall:0x0, ext:0, loc:(*time.Location)(nil)}, Xattrs:map[string]string(nil)}
#ERROR: symlink ./mysql-test-run.pl mariadb-10.2.21-linux-x86_64/mysql-test/mysql-test-run: file exists
I just created
~/opt/mysql and proceeded with default configuration. After
unpack step completed successfully I've proceeded with
deploy step to create new
replication sandbox:
[openxs@fc23 go]$ bin/dbdeployer unpack /home/openxs/mariadb-10.2.21-linux-x86_64.tar.gz
Unpacking tarball /home/openxs/mariadb-10.2.21-linux-x86_64.tar.gz to $HOME/opt/mysql/10.2.21
.........100.........200.........300.........400.........500.........600.........700.........800... ...
Renaming directory /home/openxs/opt/mysql/mariadb-10.2.21-linux-x86_64 to /home/openxs/opt/mysql/10.2.21
[openxs@fc23 go]$ bin/dbdeployer deploy replication 10.2.21
Installing and starting master
. sandbox server started
Installing and starting slave1
. sandbox server started
Installing and starting slave2
. sandbox server started
$HOME/sandboxes/rsandbox_10_2_21/initialize_slaves
initializing slave 1
initializing slave 2
Replication directory installed in $HOME/sandboxes/rsandbox_10_2_21
run 'dbdeployer usage multiple' for basic instructions'
We have access to nice enough documentation:
[openxs@fc23 go]$ bin/dbdeployer usage multiple
USING MULTIPLE SERVER SANDBOX
On a replication sandbox, you have the same commands (run "dbdeployer usage single"),
with an "_all" suffix, meaning that you propagate the command to all the members.
Then you have "./m" as a shortcut to use the master, "./s1" and "./s2" to access
the slaves (and "s3", "s4" ... if you define more).
In group sandboxes without a master slave relationship (group replication and
multiple sandboxes) the nodes can be accessed by ./n1, ./n2, ./n3, and so on.
start_all [options] > starts all nodes
status_all > get the status of all nodes
restart_all [options] > restarts all nodes
stop_all > stops all nodes
use_all "SQL" > runs a SQL statement in all nodes
use_all_masters "SQL" > runs a SQL statement in all masters
use_all_slaves "SQL" > runs a SQL statement in all slaves
clear_all > stops all nodes and removes all data
m > invokes MySQL client in the master
s1, s2, n1, n2 > invokes MySQL client in slave 1, 2, node 1, 2
The scripts "check_slaves" or "check_nodes" give the status of replication in the sandbox.
Typical sandbox directory (with some differences like
use_all_slaves etc) is created in
~/sandboxes/ and shortcut commands work as expected:
[openxs@fc23 go]$ cd ~/sandboxes/rsandbox_10_2_21/
[openxs@fc23 rsandbox_10_2_21]$ ls
check_slaves n2 sbdescription.json test_sb_all
clear_all node1 send_kill_all use_all
initialize_slaves node2 start_all use_all_masters
m restart_all status_all use_all_slaves
master s1 stop_all
n1 s2 test_replication
[openxs@fc23 rsandbox_10_2_21]$ ls master/
add_option init_db restart show_binlog status use
clear load_grants sbdescription.json show_log stop
data my sb_include show_relaylog test_sb
grants.mysql my.sandbox.cnf send_kill start tmp
[openxs@fc23 rsandbox_10_2_21]$ ls ../rsandbox_mariadb-10_1_12/
check_slaves m node1 s2 test_replication
clear_all master node2 send_kill_all use_all
connection.json n1 README start_all
default_connection.json n2 restart_all status_all
initialize_slaves n3 s1 stop_all
[openxs@fc23 rsandbox_10_2_21]$ ls ../rsandbox_mariadb-10_1_12/master/
add_option default_connection.json my send_kill tmp
change_paths grants_5_7_6.mysql mycli show_binlog use
change_ports grants.mysql my.sandbox.cnf show_relaylog USING
clear json_in_db proxy_start start
connection.json load_grants README status
data msb restart stop
[openxs@fc23 rsandbox_10_2_21]$ ./status_all
REPLICATION /home/openxs/sandboxes/rsandbox_10_2_21
master : master on - port 23322 (23322)
node1 : node1 on - port 23323 (23323)
node2 : node2 on - port 23324 (23324)
[openxs@fc23 rsandbox_10_2_21]$ ./use_all "show variables like 'gtid%'"
# master
Variable_name Value
gtid_binlog_pos 0-100-12
gtid_binlog_state 0-100-12
gtid_current_pos 0-100-12
gtid_domain_id 0
gtid_ignore_duplicates OFF
gtid_seq_no 0
gtid_slave_pos
gtid_strict_mode OFF
# server: 1
Variable_name Value
gtid_binlog_pos
gtid_binlog_state
gtid_current_pos 0-100-12
gtid_domain_id 0
gtid_ignore_duplicates OFF
gtid_seq_no 0
gtid_slave_pos 0-100-12
gtid_strict_mode OFF
# server: 2
Variable_name Value
gtid_binlog_pos
gtid_binlog_state
gtid_current_pos 0-100-12
gtid_domain_id 0
gtid_ignore_duplicates OFF
gtid_seq_no 0
gtid_slave_pos 0-100-12
gtid_strict_mode OFF
[openxs@fc23 rsandbox_10_2_21]$ ./m
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.2.21-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
master [localhost:23322] {msandbox} ((none)) > show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 2835 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
master [localhost:23322] {msandbox} ((none)) > show variables like 'gtid%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| gtid_binlog_pos | 0-100-12 |
| gtid_binlog_state | 0-100-12 |
| gtid_current_pos | 0-100-12 |
| gtid_domain_id | 0 |
| gtid_ignore_duplicates | OFF |
| gtid_seq_no | 0 |
| gtid_slave_pos | |
| gtid_strict_mode | OFF |
+------------------------+----------+
8 rows in set (0.00 sec)
master [localhost:23322] {msandbox} ((none)) > exit
Bye
For my further tests I needed slaves to have
log_slave_updates enabled and
gtid_strict_mode=ON. So, I've added these settings to
my.sandbox.cnf in
node1 and
node2 subdirectories for both slaves and restarted them:
[openxs@fc23 rsandbox_10_2_21]$ ./restart_all# executing 'stop' on /home/openxs/sandboxes/rsandbox_10_2_21
stop /home/openxs/sandboxes/rsandbox_10_2_21/node1
stop /home/openxs/sandboxes/rsandbox_10_2_21/node2
stop /home/openxs/sandboxes/rsandbox_10_2_21/master
# executing 'start' on /home/openxs/sandboxes/rsandbox_10_2_21
executing 'start' on master
. sandbox server started
executing 'start' on slave 1
. sandbox server started
executing 'start' on slave 2
. sandbox server started
I need a table to play with and I want to check that slaves are in sync:
[openxs@fc23 rsandbox_10_2_21]$ ./m
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.21-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
master [localhost:23322] {msandbox} ((none)) > use test
Database changed
master [localhost:23322] {msandbox} (test) > create table t1(id int primary key, c1 int);
Query OK, 0 rows affected (0.17 sec)
master [localhost:23322] {msandbox} (test) > exit
Bye
[openxs@fc23 rsandbox_10_2_21]$ ./use_all "show variables like 'gtid%'"
# master
Variable_name Value
gtid_binlog_pos 0-100-13
gtid_binlog_state 0-100-13
gtid_current_pos 0-100-13
gtid_domain_id 0
gtid_ignore_duplicates OFF
gtid_seq_no 0
gtid_slave_pos
gtid_strict_mode OFF
# server: 1
Variable_name Value
gtid_binlog_pos 0-100-13
gtid_binlog_state 0-100-13
gtid_current_pos 0-100-13
gtid_domain_id 0
gtid_ignore_duplicates OFF
gtid_seq_no 0
gtid_slave_pos 0-100-13
gtid_strict_mode ON# server: 2
Variable_name Value
gtid_binlog_pos 0-100-13
gtid_binlog_state 0-100-13
gtid_current_pos 0-100-13
gtid_domain_id 0
gtid_ignore_duplicates OFF
gtid_seq_no 0
gtid_slave_pos 0-100-13
gtid_strict_mode ON[openxs@fc23 rsandbox_10_2_21]$
Note the value of
gtid_current_pos on master and
gtid_slave_pos on each slave. They are the same and slaves are in sync. If you want to find out more about the format of GTIDs in MariaDB or all that
gtid% server variables, please, check
this KB article.
* * *
To summarize,
dbdeployer is a nice port of
MySQL-Sandbox into Go, with some additional features. It can be easily built from source if you have
golang version 1.8 or newer (or just downloaded if you have not). Sandboxes created with
dbdeployer may co-exists with older sandboxes in the same default directory (but
.tar.gz files are unpacked into different directory by default). It still works well with MariaDB. I am going to use replication sandboxes built with it for some further testing of various real life use cases and problems of MariaDB's GTIDs implementation (that may be presented in further posts).
