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 查看帮助
可以参看文档:
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。