DBdeployer is a tool that deploys MySQL database servers easily. This is a port of MySQL-Sandbox, originally written in Perl, and re-designed from the ground up in Go. See the features comparison for more detail.
Documentation updated for version 1.14.0 (11-Nov-2018 07:34 UTC)
The installation is simple, as the only thing you will need is a binary executable for your operating system. Get the one for your O.S. from dbdeployer releases and place it in a directory in your $PATH. (There are no binaries for Windows. See the features list for more info.)
For example:
$ VERSION=1.14.0
$ OS=linux
$ origin=https://github.com/datacharmer/dbdeployer/releases/download/$VERSION
$ wget $origin/dbdeployer-$VERSION.$OS.tar.gz
$ tar -xzf dbdeployer-$VERSION.$OS.tar.gz
$ chmod +x dbdeployer-$VERSION.$OS
$ sudo mv dbdeployer-$VERSION.$OS /usr/local/bin/dbdeployer
Of course, there are prerequisites: your machine must be able to run the MySQL server. Be aware that version 5.6+ and higher require some libraries that are not installed by default in all flavors of Linux (libnuma, libaio.)
(See this ASCIIcast for a demo of its operations.)
With dbdeployer, you can deploy a single sandbox, or many sandboxes at once, with or without replication.
The main command is deploy with its subcommands single, replication, and multiple, which work with MySQL tarball that have been unpacked into the sandbox-binary directory (by default, $HOME/opt/mysql.)
To use a tarball, you must first run the unpack command, which will unpack the tarball into the right directory.
For example:
$ dbdeployer unpack mysql-8.0.4-rc-linux-glibc2.12-x86_64.tar.gz
Unpacking tarball mysql-8.0.4-rc-linux-glibc2.12-x86_64.tar.gz to $HOME/opt/mysql/8.0.4
.........100.........200.........292
$ dbdeployer deploy single 8.0.4
Database installed in $HOME/sandboxes/msb_8_0_4
. sandbox server started
The program doesn't have any dependencies. Everything is included in the binary. Calling dbdeployer without arguments or with --help will show the main help screen.
$ dbdeployer --version
dbdeployer version 1.14.0
$ dbdeployer -h
dbdeployer makes MySQL server installation an easy task.
Runs single, multiple, and replicated sandboxes.
Usage:
dbdeployer [command]
Available Commands:
admin sandbox management tasks
defaults tasks related to dbdeployer defaults
delete delete an installed sandbox
deploy deploy sandboxes
global Runs a given command in every sandbox
help Help about any command
sandboxes List installed sandboxes
unpack unpack a tarball into the binary directory
usage Shows usage of installed sandboxes
versions List available versions
Flags:
--config string configuration file (default "$HOME/.dbdeployer/config.json")
-h, --help help for dbdeployer
--sandbox-binary string Binary repository (default "$HOME/opt/mysql")
--sandbox-home string Sandbox deployment directory (default "$HOME/sandboxes")
--version version for dbdeployer
Use "dbdeployer [command] --help" for more information about a command.
The flags listed in the main screen can be used with any commands.
The flags --my-cnf-options and --init-options can be used several times.
If you don't have any tarballs installed in your system, you should first unpack it (see an example above).
$ dbdeployer unpack -h
If you want to create a sandbox from a tarball (.tar.gz or .tar.xz), you first need to unpack it
into the sandbox-binary directory. This command carries out that task, so that afterwards
you can call 'deploy single', 'deploy multiple', and 'deploy replication' commands with only
the MySQL version for that tarball.
If the version is not contained in the tarball name, it should be supplied using --unpack-version.
If there is already an expanded tarball with the same version, a new one can be differentiated with --prefix.
Usage:
dbdeployer unpack MySQL-tarball [flags]
Aliases:
unpack, extract, untar, unzip, inflate, expand
Examples:
$ dbdeployer unpack mysql-8.0.4-rc-linux-glibc2.12-x86_64.tar.gz
Unpacking tarball mysql-8.0.4-rc-linux-glibc2.12-x86_64.tar.gz to $HOME/opt/mysql/8.0.4
$ dbdeployer unpack --prefix=ps Percona-Server-5.7.21-linux.tar.gz
Unpacking tarball Percona-Server-5.7.21-linux.tar.gz to $HOME/opt/mysql/ps5.7.21
$ dbdeployer unpack --unpack-version=8.0.18 --prefix=bld mysql-mybuild.tar.gz
Unpacking tarball mysql-mybuild.tar.gz to $HOME/opt/mysql/bld8.0.18
Flags:
-h, --help help for unpack
--prefix string Prefix for the final expanded directory
--shell Unpack a shell tarball into the corresponding server directory
--target-server string Uses a different server to unpack a shell tarball
--unpack-version string which version is contained in the tarball
--verbosity int Level of verbosity during unpack (0=none, 2=maximum) (default 1)
The easiest command is deploy single, which installs a single sandbox.
$ dbdeployer deploy -h
Deploys single, multiple, or replicated sandboxes
Usage:
dbdeployer deploy [command]
Available Commands:
multiple create multiple sandbox
replication create replication sandbox
single deploys a single sandbox
Flags:
--base-port int Overrides default base-port (for multiple sandboxes)
--binary-version string Specifies the version when the basedir directory name does not contain it (i.e. it is not x.x.xx)
--bind-address string defines the database bind-address (default "127.0.0.1")
--concurrent Runs multiple sandbox deployments concurrently
--custom-mysqld string Uses an alternative mysqld (must be in the same directory as regular mysqld)
-p, --db-password string database password (default "msandbox")
-u, --db-user string database user (default "msandbox")
--defaults strings Change defaults on-the-fly (--defaults=label:value)
--disable-mysqlx Disable MySQLX plugin (8.0.11+)
--enable-general-log Enables general log for the sandbox (MySQL 5.1+)
--enable-mysqlx Enables MySQLX plugin (5.7.12+)
--expose-dd-tables In MySQL 8.0+ shows data dictionary tables
--force If a destination sandbox already exists, it will be overwritten
--gtid enables GTID
-h, --help help for deploy
--history-dir string Where to store mysql client history (default: in sandbox directory)
--init-general-log uses general log during initialization (MySQL 5.1+)
-i, --init-options strings mysqld options to run during initialization
--keep-server-uuid Does not change the server UUID
--log-directory string Where to store dbdeployer logs (default "$HOME/sandboxes/logs")
--log-sb-operations Logs sandbox operations to a file
--my-cnf-file string Alternative source file for my.sandbox.cnf
-c, --my-cnf-options strings mysqld options to add to my.sandbox.cnf
--native-auth-plugin in 8.0.4+, uses the native password auth plugin
--port int Overrides default port
--post-grants-sql strings SQL queries to run after loading grants
--post-grants-sql-file string SQL file to run after loading grants
--pre-grants-sql strings SQL queries to run before loading grants
--pre-grants-sql-file string SQL file to run before loading grants
--remote-access string defines the database access (default "127.%")
--repl-crash-safe enables Replication crash safe
--rpl-password string replication password (default "rsandbox")
--rpl-user string replication user (default "rsandbox")
--sandbox-directory string Changes the default sandbox directory
--skip-load-grants Does not load the grants
--skip-report-host Does not include report host in my.sandbox.cnf
--skip-report-port Does not include report port in my.sandbox.cnf
--skip-start Does not start the database server
--use-template strings [template_name:file_name] Replace existing template with one from file
$ dbdeployer deploy single -h
single installs a sandbox and creates useful scripts for its use.
MySQL-Version is in the format x.x.xx, and it refers to a directory named after the version
containing an unpacked tarball. The place where these directories are found is defined by
--sandbox-binary (default: $HOME/opt/mysql.)
For example:
dbdeployer deploy single 5.7 # deploys the latest release of 5.7.x
dbdeployer deploy single 5.7.21 # deploys a specific release
dbdeployer deploy single /path/to/5.7.21 # deploys a specific release in a given path
For this command to work, there must be a directory $HOME/opt/mysql/5.7.21, containing
the binary files from mysql-5.7.21-$YOUR_OS-x86_64.tar.gz
Use the "unpack" command to get the tarball into the right directory.
Usage:
dbdeployer deploy single MySQL-Version [flags]
Flags:
-h, --help help for single
--master Make the server replication ready
If you want more than one sandbox of the same version, without any replication relationship, use the deploy multiple command with an optional --nodes flag (default: 3).
$ dbdeployer deploy multiple -h
Creates several sandboxes of the same version,
without any replication relationship.
For this command to work, there must be a directory $HOME/opt/mysql/5.7.21, containing
the binary files from mysql-5.7.21-$YOUR_OS-x86_64.tar.gz
Use the "unpack" command to get the tarball into the right directory.
Usage:
dbdeployer deploy multiple MySQL-Version [flags]
Examples:
$ dbdeployer deploy multiple 5.7.21
Flags:
-h, --help help for multiple
-n, --nodes int How many nodes will be installed (default 3)
The deploy replication command will install a master and two or more slaves, with replication started. You can change the topology to group and get three nodes in peer replication, or compose multi-source topologies with all-masters or fan-in.
$ dbdeployer deploy replication -h
The replication command allows you to deploy several nodes in replication.
Allowed topologies are "master-slave" for all versions, and "group", "all-masters", "fan-in"
for 5.7.17+.
For this command to work, there must be a directory $HOME/opt/mysql/5.7.21, containing
the binary files from mysql-5.7.21-$YOUR_OS-x86_64.tar.gz
Use the "unpack" command to get the tarball into the right directory.
Usage:
dbdeployer deploy replication MySQL-Version [flags]
Examples:
$ dbdeployer deploy replication 5.7 # deploys highest revision for 5.7
$ dbdeployer deploy replication 5.7.21 # deploys a specific revision
$ dbdeployer deploy replication /path/to/5.7.21 # deploys a specific revision in a given path
# (implies topology = master-slave)
$ dbdeployer deploy --topology=master-slave replication 5.7
# (explicitly setting topology)
$ dbdeployer deploy --topology=group replication 5.7
$ dbdeployer deploy --topology=group replication 8.0 --single-primary
$ dbdeployer deploy --topology=all-masters replication 5.7
$ dbdeployer deploy --topology=fan-in replication 5.7
Flags:
-h, --help help for replication
--master-ip string Which IP the slaves will connect to (default "127.0.0.1")
--master-list string Which nodes are masters in a multi-source deployment (default "1,2")
-n, --nodes int How many nodes will be installed (default 3)
--repl-history-dir uses the replication directory to store mysql client history
--semi-sync Use semi-synchronous plugin
--single-primary Using single primary for group replication
--slave-list string Which nodes are slaves in a multi-source deployment (default "3")
-t, --topology string Which topology will be installed (default "master-slave")
Several examples of dbdeployer usages are listed in ./cookbook.
* single.sh
* replication-master-slave.sh
* replication-group-multi-primary.sh
* replication-group-single-primary.sh
* replication-all-masters.sh
* replication-fan-in.sh
* show-sandboxes.sh
* operations-single.sh
* operations-replication.sh
* operations-restart.sh
* upgrade.sh
* delete-all.sh
See cookbook/README.md for more information.
dbdeployer expects to get the binaries from $HOME/opt/mysql/x.x.xx. For example, when you run the command dbdeployer deploy single 8.0.11, you must have the binaries for MySQL 8.0.11 expanded into a directory named $HOME/opt/mysql/8.0.11.
If you want to keep several directories with the same version, you can differentiate them using a prefix:
$HOME/opt/mysql/
8.0.11
lab_8.0.11
ps_8.0.11
myown_8.0.11
In the above cases, running dbdeployer deploy single lab_8.0.11 will do what you expect, i.e. dbdeployer will use the binaries in lab_8.0.11 and recognize 8.0.11 as the version for the database.
When the extracted tarball directory name that you want to use doesn't contain the full version number (such as /home/dbuser/build/path/5.7-extra) you need to provide the version using the option --binary-version. For example:
dbdeployer deploy single 5.7-extra \
--sandbox-binary=/home/dbuser/build/path \
--binary-version=5.7.22
In the above command, --sandbox-binary indicates where to search for the binaries, 5.7-extra is where the binaries are, and --binary-version indicates which version should be used.
Just to be clear, dbdeployer will recognize the directory as containing a version if it is only "x.x.x" or if it ends with "x.x.x" (as in lab_8.0.11.)
You can use, instead of a full version number (e.g. 8.0.11,) a short one, such as 8.0. This shortcut works starting with version 1.6.0.
When you invoke dbdeployer with a short number, it will look for the highest revision number within that version, and use it for deployment.
For example, if your sandbox binary directory contains the following:
5.7.19 5.7.20 5.7.22 8.0.1 8.0.11 8.0.4
You can issue the command dbdeployer deploy single 8.0, and it will use 8.0.11 for a single deployment. Or dbdeployer deploy replication 5.7 and it will result in a replication system using 5.7.22 (the latest one.)
If you want to deploy several instances of the same version and the same type (for example two single sandboxes of 8.0.4, or two replication instances with different settings) you can specify the data directory name and the ports manually.
$ dbdeployer deploy single 8.0.4
# will deploy in msb_8_0_4 using port 8004
$ dbdeployer deploy single 8.0.4 --sandbox-directory=msb2_8_0_4
# will deploy in msb2_8_0_4 using port 8005 (which dbdeployer detects and uses)
$ dbdeployer deploy replication 8.0.4 --concurrent
# will deploy replication in rsandbox_8_0_4 using default calculated ports 19009, 19010, 19011
$ dbdeployer deploy replication 8.0.4 \
--gtid \
--sandbox-directory=rsandbox2_8_0_4 \
--base-port=18600 --concurrent
# will deploy replication in rsandbox2_8_0_4 using ports 18601, 18602, 18603
If you have a custom organization of expanded tarballs, you may want to use the direct path to the binaries, instead of a combination of --sandbox-binary and the version name.
For example, let's assume your binaries are organized as follows:
$HOME/opt/
/percona/
/5.7.21
/5.7.22
/8.0.11
/mysql/
/5.7.21
/5.7.22
/8.0.11
You can deploy a single sandbox for a Percona server version 5.7.22 using any of the following approaches:
#1
dbdeployer deploy single --sandbox-binary=$HOME/opt/percona 5.7.22
#2
dbdeployer deploy single $HOME/opt/percona/5.7.22
#3
dbdeployer defaults update sandbox-binary $HOME/opt/percona
dbdeployer deploy single 5.7.22
#4
export SANDBOX_BINARY=$HOME/opt/percona
dbdeployer deploy single 5.7.22
Methods #1 and #2 are equivalent. They set the sandbox binary directory temporarily to a new one, and use it for the current deployement
Methods #3 and #4 will set the sandbox binary directory permanently, with the difference that #3 is set for any invocation of dbdeployer system-wide (in a different terminal window, it will use the new value,) while #4 is set only for the current session (in a different terminal window, it will still use the default.)
Be aware that, using this kind of organization may see conflicts during deployment. For example, after installing Percona Server 5.7.22, if you want to install MySQL 5.7.22 you will need to specify a --sandbox-directory explicitly.
Instead, if you use the prefix approach defined in the "standard and non-standard basedir names," conflicts should be avoided.
dbdeployer will try using the default port for each sandbox whenever possible. For single sandboxes, the port will be the version number without dots: 5.7.22 will deploy on port 5722. For multiple sandboxes, the port number is defined by using a prefix number (visible in the defaults: dbdeployer defaults list) + the port number + the revision number (for some topologies multiplied by 100.)
For example, single-primary group replication with MySQL 8.0.11 will compute the ports like this:
base port = 8011 (version number) + 13000 (prefix) + 11 (revision) * 100 = 22111
node1 port = base port + 1 = 22112
node2 port = base port + 2 = 22113
node3 port = base port + 2 = 22114
For group replication we need to calculate the group port, and we use the group-port-delta (= 125) to obtain it from the regular port:
node1 group port = 22112 + 125 = 22237
node2 group port = 22113 + 125 = 22238
node3 group port = 22114 + 125 = 22239
For MySQL 8.0.11+, we also need to assign a port for the XPlugin, and we compute that using the regular port + the mysqlx-port-delta (=10000).
Thus, for MySQL 8.0.11 group replication deployments, you would see this listing:
$ dbdeployer sandboxes --header
name type version ports
---------------- ------- ------- -----
group_msb_8_0_11 : group-multi-primary 8.0.11 [20023 20148 30023 20024 20149 30024 20025 20150 30025]
group_sp_msb_8_0_11 : group-single-primary 8.0.11 [22112 22237 32112 22113 22238 32113 22114 22239 32114]
This method makes port clashes unlikely when using the same version in different deployments, but there is a risk of port clashes when deploying many multiple sandboxes of close-by versions.
Furthermore, dbdeployer doesn't let the clash happen. Thanks to its central catalog of sandboxes, it knows which ports were already used, and will search for free ones whenever a potential clash is detected.
Bear in mind that the concept of "used" is only related to sandboxes. dbdeployer does not know if ports may be used by other applications.
You can minimize risks by telling dbdeployer which ports may be occupied. The defaults have a field reserved-ports, containing the ports that should not be used. You can add to that list by modifying the defaults. For example, if you want to exclude port 7001, 10000, and 15000 from being used, you can run
dbdeployer defaults update reserved-ports '7001,10000,15000'
or, if you want to preserve the ones that are reserved by default:
dbdeployer defaults update reserved-ports '1186,3306,33060,7001,10000,15000'
Starting with version 0.3.0, dbdeployer can deploy groups of sandboxes (deploy replication, deploy multiple) with the flag --concurrent. When this flag is used, dbdeployed will run operations concurrently.
The same flag can be used with the delete command. It is useful when there are several sandboxes to be deleted at once.
Concurrent operations run from 2 to 5 times faster than sequential ones, depending on the version of the server and the number of nodes.
Multiple sandboxes can be deployed using replication with several topologies (using dbdeployer deploy replication --topology=xxxxx:
- master-slave is the default topology. It will install one master and two slaves. More slaves can be added with the option
--nodes. - group will deploy three peer nodes in group replication. If you want to use a single primary deployment, add the option
--single-primary. Available for MySQL 5.7 and later. - fan-in is the opposite of master-slave. Here we have one slave and several masters. This topology requires MySQL 5.7 or higher. all-masters is a special case of fan-in, where all nodes are masters and are also slaves of all nodes.
It is possible to tune the flow of data in multi-source topologies. The default for fan-in is three nodes, where 1 and 2 are masters, and 2 are slaves. You can change the predefined settings by providing the list of components:
$ dbdeployer deploy replication --topology=fan-in \
--nodes=5 --master-list="1,2 3" \
--slave-list="4,5" 8.0.4 \
--concurrent
In the above example, we get 5 nodes instead of 3. The first three are master (--master-list="1,2,3") and the last two are slaves (--slave-list="4,5") which will receive data from all the masters. There is a test automatically generated to check the replication flow. In our case it shows the following:
$ ~/sandboxes/fan_in_msb_8_0_4/test_replication
# master 1
# master 2
# master 3
# slave 4
ok - '3' == '3' - Slaves received tables from all masters
# slave 5
ok - '3' == '3' - Slaves received tables from all masters
# pass: 2
# fail: 0
The first three lines show that each master has done something. In our case, each master has created a different table. Slaves in nodes 5 and 6 then count how many tables they found, and if they got the tables from all masters, the test succeeds.
By default, when sandboxes are deployed, the servers start and additional operations to complete the topology are executed automatically. It is possible to skip the server start, using the --skip-start option. When this option is used, the server is initialized, but not started. Consequently, the default users are not created, and the database, when started manually, is only accessible with user root without password.
If you deploy with --skip-start, you can run the rest of the operations manually:
$ dbdeployer deploy single --skip-start 5.7.21
$ $HOME/sandboxes/msb_5_7_21/start
$ $HOME/sandboxes/msb_5_7_21/load_grants
The same can be done for replication, but you need also to run the additional step of initializing the slaves:
$ dbdeployer deploy replication --skip-start 5.7.21 --concurrent
$ $HOME/sandboxes/rsandbox_5_7_21/start_all
$ $HOME/sandboxes/rsandbox_5_7_21/master/load_grants
# NOTE: only the master needs to load grants. The slaves receive the grants through replication
$ $HOME/sandboxes/rsandbox_5_7_21/initialize_slaves
Similarly, for group replication
$ dbdeployer deploy replication --skip-start 5.7.21 --topology=group --concurrent
$ $HOME/sandboxes/group_msb_5_7_21/start_all
$ $HOME/sandboxes/group_msb_5_7_21/node1/load_grants
$ $HOME/sandboxes/group_msb_5_7_21/node2/load_grants
$ $HOME/sandboxes/group_msb_5_7_21/node3/load_grants
$ $HOME/sandboxes/rsandbox_5_7_21/initialize_nodes
WARNING: running sandboxes with --skip-start is provided for advanced users and is not recommended.
If the purpose of skipping the start is to inspect the server before the sandbox granting operations, you may consider using --pre-grants-sql and --pre-grants-sql-file to run the necessary SQL commands (see Sandbox customization below.)
MySQL 5.7.12+ introduces the XPlugin (a.k.a. mysqlx) which enables operations using a separate port (33060 by default) on special tables that can be treated as NoSQL collections.
In MySQL 8.0.11+ the XPlugin is enabled by default, giving dbdeployer the task of defining an additional port and socket for this service. When you deploy MySQL 8.0.11 or later, dbdeployer sets the mysqlx-port to the value of the regular port + mysqlx-delta-port (= 10000).
If you want to avoid having the XPlugin enabled, you can deploy the sandbox with the option --disable-mysqlx.
For MySQL between 5.7.12 and 8.0.4, the approach is the opposite. By default, the XPlugin is disabled, and if you want to use it you will run the deployment using --enable-mysqlx. In both cases the port and socket will be computed by dbdeployer.
When the XPlugin is enabled, it makes sense to use the MySQL shell and dbdeployer will create a mysqlsh script for the sandboxes that use the plugin. Unfortunately, as of today (late April 2018) the MySQL shell is not released with the server tarball, and therefore we have to fix things manually (see next section.) dbdeployer will look for mysqlsh in the same directory where the other clients are, so if you manually merge the mysql shell and the server tarballs, you will get the appropriate version of MySQL shell. If not, you will use the version of the shell that is available in $PATH. If there is no MySQL shell available, you will get an error.
The MySQL shell is distributed as a tarball. You can install it within the server binaries directory, using dbdeployer (as of version 1.9.0.)
The simplest operation is:
$ dbdeployer unpack --shell \
mysql-shell-8.0.12-$YOUR_OS.tar.gz
This command will work if MySQL 8.0.12 was already unpacked in $SANDBOX_BINARY/8.0.12. dbdeployer recognizes the version (from the tarball name) and looks for the corresponding server. If it is found, the shell package will be temporarily expanded, and the necessary files moved into the server directory tree.
If the corresponding server directory does not exist, you can specify the wanted target:
$ dbdeployer unpack --shell \
mysql-shell-8.0.12-$YOUR_OS.tar.gz \
--target-server=5.7.23
Since the MySQL team recommends using the latest shell even for older versions of MySQL, we can insert the shell from 8.0.12 into the 5.7 server, and it will work as expected, as the shell brings with it all needed files.
Using the option --verbosity=2 we can see which files were extracted and where each component was copied or moved. Notice that the unpacked MySQL shell directory is deleted after the operation is completed.
Sometimes, when using sandboxes for testing, it makes sense to enable the general log, either during initialization or for regular operation. While you can do that with --my-cnf-options=general-log=1 or --my-init-options=--general-log=1, as of version 1.4.0 you have two simple boolean shortcuts: --init-general-log and --enable-general-log that will start the general log when requested.
Additionally, each sandbox has a convenience script named show_log that can easily display either the error log or the general log. Run ./show_log -h for usage info.
For replication, you also have show_binlog and show_relaylog in every sandbox as a shortcut to display replication logs easily.
In addition to enabling database logs, you can also have logs of the operations performed by dbdeployer when building and activating sandboxes.
The logs are disabled by default. You can enable them for a given operation using --log-sb-operations. When the logs are enabled, dbdeployer will create one or more log files in a directory under $HOME/sandboxes/logs.
For a single sandbox, the log directory will be named single_v_v_vv-xxxx, where v_v_vv is the version number and xxxx is dbdeployer Process ID. Inside the directory, there will be a file names single.log.
For a replication sandbox, the directory will be named replication_v_v_vv-xxxx and it will contain at least 3 files: master-slave-replication.log with replication operations, and two single sandbox (one for master and one for a slave) logs named replication-node-x.log. If there is more than one slave, each one will have its own log.
dbdeployer logs will record which function ran which operation, with the data used for single and compound sandboxes.
The name of the log is available inside the file sbdescription.json in each sandbox. If logging is disabled, the log field is not listed.
The logs are preserved until the corresponding sandbox is deleted.
Logging can be enabled permanently using the defaults: dbdeployer defaults update log-sb-operations true. Similarly, you can change the log-directory either for a single operation (--log-directory=...) or permanently (dbdeployer defaults update log-directory /my/path/to/logs)
What kind of information is in the logs? The most important things found in there is the data used to fill the templates. If something goes wrong, the data should give us a lead in the right direction. The logs also record the result of several choices that dbdeployer makes, such as enebling a given port or adding such and such option to the configuration file. Even if nothing is wrong, the logs can give the inquisitive user some insight on what happens when we deploy a less than usual configuration, and which templates and options can be used to alter the result.
There are several ways of changing the default behavior of a sandbox.
- You can add options to the sandbox being deployed using
--my-cnf-options="some mysqld directive". This option can be used many times. The supplied options are added to my.sandbox.cnf - You can specify a my.cnf template (
--my-cnf-file=filename) instead of defining options line by line. dbdeployer will skip all the options that are needed for the sandbox functioning. - You can run SQL statements or SQL files before or after the grants were loaded (
--pre-grants-sql,--pre-grants-sql-file, etc). You can also use these options to peek into the state of the sandbox and see what is happening at every stage. - For more advanced needs, you can look at the templates being used for the deployment, and load your own instead of the original s(
--use-template=TemplateName:FileName.)
For example:
$ dbdeployer deploy single 5.6.33 --my-cnf-options="general_log=1" \
--pre-grants-sql="select host, user, password from mysql.user" \
--post-grants-sql="select @@general_log"
$ dbdeployer defaults templates list
$ dbdeployer defaults templates show templateName > mytemplate.txt
# edit the template
$ dbdeployer deploy single --use-template=templateName:mytemplate.txt 5.7.21
dbdeployer will use your template instead of the original.
- You can also export the templates, edit them, and ask dbdeployer to edit your changes.
Example:
$ dbdeployer defaults templates export single my_templates
# Will export all the templates for the "single" group to the direcory my_templates/single
$ dbdeployer defaults templates export ALL my_templates
# exports all templates into my_templates, one directory for each group
# Edit the templates that you want to change. You can also remove the ones that you want to leave untouched.
$ dbdeployer defaults templates import single my_templates
# Will import all templates from my_templates/single
Warning: modifying templates may block the regular work of the sandboxes. Use this feature with caution!
- Finally, you can modify the defaults for the application, using the "defaults" command. You can export the defaults, import them from a modified JSON file, or update a single one on-the-fly.
Here's how:
$ dbdeployer defaults show
# Internal values:
{
"version": "1.5.0",
"sandbox-home": "$HOME/sandboxes",
"sandbox-binary": "$HOME/opt/mysql",
"use-sandbox-catalog": true,
"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,
"group-port-delta": 125,
"mysqlx-port-delta": 10000,
"master-name": "master",
"master-abbr": "m",
"node-prefix": "node",
"slave-prefix": "slave",
"slave-abbr": "s",
"sandbox-prefix": "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
],
"timestamp": "Sat May 12 14:37:53 CEST 2018"
}$ dbdeployer defaults update master-slave-base-port 15000
# Updated master-slave-base-port -> "15000"
# Configuration file: $HOME/.dbdeployer/config.json
{
"version": "1.5.0",
"sandbox-home": "$HOME/sandboxes",
"sandbox-binary": "$HOME/opt/mysql",
"use-sandbox-catalog": true,
"master-slave-base-port": 15000,
"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,
"group-port-delta": 125,
"mysqlx-port-delta": 10000,
"master-name": "master",
"master-abbr": "m",
"node-prefix": "node",
"slave-prefix": "slave",
"slave-abbr": "s",
"sandbox-prefix": "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
],
"timestamp": "Sat May 12 14:37:53 CEST 2018"
}Another way of modifying the defaults, which does not store the new values in dbdeployer's configuration file, is through the --defaults flag. The above change could be done like this:
$ dbdeployer --defaults=master-slave-base-port:15000 \
deploy replication 5.7.21
The difference is that using dbdeployer defaults update the value is changed permanently for the next commands, or until you run a dbdeployer defaults reset. Using the --defaults flag, instead, will modify the defaults only for the active command.
You can list the available MySQL versions with
$ dbdeployer versions
Also "available" is a recognized alias for this command.
And you can list which sandboxes were already installed
$ dbdeployer sandboxes # Aliases: installed, deployed
The command "usage" shows how to use the scripts that were installed with each sandbox.
$ dbdeployer usage
USING A SANDBOX
Change directory to the newly created one (default: $SANDBOX_HOME/msb_VERSION
for single sandboxes)
[ $SANDBOX_HOME = $HOME/sandboxes unless modified with flag --sandbox-home ]
The sandbox directory of the instance you just created contains some handy
scripts to manage your server easily and in isolation.
"./start", "./status", "./restart", and "./stop" do what their name suggests.
start and restart accept parameters that are eventually passed to the server.
e.g.:
./start --server-id=1001
./restart --event-scheduler=disabled
"./use" calls the command line client with the appropriate parameters,
Example:
./use -BN -e "select @@server_id"
./use -u root
"./clear" stops the server and removes everything from the data directory,
letting you ready to start from scratch. (Warning! It's irreversible!)
"./send_kill" does almost the same as "./stop", as it sends a SIGTERM (-15) kill
to shut down the server. Additionally, when the regular kill fails, it will
send an unfriendly SIGKILL (-9) to the unresponsive server.
"./add_option" will add one or more options to my.sandbox.cnf, and restarts the
server to apply the changes.
"init_db" and "load_grants" are used during the server initialization, and should not be used
in normal operations. They are nonetheless useful to see which operations were performed
to set up the server.
"./show_binlog" and "./show_relaylog" will show the latest binary log or relay-log.
"./my" is a prefix script to invoke any command named "my*" from the
MySQL /bin directory. It is important to use it rather than the
corresponding globally installed tool, because this guarantees
that you will be using the tool for the version you have deployed.
Examples:
./my sqldump db_name
./my sqlbinlog somefile
"./mysqlsh" invokes the mysql shell. Unlike other commands, this one only works
if mysqlsh was installed, with preference to the binaries found in "basedir".
This script is created only if the X plugin was enabled (5.7.12+ with --enable-mysqlx
or 8.0.11+ without --disable-mysqlx)
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.
Every sandbox has a file named sbdescription.json, containing important information on the sandbox. It is useful to determine where the binaries come from and on which conditions it was installed.
For example, a description file for a single sandbox would show:
{
"basedir": "/home/dbuser/opt/mysql/5.7.22",
"type": "single",
"version": "5.7.22",
"port": [
5722
],
"nodes": 0,
"node_num": 0,
"dbdeployer-version": "1.5.0",
"timestamp": "Sat May 12 14:26:41 CEST 2018",
"command-line": "dbdeployer deploy single 5.7.22"
}And for replication:
{
"basedir": "/home/dbuser/opt/mysql/5.7.22",
"type": "master-slave",
"version": "5.7.22",
"port": [
16745,
16746,
16747
],
"nodes": 2,
"node_num": 0,
"dbdeployer-version": "1.5.0",
"timestamp": "Sat May 12 14:27:04 CEST 2018",
"command-line": "dbdeployer deploy replication 5.7.22 --gtid --concurrent"
}You can run a command in several sandboxes at once, using the global command, which propagates your command to all the installed sandboxes.
$ dbdeployer global -h
This command can propagate the given action through all sandboxes.
Usage:
dbdeployer global [command]
Examples:
$ dbdeployer global use "select version()"
$ dbdeployer global status
$ dbdeployer global stop
Available Commands:
restart Restarts all sandboxes
start Starts all sandboxes
status Shows the status in all sandboxes
stop Stops all sandboxes
test Tests all sandboxes
test-replication Tests replication in all sandboxes
use Runs a query in all sandboxes
Flags:
-h, --help help for global
The sandboxes can also be deleted, either one by one or all at once:
$ dbdeployer delete -h
Stops the sandbox (and its depending sandboxes, if any), and removes it.
Warning: this command is irreversible!
Usage:
dbdeployer delete sandbox_name (or "ALL") [flags]
Aliases:
delete, remove, destroy
Examples:
$ dbdeployer delete msb_8_0_4
$ dbdeployer delete rsandbox_5_7_21
Flags:
--concurrent Runs multiple deletion tasks concurrently.
--confirm Requires confirmation.
-h, --help help for delete
--skip-confirm Skips confirmation with multiple deletions.
You can lock one or more sandboxes to prevent deletion. Use this command to make the sandbox non-deletable.
$ dbdeployer admin lock sandbox_name
A locked sandbox will not be deleted, even when running dbdeployer delete ALL.
The lock can also be reverted using
$ dbdeployer admin unlock sandbox_name
dbdeployer 1.10.0 introduces upgrades:
$ dbdeployer admin upgrade -h
Upgrades a sandbox to a newer version.
The sandbox with the new version must exist already.
The data directory of the old sandbox will be moved to the new one.
Usage:
dbdeployer admin upgrade sandbox_name newer_sandbox [flags]
Examples:
dbdeployer admin upgrade msb_8_0_11 msb_8_0_12
Flags:
-h, --help help for upgrade
To perform an upgrade, the following conditions myst be met:
- Both sandboxes must be single deployments.
- The older version must be one major version behind (5.6.x to 5.7.x, or 5.7.x to 8.0.x, but not 5.6.x to 8.0.x) or same major version but different revision (e.g. 5.7.22 to 5.7.23)
- The newer version must have been already deployed.
- The newer version must have mysql_upgrade in its base directory (e.g $SANDBOX_BINARY/5.7.23/bin)
dbdeployer checks all the conditions, then
- stops both databases;
- renames the data directory of the newer version;
- moves the data directory of the older version under the newer sandbox;
- restarts the newer version;
- runs
mysql_upgrade.
The older version is, at this point, not operational anymore, and can be deleted.
Should you need to compile your own binaries for dbdeployer, follow these steps:
- Make sure you have go installed in your system, and that the
$GOPATHvariable is set. - Run
go get -u github.com/datacharmer/dbdeployer. This will import all the code that is needed to build dbdeployer. - Change directory to
$GOPATH/src/github.com/datacharmer/dbdeployer. - Run
./scripts/build.sh {linux|OSX} 1.14.0 - If you need the docs enabled binaries (see the section "Generating additional documentation") run
MKDOCS=1 ./scripts/build.sh {linux|OSX} 1.14.0
Between this file and the API API list, you have all the existing documentation for dbdeployer. Should you need additional formats, though, dbdeployer is able to generate them on-the-fly. Tou will need the docs-enabled binaries: in the distribution list, you will find:
- dbdeployer-1.14.0-docs.linux.tar.gz
- dbdeployer-1.14.0-docs.osx.tar.gz
- dbdeployer-1.14.0.linux.tar.gz
- dbdeployer-1.14.0.osx.tar.gz
The executables containing -docs in their name have the same capabilities of the regular ones, but in addition they can run the hidden command tree, with alias docs.
This is the command used to help generating the API documentation.
$ dbdeployer-docs tree -h
This command is only used to create API documentation.
You can, however, use it to show the command structure at a glance.
Usage:
dbdeployer tree [flags]
Aliases:
tree, docs
Flags:
--api Writes API template
--bash-completion creates bash-completion file
-h, --help help for tree
--man-pages Writes man pages
--markdown-pages Writes Markdown docs
--rst-pages Writes Restructured Text docs
--show-hidden Shows also hidden commands
In addition to the API template, the tree command can produce:
- man pages;
- Markdown documentation;
- Restructured Text pages;
- Command line completion script (see next section).
There is a file ./docs/dbdeployer_completion.sh, which is automatically generated with dbdeployer API documentation. If you want to use bash completion on the command line, copy the file to the bash completion directory. For example:
# Linux
$ sudo cp ./docs/dbdeployer_completion.sh /etc/bash_completion.d
$ source /etc/bash_completion
# OSX
$ sudo cp ./docs/dbdeployer_completion.sh /usr/local/etc/bash_completion.d
$ source /usr/local/etc/bash_completion
Then, you can use completion as follows:
$ dbdeployer [tab]
admin defaults delete deploy global sandboxes unpack usage versions
$ dbdeployer dep[tab]
$ dbdeployer deploy [tab][tab]
multiple replication single
$ dbdeployer deploy s[tab]
$ dbdeployer deploy single --b[tab][tab]
--base-port= --bind-address=
If you need to create sandboxes from other Go apps, see dbdeployer-as-library.md.
As of version 1.0.0, dbdeployer adheres to the principles of semantic versioning. A version number is made of Major, Minor, and Revision. When changes are applied, the following happens:
- Backward-compatible bug fixes increment the Revision number.
- Backward-compatible new features increment the Minor number.
- Backward incompatible changes (either features or bug fixes that break compatibility with the API) increment the Major number.
The starting API is defined in API-1.0.md (generated manually.) The file API-1.1.md contains the same API definition, but was generated automatically and can be used to better compare the initial API with further version.
README.md is generated by processing ./mkreadme/readme_template.md. Do not edit it directly, as its contents will be overwritten.