mysql-schema-sync同步两个实例
程序员文章站
2022-04-03 14:08:40
参考:https://github.com/hidu/mysql-schema-sync 需求:测试环境表结构变动同步到开发环境,两个实例各有数百个库,不适合每个库写一个配置文件 环境:操作系统ubuntu16.04、两个数据库实例percona5.7、golang版本1.12 安装mysql-sc ......
参考:
需求:测试环境表结构变动同步到开发环境,两个实例各有数百个库,不适合每个库写一个配置文件
环境:操作系统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":"",
"from":"",
"password":"asah8ncmbr7yhtg23nkv",
"to":""
}
}
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/"
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/ 2>/dev/null >db_alter.sql')
break