參考：https://github.com/hidu/mysql-schema-sync 需求：測試環境表結構變動同步到開發環境，兩個實例各有數百個庫，不適合每個庫寫一個配置文件 環境：操作系統ubuntu16.04、兩個資料庫實例percona5.7、golang版本1.12 安裝mysql-sc ...
參考：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":"[email protected]", "password":"AsAH8Ncmbr7yHTG23Nkv", "to":"[email protected]" } } 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
