Technology sharing | how to use dbdeployer to quickly build MySQL test environment

Author: Yu Zhenxing

I. Introduction to tools

dbdeployer is a very powerful deployment tool for database test environment, which can deploy different architectures and versions of database environment with one click.
For example: MySQL master-slave replication, GTID mode replication, MySQL group replication (single master mode, multi master mode, etc.)
The complete database type support and version can be viewed by using the dbdeployer admin capabilities command after the installation of dbdeployer. The following are the currently supported database and component types

Oracle MySQL
Percona MySQL
MariaDB
TiDB
MySQL NDB Cluster
Percona XtraDB Cluster
mysql-shell

This paper mainly introduces how to quickly build MySQL test environment based on dbdeployer and how to use dbdeployer. The complete functional features and usage can be found in the dbdeployer documentation manual. The dbdeployer documentation manual describes the features and various usage of the tool in detail. You can see the relevant links at the end of the article.

II. Tool installation

This article takes the deployment under MacOS as an example. The installation and deployment of Linux platform is basically similar (the tool does not provide the Windows version), which can be accessed

https://github.com/datacharme...

Get the latest version of dbdeployer download link. In the following, shell > represents the command line prompt

## Download the latest package
shell> wget https://github.com/datacharmer/dbdeployer/releases/download/v1.42.0/dbdeployer-1.42.0.osx.tar.gz

## After the software is decompressed, there is only a single compiled executable file
shell> tar xzvf dbdeployer-1.42.0.osx.tar.gz

## Copy the extracted software to the system executable directory for easy use
shell> chmod +x dbdeployer-1.42.0.osx
shell> mv dbdeployer-1.42.0.osx /usr/local/bin/dbdeployer

## Verify installation succeeded
shell> dbdeployer --version
shell> dbdeployer --help

III. tool configuration

The software uses the current user's $HOME/.dbdeployer/config.json as the configuration file by default. This file is not generated by default. You can use the dbdeployer defaults export path command to export a copy and modify it. Next, I only modify the sandbox binary parameter in the configuration file to specify the directory as a custom $home / sandboxes / mysql_

## Creating MySQL package and decompressed software directory
shell> mkdir -p ~/sandboxes/{mysql_package,mysql_base}

## Change the sandbox binary parameter in the default configuration to the created directory path
## In this part, you can also use dbdeployer defaults export /Users/yuzhenxing/.dbdeployer/config.json to export the configuration file first, and then vim to edit and modify it manually
shell> dbdeployer defaults update sandbox-binary $HOME/sandboxes/mysql_base

## View modified configuration information
## The configuration contains initialization information of all kinds of MySQL, which can be adjusted flexibly according to the actual situation
shell> dbdeployer defaults show
# Configuration file: /Users/yuzhenxing/.dbdeployer/config.json
{
     "version": "1.39.0",
     "sandbox-home": "$HOME/sandboxes",
     "sandbox-binary": "$HOME/sandboxes/mysql_base",
     "use-sandbox-catalog": true,
     "log-sb-operations": false,
     "log-directory": "/Users/yuzhenxing/sandboxes/logs",
     "cookbook-directory": "recipes",
     "shell-path": "/bin/bash",
     "master-slave-base-port": 11000,
     "group-replication-base-port": 12000,
     "group-replication-sp-base-port": 13000,
     "fan-in-replication-base-port": 14000,
     "all-masters-replication-base-port": 15000,
     "multiple-base-port": 16000,
     "pxc-base-port": 18000,
     "ndb-base-port": 19000,
     "ndb-cluster-port": 20000,
     "group-port-delta": 125,
     "mysqlx-port-delta": 10000,
     "admin-port-delta": 11000,
     "master-name": "master",
     "master-abbr": "m",
     "node-prefix": "node",
     "slave-prefix": "slave",
     "slave-abbr": "s",
     "sandbox-prefix": "msb_",
     "imported-sandbox-prefix": "imp_msb_",
     "master-slave-prefix": "rsandbox_",
     "group-prefix": "group_msb_",
     "group-sp-prefix": "group_sp_msb_",
     "multiple-prefix": "multi_msb_",
     "fan-in-prefix": "fan_in_msb_",
     "all-masters-prefix": "all_masters_msb_",
     "reserved-ports": [
         1186,
         3306,
         33060,
         33062
     ],
     "remote-repository": "https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata",
     "remote-index-file": "available.json",
     "remote-completion-url": "https://raw.githubusercontent.com/datacharmer/dbdeployer/master/docs/dbdeployer_completion.sh",
     "remote-tarball-url": "https://raw.githubusercontent.com/datacharmer/dbdeployer/master/downloads/tarball_list.json",
     "pxc-prefix": "pxc_msb_",
     "ndb-prefix": "ndb_msb_",
     "timestamp": "Thu Nov 21 15:58:56 CST 2019"
 }

