Use SchemaSync to automatically synchronize MySql _ MySQL

SchemaSync is an open-source command line tool for MySql synchronization. it is used to read the schema of the source and target databases and automatically generate SQL statements for synchronous update and rollback to facilitate automatic Schema synchronization.

Web: http://www.schemasync.org

Source code: https://github.com/mmatuson/SchemaSync

Command format

Schemasync [options]

# Source/target format: mysql: // user: pass @ host: port/database

# Output (SQL script ): [_ ]. YYYYMMDD. (patch revert )[_ ]. SQL

Usage

Download SchemaSync first:

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

Decompress the package:

Tar xvzf SchemaSync-0.9.2.tar.gz

Execute the installation (python setuptools is required)

Python setup. py install

You also need to install python-mySQLdb

Apt-get install python-mysqldb

Now, you can use the schemasync command to compare any two mysql DB Instances and automatically generate SQL statements for synchronization and rollback between the two instances:

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

We can see that it automatically generates two SQL files. * patch. SQL is the updated SQL script, and * revert. SQL is the SQL script used for rollback. The following is an example of two scripts.

Output file content

More db_ B .20140716.patch. SQL

--

-- Schema Sync 0.9.1 Patch Script

-- Created: Wed, Jul 16,201 4

-- 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 'body' ('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,201 4

-- 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_generaRochelle CI;

Drop table 'contacts ';

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 DEFAULTNULL, 'timer _ end' bigint (20) unsigned default null, 'timer _ wait' bigint (20) Unsigned default null, 'spin' 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 DEFAULTCHARSET = 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 DEFAULTNULL, 'timer _ end' bigint (20) unsigned default null, 'timer _ wait' bigint (20) Unsigned default null, 'spin' 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 DEFAULTCHARSET = 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, 'spin' 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 DEFAULTNULL, '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 _ start' 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) unsignedNot 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' inT (11) default null, 'name' varchar (128) not null) ENGINE = PERFORMANCE_SCHEMA default charset = utf8;