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>


# source/target format: mysql://user:pass@host:port/database
# output format: <database>[_<tag>].YYYYMMDD.(patch|revert)[_<version>].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_unicode_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,回滚时候用

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15498/viewspace-2155639/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15498/viewspace-2155639/