python创建数据库并对表结构备份_Python实现备份MySQL数据库的方法示例

本文实例讲述了Python实现备份MySQL数据库的方法。分享给大家供大家参考，具体如下：

#!/usr/bin/env python

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

#导入模块

import MySQLdb

import time

import datetime

import os

"""

Purpose: 备份数据库

Created: 2015/5/12

Modified:2015/5/12

@author: guoyJoe

"""

dbUser='root'

dbPasswd='root'

dbHost='192.168.1.6'

dbCharset = 'utf8'

backupDir = '/u02/backup/mysql'

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

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

sqlStr1 = "show databases like 'db%'"

try:

connDB= MySQLdb.connect("192.168.1.6","root","root","test" )

connDB.select_db('test')

curSql1=connDB.cursor()

curSql1.execute(sqlStr1)

allDatabase = curSql1.fetchall()

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)

print fileName

if os.path.exists(fileName):

os.remove(fileName)

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

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

#异常

except MySQLdb.Error,err_msg:

print "MySQL error msg:",err_msg

希望本文所述对大家Python程序设计有所帮助。