SQL同步脚本

本地同步到远端

#!/usr/local/python3.8/bin/python3
# -*- coding:UTF-8 -*-

import sys
import yaml
import pymysql, json
import paramiko
import logging

with open("/usr/local/sqlsyn/conf/config.yaml", encoding="utf-8") as f:
    conf = yaml.safe_load(f)

LOCAL_NAME = conf.get("LOCAL")
LOCAL_HOST = LOCAL_NAME.get("HOST")
LOCAL_USER = LOCAL_NAME.get("USER")
LOCAL_PASSWD = LOCAL_NAME.get("PASSWORD")
LOCAL_PORT = LOCAL_NAME.get("PORT", 3306)
LOCAL_DATABASE = LOCAL_NAME.get("DATABASE")

REMOTE_NAME = conf.get("REMOTE")
REMOTE_HOST = REMOTE_NAME.get("HOST")
REMOTE_USER = REMOTE_NAME.get("USER")
REMOTE_PASSWD = REMOTE_NAME.get("PASSWORD")
REMOTE_PORT = REMOTE_NAME.get("PORT", 3306)
REMOTE_DATABASE = REMOTE_NAME.get("DATABASE")
LOGFILE = conf.get("LOGFILE")

root = logging.getLogger()
root.setLevel(logging.INFO)
fh = logging.FileHandler(LOGFILE, "a+", "UTF-8")
fh.setLevel(logging.INFO)
formater = logging.Formatter(
    "%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s")
fh.setFormatter(formater)
root.addHandler(fh)

# 获取脚本参数
args_list = sys.argv[1:]
if len(args_list) < 2:
    print("参数传入错误.")
    sys.exit(3)

remotehost = args_list[0]
id = args_list[1]


# 连接本地数据库
def local(sql):
    '''
    :param sql: 要执行的sql语句
    :return: 一条记录和多条记录
    '''
    local_conn = pymysql.connect(host=LOCAL_HOST, user=LOCAL_USER, password=LOCAL_PASSWD,
                                 database=LOCAL_DATABASE, charset='utf8', port=LOCAL_PORT)
    # 得到一个可以执行SQL语句并且将结果作为字典返回的游标(默认返回的结果为元组)
    local_cursor = local_conn.cursor(cursor=pymysql.cursors.DictCursor)
    if 'SELECT' in sql:
        local_cursor.execute(sql)
        # 返回结果为字典格式,fetchone()返回一条记录
        local_one = local_cursor.fetchone()
        # 返回结果为字典格式,fetchall()返回多条记录
        if local_one:
            # 返回结果为字典格式,fetchall()返回多条记录
            local_all = local_cursor.fetchall()
#            remote_all.append(remote_one)
            local_all.insert(0, local_one)
        else:
            local_all = []
        local_cursor.close()
        local_conn.close()
        return local_one, local_all
    elif 'UPDATE' or 'INSERT' in sql:
        try:
            local_one = local_cursor.execute(sql)
            local_all = None
            local_conn.commit()
            local_cursor.close()
            local_conn.close()
            return local_one, local_all
        except Exception as f:
            # Rollback in case there is any error
            print(f)
            local_conn.rollback()
        local_cursor.close()
        local_conn.close()



# 连接远程数据库
def remote(sql):
    '''
    :param sql: 要执行的sql语句
    :return: 一条记录和多条记录
    '''
    remote_conn = pymysql.connect(host=remotehost, user=REMOTE_USER, password=REMOTE_PASSWD,
                                  database=REMOTE_DATABASE, charset='utf8', port=REMOTE_PORT)
    # 得到一个可以执行SQL语句并且将结果作为字典返回的游标(默认返回的结果为元组)
    remote_cursor = remote_conn.cursor(cursor=pymysql.cursors.DictCursor)
    if 'SELECT' in sql:
        remote_cursor.execute(sql)
        # 返回结果为字典格式,fetchone()返回一条记录
        remote_one = remote_cursor.fetchone()
        # 返回结果为字典格式,fetchall()返回多条记录
        remote_all = remote_cursor.fetchall()
        local_all.insert(0, local_one)
