合 MySQL环境部署神器dbdeployer，Sandbox的替代品
Tags： MySQLdbdeployer
👉 本文共约3312个字，系统预计阅读时间或需13分钟。
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的安装包
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
