Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use SchemaSync, a synchronization tool for mysql table structure. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

SchemaSync is an open source command-line tool that reads the schema of the source DB and the target DB, automatically generates synchronous updates and rollback sql, and facilitates automated Schema synchronization.

Download address:

Https://github.com/mmatuson/SchemaSync

Find Clone or download and download SchemaSync-master.zip

Extract and install:

# unzip SchemaSync-master.zip

# cd SchemaSync-master

# python setup.py install

The syntax for running SchemaSync is as follows:

SYNOPSIS

=

Schemasync [options]

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

# output format: [_] .YYYYMMDD. (patch | revert) [_] .sql

An example of an actual operation:

Environment: python2.7

MySQL version: 5.7.21

Database character set: 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

Remarks:-- tag can be named as you like

[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, `accountion`varchar (1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT'', `userlevel`varchar (1024) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT'', `author`varchar (1024) 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`Url` (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 (128C) 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 `commentUrlMd5` (`commentUrlMd5`), KEY `commentUrlId` (`commentId`), KEY `commentId` (`ctime`), KEY `index_ monitorId` (`monitorId`), KEY `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 purl 44purl 58647] 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

The explanation is as follows:

S91_DATABASE.20180605.patch.sql is patch sql, and you need to log in to slave library 192.168.0.52 to execute.

# mysql-uroot-p-h 192.168.0.52

Mysql > use S91

Mysql > source S91_DATABASE.20180605.patch.sql

S91_DATABASE.20180605.revert.sql is the rollback SQL, which is used when rollback

On "mysql table structure synchronization tool SchemaSync" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.