#        remote_all.append(remote_one)
        remote_cursor.close()
        remote_conn.close()
    elif 'UPDATE' in sql:
        try:
            remote_one = remote_cursor.execute(sql)
            remote_all = None
            remote_conn.commit()
            remote_cursor.close()
            remote_conn.close()
        except Exception as f:
            # Rollback in case there is any error
            print(f)
            remote_conn.rollback()
    remote_cursor.close()
    remote_conn.close()
    return remote_one, remote_all


# 定义要执行的SQL语句
local_sql = "SELECT member_count,total_data_count,upload_total_traffic_size,aggregate_table_amount,dataview_num,extend_params,cpu_nums FROM t_site_info WHERE id='{}';".format(
    id)
remote_select_sql = "SELECT skey,svalue FROM t_site_collocation_limit WHERE skey IN('member_count','total_data_count','upload_total_traffic_size','dataview_num','aggregate_table_count','business_type');"

local_res = local(local_sql)[0]
remote_res = remote(remote_select_sql)[1]
business_type = int(json.loads(local_res.get('extend_params')).get('business_type')) + 1
success = 0
#sql_num=[]
sql_num=['执行的sql语句']
for i in remote_res:
    if i['skey'] == 'business_type' and business_type != i['svalue']:
        sql = "UPDATE t_site_collocation_limit SET `svalue` = '{}' WHERE `skey` = 'business_type';".format(
            business_type)
        success += remote(sql)[0]
        sql_num.append(sql)
    elif i['skey'] == 'aggregate_table_count' and i['svalue'] != local_res['aggregate_table_amount']:
        sql = "UPDATE t_site_collocation_limit SET `svalue` = '{}' WHERE `skey` = 'aggregate_table_count';".format(
            local_res['aggregate_table_amount'])
        success += remote(sql)[0]
        sql_num.append(sql)
    for k in local_res:
        if i['skey'] == k and i['svalue'] != local_res[k]:
            print(i['skey'],i['svalue'])
            sql = "UPDATE t_site_collocation_limit SET `svalue` = '{}' WHERE `skey` = '{}'".format(local_res[k], k)
            success += remote(sql)[0]
            sql_num.append(sql)
if success != 0:
    root.info('私有云({})sql同步完成，修改sql条数{}'.format(remotehost,success))
    #msg=' \n'.join(sql_num)
    #root.info('执行的sql语句：\n{}'.format(msg))
    for i in sql_num:
        root.info(i)


def ssh_cmd(hostname):
    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    sql = "SELECT account,password FROM t_client_info WHERE outip='{}';".format(hostname)
    local_one, local_all = local(sql)
    ssh.connect(hostname=hostname, port=22, username=local_one.get('account'), password=local_one.get('password'))
    stdin, stdout, stderr = ssh.exec_command("lscpu | sed -n '4p'| awk '{print $2}'")
    res = stdout.read().decode()
    return ssh, res

ssh, res = ssh_cmd(hostname=remotehost)
if int(res) != local_res.get('cpu_nums'):
    if local_res.get('cpu_nums') == 2:
        stdin, stdout, stderr = ssh.exec_command(
            'rm /etc/my.cnf && cp /etc/my_2_4G.cnf /etc/my.cnf && /etc/init.d/mysqld restart')
        res = stdout.read().decode()
        root.info('{}私有云({})重启Mysql服务正常'.format(res, remotehost))

    elif local_res.get('cpu_nums') == 4:
        stdin, stdout, stderr = ssh.exec_command(
            'rm /etc/my.cnf && cp /etc/my_4_8G.cnf /etc/my.cnf && /etc/init.d/mysqld restart')
        res = stdout.read().decode()
        root.info('{}私有云({})重启Mysql服务正常'.format(res, remotehost))

    elif local_res.get('cpu_nums') == 8:
        stdin, stdout, stderr = ssh.exec_command(
            'rm /etc/my.cnf && cp /etc/my_8_16G.cnf /etc/my.cnf && /etc/init.d/mysqld restart')
        res = stdout.read().decode()
        root.info('{}私有云({})重启Mysql服务正常'.format(res, remotehost))
ssh.close()
print(success, end='')

远端同步本地，请求本地的API接口获取本地数据

