用SchemaSync自动进行MySql同步_MySQL

SchemaSync是一个开源的命令行工具，用于读取源DB和目标DB的schema，自动生成同步更新和回滚的sql，方便自动化的Schema同步。

网址：http://www.schemasync.org

源码：https://github.com/mmatuson/SchemaSync

命令格式

schemasync [options]

# source/target 格式: mysql://user:pass@host:port/database

# 输出(sql脚本): [_].YYYYMMDD.(patch revert)[_].sql

使用方法

先下载SchemaSync：

wget http://www.schemasync.org/downloads/SchemaSync-0.9.2.tar.gz

然后解压：

tar xvzf SchemaSync-0.9.2.tar.gz

执行安装（需要python的setuptools）

python setup.py install

还需要安装 python-mySQLdb

apt-get install python-mysqldb

好了，现在可以用schemasync命令比较任意两个mysql DB实例，并自动生成两者之间同步和回滚的SQL语句：

schemasync mysql://root:pass@host1:3306/db_A mysql://root:pass@host2:3306/db_B

Migration scripts created for mysql://localhost/db_B

Patch Script: ~/db_B.20140716.patch.sql

Revert Script: ~/db_B.20140716.revert.sql

可以看到它自动产生了两个sql文件，*patch.sql是更新的sql脚本，*revert.sql则是回滚用的sql脚本。下面列出两个脚本的示例。

输出文件内容

more db_B.20140716.patch.sql

--

-- Schema Sync 0.9.1 Patch Script

-- Created: Wed, Jul 16, 2014

-- Server Version: 5.5.37-0ubuntu0.12.04.1-log

-- Apply To: host2/db_B

--

USE `db_B`;

ALTER DATABASE `db_B` CHARACTER SET=latin1 COLLATE=latin1_swedish_ci;

