dbdeployer--MySQL环境部署神器，Sandbox的替代品

https://github.com/datacharmer/dbdeployer

https://github.com/datacharmer/mysql-sandbox

dbdeployer是go语言版实现的sandbox，和sandbox同一个作者。

dbdeployer能够快速部署数据库测试环境，一键实现Oracle MySQL、Percona MySQL、MariaDB、TiDB、MySQL NDB Cluster、PXC等测试环境的构建。

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.

---  sandbox
---  sandbox
yum install cpan  perl-Test-Simple -y
cpan MySQL::Sandbox
echo 'export SANDBOX_AS_ROOT=1' >> /root/.bash_profile
source /root/.bash_profile
--单实例
make_sandbox mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
mysql -S/tmp/mysql_sandbox5562.sock -uroot -pmsandbox
--多实例
make_multiple_sandbox --how_many_nodes=2  mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz
$SANDBOX_HOME/multi_msb_mysql-5_6_48/n1
--主从复制
make_replication_sandbox  mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
make_replication_sandbox --gtid --how_many_slaves=1 mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
$SANDBOX_HOME/rsandbox_mysql-5_7_30/test_replication
---不同版本
export SANDBOX_BINARY=/soft
ls $SANDBOX_BINARY
make_sandbox 5.5.62
make_multiple_custom_sandbox 5.5.62 5.7.30 5.6.48
---  dbdeployer
VERSION=1.50.0
OS=linux
origin=https://github.com/datacharmer/dbdeployer/releases/download/v$VERSION
wget $origin/dbdeployer-$VERSION.$OS.tar.gz
tar -xzf dbdeployer-$VERSION.$OS.tar.gz
chmod +x dbdeployer-$VERSION.$OS
mv dbdeployer-$VERSION.$OS /usr/local/bin/dbdeployer
dbdeployer unpack  mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz
dbdeployer unpack  mysql-5.6.48-linux-glibc2.12-x86_64.tar.gz
dbdeployer unpack  mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
dbdeployer unpack  mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
mkdir -p /root/opt/mysql
ln -s /usr/local/mysqlsoft/mysql-5.5.62-linux-glibc2.12-x86_64 /root/opt/mysql/5.5.62
ln -s /usr/local/mysqlsoft/mysql-5.6.48-linux-glibc2.12-x86_64 /root/opt/mysql/5.6.48
ln -s /usr/local/mysqlsoft/mysql-5.7.30-linux-glibc2.12-x86_64 /root/opt/mysql/5.7.30
ln -s /usr/local/mysqlsoft/mysql-8.0.16-linux-glibc2.12-x86_64 /root/opt/mysql/8.0.16
ll /root/opt/mysql/
dbdeployer remote list
dbdeployer downloads list
dbdeployer sandboxes --full-info
dbdeployer global status
dbdeployer global stop msb_8_0_19
dbdeployer delete msb_8_0_19
# 默认密码：msandbox
--- 单机
dbdeployer deploy single 8.0.19 --bind-address=0.0.0.0 --port=3351 --remote-access='%' --native-auth-plugin --gtid --my-cnf-options="skip_name_resolve" --pre-grants-sql="create user root@'%' identified with mysql_native_password by 'lhr';grant all on *.* to root@'%' with grant option;flush privileges;"
dbdeployer deploy single 5.7.30 --bind-address=0.0.0.0 --port=3343 --remote-access='%' --gtid --my-cnf-options="skip_name_resolve" --my-cnf-options="character_set_server=utf8mb4" --pre-grants-sql="grant all on *.* to root@'%' identified by 'lhr'  with grant option;flush privileges;"
dbdeployer deploy single 5.7.30 --bind-address=0.0.0.0 --port=3343 --remote-access='%' --sandbox-binary /usr/local/mysqlsoft --gtid --my-cnf-options="skip_name_resolve" --my-cnf-options="character_set_server=utf8mb4" --pre-grants-sql="grant all on *.* to root@'%' identified by 'lhr'  with grant option;flush privileges;"
mysql -uroot -pmsandbox -S/tmp/mysql_sandbox3343.sock
firewall-cmd --add-port=3300-3500/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-port
--部署2个mysql(using ports 3335，3336)
dbdeployer deploy multiple 5.6.48 --bind-address=0.0.0.0 --base-port=3334 --nodes=2 --remote-access='%' --my-cnf-options="skip_name_resolve" --my-cnf-options="character_set_server=utf8mb4" --pre-grants-sql="grant all on *.* to root@'%' identified by 'lhr'  with grant option;flush privileges;"
-- 主从复制
--部署1主2从: (using ports 3344,3345,3346)
dbdeployer deploy replication 5.7.30 --bind-address=0.0.0.0 --base-port=3343 --nodes=3 --gtid --remote-access='%' --my-cnf-options="skip_name_resolve" --my-cnf-options="character_set_server=utf8mb4" --pre-grants-sql="grant all on *.* to root@'%' identified by 'lhr'  with grant option;flush privileges;"
--安装3主2从（多主多从）：(using ports 3347,3348,3349,3350,3351)
dbdeployer deploy replication 5.7.30 --topology=fan-in --nodes=5 --master-list="1,2,3" --slave-list="4,5" --base-port=3346 --bind-address=0.0.0.0  --gtid --remote-access='%' --my-cnf-options="skip_name_resolve" --my-cnf-options="character_set_server=utf8mb4" --pre-grants-sql="grant all on *.* to root@'%' identified by 'lhr'  with grant option;flush privileges;"
--安装组复制：(using ports 3352,3353,3354)
-- 多主
dbdeployer deploy replication 8.0.19 --topology=group --bind-address=0.0.0.0 --base-port=3351 --native-auth-plugin --remote-access='%' --gtid --my-cnf-options="skip_name_resolve" --pre-grants-sql="create user root@'%' identified with mysql_native_password by 'lhr';grant all on *.* to root@'%' with grant option;flush privileges;"
-- 单主
dbdeployer deploy replication 8.0.19 --topology=group --single-primary --bind-address=0.0.0.0 --base-port=3354 --native-auth-plugin --remote-access='%' --gtid --my-cnf-options="skip_name_resolve" --pre-grants-sql="create user root@'%' identified with mysql_native_password by 'lhr';grant all on *.* to root@'%' with grant option;flush privileges;"
--函数切换：从MySQL 8.0.13开始，可以使用函数进行在线修改MGR模式。
select group_replication_switch_to_multi_primary_mode(); #单主切多主
select group_replication_switch_to_single_primary_mode('00015802-3333-3333-3333-333333333333') ;  #多主切单主
-- 安装pxc
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/Percona-XtraDB-Cluster-5.7.29-31.43/binary/tarball/Percona-XtraDB-Cluster-5.7.29-rel32-43.1.Linux.x86_64.ssl102.tar.gz
dbdeployer unpack --prefix=pxc Percona-XtraDB-Cluster-5.7.29-rel32-43.1.Linux.x86_64.ssl102.tar.gz
echo "export PATH=/root/opt/mysql/pxc5.7.28/bin:$PATH" >> /root/.bashrc 
source /root/.bashrc
ln -s /usr/bin/innobackupex2420 /usr/bin/innobackupex
ln -s /usr/bin/xtrabackup2420 /usr/bin/xtrabackup
ln -s /usr/bin/xbstream2420 /usr/bin/xbstream
dbdeployer deploy replication pxc5.7.25 --topology=pxc  --bind-address=0.0.0.0  --gtid --remote-access='%'   --my-cnf-options="skip_name_resolve" --my-cnf-options="character_set_server=utf8mb4"  --my-cnf-options="wsrep_sst_method=xtrabackup-v2" --pre-grants-sql="grant all on *.* to root@'localhost' with grant option;flush privileges;"
dbdeployer deploy replication pxc5.7.25 --topology=pxc  --bind-address=0.0.0.0  --gtid --remote-access='%'   --my-cnf-options="skip_name_resolve" --my-cnf-options="character_set_server=utf8mb4"  --pre-grants-sql="grant all on *.* to root@'%' identified by 'lhr'  with grant option;flush privileges;"
rsync: recv_generator: mkdir "test" (in rsync_sst) failed: Permission denied (13)
*** Skipping any contents from this failed directory ***
rsync: open "ibdata1" (in rsync_sst) failed: Permission denied (13)
rsync: open "wsrep_sst_binlog.tar" (in rsync_sst) failed: Permission denied (13)
rsync error: some files/attrs were not transferred (see previous errors) (code 23) at main.c(1179) [sender=3.1.2]
        2020-06-03T01:02:06.610041Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
        2020-06-03T01:02:06.613805Z WSREP_SST: [ERROR] rsync returned code 23:
        2020-06-03T01:02:06.617425Z WSREP_SST: [ERROR] ****************************************************** 
