Python3 DB Backup V2

Posted on by Fatih Acar

You can take export backup of Oracle, PostgreSQL and MySQL databases with one backup script. There are 3 files (config.py, functions.py, dbbackup.py) for backup operation. You can edit config file regarding to your system.

You can download python scripts by clicking : dbbackup

config.py

#######################
# # # Config File # # #
#######################

#########################
# Author : Fatih Acar | Msc. Computer Engineer, Database Administrator
# E-Mail : fatih@fatihacar.com
# Program Name : DBBackup
# Date : 15.02.2017
# Version : 1.0
# Description : Program take full database backup of Oracle,PostgreSQL or MySQL databases with export methods of databases.
#########################

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

#########################################################

#########################
# Preinstallation Steps #
#########################

## Package Install ##
# yum install python3
# yum install python3-paramiko
# install sshpass package of epel repo
#########################

#########################################################

#########################
# Script Properties #
#########################

## Database Properties ##
dbtype = ‘Oracle’ # “Oracle” or “PostgreSQL” or “MySQL”
systemname = ‘DB Name’
ip = ‘DB IP Address’
# Oracle Properties #
orasid = ‘DB SID’
oraport = ‘1521’
orabackupdir = ‘Export DIR’
orabackupuser = ‘dbbackupuser’
orabackupuserpass = ‘backup user password’
# PostgreSQL Properties #
pgport = ‘5432’
# MySQL Properties #
myport = ‘3306’
#########################

## SFTP Remote Storage Transport##
sftptransportenable = 0 # 1 or 0
SFTP_server=”
SFTP_port=”
SFTP_user=”
SFTP_pass=”
#########################

## SCP Remote Storage Transport ##
scptransportenable = 1 # 1 or 0
usesshpass = 0 # 1 or 0 “0” means of using ssh-keygen. You have to configure ssh-keygen
SCP_server=’backup server IP’
SCP_port=’SSH Port’
SCP_user=’backup’
SCP_pass=”
SCP_dir=’/ddbackup/dbname/’
#########################

## Backup Properties ##
backup_alias = ‘dbname_full_backup’
backupdir = ‘/backup/FULL_EXPDP/’
zipdir = ‘/backup/zip/’
zip_pass = ‘123456789’
#########################

## Deletion Policy ##
deleteoldbackupenable = 1 # 1 or 0
retention = ‘7’ #day
#########################

## Mail Server Properties ##
mailserver = “mail server IP”
mailfrom = “mailfromaddress”
mailto = “mailtoaddress”
#########################

## Log Properties ##
logfile = ‘/opt/dbbackup/log/dbbackuplog.log’
#########################

#########################################################

##########################
# Postinstallation Steps #
##########################

## Crontab Config ##
# Every day at 01:01 take backup
# 1 1 * * * python3 /opt/dbbackup/dbbackup.py > /dev/null
#########################

###################################################

functions.py

#########################
# # # Function File # # #
#########################

#########################
# Author : Fatih Acar | Msc. Computer Engineer, Database Administrator
# E-Mail : fatih@fatihacar.com
# Program Name : DBBackup
# Date : 15.01.2024
# Version : 2.0
# Description : Program take full database backup of Oracle,PostgreSQL or MySQL databases with export methods of databases.
#########################

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

import sys
import string
from time import *
from config import *
import os
import sys
import smtplib
import logging
import time

from email.mime.text import MIMEText

def SendMail(subject,content):

mail = MIMEText(content)
mail[“From”] = mailfrom
mail[“To”] = mailto
mail[“Subject”] = subject

send = smtplib.SMTP(mailserver)
send.sendmail(mailfrom,mailto,mail.as_string())
send.quit()

def GetDateTime():

mounth=str(localtime()[1])
hour=str(localtime()[3])
min=str(localtime()[4])
sec=str(localtime()[5])
if(len(str(localtime()[1]))==1):
mounth=”0″+str(localtime()[1])
day=str(localtime()[2])
if(len(str(localtime()[2]))==1):
day=”0″+str(localtime()[2])
return str(localtime()[0])+”_”+mounth+”_”+day+”_”+hour+”_”+min+”_”+sec

