mysql表結構同步工具SchemaSync使用初探

SchemaSync是一個開源的命令列工具，用於讀取源DB和目標DB的schema，自動生成同步更新和回滾的sql，方便自動化的Schema同步。


下載地址:


找到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,回滾時候用