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

# @Time : 2020-07-29

# @Author : Dingjs

#DB基本信息

import os

import time

import datetime

import glob

import shutil

"""

Python3.7 來執行mysql數據備份操作

自動保持7天的數據

備份的sql文件名精確到時分秒，這樣即使一天備份多次，也不會進行覆蓋

"""

#數據庫基本信息設定

DB_HOST = '輸入數據庫地址'

DB_USER = '輸入數據庫賬號'

DB_USER_PASSWORD = '輸入數據庫密碼'

DB_PORT = '端口號3306'

#多庫備份

#寫法一：把需要備份的庫名寫到txt文件中，然后讀取txt文件內的數據

#DB_NAME = '/backup/dbnames.txt'

#寫法二：通過執行sql語句來匹配下載的庫

# sqlStr1 = "show databases like 'iot%'"

#單庫備份

DB_NAME = 'iot_test'

#備份路徑

BACKUP_PATH = '../conn_DB/sql_path/'

#設置編碼格式

DB_CharSet = 'utf8'

#創建datatime，作為備份文件名稱

DATETIME = time.strftime('%Y%m%d')

#這里主要是做.sql文件的日期區分

DT_time = time.strftime("%Y%m%d_%H%M%S")

TODAYBACKUPPATH = BACKUP_PATH + DATETIME

#保留7天的備份文件

print ('del folder seven days ago')

folders = glob.glob('/backup/dbbackup/*')

today = datetime.datetime.now()

for item in folders:

try:

foldername = os.path.split(item)[1]

day = datetime.datetime.strptime(foldername, "%Y%m%d")

diff = today - day

if diff.days >= 7:

shutil.rmtree(item)

except:

pass

print("creating backup folder")

#查看備份文件夾是否存在，不存在，則創建

if not os.path.exists(TODAYBACKUPPATH):

os.makedirs(TODAYBACKUPPATH)

# Code for checking if you want to take single database backup or assinged multiple backups in DB_NAME.

#開始備份文件

print ("checking for databases names file.")

if os.path.exists(DB_NAME):

file1 = open(DB_NAME)

multi = 1

print("Databases file found...")

print ("Starting backup of all dbs listed in file " + DB_NAME)

else:

print ("Databases file not found...")

print ("Starting backup of database " + DB_NAME)

multi = 0

# Starting actual database backup process.

if multi:

in_file = open(DB_NAME,"r")

flength = len(in_file.readlines())

in_file.close()

p = 1

dbfile = open(DB_NAME,"r")

while p <= flength:

# 從數據庫讀取文件

db = dbfile.readline()

db = db[:-1]

#在python中通過cmd執行mysqldump備份語句

# dumpcmd = "mysqldump -u " + DB_USER + "-h"+DB_HOST + " -p" + DB_USER_PASSWORD + " " + db + " > " + TODAYBACKUPPATH + "/" + db + ".sql"

# os.system(dumpcmd)

#在python中通過cmd來執行mysqldump命令的另一種寫法

os.system( "mysqldump -h%s -u%s -p%s %s --default_character-set=%s > %s/%s_%s.sql" % (

DB_HOST, DB_USER, DB_USER_PASSWORD, db, DB_CharSet, TODAYBACKUPPATH, DT_time, db))

p = p + 1

dbfile.close()

else:

db = DB_NAME

# dumpcmd = "mysqldump -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + TODAYBACKUPPATH + "/" + db + ".sql"

# os.system(dumpcmd)

os.system("mysqldump -h%s -u%s -p%s %s --default_character-set=%s > %s/%s_%s.sql" % (

DB_HOST, DB_USER, DB_USER_PASSWORD, db, DB_CharSet, TODAYBACKUPPATH,DT_time, db))

#結束后，打印執行狀態信息

print ("Backup script completed")

print("Your backups has been created in '" + TODAYBACKUPPATH + "' directory")

總結

以上是生活随笔為你收集整理的python远程备份mysql_python3把服务器备份mysql数据库下载到本地的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。