python备份mysql脚本

#!/usr/bin/env python

# -*- coding:utf-8 -*-

#导入模块

import MySQLdb

import time

import datetime

import os

"""

Purpose: 备份数据库

Created: 2022/9/9

Modified:2022/9/9

@author: lbwb

"""

dbUser='root'

dbPasswd='**********'

dbHost='172.22.***.***'

dbPort='3306'

dbCharset = 'utf8'

backupDir = '/data/mysql/mysqlbak'

backupDate = time.strftime("%Y%m%d")

db='gw'

#查出MySQL中所有的数据库名称

sqlStr1 = "show databases like '%'"

try:

 connDB= MySQLdb.connect(host="172.22.***.***",port=3306,user="root",passwd="******",db="gw",charset="utf8")

 connDB.select_db('gw')

 curSql1=connDB.cursor()

 curSql1.execute(sqlStr1)

 allDatabase = curSql1.fetchall()

 print(allDatabase)

 print('The database backup to start! %s'  %time.strftime('%Y-%m-%d %H:%M:%S'))

 for db in allDatabase:

   dbName = db[0]

   fileName = '%s/%s_%s.sql' %(backupDir,backupDate,dbName)

   fileName1 = 'hanyyn%s.tar.gz' %(backupDate)

   print(fileName)

   if os.path.exists(fileName):

       os.remove(fileName)

   os.system("mysqldump -h%s -u%s -p%s -P%s %s --default_character-set=%s > %s/%s_%s.sql" %(dbHost,dbUser,dbPasswd,dbPort,dbName,dbCharset,backupDir,backupDate,dbName))

 os.system("cd %s;tar -zcvf hanyyn%s.tar.gz *.sql;rm -f *.sql" %(backupDir,backupDate))  

 print('The database backup success! %s' %time.strftime('%Y-%m-%d %H:%M:%S'))

#异常

except MySQLdb.Error  as e:

 print('Error: %s' % e)

#except MySQLdb.Error:

#  print("MySQL error msg:",err_msg)