2020-06-03T01:02:06.618566Z 0 [ERROR] WSREP: Process completed with error: wsrep_sst_rsync --role 'donor' --address '127.0.0.1:26365/rsync_sst' --socket '/tmp/mysql_sandbox26226.sock' --dat
adir '/root/sandboxes/pxc_msb_pxc5_7_25/node1/data/' --defaults-file '/root/sandboxes/pxc_msb_pxc5_7_25/node1/my.sandbox.cnf' --defaults-group-suffix '' --mysqld-version '5.7.25-28-31.35'  
--binlog 'mysql-bin' --gtid 'cdcfac35-a535-11ea-b019-2e688b6160cc:18' : 255 (Unknown error 255)
若使用pxb同步也会报错：
2020-06-03T01:05:39.481216Z 8 [Note] Access denied for user 'root'@'localhost' (using password: NO)
        2020-06-03T01:05:39.487382Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 
        2020-06-03T01:05:39.491296Z WSREP_SST: [ERROR] xtrabackup finished with error: 1.  Check /root/sandboxes/pxc_msb_pxc5_7_25/node1/data//innobackup.backup.log
--------------- innobackup.backup.log (START) --------------------
xtrabackup: recognized server arguments: --datadir=/root/sandboxes/pxc_msb_pxc5_7_25/node1/data --tmpdir=/root/sandboxes/pxc_msb_pxc5_7_25/node1/tmp --server-id=26226 --log_bin=mysql-bin --innodb_file_per_table=1 --innodb_flush_method=O_DIRECT --defaults_group=mysqld --parallel=4 
xtrabackup: recognized client arguments: --user=msandbox --password=* --port=26226 --socket=/tmp/mysql_sandbox26226.sock --user=root --password=* --socket=/tmp/mysql_sandbox26226.sock --lock-ddl=1 --backup=1 --galera-info=1 --binlog-info=ON --stream=xbstream --target-dir=/root/sandboxes/pxc_msb_pxc5_7_25/node1/tmp/pxc_sst_x4Hu/donor_xb_GgZJ 
200603 09:05:36  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=26226;mysql_socket=/tmp/mysql_sandbox26226.sock' as 'root'  (using password: NO).
200603 09:05:36  version_check Connected to MySQL server
200603 09:05:36  version_check Executing a version check against the server...
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at - line 237.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at - line 237.
# A software update is available:
200603 09:05:39  version_check Done.
200603 09:05:39 Connecting to MySQL server host: localhost, user: root, password: set, port: 26226, socket: /tmp/mysql_sandbox26226.sock
Failed to connect to MySQL server: Access denied for user 'root'@'localhost' (using password: NO).
--------------- innobackup.backup.log (END) ----------------------
        2020-06-03T01:05:39.498274Z WSREP_SST: [ERROR] ****************************************************** 
        2020-06-03T01:05:39.502633Z WSREP_SST: [ERROR] Cleanup after exit with status:22
