dbdeployer 部署mysql测试环境

dbdeployer是一种可以轻松部署安装mysql服务器的工具，支持不同版本的数据库的一键安装

支持如下数据库部署

· Oracle MySQL

· Percona MySQL

· MariaDB

· TiDB

· MySQL NDB Cluster

· Percona XtraDB Cluster

· mysql-shell

接下我们只讨论官方mysql的安装部署

1. 首先安装工具

Linux下载最新的安装包

wget https://github.com/datacharmer/dbdeployer/releases/download/v1.56.0/dbdeployer-1.56.0-docs.linux.tar.gz

解压就可以了

tar -zxvf dbdeployer-1.56.0-docs.linux.tar.gz

mv dbdeployer-1.56.0-docs.linux dbdeployer

./dbdeployer –help 查看工具的帮助

2. 查看工具的默认配置

[root@node02 deploy]# ./dbdeployer defaults show

# Internal values:

{

"version": "1.54.0",

"sandbox-home": "$HOME/sandboxes",

"sandbox-binary": "$HOME/opt/mysql",

"use-sandbox-catalog": true,

"log-sb-operations": false,

"log-directory": "/root/sandboxes/logs",

"cookbook-directory": "recipes",

"shell-path": "/usr/bin/bash",

"master-slave-base-port": 11000,

"group-replication-base-port": 12000,

"group-replication-sp-base-port": 13000,

"fan-in-replication-base-port": 14000,

"all-masters-replication-base-port": 15000,

"multiple-base-port": 16000,

"pxc-base-port": 18000,

"ndb-base-port": 19000,

"ndb-cluster-port": 20000,

"group-port-delta": 125,

"mysqlx-port-delta": 10000,

"admin-port-delta": 11000,

"master-name": "master",

"master-abbr": "m",

"node-prefix": "node",

"slave-prefix": "slave",

"slave-abbr": "s",

"sandbox-prefix": "msb_",

"imported-sandbox-prefix": "imp_msb_",

"master-slave-prefix": "rsandbox_",

"group-prefix": "group_msb_",

"group-sp-prefix": "group_sp_msb_",

"multiple-prefix": "multi_msb_",

"fan-in-prefix": "fan_in_msb_",

"all-masters-prefix": "all_masters_msb_",

"reserved-ports": [

1186,

3306,

33060,

33062

],

"remote-repository": "https://raw.githubusercontent.com/datacharmer/mysql-docker-minimal/master/dbdata",

"remote-index-file": "available.json",

"remote-completion-url": "https://raw.githubusercontent.com/datacharmer/dbdeployer/master/docs/dbdeployer_completion.sh",

"remote-tarball-url": "https://raw.githubusercontent.com/datacharmer/dbdeployer/master/downloads/tarball_list.json",

"pxc-prefix": "pxc_msb_",

"ndb-prefix": "ndb_msb_",

"default-sandbox-executable": "default",

"download-name-linux": "mysql-{{.Version}}-linux-glibc2.17-x86_64{{.Minimal}}.{{.Ext}}",

"download-name-macos": "mysql-{{.Version}}-macos10.15-x86_64.{{.Ext}}",

"download-url": "https://dev.mysql.com/get/Downloads/MySQL",

"timestamp": "Thu Dec 3 11:11:01 CST 2020"

}

[root@node02 deploy]#

"sandbox-home": "$HOME/sandboxes",

"sandbox-binary": "$HOME/opt/mysql",

sandbox-binary mysql安装包解压存放的位置

sandbox-home mysql 数据文件存放路径

如果不想放在默认用户的家目录下可以进行修改

dbdeployer defaults update

[root@node02 deploy]# ./dbdeployer defaults update sandbox-home /data/sandboxes

# Updated sandbox-home -> "/data/sandboxes"

# Configuration file: /root/.dbdeployer/config.json

