• 配置文件
  配置使用csv，倆文件，一個(gè)被備份的數(shù)據(jù)庫(kù)機(jī)器ip，用戶名，密碼，數(shù)據(jù)名稱，數(shù)據(jù)庫(kù)用戶，密碼，數(shù)據(jù)庫(kù)名稱信息，稱為source.csv,另一個(gè)目標(biāo)數(shù)據(jù)庫(kù)機(jī)器ip，用戶名，密碼，數(shù)據(jù)名稱，數(shù)據(jù)庫(kù)用戶，密碼，數(shù)據(jù)庫(kù)名稱，稱為destination.csv
  如：

source.csv

 192.168.3.161, root, 123456, root, 123456, db_1

destination.csv

192.168.3.162, root, 123456, root, 123456, db_1

意思是將192.168.3.161的db1數(shù)據(jù)庫(kù)遷移到192.168.3.162的db1數(shù)據(jù)庫(kù)

• 讀取配置文件ReadCSV.py

def read(file):
    config_ = []
    with open(file, encoding='utf8') as f:
        f_csv = csv.reader(f)
        # headers = next(f_csv)
        for row in f_csv:
            config_.append(row)
    return config_

• 執(zhí)行遠(yuǎn)程命令SSH.py

# 返回一個(gè)字符串，單個(gè)命令,返回該命令的遠(yuǎn)程輸出
def ssh(ip, user_name, pass_wd, cmd):
    result_str = ""
    try:
        ssh_client = paramiko.SSHClient()
        ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        ssh_client.connect(ip, 22, user_name, pass_wd, timeout=5)
        print ("執(zhí)行遠(yuǎn)程命令:服務(wù)器ip:%s,命令:%s" %(ip,cmd))
        std_in, stdout, stderr = ssh_client.exec_command(cmd)
        #           stdin.write("Y")   #簡(jiǎn)單交互，輸入 ‘Y’
        out = stdout.readlines()
        err = stderr.readline()
        # 屏幕輸出
        for o in out:
            result_str += o
            print("remoteOut:"+o)
        # print('%s\t執(zhí)行完畢\n' % ip)
        # print("result_str:"+result_str)
        ssh_client.close()
    except Exception as e:
        print('%s\tError:%s\n' % (ip, e))

    return result_str

def upload(host_ip, username, password, local_path, remote_path):
    t = paramiko.Transport((host_ip, 22))
    t.connect(username=username, password=password)  # 登錄遠(yuǎn)程服務(wù)器
    sftp = paramiko.SFTPClient.from_transport(t)  # sftp傳輸協(xié)議
    sftp.put(local_path, remote_path)
    t.close()


def download(host_ip, username, password, remote_path, local_path):
    t = paramiko.Transport((host_ip, 22))
    t.connect(username=username, password=password)  # 登錄遠(yuǎn)程服務(wù)器
    sftp = paramiko.SFTPClient.from_transport(t)  # sftp傳輸協(xié)議
    src = remote_path
    des = local_path
    sftp.get(src, des)
    t.close()

• 遷移數(shù)據(jù)庫(kù)DataMigration.py

__author__ = 'micocube'
# python 3.5
# DataMigration.py,SSH.py,ReadCSV.py在同一目錄下
from . import SSH
from . import ReadCSV


# dump數(shù)據(jù)庫(kù)
# 被備份的數(shù)據(jù)庫(kù)遠(yuǎn)程服務(wù)器ip，用戶名，密碼，mysql用戶名，密碼，數(shù)據(jù)庫(kù)名稱
# mysqldump -B選項(xiàng)會(huì)生成建庫(kù)語(yǔ)句，生成的sql文件放到/data/數(shù)據(jù)庫(kù)名稱.sql
def dump_data_base(ip, user_name, password, mysql_user, mysql_password, db_name):
    res_str = SSH.ssh(ip,user_name,password,"mysqldump -h" + ip + " -u" + mysql_user +" -p" + mysql_password + " -B " + db_name + " > /data/" + db_name + ".sql")
    print(res_str)


