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.