!/usr/bin/env python3

-- coding:utf-8 --

import pymysql
import time
import datetime
import os
import shutil

dbUser='db_order'
dbPasswd='db_order'
dbHost='192.168.16.124'
dbCharset = 'utf8'
backupDir = '/home/developer/mysqlbackup'
backupDate = time.strftime("%Y%m%d")

if not os.path.exists(backupDir):
os.makedirs(backupDir)
print("backupDir exists:%s"%os.path.exists(backupDir))

连接数据库

conn = pymysql.connect(host=dbHost,port= 3306,user = dbUser,passwd=dbPasswd,db='db_order_sharding')

创建游标

cur = conn.cursor()
cur.execute("show databases like 'db_%'")
databases = cur.fetchall()
print(databases)
print('The database backup to start! %s' %time.strftime('%Y-%m-%d %H:%M:%S'))
for db in databases:
dbName = db[0]
print('databaseName:%s'%dbName)
fileName = '%s_%s.sql' %(backupDate,dbName)
print('fileName:%s'%fileName)
print('fileName:%s'%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'))

cur.execute("show tables from db_order_sharding")
tables = cur.fetchall()
print(tables)
cur.execute("use db_order_sharding")
for tb in tables:
tableName = tb[0]
print("tableName:%s"%tableName)
cur.execute("describe %s" % tableName)
fields = cur.fetchall()
print("fields of %s:%s" %(tableName, fields))
fieldList = []
for field in fields:
fieldList.append(field[0])
fieldStr = ",".join(fieldList)
print(fieldStr)
#print("fieldList:%s" % filedStr[0])
txtFileName = backupDir + "/" + tableName + ".txt"
if os.path.exists(txtFileName):
os.remove(txtFileName)
cur.execute("select * into outfile '%s/%s.txt' FIELDS TERMINATED BY ',' FROM %s"% (backupDir, tableName, tableName))