CREATE TABLE `contact` ( `Id` int(11) DEFAULT NULL, `PhoneNumber` varchar(255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `person` ( `Id` int(11) DEFAULT NULL, `LastName` varchar(255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE `cond_instances`;

DROP TABLE `events_waits_current`;

DROP TABLE `events_waits_history`;

DROP TABLE `events_waits_history_long`;

DROP TABLE `events_waits_summary_by_instance`;

DROP TABLE `events_waits_summary_by_thread_by_event_name`;

DROP TABLE `events_waits_summary_global_by_event_name`;

DROP TABLE `file_instances`;

DROP TABLE `file_summary_by_event_name`;

DROP TABLE `file_summary_by_instance`;

DROP TABLE `mutex_instances`;

DROP TABLE `performance_timers`;

DROP TABLE `rwlock_instances`;

DROP TABLE `setup_consumers`;

DROP TABLE `setup_instruments`;

DROP TABLE `setup_timers`;

DROP TABLE `threads`;

more db_B.20140716.revert.sql

--

-- Schema Sync 0.9.1 Revert Script

-- Created: Wed, Jul 16, 2014

-- Server Version: 5.5.37-0ubuntu0.12.04.1-log

-- Apply To: host2/db_B

--

USE `db_B`;

ALTER DATABASE `db_B` CHARACTER SET=utf8 COLLATE=utf8_general_ci;

DROP TABLE `contact`;

DROP TABLE `person`;

CREATE TABLE `cond_instances` ( `NAME` varchar(128) NOT NULL, `OBJECT_INSTANCE_BEGIN` bigint(20) NOT NULL) ENGINE=db_B DEFAULT CHARSET=utf8;

CREATE TABLE `events_waits_current` ( `THREAD_ID` int(11) NOT NULL, `EVENT_ID` bigint(20) unsigned NOT NULL, `EVENT_NAME` varchar(128) NOT NULL, `SOURCE` varchar(64) DEFAULT NULL, `TIMER_START` bigint(20) unsigned DEFAULT NULL, `TIMER_END` bigint(20) unsigned DEFAULT NULL, `TIMER_WAIT` bigint(20) unsigned DEFAULT NULL, `SPINS` int(10) unsigned DEFAULT NULL, `OBJECT_SCHEMA` varchar(64) DEFAULT NULL, `OBJECT_NAME` varchar(512) DEFAULT NULL, `OBJECT_TYPE` varchar(64) DEFAULT NULL, `OBJECT_INSTANCE_BEGIN` bigint(20) NOT NULL, `NESTING_EVENT_ID` bigint(20) unsigned DEFAULT NULL, `OPERATION` varchar(16) NOT NULL, `NUMBER_OF_BYTES` bigint(20) unsigned DEFAULT NULL, `FLAGS` int(10) unsigned DEFAULT NULL) ENGINE=db_B DEFAULT CHARSET=utf8;

CREATE TABLE `events_waits_history` ( `THREAD_ID` int(11) NOT NULL, `EVENT_ID` bigint(20) unsigned NOT NULL, `EVENT_NAME` varchar(128) NOT NULL, `SOURCE` varchar(64) DEFAULT NULL, `TIMER_START` bigint(20) unsigned DEFAULT NULL, `TIMER_END` bigint(20) unsigned DEFAULT NULL, `TIMER_WAIT` bigint(20) unsigned DEFAULT NULL, `SPINS` int(10) unsigned DEFAULT NULL, `OBJECT_SCHEMA` varchar(64) DEFAULT NULL, `OBJECT_NAME` varchar(512) DEFAULT NULL, `OBJECT_TYPE` varchar(64) DEFAULT NULL, `OBJECT_INSTANCE_BEGIN` bigint(20) NOT NULL, `NESTING_EVENT_ID` bigint(20) unsigned DEFAULT NULL, `OPERATION` varchar(16) NOT NULL, `NUMBER_OF_BYTES` bigint(20) unsigned DEFAULT NULL, `FLAGS` int(10) unsigned DEFAULT NULL) ENGINE=db_B DEFAULT CHARSET=utf8;

CREATE TABLE `events_waits_history_long` ( `THREAD_ID` int(11) NOT NULL, `EVENT_ID` bigint(20) unsigned NOT NULL, `EVENT_NAME` varchar(128) NOT NULL, `SOURCE` varchar(64) DEFAULT NULL, `TIMER_START` bigint(20) unsigned DEFAULT NULL, `TIMER_END` bigint(20) unsigned DEFAULT NULL, `TIMER_WAIT` bigint(20) unsigned DEFAULT NULL, `SPINS` int(10) unsigned DEFAULT NULL, `OBJECT_SCHEMA` varchar(64) DEFAULT NULL, `OBJECT_NAME` varchar(512) DEFAULT NULL, `OBJECT_TYPE` varchar(64) DEFAULT NULL, `OBJECT_INSTANCE_BEGIN` bigint(20) NOT NULL, `NESTING_EVENT_ID` bigint(20) unsigned DEFAULT NULL, `OPERATION` varchar(16) NOT NULL, `NUMBER_OF_BYTES` bigint(20) unsigned DEFAULT NULL, `FLAGS` int(10) unsigned DEFAULT NULL) ENGINE=db_B DEFAULT CHARSET=utf8;

CREATE TABLE `events_waits_summary_by_instance` ( `EVENT_NAME` varchar(128) NOT NULL, `OBJECT_INSTANCE_BEGIN` bigint(20) NOT NULL, `COUNT_STAR` bigint(20) unsigned NOT NULL, `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL, `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL, `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL, `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8;

CREATE TABLE `events_waits_summary_by_thread_by_event_name` (`THREAD_ID` int(11) NOT NULL, `EVENT_NAME` varchar(128) NOT NULL, `COUNT_STAR` bigint(20) unsigned NOT NULL, `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL, `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL, `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL, `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8;

CREATE TABLE `events_waits_summary_global_by_event_name` ( `EVENT_NAME` varchar(128) NOT NULL, `COUNT_STAR` bigint(20) unsigned NOT NULL, `SUM_TIMER_WAIT` bigint(20) unsigned NOT NULL, `MIN_TIMER_WAIT` bigint(20) unsigned NOT NULL, `AVG_TIMER_WAIT` bigint(20) unsigned NOT NULL, `MAX_TIMER_WAIT` bigint(20) unsigned NOT NULL) ENGINE=db_B DEFAULT CHARSET=utf8;

CREATE TABLE `file_instances` ( `FILE_NAME` varchar(512) NOT NULL, `EVENT_NAME` varchar(128) NOT NULL, `OPEN_COUNT` int(10) unsigned NOT NULL) ENGINE=db_B DEFAULT CHARSET=utf8;

CREATE TABLE `file_summary_by_event_name` ( `EVENT_NAME` varchar(128) NOTNULL, `COUNT_READ` bigint(20) unsigned NOT NULL, `COUNT_WRITE` bigint(20) unsigned NOT NULL, `SUM_NUMBER_OF_BYTES_READ` bigint(20) unsigned NOT NULL, `SUM_NUMBER_OF_BYTES_WRITE` bigint(20) unsigned NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8;

CREATE TABLE `file_summary_by_instance` ( `FILE_NAME` varchar(512) NOT NULL, `EVENT_NAME` varchar(128) NOT NULL, `COUNT_READ` bigint(20) unsigned NOT NULL, `COUNT_WRITE` bigint(20) unsigned NOT NULL, `SUM_NUMBER_OF_BYTES_READ` bigint(20) unsigned NOT NULL, `SUM_NUMBER_OF_BYTES_WRITE` bigint(20) unsigned NOT NULL) ENGINE=db_B DEFAULT CHARSET=utf8;

CREATE TABLE `mutex_instances` ( `NAME` varchar(128) NOT NULL, `OBJECT_INSTANCE_BEGIN` bigint(20) NOT NULL, `LOCKED_BY_THREAD_ID` int(11) DEFAULT NULL) ENGINE=db_B DEFAULT CHARSET=utf8;

CREATE TABLE `performance_timers` ( `TIMER_NAME` enum('CYCLE','NANOSECOND','MICROSECOND','MILLISECOND','TICK') NOT NULL, `TIMER_FREQUENCY` bigint(20) DEFAULT NULL, `TIMER_RESOLUTION` bigint(20) DEFAULT NULL, `TIMER_OVERHEAD` bigint(20) DEFAULT NULL) ENGINE=db_B DEFAULT CHARSET=utf8;

CREATE TABLE `rwlock_instances` ( `NAME` varchar(128) NOT NULL, `OBJECT_INSTANCE_BEGIN` bigint(20) NOT NULL, `WRITE_LOCKED_BY_THREAD_ID` int(11) DEFAULT NULL, `READ_LOCKED_BY_COUNT` int(10) unsigned NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8;CREATE TABLE `setup_consumers` ( `NAME` varchar(64) NOT NULL, `ENABLED` enum('YES','NO') NOT NULL) ENGINE=db_B DEFAULT CHARSET=utf8;

CREATE TABLE `setup_instruments` ( `NAME` varchar(128) NOT NULL, `ENABLED` enum('YES','NO') NOT NULL, `TIMED` enum('YES','NO') NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8;

CREATE TABLE `setup_timers` ( `NAME` varchar(64) NOT NULL, `TIMER_NAME` enum('CYCLE','NANOSECOND','MICROSECOND','MILLISECOND','TICK') NOT NULL) ENGINE=db_B DEFAULT CHARSET=utf8;

CREATE TABLE `threads` ( `THREAD_ID` int(11) NOT NULL, `PROCESSLIST_ID` in t(11) DEFAULT NULL, `NAME` varchar(128) NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8;