# 拷貝數(shù)據(jù)庫(kù)到本地，如果直接scp的話要輸入密碼
# 被備份的數(shù)據(jù)庫(kù)遠(yuǎn)程服務(wù)器ip，用戶名，密碼，數(shù)據(jù)庫(kù)名稱
# 數(shù)據(jù)庫(kù)名稱主要用來(lái)拼接sql文件名稱
def copy_data(des_ip, des_user_name, des_password, db_name):
    SSH.download(des_ip, des_user_name, des_password, "/data/"+db_name + ".sql","resources/" + db_name + ".sql")


# 上傳數(shù)據(jù)庫(kù)文件到遷移的目標(biāo)機(jī)器
# 目標(biāo)遠(yuǎn)程服務(wù)器ip，用戶名，密碼，數(shù)據(jù)庫(kù)名稱
# 數(shù)據(jù)庫(kù)名稱主要用來(lái)拼接sql文件名稱
def upload_data(des_ip, des_user_name, des_password, db_name):
    SSH.upload(des_ip, des_user_name, des_password, "resources/" + db_name + ".sql", "/data/" + db_name + ".sql")


# 導(dǎo)入數(shù)據(jù)庫(kù)到目標(biāo)機(jī)器的數(shù)據(jù)庫(kù)
# logs是個(gè)空數(shù)據(jù)庫(kù)，mysqldump 已經(jīng)生成了建庫(kù)語(yǔ)句，對(duì)logs數(shù)據(jù)庫(kù)并無(wú)影響
# 目標(biāo)遠(yuǎn)程服務(wù)器ip，用戶名，密碼，數(shù)據(jù)庫(kù)用戶名，數(shù)據(jù)庫(kù)密碼，數(shù)據(jù)庫(kù)名稱
def import_data_base(ip, user_name, password, mysql_user, mysql_password, db_name):
    res_str = SSH.ssh(ip,user_name,password,"mysql -h" + ip + " -u" + mysql_user +" -p" + mysql_password + " logs " + " < /data/" + db_name + ".sql")
    print(res_str)


def read_config():
    # 在代碼目錄建立了一個(gè)叫resources的文件夾，存放配置文件，
    source = ReadCSV.read("resources/source.csv")
    destination = ReadCSV.read("resources/destination.csv")
    return source,destination


if __name__ == "__main__":
    source, destination = read_config()
    for i in range(0,len(source)):
        #  被備份的數(shù)據(jù)庫(kù)機(jī)器ip，用戶名，密碼，數(shù)據(jù)名稱，數(shù)據(jù)庫(kù)用戶，密碼，數(shù)據(jù)庫(kù)名稱
        # '192.168.3.161', 'root', '123456', 'root', '123456', 'db_1'
        sou = source[i]
        #  目標(biāo)數(shù)據(jù)庫(kù)機(jī)器ip，用戶名，密碼，數(shù)據(jù)名稱，數(shù)據(jù)庫(kù)用戶，密碼，數(shù)據(jù)庫(kù)名稱
        # '192.168.3.162', 'root', '123456', 'root', '123456', 'db_1'
        des = destination[i]
        # 將192.168.3.161的db1數(shù)據(jù)庫(kù)遷移到192.168.3.162的db1數(shù)據(jù)庫(kù)
        # 如果161上已經(jīng)有db_1的數(shù)據(jù)庫(kù)，可以遠(yuǎn)程執(zhí)行mysqladmin -u**** -p*** create db_2 先建庫(kù)，dump的時(shí)候不加-B選項(xiàng)
        dump_data_base(sou[0],sou[1],sou[2],sou[3],sou[4],sou[5])
        copy_data(sou[0], sou[1], sou[2], sou[5])
        # 刪除被備份的sql
        SSH.ssh(sou[0], sou[1], sou[2], "rm -f /data/" + sou[5] + ".sql")
        upload_data(des[0], des[1], des[2], des[5])
        import_data_base(des[0], des[1], des[2], des[3], des[4], des[5])
        SSH.ssh(des[0], des[1], des[2], "rm -f /data/" + sou[5] + ".sql")
        print("source:"+str(sou) + "destination:" + str(des))