Python3.7备份MySQL数据库

最新推荐文章于 2024-04-20 15:42:20 发布

vincen_space

最新推荐文章于 2024-04-20 15:42:20 发布

阅读量527

点赞数 2

分类专栏： Python 文章标签： python mysql

本文链接：https://blog.csdn.net/vincen123/article/details/106784062

版权

Python 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL用户：root
MySQL密码：123456
需要事先使用pip3安装pymysql库

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# author: vincen


import pymysql.cursors
import os
import time

db_host = '127.0.0.1'
db_user = 'root'
db_passwd = '123456'
# db_name = 'zabbix'

backup = '/data/backup/mysql'

time_year = time.strftime('%Y')
time_month = time.strftime('%m')
time_day = time.strftime('%d')

todayBackupDir = backup + '/' + time_year + '/' + time_month + '/' + time_day
# backupName = db_name + '_' + time.strftime('%Y%m%d') + time.strftime('%H%M') + '.sql'


def checkDir():
    '''检查是否存在对应的目录，如果不存在就创建'''
    if os.path.isdir(todayBackupDir):
        resInfo = 'the backup directory %s already exits...' % todayBackupDir
        print(resInfo)
    else:
        os.makedirs(todayBackupDir)
        resInfo1 = 'the backup directory has created....'
        print(resInfo1)


def backup(databaseName):
    '''备份数据库'''
    checkDir()
    backupName = databaseName + '_' + time.strftime('%Y%m%d') + time.strftime('%H%M') + '.sql'
    try:
        print('start backup  %s database...' % databaseName)
        backupcmd = "/usr/bin/mysqldump -u%s -p%s %s | /usr/bin/gzip > %s/%s.tar.gz" % (db_user,db_passwd,databaseName,todayBackupDir,backupName)
        os.system(backupcmd)
        return "the %s database backup is complete" % databaseName
    except:
        return False



def main():
    connection = pymysql.connect(host='127.0.0.1',user='root',password='123456')
                                 # db='zabbix',
                                 # charset='utf8')
                                 # cursorclass=pymysql.cursors.DictCursor)
    results = []
    try:
        with connection.cursor() as cursor:
            sql = '''SHOW DATABASES'''
            cursor.execute(sql)
            result = cursor.fetchall()
            # print(result)
            for i in range(len(result)):
                results.append(result[i][0])
            # print(results)
    finally:
        connection.close()
    # a = len(results)
    # print(type(a))
    for databasename in results:
        # databaseName = results[i][0]
        if databasename != "information_schema" and databasename != "mysql" and databasename != "performance_schema":
            # pass
            # print(databasename)
            backup(databasename)



if __name__ == "__main__":
    main()