用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脚本): 使用方法 先下载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; |
- 上一篇:MySQL循环语句_MySQL
- 下一篇:mysql TIMESTAMP_MySQL