IV. basic use

Before using, we need to download the MySQL package of the corresponding operating system. The dbdeployer software provides the management command to download the MySQL software. We can download it through the dbdeployer tool, or download the MySQL package ourselves. We place the downloaded software in the directory ~ / sandboxes / mysql_packagethat has been created before. Take MacOS system as an example, Linux is similar.

1. Download MySQL using dbdeployer

## View the MySQL package that dbdeployer tool supports downloading
shell> dbdeployer downloads list
Available tarballs  ()
                       name                            OS     version     flavor        size   minimal
--------------------------------------------------- -------- --------- ------------- -------- ---------
 tidb-master-darwin-amd64.tar.gz                     Darwin     3.0.0   tidb           26 MB
 mysql-5.7.26-macos10.14-x86_64.tar.gz               Darwin    5.7.26   mysql         337 MB
 mysql-8.0.16-macos10.14-x86_64.tar.gz               Darwin    8.0.16   mysql         153 MB
 mysql-8.0.15-macos10.14-x86_64.tar.gz               Darwin    8.0.15   mysql         139 MB
 mysql-5.7.25-macos10.14-x86_64.tar.gz               Darwin    5.7.25   mysql         337 MB
 mysql-5.6.41-macos10.13-x86_64.tar.gz               Darwin    5.6.41   mysql         176 MB
 mysql-5.5.53-osx10.9-x86_64.tar.gz                  Darwin    5.5.53   mysql         114 MB
 mysql-5.1.73-osx10.6-x86_64.tar.gz                  Darwin    5.1.73   mysql          82 MB
 mysql-5.0.96-osx10.5-x86_64.tar.gz                  Darwin    5.0.96   mysql          61 MB
 mysql-cluster-8.0.16-dmr-macos10.14-x86_64.tar.gz   Darwin    8.0.16   ndb           252 MB
 mysql-8.0.17-macos10.14-x86_64.tar.gz               Darwin    8.0.17   mysql         155 MB
 mysql-5.7.27-macos10.14-x86_64.tar.gz               Darwin    5.7.27   mysql         337 MB
 mysql-cluster-gpl-7.6.10-macos10.14-x86_64.tar.gz   Darwin    7.6.10   ndb           482 MB
 mysql-cluster-8.0.17-rc-macos10.14-x86_64.tar.gz    Darwin    8.0.17   ndb           255 MB
 mysql-cluster-gpl-7.6.11-macos10.14-x86_64.tar.gz   Darwin    7.6.11   ndb           482 MB
 mysql-shell-8.0.17-macos10.14-x86-64bit.tar.gz      Darwin    8.0.17   mysql-shell    17 MB
 mysql-5.7.28-macos10.14-x86_64.tar.gz               Darwin    5.7.28   mysql         374 MB
 mysql-8.0.18-macos10.14-x86_64.tar.gz               Darwin    8.0.18   mysql         166 MB

## Download and unzip the specified package
shell> cd sandboxes/mysql_package

shell> dbdeployer downloads get-unpack mysql-8.0.17-macos10.14-x86_64.tar.gz

2. Download MySQL by yourself

Visit https://downloads.mysql.com/a... Different versions of MySQL can be downloaded

## Download and unzip MySQL package
shell> cd sandboxes/mysql_package

shell> wget https://downloads.mysql.com/archives/get/file/mysql-8.0.17-macos10.14-x86_64.tar.gz

shell> dbdeployer unpack mysql-8.0.17-macos10.14-x86_64.tar.gz

3. Rapid deployment instance

The following example simply uses several commands to implement MySQL deployment of various architectures. For detailed usage, see the dbdeployer document link at the end of the article.

## Deploy a single node MySQL, open GTID and specify character set
## Parameters such as GTID and character can also be specified in MySQL configuration file after deployment
## Note: the deployed database runs automatically by default. You can specify the -- skip start parameter to initialize but not start
shell> dbdeployer deploy single 8.0.17 --gtid --my-cnf-options="character_set_server=utf8mb4"

## Deploy a master-slave replication MySQL (initialize 3 nodes by default, one master and two slaves)
shell> dbdeployer deploy replication 8.0.17 --repl-crash-safe --gtid --my-cnf-options="character_set_server=utf8mb4"

