skeema简单使用

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环境

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

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

使用

init

在演练环境

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

-d dump的sql文件存储路径

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

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

-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文件

#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], 并增加一些参数

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参数

添加生产环境

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

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

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

最终的.skeema文件如下

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

拉取生产环境表结构

cd 3358
skeema pull product -p

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

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

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

查看生产的文件

-- 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 `ytree_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 `ytree_iam_role_members_profile_id_ce949eff_fk_users_profile_uid` FOREIGN KEY (`profile_id`) REFERENCES `sss_users_profile` (`uid`),
  CONSTRAINT `ytree_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命令, 记得把"`"转义掉