ssh备份策略

1.ssh从本地备份远程

import datetime
import os
import re
import time
import win32gui
import paramiko  # 导入paramiko
import pyautogui
import pymysql
import pyperclip
import win32con

db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='filetime',
                     charset='utf8mb4')
cursor = db.cursor()


def insert_data(path, time):
    # 如果数据库不存在文件，则创建一条记录包括文件绝对路径和修改时间
    ins = 'insert into file (path, modifytime) values (%s, %s)'
    cursor.execute(ins, (path, time))
    db.commit()
    # cursor.close()
    # db.close()


def text(word):
    # 写入修改事件
    ins = 'insert into text (content) values (%s)'
    cursor.execute(ins, "file_move事件" + word)
    db.commit()
    # cursor.close()
    # db.close()


def select_data():
    # 查询file表文件名修改时间
    sql = 'select  path, modifytime from file '
    # print(sql)
    sql = cursor.execute(sql)
    # print(sql)
    all_data = cursor.fetchmany(sql)  # 查询的个数
    print(all_data)
    # for i in all_data:
    #      print(i[0], i[1])
    return all_data


def sql_update(path, modifytime):
    # 修改数据库file表的修改过文件时间名字的时间
    try:
        ins = 'UPDATE  file SET modifytime = "{}" WHERE path = %s'.format(modifytime)
        print(ins, path)
        cursor.execute(ins, path)

        db.commit()
        # print(ins)
        # cursor.close()
        # db.close()
    except Exception as result:
        print("sql_update错误", result)


def ssh_file_manage(cmdList):
    # ssh命令:第一种ssh连接执行指令方式,把文件发送到centos上,并修改数据库文件时间
    transport = paramiko.Transport((hostname, port))
    transport.connect(username=username, password=password)
    sftp = paramiko.SFTPClient.from_transport(transport)
    # 遍历cmd列表里面文件一个一个上传
    for index, filepath, filename, filetime in cmdList:
        filepath = filepath.replace("\\", "/")
        print(filepath, filename, linux_path + filepath+filename)
        try:
            sftp.put(win_path + filepath+filename, linux_path + filepath+filename)

            # sftp.get('/home/blog/media' + cmd, 'C:/Users/yys53/OneDrive/python/blog/media' + cmd)
            if index == 0:
                insert_data(filepath+filename, filetime)
            elif index == 1:
                sql_update(filepath+filename, filetime)  # 修改时间
            text('把win10文件:C:/Users/yys53/OneDrive/python' + filepath+filename + linux_path + filepath+filename)
            if filepath:
                print("py文件更新需要reload")
                reload.append(filepath)
        except FileNotFoundError:
            mkdir_list.append("mkdir -p %s" % linux_path+filepath)
    transport.close()


def ssh_console(cmdList):
    # ssh命令:如果不存在文件夹用ssh来新建,还用执行reload
    try:
        client = paramiko.SSHClient()
        client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        # 创建ssh连接
        client.connect(hostname=hostname, port=port, username=username, password=password)
        # 开启ssh管道
        ssh = client.get_transport().open_session()
        ssh.get_pty()
        ssh.invoke_shell()
        for cmd in cmdList:
            # 远程主机需要有这个py文件
            ssh.sendall(cmd + " \n")
            time.sleep(0.5)  # 等待多少秒远程的遍历完
            result = ssh.recv(302400)  # 显示多少字
            # print(result)
            result = result.decode("utf-8")
            print("结果", result)
    except Exception as e:
        print("[%s] %s 目标失败，原因是 %s" % (datetime.datetime.now(), hostname, str(e)))
    else:
        pass
        # print("[%s] %s 目标成功" % (datetime.datetime.now(), hostname))
    finally:
        ssh.close()
        client.close()


def compare_tme():

    data = select_data()

    for i in half_file_list:
        # print(win_path+i)
        for root, dirs, files in os.walk(win_path + i):
            # (win_path + '\\blog\\' + iroots)代表需要遍历的根文件夹
            # root表示正在遍历的文件夹的名字（根 / 子）
            # dirs记录正在遍历的文件夹下的子文件夹集合
            # files记录正在遍历的文件夹中的文件集合
            for file in files:
                # print(root[-1])
                if root[-1] == '\\':
                    pass
                else:
                    root = root + "\\"
                # print(r_list)
                f = root + file

                mtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(os.path.getmtime(f)))  # 修改时间
                filepath = root[len(win_path):].replace("\\", "/")    # 获取后缀不同的地方得到文件路径,不包括文件名

                for i in data:
                    # i[0]为数据库一半路径+文件名,i[1]为最后修改时间
                    # 判断文件夹匹配
                    if filepath + file == i[0]:
                        # print(r_list[0] + file, i[0])
                        # 如果文件夹相等,比一下时间
                        if i[1] == mtime:
                            pass
                            # print("满足")
                        else:
                            print("时间不满足%s,当前时间%s,最近修改时间%s" % (filepath + file, i[1], mtime))
                            # 不满足做的事
                            # 1.移动文件
                            filepath_time_list.append((1, filepath, file, mtime))
                            print(filepath)
                            time.sleep(0.5)
                        break
                else:
                    print("没有此文件:%s，需要插入file表中" % filepath + file)
                    # # 插入file表
                    filepath_time_list.append((0, filepath, file, mtime))
                # print("文件路径:%s, 最新修改 : %s" % (file_dir, mtime))


