欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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