MySQL Fabric Auto Sync tool-schemasync

Background:

Recent projects in the development period, the basic daily test version submitted to test, in order to ensure that the test run smoothly, the test library needs to be synchronized, the first is to take the library of the development of the backup library to recover, covering the test library, but as the test progresses, the testers began to feedback that they made the test data is not, Changes the database synchronization method, uses the NAVICAT structure synchronization function to carry on the structure synchronization, but each time points to the point is too troublesome, changes the rollback is a troublesome matter and has no technical content. Inadvertently find a tool schemasync, you can compare the difference between the development library and the test library to generate patches and revert two SQL files, where path is a forward change, revert is the change of the rollback script, Since this synchronization database structure only need to dashing a command to execute a script, so easy.

Installation Requirements:

• Python 2.4,2.5,2.6 Other versions haven't tried, I'm using 2.6.

• MySQL requirements are above 5.0 and I use the thing Percona5.6

• MYSQLDB,1.2.1P2 or more

• Schemaobject 0.5.3 or more, installation will be installed automatically, no tube

Installation:

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

TAR-ZXVF TAR-ZXVF schemasync-0.9.2.tar.gz

CD SchemaSync-0.9.2

sudo python setup.py install

How to use:

Usage:

Schemasync [Options] <source> <target>

Source/target Format:mysql://user:[email protected]:p ort/database

A MySQL Schema Synchronization Utility

Options:

-H,--help show this help message and exit

-V,--version show version and exit.

-R,--revision if the name of the file to be generated already exists, enabling this parameter will add a serial number to the new file

-A,--sync-auto-inc synchronous self-growth value, generally do not need

-C,--sync-comments synchronization notes, notes for post-database maintenance is important, it is recommended to enable

--tag=tag the generated file, it will be shown in the file name

--output-directory=output_directory

The path to the build patch and revert file must be an absolute path, which defaults to the current working path

--log-directory=log_directory set the path of the log file, must be an absolute path, the default is Output-directory, the file name is Schemasync.log

The options are simple and nothing worth emphasizing.

For the later maintenance convenience, we require each field has comments, but the people who build the table in English pain, see people also uncomfortable, is English, then the problem comes, use--sync-comments parameter Chinese will garbled, resulting in the field notes are not available, The root cause is that the Python script does not specify the use of the UTF8 character set, resulting in the resulting patch file is ANSI-encoded, the simple and brutal solution is to download the downloaded source package in the Py file all specify the UTF8 character set, that is, the following file

[[email protected] schemasync-0.9.2]# Find/-name ' *.py '

./ez_setup.py

./setup.py

./schemasync/schemasync.py

./schemasync/syncdb.py

./schemasync/utils.py

./schemasync/__init__.py

All add #-*-coding:utf-8-*-

Each time you specify the password is also more troublesome, wrote a shell script, each time only need to specify the source IP, destination IP, port number, library name can be

mysqlha_sync2test.sh

#!/bin/bash

S_host=$1

D_host=$2

Port=$3

Db=$4

Mysql_path= "/usr/local/mysql-5.6.24"

Sync_time= ' date + '%h%m%s '

Sync_date= ' date + '%y%m%d '

S_user= ' Db_read_user '

S_pwd= ' 123456 '

D_user= ' Root '

D_pwd= ' 123456 '

Schemasync--tag= "${sync_time}"--output-dir= "/data/mysql_schemasync"--sync-comments mysql://${s_user}:${s_pwd}@$ {s_host}:${port}/${db} mysql://${d_user}:${d_pwd}@${d_host}:${port}/${db}

Patch_file= "/data/mysql_schemasync/${db}_${sync_time}.${sync_date}.patch.sql"

If [-F $patch _file]

Then

CD ${mysql_path}&&./bin/mysql-h${d_host}-p${port}-u${d_user}-p${d_pwd} $db < "${patch_file}"

Else

echo "is structurally consistent and does not require migration operation"

Fi

This article is from the "Wolf_zh" blog, make sure to keep this source http://wolfzh.blog.51cto.com/3723286/1670224

MySQL Fabric Auto Sync tool-schemasync