mysql表结构同步工具SchemaSync怎么用

这篇文章将为大家详细讲解有关mysql表结构同步工具SchemaSync怎么用，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。
SchemaSync是一个开源的命令行工具，用于读取源DB和目标DB的schema，自动生成同步更新和回滚的sql，方便自动化的Schema同步。

下载地址:
https://github.com/mmatuson/SchemaSync

找到Clone or download，然后下载SchemaSync-master.zip

解压缩并安装:
#unzip SchemaSync-master.zip
#cd SchemaSync-master
#python setup.py install

SchemaSync运行语法如下：
SYNOPSIS
========
schemasync [options]

# source/target format: mysql://user:pass@host:port/database
# output format: [_].YYYYMMDD.(patch|revert)[_].sql

实际运行的一个例子:
环境:python2.7
MySQL版本:5.7.21
数据库字符集:utf8mb4
root@test ~/20180605/new]#schemasync mysql://root:123456@192.168.0.51:3306/S91 mysql://root:123456@192.168.0.52:3306/S91 –tag=DATABASE
Migration scripts created for mysql://192.168.0.52/S91
Patch Script: /root/20180605/new/S91_DATABASE.20180605.patch.sql
Revert Script: /root/20180605/new/S91_DATABASE.20180605.revert.sql

备注:–tag可以随便取名

[root@test ~/20180605/new]#ll
total 12
-rw-r–r– 1 root root 424 Jun 5 13:44 S91_DATABASE.20180605.patch.sql
-rw-r–r– 1 root root 2280 Jun 5 13:44 S91_DATABASE.20180605.revert.sql
-rw-r–r– 1 root root 221 Jun 5 13:44 schemasync.log

[root@test ~/20180605/new]#cat S91_DATABASE.20180605.patch.sql
—
— Schema Sync 0.9.4 Patch Script
— Created: Tue, Jun 05, 2018
— Server Version: 5.7.21-log
— Apply To: 192.168.140.52/S91
—

USE `S91`;
SET FOREIGN_KEY_CHECKS = 0;
ALTER DATABASE `S91` CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci;
DROP TABLE `test_comment_bak`;
ALTER TABLE `test_comment` ADD COLUMN `webnamePid` int(11) NOT NULL DEFAULT 0 AFTER `commentUrl`;
SET FOREIGN_KEY_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;

[root@test ~/20180605/new]#cat S91_DATABASE.20180605.revert.sql
—
— Schema Sync 0.9.4 Revert Script
— Created: Tue, Jun 05, 2018
— Server Version: 5.7.21-log
— Apply To: 192.168.140.52/S91
—

USE `S91`;
SET FOREIGN_KEY_CHECKS = 0;
ALTER DATABASE `S91` CHARACTER SET=utf8mb4 COLLATE=utf8mb4_general_ci;
CREATE TABLE `test_comment_bak` ( `id` bigint(20) NOT NULL AUTO_INCREMENT , `commentId` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `infoId` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 免费主机域名” , `productUrl` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `urlmd5` char(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `sourceType` varchar(4) COLLATE utf8mb4_unic免费主机域名ode_ci NOT NULL DEFAULT ‘0’ , `infoFlag` varchar(5) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `title` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `siteName` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `commenttitle` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `content` text COLLATE utf8mb4_unicode_ci NOT NULL , `description` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `userlevel` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `author` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `sourceIconUrl` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `webname` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `channel` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `ctime` datetime NOT NULL , `gtime` datetime NOT NULL , `affections` tinyint(4) NOT NULL , `monitorId` int(11) NOT NULL DEFAULT ‘0’ , `inputDbTime` datetime NOT NULL , `webDomain` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , `commentUrlMd5` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL , `commentUrl` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ” , PRIMARY KEY (`id`), UNIQUE KEY `index_commentUrlMd5` (`commentUrlMd5`), KEY `index_commentId` (`commentId`), KEY `index_ctime` (`ctime`), KEY `index_monitorId` (`monitorId`), KEY `index_webDomain` (`webDomain`), KEY `index_inputDbTime` (`inputDbTime`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `test_comment` DROP COLUMN `webnamePid`;
SET FOREIGN_KEY_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;

[root@test ~/20180605/new]#cat schemasync.log
[INFO 2018-06-05 13:44:58,647] Migration scripts created for mysql://192.168.0.52/S91
Patch Script: /root/20180605/new/S91_DATABASE.20180605.patch.sql
Revert Script: /root/20180605/new/S91_DATABASE.20180605.revert.sql

解释如下：
S91_DATABASE.20180605.patch.sql为patch sql，需要登陆到从库192.168.0.52上执行
# mysql -uroot -p -h 192.168.0.52
mysql>use S91
mysql>source S91_DATABASE.20180605.patch.sql

S91_DATABASE.20180605.revert.sql为回滚SQL,回滚时候用
关于“mysql表结构同步工具SchemaSync怎么用”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。

相关推荐: mysql数据库优化语句

本篇内容主要讲解“mysql数据库优化语句”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“mysql数据库优化语句”吧!mysql优化语句数据库语句： Ddl(数据定义语言) alter create drop Dm…