2020-06-03T01:05:39.527473Z 0 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'donor' --address '127.0.0.1:26365/xtrabackup_sst//1' --socket '/tmp/mysql_sandbox26226.sock' --datadir '/root/sandboxes/pxc_msb_pxc5_7_25/node1/data/' --defaults-file '/root/sandboxes/pxc_msb_pxc5_7_25/node1/my.sandbox.cnf' --defaults-group-suffix '' --mysqld-version '5.7.25-28-31.35'  --binlog 'mysql-bin' --gtid '445da623-a536-11ea-88f5-2e8b351e1cee:18' : 22 (Invalid argument)

dbdeployer的安装：

直接到github下载release包即可

tar xf dbdeployer-1.19.0.linux.tar.gz -C ./

chmod +x dbdeployer-1.19.0.linux

mv dbdeployer-1.19.0.linux /usr/local/bin/dbdeployer

mkdir /root/opt/mysql -pv

【方式1、离线方式 部署mysql安装包】

将 mysql(我这里用的是5.7和8.0）的general安装包传到服务器上，然后执行

dbdeployer unpack  mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz

dbdeployer unpack  mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz

unpack后，在/root/opt/mysql 目录下自动解压成如下样子：

如果要解压后的文件夹名称特殊化，则可以如下方法：

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

【方式2、在线下载的方式部署mysql安装包】

dbdeployer remote --help

dbdeployer remote list   # 查看远程可下载的mysql安装包

Files available in https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata/available.json

4.1 -> [mysql-4.1.22]

5.0 -> [mysql-5.0.15 mysql-5.0.96]

5.1 -> [mysql-5.1.72]

5.5 -> [mysql-5.5.61 mysql-5.5.62]

5.6 -> [mysql-5.6.41 mysql-5.6.43]

5.7 -> [mysql-5.7.24 mysql-5.7.25]

8.0 -> [mysql-8.0.13 mysql-8.0.15]

dbdeployer remote download mysql-5.6.43   # 在线下载 mysql-5.6.43的安装包

dbdeployer unpack mysql-5.6.43.tar.xz 

常用命令：

dbdeployer  -h 列出各种帮助命令

dbdeployer sandboxes     查看当前部署了哪些mysql sanbox

dbdeployer deploy -h    查看部署相关的命令及参数

Available Commands:

 dbdeployer deploy  multiple     --help   部署多个独立的mysql

 dbdeployer deploy  replication  --help  部署复制环境的mysql

 dbdeployer deploy  single      --help      部署单节点mysql

dbdeployer deploy replication --help  查看帮助

dbdeployer deploy replication 8.0.15 --bind-address='0.0.0.0' --gtid --read-only-slaves --nodes=2 --semi-sync --topology=master-slave   # 部署一套1M1S的gtid复制集群

dbdeployer deploy replication 5.7.25 --bind-address='0.0.0.0' --topology=group  部署一套多主MGR集群

dbdeployer deploy replication 5.7.25 --bind-address='0.0.0.0' --topology=group --single-primary  部署一套单主MGR集群

dbdeployer deploy single  5.7.25 --bind-address='0.0.0.0' --gtid  部署一台单机GTID的MySQL5.7实例

dbdeployer deploy single 5.7.25 --bind-address='0.0.0.0' --gtid --init-general-log --enable-general-log --disable-mysqlx --force  #强制覆盖部署一他gtid 5.7实例，并且generallog

dbdeployer global status     列出全部sanbox实例的情况

dbdeployer delete rsandbox_8_0_15  删除 rsandbox_8_0_15 实例

下载最新的包：https://github.com/datacharmer/dbdeployer/releases  
解压：  tar -xzf dbdeployer-1.5.0.linux.tar.gz
赋予执行权限： chmod +x dbdeployer-1.5.0.linux
拷贝到bin目录： sudo mv dbdeployer-1.5.0.linux /usr/local/bin/dbdeployer
 
  部署MySQL环境都是秒级别（10-30秒，根据实例多少有关），使用起来很方便，还有各种管理命令。

  dbdeployer delete rsandbox_8_0_11 --sandbox-home=/home/data -- sandbox-binary=/usr/local;

   --这里的：rsandbox_8_0_11  是文件夹名称

   --部署1主多从（1拖4) (using ports 18601, 18602, 18603,18604)
   dbdeployer deploy replication 8.0.11 --bind-address=0.0.0.0 --base-port=18600 --sandbox-home=/home/data --sandbox-binary=/usr/local --nodes=4;

   --部署4个mysql,(using ports 18601, 18602, 18603,18604)
   dbdeployer deploy multiple 8.0.11 --bind-address=0.0.0.0 --base-port=18600 --sandbox-home=/home/data --sandbox-binary=/usr/local --nodes=4;

   安装3主2从（多主多从）：

     dbdeployer deploy replication --topology=fan-in   --nodes=5 --master-list="1,2,3"  --bind-address=0.0.0.0   --native-auth-plugin  --slave-list="4,5"  8.0.11 --sandbox-home=/home/data  --remote-access="%" --sandbox-binary=/usr/local  --db-password="cc.123" --db-user="sa"  --post-grants-sql=" grant all on *.* to 'sa'@'%' WITH GRANT OPTION "

      --remote-access="%"  账户远程访问的IP，% 为全部

      --db-user 新数据库账户 

      --db-password 数据库密码

      --post-grants-sql  后面执行的数据库授权Sql

       --native-auth-plugin  客户端如SQLLog可以访问MySQL，是在MySQL 8.0.4+ 有效

   安装组复制：

    dbdeployer deploy replication --topology=group     --bind-address=0.0.0.0      8.0.11 --sandbox-home=/home/data --remote-access="%" --sandbox-binary=/usr/local  --native-auth-plugin  --db-password="cc.123" --db-user="sa"  --post-grants-sql=" grant all on *.* to 'sa'@'%' WITH GRANT OPTION ";

   加上： --single-primary  明确是单主   

    dbdeployer deploy --topology=all-masters replication  --bind-address=0.0.0.0   8.0.11 --sandbox-home=/home/td  --remote-access="%" --sandbox-binary=/usr/local  --native-auth-plugin --db-password="cc.123" --db-user="sa"  --post-grants-sql=" grant all on *.* to 'sa'@'%' WITH GRANT OPTION " --concurrent；

详细说明:
      --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
      --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
      --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.%")
      --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 replication 5.7.21
        # (implies topology = master-slave) 默认主从

        $ dbdeployer deploy --topology=master-slave replication 5.7.21
        # (explicitly setting topology) 明确主从

        $ dbdeployer deploy --topology=group replication 5.7.21  组复制多主
        $ dbdeployer deploy --topology=group replication 8.0.4 --single-primary 组复制单主
        $ dbdeployer deploy --topology=all-masters replication 5.7.21   多主复制
        $ dbdeployer deploy --topology=fan-in replication 5.7.21  多主单（多)从复制 

   注意： 安装的过程中当前目录一定有，不然报错。比如在删除的rsandbox_8_0_11文件夹下执行dbdeployer命令

    配置后的备份：cd到MySQL的bin下执行   

./mysqldump -h 192.168.60.167  -u sa -p -P8011 --socket=/tmp/mysql_sandbox8011.sock  test > test_db.sql;

About Me