MySQL环境部署神器dbdeployer，Sandbox的替代品
Tags： dbdeployerMySQL
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 | --- 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 实例
1 2 3 4 5 6 | 下载最新的包：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;
1 | --这里的：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 --top