#!/usr/bin/env python
# -*- coding:UTF-8 -*-
# 当执行env python时，它其实会去env | grep PATH里(也就是 /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)这几个路径里去依次查找名为python的可执行文件
import time,datetime
import logging
import pymysql
import json
import hashlib
import requests
import os,sys

root = logging.getLogger()
root.setLevel(logging.INFO)
fh = logging.FileHandler('/var/log/sqlsyn.log', "a+", "UTF-8")
fh.setLevel(logging.INFO)
formater = logging.Formatter(
    "%(asctime)s - %(filename)s[line:%(lineno)d] - %(levelname)s: %(message)s")
fh.setFormatter(formater)
root.addHandler(fh)


# 连接本地数据库
def local(sql):
    '''
    :param sql: 要执行的sql语句
    :return: 一条记录和多条记录
    '''
    local_conn = pymysql.connect(host='IP', user='root', password='123456',
                                 database='database_name', charset='utf8', port=3908)
    # 得到一个可以执行SQL语句并且将结果作为字典返回的游标(默认返回的结果为元组)
    local_cursor = local_conn.cursor(cursor=pymysql.cursors.DictCursor)
    if 'SELECT' in sql:
        local_cursor.execute(sql)
        # 返回结果为字典格式,fetchone()返回一条记录
        local_one = local_cursor.fetchone()
        # 返回结果为字典格式,fetchall()返回多条记录
        local_all = local_cursor.fetchall()
        local_all.append(local_one)
    elif 'UPDATE' in sql:
        try:
            local_one = local_cursor.execute(sql)
            local_all = None
            local_conn.commit()
        except Exception as f:
            # Rollback in case there is any error
            print(f)
            local_conn.rollback()
    local_cursor.close()
    local_conn.close()
    return local_one, local_all


sql = " "
domain = local(sql)[0].get('domain')
sql1 = " "
site_only_code = local(sql1)[0].get('svalue')

#生成token
def md5token():
    ttime = int(time.time())
    s = " "
    md5token = hashlib.md5(s.encode()).hexdigest()
    return str(md5token)

#解决https请求告警
requests.packages.urllib3.disable_warnings()

url = 'https://{}/api/v1/test'.format(domain)
headers = {'Accept': '*/*', 'Content-Type': 'application/json', 'site_only_code': site_only_code,
           'time': str(int(time.time())), 'token': md5token()}
res = requests.post(url, headers=headers, verify=False)
res = json.loads(res.text)
cc = res.get('data').get('info')
cc_res = {}

local_sql = " "
local_res = local(local_sql)[1]


for i in local_res:
    for k in cc_res:
        if i['skey'] == k and i['svalue'] != cc_res[k]:
            sql = " "
            local(sql)
            root.info(sql)
root.info('数据库同步完成')

strftime=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
if int(cc.get('cpu_nums')) == 2:
    res = os.popen('rm /etc/my.cnf && cp /etc/my_2_4G.cnf /etc/my.cnf && /etc/init.d/mysqld restart').read()
    print ("{} {}[{}] {}".format(strftime,__file__,sys._getframe().f_lineno,res))
    #获取脚本名称：__file__，获取行号：sys._getframe().f_lineno
    print ("{} {}[{}] MySQL重启完成！！！！！".format(strftime,__file__,sys._getframe().f_lineno))

elif int(cc.get('cpu_nums')) == 4:
    res = os.popen('rm /etc/my.cnf && cp /etc/my_4_8G.cnf /etc/my.cnf && /etc/init.d/mysqld restart').read()
    print ("{} {}[{}] {}".format(strftime,__file__,sys._getframe().f_lineno,res))
    print ("{} {}[{}] MySQL重启完成！！！！！".format(strftime,__file__,sys._getframe().f_lineno))

elif int(cc.get('cpu_nums')) == 8:
    res = os.popen('rm /etc/my.cnf && cp /etc/my_8_16G.cnf /etc/my.cnf && /etc/init.d/mysqld restart').read()
    print ("{} {}[{}] {}".format(strftime,__file__,sys._getframe().f_lineno,res))
    print ("{} {}[{}] MySQL重启完成！！！！！".format(strftime,__file__,sys._getframe().f_lineno))

#脚本运行方式
/usr/bin/python3 sqlsyn.py >>/var/log/sqlsyn.log