mysql-schema-sync同步两个实例
参考：https://github.com/hidu/mysql-schema-sync
需求：测试环境表结构变动同步到开发环境，两个实例各有数百个库，不适合每个库写一个配置文件
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。环境：操作系统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@souche.com", "password":"AsAH8Ncmbr7yHTG23Nkv", "to":" wangmaosen@souche.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
更多精彩