def MakeZip(filename,filestozip):

status = os.system(‘zip -P ‘+zip_pass+’ ‘+zipdir+filename+’.zip ‘+backupdir+filestozip)
return status

def WriteLog(logfile,logcontent):

logcontent = GetDateTime()+’ : ‘+logcontent
try:
file=open(logfile,’a’)
file.write(logcontent+’\n’)
file.close()
except:
print (‘HATA : Log yazilirken hata olustu !’)

def BackupDB(backupparts,backupname,dbtype):

if(dbtype == ‘PostgreSQL’):
status = os.system(‘su – postgres -c “pg_dumpall -p ‘ +pgport+ ‘ > ‘+backupdir+backupname+'”‘)
return status
elif(dbtype == ‘Oracle’):
status = os.system(‘su – oracle -c “expdp ‘+orabackupuser+’/’+orabackupuserpass+’@’+orasid+’ DIRECTORY=’+orabackupdir+’ DUMPFILE=’+backupparts+’ FULL=Y PARALLEL=8″‘)
return status
elif(dbtype == ‘MySQL’):
status = os.system(‘mysqldump –port=’+myport+’ –all-databases > ‘+backupdir+backupname)
return status
else:
return ‘\033[91mconfig.py dosyasindaki dbtype parametre degeri dogru degil !\033[0m’

def RemoveBackup(backupname):

status = os.system(‘rm -f ‘+backupdir+backupname)
return status

def RemoveOldZipBackup():

try:
current_time = time.time()
retention_period = int(retention) * 24 * 60 * 60 # Convert retention days to seconds

for filename in os.listdir(zipdir):
file_path = os.path.join(zipdir, filename)
if os.path.isfile(file_path):
file_age = current_time – os.path.getmtime(file_path)
if file_age > retention_period:
os.remove(file_path)
print(f”Deleted: {filename}”)

return 0 # Return success
except Exception as e:
print(f”An error occurred: {e}”)
return 1 # Return failure

def TransportBackupSFTP(backupname):

try:
tp=paramiko.Transport((SFTP_server,int(SFTP_port)))
tp.connect(username=SFTP_user,password=SFTP_pass)
sftp=paramiko.SFTPClient.from_transport(tp)
sftp.put(zipdir+backupname,”/”+backupname)
return 0
except:
return 1
sftp.close()
tp.close()

def TransportBackupSCP(backupname):

if(usesshpass==1):
status = os.system(‘sshpass -p ‘ +SCP_pass+ ‘ scp -P ‘ +SCP_port+ ‘ ‘ +zipdir+backupname+ ‘ ‘ +SCP_user+ ‘@’ +SCP_server+ ‘:’+SCP_dir)
return status
elif(usesshpass==0):
status = os.system(‘scp -P ‘ +SCP_port+ ‘ ‘ +zipdir+backupname+ ‘ ‘ +SCP_user+ ‘@’ +SCP_server+ ‘:’+SCP_dir)
return status
else:
return ‘\033[91mconfig.py dosyasindaki usesshpass parametre degeri dogru degil !\033[0m’

dbbackup.py

from config import *
from functions import *
import time

 

backupname = backup_alias+’_’+GetDateTime()+’.dmp’
filestozip = backup_alias+’_’+GetDateTime()+’*.dmp’
backupparts = backup_alias+’_’+GetDateTime()+’_%u.dmp’

 

def main():
functions=[(BackupDB,(backupparts,backupname,dbtype)),(MakeZip,(backupname,filestozip)),(RemoveBackup,(filestozip,)),(TransportBackupSCP,(backupname+’.zip’,)),(RemoveOldZipBackup,())]
logging.basicConfig()
for func,args in functions:
result=func(*args)
function_name=func.__name__
if result == 0:
print(f”{function_name} adimi başarıyla tamamlandi.”)
WriteLog(logfile,f”{function_name} adimi başarıyla tamamlandi.”)
else:
WriteLog(logfile,f”{function_name} adiminda hata alindi. İşlemler durduruldu.”)
print(f”{function_name} adiminda hata alindi. İşlemler durduruldu.”)
return
print(“Bütün adımlar başarıyla tamamlandi.”)

main()

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.