if __name__ == '__main__':
    filepath_time_list = []
    mkdir_list = []
    reload = []
    win_path = 'C:\\Users\\yys53\\OneDrive\\python\\blog'
    linux_path = "/home/blog"
    hostname =ip
    username = ""
    password = ""
    port = 22
    # 需要同步blog下的文件相对路径
    half_file_list = ["\\blog\\", '\\home\\', '\\templates\\', '\\static\\', '\\diary\\', '\\users\\']

    compare_tme()
    print(filepath_time_list)
    if len(filepath_time_list) > 0:
        # 获取所有需要上传列表相对路径,一次性上传完
        ssh_file_manage(filepath_time_list)

    if mkdir_list:
        print("有文件夹不存在需要创建")
        print(list(set(mkdir_list)))
        cmdList = []
        ssh_console(list(set(mkdir_list)))

        # 创建之后再移动文件
        filepath_time_list = []
        compare_tme()
        print(filepath_time_list)
        if len(filepath_time_list) > 0:
            # 获取所有需要上传列表相对路径,一次性上传完
            ssh_file_manage(filepath_time_list)

    if reload:
        print("有文件上传CentOs需要重启supervisor")
        ssh_console(['supervisorctl reload'])

2.ssh从远程下载到本地

在远程新建一个file.py文件获取文件路径和时间

import time
import os
path = "/home/blog/media"
len_p = len(path)
lis = []
for root, dirs, files in os.walk(path):
    for file in files:
        print(root)
        if root[-1] == '/':
            pass
        else:
            root = root + "/"
        f = root + file
        # print(f)
        mtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(os.path.getmtime(f)))
        lis.append((root[len_p:], file ,mtime))
print(lis)

也可以根据md5,但是比较慢

def get_file_md5(md5_path):
    d5 = hashlib.md5()
    with open(md5_path, 'rb') as f:
        while True:
            data = f.read(2024)
            if not data:
                break
            d5.update(data)  # update添加时会进行计算
            return d5.hexdigest()

本地计算机获取文件内容

import datetime
import os
import time

import paramiko  # 导入paramiko
import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='filetime',
                     charset='utf8mb4')
cursor = db.cursor()



def insert_data(path, file_time):
    ins = 'insert into getfile(path,time)  values (%s, %s)'
    cursor.execute(ins, (path, file_time))
    db.commit()


def sql_update(path, modifytime):
    # 修改数据库file表的修改过文件时间名字的时间
    try:
        ins = 'UPDATE  getfile SET time = "{}" WHERE path = %s'.format(modifytime)

        cursor.execute(ins, path)
        db.commit()

        # cursor.close()
        # db.close()
    except Exception as result:
        print("sql_update错误", result)


def text(word):

    ins = 'insert into text (content) values (%s)'
    cursor.execute(ins, "file_get事件" + word)
    db.commit()


def select_data():
    sql = 'select  path, time from getfile '
    # print(sql)
    sql = cursor.execute(sql)
    # print(sql)
    all_data = cursor.fetchmany(sql)  # 查询的个数
    # print(all_data)
    # for i in all_data:
    #      print(i[0], i[1])
    return all_data