## Deploy a single master Mgr (initialize 3 nodes by default)
shell> dbdeployer deploy --topology=group replication 8.0.17 --single-primary

## Deploy a multi master Mgr (initialize 3 nodes by default)
shell> dbdeployer deploy --topology=all-masters replication 8.0.17

4. Instance group operation

After one click deployment, the data directory corresponding to each instance group will be generated in the $HOME/sandboxes directory, which contains the following information (some information)

One click start stop script for all instances of this group
One click login database script
One click Reset script for all instances of the group (clear all test data and re initialize to a new master-slave)
The data directory of the master-slave instance (the master database is the master database, and the slave databases are node1 and node2, respectively)
- Profile of each instance
- Default user authorization command
- Individual start stop instance command
- binlog and relaylog parsing commands

Use example

## View the status of all instances of this group
shell> cd ~/sandboxes/rsandbox_8_0_17
shell> ./status_all
REPLICATION  /Users/yuzhenxing/sandboxes/rsandbox_8_0_17
master : master on  -  port    20718 (20718)
node1 : node1 on  -  port    20719 (20719)
node2 : node2 on  -  port    20720 (20720)

## One click restart all instances of the group
shell> ./restart_all
stop /Users/yuzhenxing/sandboxes/rsandbox_8_0_17/master
stop /Users/yuzhenxing/sandboxes/rsandbox_8_0_17/node1
stop /Users/yuzhenxing/sandboxes/rsandbox_8_0_17/node2
executing 'start' on master
. sandbox server started
executing 'start' on slave 1
. sandbox server started
executing 'start' on slave 2
. sandbox server started

## Restart an instance of the group separately
shell> cd ~/sandboxes/rsandbox_8_0_17/master
shell> ./restart

## Log in to the specified instance
shell> ./use

V. dbdeployer common management commands

The following are the common commands summarized in the process of using dbdeployer. See the dbdeployer document link at the end of the article for details.

## View various database versions and version features supported by dbdeployer (the output information is too long or too much, and the output result has been omitted)
shell> dbdeployer admin capabilities
shell> dbdeployer admin capabilities percona
shell> dbdeployer admin capabilities mysql


## Use dbdeployer to view the basic information of the specified version of MySQL
shell> dbdeployer downloads get-by-version 5.7 --newest --dry-run

Name:          mysql-5.7.28-macos10.14-x86_64.tar.gz
Short version: 5.7
Version:       5.7.28
Flavor:        mysql
OS:            Darwin
URL:           https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-macos10.14-x86_64.tar.gz
Checksum:      MD5: 00c2cabb06d8573b5b52d3dd1576731e
Size:          374 MB

## View all MySQL basic information currently installed
## Including schema type, version, port, etc. (output information is too long or too much, output result has been omitted)
shell> dbdeployer sandboxes --full-info

## View the operation of MySQL in each group
shell> dbdeployer global status
MULTIPLE  /Users/yuzhenxing/sandboxes/all_masters_msb_8_0_17
node1 : node1 on  -  port    23818 (23818)
node2 : node2 on  -  port    23819 (23819)
node3 : node3 on  -  port    23820 (23820)

MULTIPLE  /Users/yuzhenxing/sandboxes/group_sp_msb_8_0_17
node1 : node1 off  -   (22718)
node2 : node2 off  -   (22719)
node3 : node3 off  -   (22720)

## Batch stop all running MySQL (output information is too long or too much, output result has been omitted)
shell> dbdeployer global stop

## Delete the deployed MySQL instance
## If it is running, it will stop first, and then clear the instance related directory
shell> dbdeployer delete msb_8_0_17

## The instance can be locked to prevent deletion by mistake
## Dbdeployer sandboxes -- full info command to find out which group of instances are locked
shell> dbdeployer admin lock group_sp_msb_8_0_17
shell> dbdeployer delete group_sp_msb_8_0_17
shell> dbdeployer admin unlock group_sp_msb_8_0_17
shell> dbdeployer sandboxes --full-info

Other more dbdeployer use and management commands can be viewed by executing dbdeployer or dbdeployer --help. For more detailed usage and examples, see the official dbdeployer manual link at the end of this article.

Vi. relevant links

Official Manual of dbdeployer
- https://github.com/datacharme...
Download links of MySQL historical versions
- https://downloads.mysql.com/a...
Functional features of dbdeployer
- https://github.com/datacharme...

Posted by timbr8ks on Wed, 27 Nov 2019 02:11:44 -0800

Programmer Group