Python与mysqldump的数据库备份

mysqldump -uroot -proot database1 > database1.sql

mysql -uroot -proot database2 < database1.sql

mysqldump -uroot -proot database1 tablename > table1.sql

mysql -uroot -proot database1 < database1.sql

mysqldump -utest -ptest database table --default-character-set=utf8 -t --where="id>22" >c:\test.sql

#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   backupMySQL.py
@Time    :   2019/10/12 14:48:49
@Author  :   MrYong 
@Version :   1.0
@Contact :   m.yong@foxmail.com
@License :   (C)Copyright 2018-2019, MR YONG
@Desc    :   None
'''

# here put the import lib
import os,zipfile,re
from datetime import date, datetime
class BackupMySQL(object):

    def __init__(self, options):
        '''
        初始化配置信息，mysql链接信息，备份文件保存信息
        '''
        self.host = options['host'] if "host" in options.keys() else 'localhost'
        if 'db_name' not in options.keys():
            print('请设置数据库名称')
            return
        self.db_name = options['db_name']

        if 'db_user_name' not in options.keys():
            print('请设置数据库用户名')
            return
        self.db_user_name = options['db_user_name']

        if 'db_user_pwd' not in options.keys():
            print('请设置数据库密码')
            return
        self.db_user_pwd = options['db_user_pwd']
        self.port = options['port'] if 'port' in options.keys() else 3306
        self.mysql_dump_exe = options['mysql_dump_exe'] if 'mysql_dump_exe' in options.keys() else '"D:\\MySQL\\MySQL Server 5.7\\bin\\mysqldump.exe"'
        
        self.backup_dir = options['backup_dir'] if 'backup_dir' in options.keys() else 'D:\\dataBaseBackup'

        self.max_clear_days = options['max_clear_days'] if 'max_clear_days' in options.keys() else 15
        
        self.init()

    def init(self):
        '''
        备份目录创建
        文件名称生成
        '''
        #备份目录不存在创建
        if not os.path.exists(self.backup_dir):
            os.makedirs(self.backup_dir)
        
        #生成备份文件名称
        today_date = date.today().strftime('%Y-%m-%d')
        self.file_name = today_date + '.sql'
        self.zip_file_name = today_date + '.zip'
    
    def search_file(self, pattern):
        '''
        列出备份文件目录
        '''
        file_lsit = []
        with os.scandir(self.backup_dir) as files:
            for file in files:
                if not file.name.startswith('.') and file.is_file():
                    if re.fullmatch(pattern, file.name):
                        file_lsit.append(file.name)
        return file_lsit
    
    def clear_file(self):
        '''
        清理max_clear_days的过期文件
        '''
        files = self.search_file(r'\d{4}-\d{2}-\d{2}.zip')
    
        for file in files:
            file_date = file.split('.')[0]
            time = datetime.strptime(file_date, '%Y-%m-%d')
            days = (datetime.today() - time).days
            if days > self.max_clear_days:
                print('正在删除文件：%s'%file)
                os.remove(self.backup_dir + '\\' + file)
        print('清理完成')

    def run(self):
        '''
        数据库备份执行
        '''
        #切换到备份目录
        os.chdir(self.backup_dir)
        
        #生成备份数据库命令
        run_backup = '%s --host=%s --user=%s --password=%s --protocol=tcp --port=%s --default-character-set=utf8 %s > %s'%(
            self.mysql_dump_exe, 
            self.host, 
            self.db_user_name, 
            self.db_user_pwd, 
            self.port,
            self.db_name, 
            self.file_name
        )
        print(run_backup)
        os.system(run_backup)

        self.save_file()
    
    def save_file(self):
        '''
        保存为压缩文件
        '''    
        if os.path.exists(self.file_name):
            with zipfile.ZipFile(self.zip_file_name, 'w', zipfile.ZIP_DEFLATED) as zp:
                zp.write(self.file_name)

        os.remove(self.file_name)
        
        print('备份数据库成功')

if __name__ == "__main__":
    options = {
        'host':'127.0.0.1',
        'db_name' :'test',
        'db_user_name' :'root',
        'db_user_pwd' :'root',
        'max_clear_days':30,
    }

    backup = BackupMySQL(options)
    #执行备份
    backup.run()
    #执行清理
    #backup.clear_file()