def sshRunCmd(hostname, username, password, cmdList):
    try:
        client = paramiko.SSHClient()
        client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        # 创建ssh连接
        client.connect(hostname=hostname, port=22, username=username, password=password)
        # 开启ssh管道
        ssh = client.get_transport().open_session()
        ssh.get_pty()
        ssh.invoke_shell()
        # 执行指令
        sq_all_data = select_data()
        for cmd in cmdList:
            # 带个\n相当于回车
            ssh.sendall(cmd + " \n")
            # 如果读取很多文件改下time.sleep和recv的值,如果获取md5要改成3秒60万左右
            time.sleep(1)   # 等待多少秒远程的遍历完
            result = ssh.recv(302400)  # 显示多少字
            # print(result)
            result = result.decode("utf-8")
            # print(result)
            import re
            re_lis = re.compile("(\\[\\(.*?\\)\\])", re.S).findall(result)
            # print(re_lis[0])
            for path, filename, file_time in eval(re_lis[0]):
                # print(i[0], i[1])
                for j in sq_all_data:
                    sq_file = j[0]
                    sq_time = j[1]
                    if sq_file == path+filename:
                        if sq_time == file_time:
                            pass
                            # print("满足")
                            break
                        else:
                            print("时间不满足：" + path+filename)
                            lis.append((1, path+filename, file_time))
                            break
                else:
                    print("文件不存在：" + path+filename)
                    if not os.path.exists(pc_file + path):
                        # 递归创建文件
                        os.makedirs(pc_file + path)
                        print("文件夹不存在，需要创建")
                    else:
                        pass
                        # print("文件夹存在，不需创建")
                    lis.append((0, path+filename, file_time))
    except Exception as e:
        print("[%s] %s 目标失败，原因是 %s" % (datetime.datetime.now(), hostname, str(e)))
    else:
        pass
        # print("[%s] %s 目标成功" % (datetime.datetime.now(), hostname))
    finally:
        ssh.close()
        client.close()


def file_manage(cmdList):
    if cmdList:
        transport = paramiko.Transport((hostname, port))
        transport.connect(username=username, password=password)
        sftp = paramiko.SFTPClient.from_transport(transport)
        for index, filename, filetime in cmdList:
            try:
                sftp.get(linux_file + filename, pc_file + filename)
                # 等于0时,文件不存在，需要插入数据，等于1时文件存在，时间需修改，需要更新数据
                if index == 0:
                    insert_data(filename, filetime)  # 数据都带\r所以加个
                elif index == 1:
                    sql_update(filename, filetime)
                text('把centos文件:' + linux_file + filename + '复制到 ' + pc_file + filename)
            except Exception as e:
                print("错误路径:%s,原因%s" % (index + filename + filename, e))
                text("错误路径:%s,原因%s" % (index + filename + filename, e))

        transport.close()
    os.system('mysqldump yys  -h 175.24.115.96 -u root -p519Yang982  | mysql  yys -u root -p123456')    # 复制数据库


if __name__ == '__main__':
    hostname = ip
    username = ""
    password = ""
    port = 22
    lis = []

    pc_file = "C:/Users/yys53/OneDrive/python/blog/media"
    linux_file = '/home/blog/media'
    # 远程主机需要有这个py文件
    cmdList = ["python3 /home/blog/file.py"]
    sshRunCmd(hostname, username, password, cmdList)
    # select_data()
    print(lis)
    file_manage(lis)

判断远程和本地文件时间是否相等进行复制文件

  3.bachup的mysql 表

create table backup(
id int unsigned primary key auto_increment not null, 
path varchar(200), 
modifytime varchar(20)
)charset=utf8mb4;

  4.backup python代码(属于本地复制远程)

import datetime
import os
import time

import paramiko  # 导入paramiko
import pymysql

db = pymysql.connect(host='localhost', port=3306, user='root', password='123456', database='filetime',
                     charset='utf8mb4')
cursor = db.cursor()


def insert_data(path, time):
    # 如果数据库不存在文件，则创建一条记录包括文件绝对路径和修改时间
    ins = 'insert into backup (path, modifytime) values (%s, %s)'
    cursor.execute(ins, (path, time))
    db.commit()
    # cursor.close()
    # db.close()


def text(word):
    # 写入修改事件
    ins = 'insert into text (content) values (%s)'
    cursor.execute(ins, "file_move事件" + word)
    db.commit()
    # cursor.close()
    # db.close()


def select_data():
    # 查询file表文件名修改时间
    sql = 'select  path, modifytime from backup '
    # print(sql)
    sql = cursor.execute(sql)
    # print(sql)
    all_data = cursor.fetchmany(sql)  # 查询的个数
    print(all_data)
    # for i in all_data:
    #      print(i[0], i[1])
    return all_data


def sql_update(path, modifytime):
    # 修改数据库file表的修改过文件时间名字的时间
    try:
        ins = 'UPDATE  backup SET modifytime = "{}" WHERE path = %s'.format(modifytime)
        print(ins, path)
        cursor.execute(ins, path)

        db.commit()
        # print(ins)
        # cursor.close()
        # db.close()
    except Exception as result:
        print("sql_update错误", result)


