参考：https://github.com/hidu/mysql-schema-sync
需求：测试环境表结构变动同步到开发环境，两个实例各有数百个库，不适合每个库写一个配置文件
环境：操作系统ubuntu16.04、两个数据库实例percona5.7、golang版本1.12
安装mysql-schema-sync：go get -u github.com/hidu/mysql-schema-sync
Config.json文件如下
{
"source":"slave:slave@(172.17.40.110:3306)/temp_2",
"dest":"slave:slave@(172.17.40.148:3306)/temp_2",
"tables":[],
"tables_ignore":["a”], ##最新的不指定默认全部ignore
"email":{
"send_mail":true,
"smtp_host":"smtp.mxhichina.com:25",
"from":"monitor@xxx.com",
"password":"xxxxxx",
"to":"xxx@xxx.com"
}
}
python对比脚本如下，执行的话把最后的命令改成执行的即可：
import os,sys
import json
import pymysql
def get_m_json(filepath,key,value):
key_ = key.split(".")
key_length = len(key_)
with open(filepath, 'r') as f:
json_data = json.load(f)
i = 0
a = json_data
while i < key_length :
if i+1 == key_length :
a[key_[i]] = "slave:slave@(172.17.40.110:3306)/" + value
i = i + 1
else :
a = a[key_[i]]
i = i + 1
f.close()
return json_data
def get_s_json(filepath,key,value):
key_ = key.split(".")
key_length = len(key_)
with open(filepath, 'r') as f:
json_data = json.load(f)
i = 0
a = json_data
while i < key_length :
if i+1 == key_length :
a[key_[i]] = "slave:slave@(172.17.40.148:3306)/" + value
i = i + 1
else :
a = a[key_[i]]
i = i + 1
f.close()
return json_data
def rewrite_json_file(filepath,json_data):
with open(filepath, 'w') as f:
json.dump(json_data,f)
f.close()
if __name__ == '__main__':
json_path = "/root/go/src/github.com/hidu/mysql-schema-sync/config.json"
conn = pymysql.connect(host="172.17.40.110", port=3306, user="slave", password="slave", database="information_schema")
cursor = conn.cursor()
sql = "select SCHEMA_NAME from SCHEMATA where SCHEMA_NAME not in ('mysql','information_schema','performance_schema');"
cursor.execute(sql)
m_dbs = cursor.fetchall()
cursor.close()
conn.close()
conn = pymysql.connect(host="172.17.40.148", port=3306, user="slave", password="slave", database="information_schema")
cursor = conn.cursor()
sql = "select SCHEMA_NAME from SCHEMATA where SCHEMA_NAME not in ('mysql','information_schema','performance_schema');"
cursor.execute(sql)
s_dbs = cursor.fetchall()
cursor.close()
conn.close()
for m_db in m_dbs:
for s_db in s_dbs:
if m_db == s_db:
m_json_data = get_m_json(json_path,"source",m_db[0])
rewrite_json_file(json_path,m_json_data)
s_json_data = get_s_json(json_path,"dest",m_db[0])
rewrite_json_file(json_path,s_json_data)
os.system('mysql-schema-sync -conf /root/go/src/github.com/hidu/mysql-schema-sync/config.json 2>/dev/null >db_alter.sql')
break