{

"version": "1.54.0",

"sandbox-home": "/data/sandboxes",

"sandbox-binary": "$HOME/opt/mysql",

[root@node02 deploy]# ./dbdeployer defaults update sandbox-binary /data/opt/mysql

# Updated sandbox-binary -> "/data/opt/mysql"

# Configuration file: /root/.dbdeployer/config.json

{

"version": "1.54.0",

"sandbox-home": "/data/sandboxes",

"sandbox-binary": "/data/opt/mysql",

"use-sandbox-catalog": true,

3. 初始化：

./dbdeployer init

创建目录和初始环境变量并下载最新的mysql数据库

4. 下载数据库安装包

查看下载列表：

./dbdeployer downloads list

下载指定版本的数据库不解压

./dbdeployer downloads get mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

解压安装包

./dbdeployer unpack mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz

下载并解压：

./dbdeployer downloads get-unpack mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz

注意：下载安装tar存放工具的当前目录下，解压到sandbox-binary指定的目录并进行命名

5. 部署安装mysql

安装单节点mysql 5.7.31

./dbdeployer deploy single 5.7.31

-p, --db-password string database password (default "msandbox")

-u, --db-user string database user (default "msandbox")

./dbdeployer deploy single 5.7.31 -u wangwei -p guiji@123

默认的用户名和密码是msandbox/ msandbox

安装主从复制（默认一主两从）

./dbdeployer deploy replication 8.0.18

或者

dbdeployer deploy --topology=master-slave replication 5.7

安装mgr 单主模式环境

./dbdeployer deploy --topology=group replication 8.0 --single-primary

6. 数据库的使用安装目录下的脚本使用

metadata         Runs a metadata query in all sandboxes

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

可以在目录下执行./执行脚本

也可以通过命令行

$ dbdeployer global use "select version()"

$ dbdeployer global status

$ dbdeployer global stop --version=5.7.27

$ dbdeployer global stop --short-version=8.0

$ dbdeployer global status --port-range=5000-8099

$ dbdeployer global start --flavor=percona

$ dbdeployer global start --flavor='!percona' --type=single

$ dbdeployer global metadata version --flavor='!percona' --type=single

[root@node02 deploy]# ./dbdeployer global use "select version()" --version=5.7.31

# Running "use" on msb_5_7_31

version()

5.7.31

./user直接调用脚本登录数据库

停止启动数据库

[root@node02 deploy]# ./dbdeployer global stop --version=5.7.31

# Running "stop" on msb_5_7_31

stop /data/sandboxes/msb_5_7_31

[root@node02 deploy]# ./dbdeployer global start --version=5.7.31

# Running "start" on msb_5_7_31

. sandbox server started

重启数据库：

[root@node02 deploy]# ./dbdeployer global restart --version=5.7.31

# Running "restart" on msb_5_7_31

stop /data/sandboxes/msb_5_7_31

. sandbox server started

直接调用脚本：

[root@node02 msb_5_7_31]# ./restart

stop /data/sandboxes/msb_5_7_31

. sandbox server started

[root@node02 msb_5_7_31]#

删除指定的数据库实例和所有实例

./dbdeployer delete msb_5_7_31

./dbdeployer delete all 删除所有

1. Mysql 在初始化的时候使用默认的参数初始化的，要调整参数可以通过

通过 --my-cnf-options=” ”

[root@node02 deploy]# ./dbdeployer deploy single 8.0.18 --my-cnf-options="character_set_server=utf8mb4"

Database installed in /data/sandboxes/msb_8_0_18

run 'dbdeployer usage single' for basic instructions'

. sandbox server started

参数会添加到配置文件中my.sandbox.cnf

添加多个配置就写多个--my-cnf-options= 例如：

./dbdeployer deploy single 5.7.31 --my-cnf-options="server_id=2223306" --my-cnf-options="character_set_server=utf8mb4"

2. 问题

在8.0以上版本的添加表名称忽略大小写问题的参数数据启动报错

--my-cnf-options="lower_case_table_names = 1"

查看erro日志

2020-12-03T06:42:46.057632Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').

2020-12-03T06:42:46.058063Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.

2020-12-03T06:42:46.058558Z 0 [ERROR] [MY-010119] [Server] Aborting

通过错误发现就是初始化的时候lower_case_table_names 是0 启动的是1 导致的错误

从mysql8.0开始lower_case_table_names只能在初始化的时候指定，初始化后不能改变这个值，所以启动报错

lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited.

也就是说通过--my-cnf-options添加的参数是在启动的时候生效的

通过源码的templates.go中初始化模板

用./dbdeployer defaults templates list 也可以看到有一个模板init_db_template : Initialization template for the database

查看模板的内容：

./dbdeployer defaults templates show init_db_template

所以要解决这个问题有两种方法

1. 通过改源码模板重新编译

2. 直接用指令改模板的内容

我通过第二种方式解决这个问题

导出模板到当前目录tempdir

[root@node02 deploy]# ./dbdeployer defaults templates export single tempdir

更新模板内容

vim init_db_template

添加--lower-case-table-names=1 保存然后导入模板

导入模板：

[root@node02 deploy]# ./dbdeployer defaults templates import single tempdir

查看可以到模板已经更新了

再次使用

./dbdeployer deploy single 8.0.18 --my-cnf-options="lower_case_table_names = 1" 部署

部署成功

所以如果有参数初始后不能改变的，建议直接添加到模板，不用默认的配置

如果想添加很多参数可以通过--my-cnf-file 指定参数文件，会把指定的参数文件合并到my.sandbox.cnf 参数文件中 注意这里只对[mysqld] 有效

./dbdeployer deploy single 8.0.18 --my-cnf-file=my3306.cnf

Dbdeployer 功能强大参数众多可以通过—help 查看帮助

可以参看文档：

https://github.com/datacharmer/dbdeployer