def ssh_file_manage(cmdList):
    # ssh命令:第一种ssh连接执行指令方式,把文件发送到centos上,并修改数据库文件时间
    transport = paramiko.Transport((hostname, port))
    transport.connect(username=username, password=password)
    sftp = paramiko.SFTPClient.from_transport(transport)
    # 遍历cmd列表里面文件一个一个上传
    for index, filepath, filename, filetime in cmdList:
        filepath = filepath.replace("\\", "/")
        print(filepath, filename, linux_path + filepath + filename)
        try:
            sftp.put(win_path + filepath + filename, linux_path + filepath + filename)

            # sftp.get('/home/blog/media' + cmd, 'C:/Users/yys53/OneDrive/python/blog/media' + cmd)
            if index == 0:
                insert_data(filepath + filename, filetime)
            elif index == 1:
                sql_update(filepath + filename, filetime)  # 修改时间
            text('把win10文件:C:/Users/yys53/OneDrive/python' + filepath + filename + linux_path + filepath + filename)

        except FileNotFoundError:
            mkdir_list.append("mkdir -p %s" % linux_path + filepath)
    transport.close()


def ssh_console(cmdList):
    # ssh命令:如果不存在文件夹用ssh来新建,还用执行reload
    try:
        client = paramiko.SSHClient()
        client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        # 创建ssh连接
        client.connect(hostname=hostname, port=port, username=username, password=password)
        # 开启ssh管道
        ssh = client.get_transport().open_session()
        ssh.get_pty()
        ssh.invoke_shell()
        for cmd in cmdList:
            # 远程主机需要有这个py文件
            ssh.sendall(cmd + " \n")
            time.sleep(0.5)  # 等待多少秒远程的遍历完
            result = ssh.recv(302400)  # 显示多少字
            # print(result)
            result = result.decode("utf-8")
            print("结果", result)
    except Exception as e:
        print("[%s] %s 目标失败，原因是 %s" % (datetime.datetime.now(), hostname, str(e)))
    else:
        pass
        # print("[%s] %s 目标成功" % (datetime.datetime.now(), hostname))
    finally:
        ssh.close()
        client.close()


def compare_tme():
    data = select_data()
    for root, dirs, files in os.walk(win_path):
        # (win_path + '\\blog\\' + iroots)代表需要遍历的根文件夹
        # root表示正在遍历的文件夹的名字（根 / 子）
        # dirs记录正在遍历的文件夹下的子文件夹集合
        # files记录正在遍历的文件夹中的文件集合
        for file in files:
            # print(root[-1])
            if root[-1] == '/':
                pass
            else:
                root = root + "/"
            # print(r_list)
            f = root + file

            mtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(os.path.getmtime(f)))  # 修改时间
            filepath = root[len(win_path):].replace("\\", "/")  # 获取后缀不同的地方得到文件路径,不包括文件名

            for i in data:
                # i[0]为数据库一半路径+文件名,i[1]为最后修改时间
                # 判断文件夹匹配
                if filepath + file == i[0]:
                    # print(r_list[0] + file, i[0])
                    # 如果文件夹相等,比一下时间
                    if i[1] == mtime:
                        pass
                        # print("满足")
                    else:
                        print("时间不满足%s,当前时间%s,最近修改时间%s" % (filepath + file, i[1], mtime))
                        # 不满足做的事
                        # 1.移动文件
                        filepath_time_list.append((1, filepath, file, mtime))
                        print(filepath)
                        time.sleep(0.5)
                    break
            else:
                print("没有此文件:%s，需要插入file表中" % filepath + file)
                # # 插入file表
                filepath_time_list.append((0, filepath, file, mtime))
            # print("文件路径:%s, 最新修改 : %s" % (file_dir, mtime))


if __name__ == '__main__':
    filepath_time_list = []
    mkdir_list = []
    win_path = 'G:/backup'
    linux_path = "/home/yys/backup"
    hostname = "192.168.31.133"
    username = "yys"
    password = "123456"
    port = 22
    # 需要同步blog下的文件相对路径
    compare_tme()
    print(filepath_time_list)
    if len(filepath_time_list) > 0:
        # 获取所有需要上传列表相对路径,一次性上传完
        ssh_file_manage(filepath_time_list)

    if mkdir_list:
        print("有文件夹不存在需要创建")
        print(list(set(mkdir_list)))
        cmdList = []
        ssh_console(list(set(mkdir_list)))
        # 创建之后再移动文件
        filepath_time_list = []
        compare_tme()
        print(filepath_time_list)
        if len(filepath_time_list) > 0:
            # 获取所有需要上传列表相对路径,一次性上传完
            ssh_file_manage(filepath_time_list)