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:latestWe 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 foundThus, 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.26Now there are two versions that can be used for operations.
$ dbdeployer versions
Basedir: /home/msandbox/opt/mysql
5.7.26 8.0.16And 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:
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.
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.
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.
Please report issues (with a way of reproducing them) at https://github.com/datacharmer/dbdeployer/issues
Post a Comment