skeema简单使用

简介

Skeema is a tool for managing MySQL tables and schema changes in a declarative fashion using pure SQL. It provides a CLI tool allowing you to:

  • Export CREATE TABLE statements to the filesystem, for tracking in a repo (git, hg, svn, etc)
  • Diff changes in the schema repo against live DBs to automatically generate DDL
  • Manage multiple environments (e.g. dev, staging, prod) and keep them in sync with ease
  • Configure use of online schema change tools, such as pt-online-schema-change, for performing ALTERs
  • Convert non-online migrations from frameworks like Rails or Django into online schema changes in production

Skeema supports a pull-request-based workflow for schema change submission, review, and execution. This permits your team to manage schema changes in exactly the same way as you manage code changes. Our new companion Cloud Linter for GitHub repos provides automatic linting of schema change commits and pull requests.

我这的需求是同步生产环境表结构到演练环境, 以下是针对我这个场景的具体使用方法

权限

skeema需要的用户权限具体见https://www.skeema.io/docs/requirements/

这里说一下重点, skeema diff时会在目标环境创建一个_skeema_tmp临时数据库, 然后会删除这个库

安装

安装go环境

1
2
3
wget https://dl.google.com/go/go1.16.linux-amd64.tar.gz
tar -C /usr/local -xzf go1.16.linux-amd64.tar.gz
export PATH=$PATH:/usr/local/go/bin

安装skeema

1
2
curl -LO https://github.com/skeema/skeema/releases/latest/download/skeema_amd64.rpm
rpm -ivh skeema

使用

init

在演练环境

1
skeema init -h 127.0.0.1 -P 3358 -u fanboshi -p -d 3358 --schema sss

-d dump的sql文件存储路径

–schema 只导出sss这个数据库的结构信息

随后3358目录下回包含一些.sql文件和一个.skeema文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-rw-r--r--   1 root root  213 Feb 18 12:37 .skeema
-rw-r--r-- 1 root root 1521 Feb 18 12:33 sss_cmdb_instances.sql
-rw-r--r-- 1 root root 305 Feb 18 12:33 sss_cmdb_vip.sql
-rw-r--r-- 1 root root 1122 Feb 18 12:33 sss_host_host.sql
-rw-r--r-- 1 root root 388 Feb 18 12:33 sss_cmdb_cluster_domain_name_config.sql
-rw-r--r-- 1 root root 369 Feb 18 12:33 sss_cmdb_cluster_portrayal_mapping.sql
-rw-r--r-- 1 root root 602 Feb 18 12:33 sss_cmdb_cluster.sql
-rw-r--r-- 1 root root 1148 Feb 18 12:33 sss_cmdb_cluster_users.sql
-rw-r--r-- 1 root root 304 Feb 18 12:33 sss_cmdb_instance_ports.sql
-rw-r--r-- 1 root root 756 Feb 18 12:33 sss_cmdb_instances_port.sql
-rw-r--r-- 1 root root 244 Feb 18 12:33 sss_cmdb_productlines.sql
-rw-r--r-- 1 root root 595 Feb 18 12:33 sss_cmdb_services.sql
-rw-r--r-- 1 root root 274 Feb 18 12:33 sss_dbms_environment.sql
-rw-r--r-- 1 root root 815 Feb 18 12:33 sss_dbms_sql_config.sql
-rw-r--r-- 1 root root 625 Feb 18 12:33 sss_cmdb_environments.sql

编辑.skeema文件

1
2
3
4
5
6
7
8
9
10
#cat .skeema 
default-character-set=utf8
default-collation=utf8_general_ci
schema=sss

[production]
flavor=percona:5.7
host=127.0.0.1
port=3358
user=fanboshi

我们这里修改[production][drill], 并增加一些参数

1
2
3
4
5
6
7
8
9
10
11
alter-wrapper="gh-ost --allow-on-master --assume-rbr  --initially-drop-ghost-table --initially-drop-old-table -exact-rowcount --approve-renamed-columns --concurrent-rowcount=false --chunk-size=800 --hooks-path=/tmp/hook --user={USER} --ask-pass --host={HOST} --port={PORT} --database={SCHEMA} --table={TABLE} --alter={CLAUSES} --execute"
default-character-set=utf8
default-collation=utf8_general_ci
schema=sss

[drill]
flavor=percona:5.7
host=127.0.0.1
port=3358
user=fanboshi
password=superpass #可加可不加, 如果没有定义password, 则需要在命令行增加-p参数

添加生产环境

1
skeema add-environment product -h 172.16.120.11 -P 3358 -u fanboshi 

或者直接在.skeema添加如下信息

1
2
3
4
5
[product]
flavor=percona:5.7
host=172.16.120.11
port=3358
user=fanboshi

最终的.skeema文件如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
alter-wrapper="gh-ost --allow-on-master --assume-rbr  --initially-drop-ghost-table --initially-drop-old-table -exact-rowcount --approve-renamed-columns --concurrent-rowcount=false --chunk-size=800 --hooks-path=/tmp/hook --user={USER} --ask-pass --host={HOST} --port={PORT} --database={SCHEMA} --table={TABLE} --alter={CLAUSES} --execute"
default-character-set=utf8
default-collation=utf8_general_ci
schema=sss

[drill]
flavor=percona:5.7
host=127.0.0.1
port=3358
user=fanboshi
#password=superpass #可加可不加, 如果没有定义password, 则需要在命令行增加-p参数

[product]
flavor=percona:5.7
host=172.16.120.11
port=3358
user=fanboshi

拉取生产环境表结构

1
2
cd 3358
skeema pull product -p

生成drill环境与生产环境的差异sql文件

1
skeema diff drill --allow-unsafe -psuperpass > /tmp/diff.sql 

如果没有在.skeema文件中指定password参数, 则要在命令行指定-p参数并填写密码

查看生产的文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- instance: 127.0.0.1:3358
USE `sss`;
\! gh-ost --allow-on-master --assume-rbr --initially-drop-ghost-table --initially-drop-old-table -exact-rowcount --approve-renamed-columns --concurrent-rowcount=false --chunk-size=800 --hooks-path=/tmp/hook --user=fanboshi --ask-pass --host=127.0.0.1 --port=3358 --database=sss --table=sss_cmdb_service_name --alter='DROP COLUMN `environment_name`, DROP COLUMN `productline_name`, DROP COLUMN `cmdb_cluster_name`' --execute
CREATE TABLE `sss_domain_manage` (
`id` int(11) NOT NULL AUTO_INCREMENT,
...
PRIMARY KEY (`id`),
UNIQUE KEY `domain_name` (`domain_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `sss_iam_role_members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
...
UNIQUE KEY `sss_iam_role_members_role_id_profile_id_93c492ff_uniq` (`role_id`,`profile_id`),
KEY `ytree_iam_role_members_profile_id_ce949eff_fk_users_profile_uid` (`profile_id`),
CONSTRAINT `sss_iam_role_members_profile_id_ce949eff_fk_users_profile_uid` FOREIGN KEY (`profile_id`) REFERENCES `sss_users_profile` (`uid`),
CONSTRAINT `sss_iam_role_members_role_id_8a1e407b_fk_ytree_iam_role_id` FOREIGN KEY (`role_id`) REFERENCES `sss_iam_role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

可以看到这就是一个.sql文件, 注意生成的gh-ost语句前有一个\!, 大家应该明白是啥意思吧

如果是想手动执行gh-ost命令, 记得把”`”转义掉

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2022 Fan() All Rights Reserved.

访客数 : | 访问量 :