Python Script for MySQL Database Backup

Tech World 2013-12-03 Channel: Linux

Abstract: db = DB_NAME dumpcmd = "mysqldump -h " + DB_HOST + " -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + pipes.quote(TODAYBACKUPPATH) + "/"

Linux . A-Simple-Bash-Script-for-MySQL-Database-Backup

Linux . An-Advance-Bash-Script-for-MySQL-Database-Backup

Linux . MySQL-Database-Backup-to-FTP-Server---Shell-Script

This is a simple python script to backup MySQL databases using the mysqldump utility. This script has been tested with the Python 3.5 and 2.7.15.

How to Use Script

This script is very easy to use, Download or copy this script on your local system and execute it with python. This script is capable to take multiple databases backup

Single Database Backup: If you want to use this script for taking single database backup, edit script as below. For example database name is mydb.

  DB_NAME = 'mydb'

Multiple Databases Backup: To take multiple databases backup, create an text file like /backup/dbnames.txt and add databases names one per line like below

# cat /backup/dbnames.txt
database1
mydb

And add this file to script like below.

  DB_NAME = '/backup/dbnames.txt'

Change Backup Location: You can change below variable to change the location of backup path.

  BACKUP_PATH = '/backup/dbbackup/'

Python MySQL Backup Script

Click here or use below command to download script from Github or you can simply copy below script.

#!/usr/bin/python ########################################################### # # This python script is used for mysql database backup # using mysqldump and tar utility. # # Written by : Rahul Kumar # Website: http://tecadmin.net # Created date: Dec 03, 2013 # Last modified: Aug 17, 2018 # Tested with : Python 2.7.15 & Python 3.5 # Script Revision: 1.4 # ########################################################## # Import required python libraries import os import time import datetime import pipes # MySQL database details to which backup to be done. Make sure below user having enough privileges to take databases backup. # To take multiple databases backup, create any file like /backup/dbnames.txt and put databases names one on each line and assigned to DB_NAME variable. DB_HOST = 'localhost' DB_USER = 'root' DB_USER_PASSWORD = '_mysql_user_password_' #DB_NAME = '/backup/dbnameslist.txt' DB_NAME = 'db_name_to_backup' BACKUP_PATH = '/backup/dbbackup' # Getting current DateTime to create the separate backup folder like "20180817-123433". DATETIME = time.strftime('%Y%m%d-%H%M%S') TODAYBACKUPPATH = BACKUP_PATH + '/' + DATETIME # Checking if backup folder already exists or not. If not exists will create it. try: os.stat(TODAYBACKUPPATH) except: os.mkdir(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() # reading database name from file db = db[:-1] # deletes extra line dumpcmd = "mysqldump -h " + DB_HOST + " -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql" os.system(dumpcmd) gzipcmd = "gzip " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql" os.system(gzipcmd) p = p + 1 dbfile.close() else: db = DB_NAME dumpcmd = "mysqldump -h " + DB_HOST + " -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql" os.system(dumpcmd) gzipcmd = "gzip " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql" os.system(gzipcmd) print ("") print ("Backup script completed") print ("Your backups have been created in '" + TODAYBACKUPPATH + "' directory")12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182#!/usr/bin/python ############################################################# This python script is used for mysql database backup# using mysqldump and tar utility.## Written by : Rahul Kumar# Website: http://tecadmin.net# Created date: Dec 03, 2013# Last modified: Aug 17, 2018 # Tested with : Python 2.7.15 & Python 3.5# Script Revision: 1.4########################################################### # Import required python libraries import osimport timeimport datetimeimport pipes # MySQL database details to which backup to be done. Make sure below user having enough privileges to take databases backup.# To take multiple databases backup, create any file like /backup/dbnames.txt and put databases names one on each line and assigned to DB_NAME variable. DB_HOST = 'localhost' DB_USER = 'root'DB_USER_PASSWORD = '_mysql_user_password_'#DB_NAME = '/backup/dbnameslist.txt'DB_NAME = 'db_name_to_backup'BACKUP_PATH = '/backup/dbbackup' # Getting current DateTime to create the separate backup folder like "20180817-123433".DATETIME = time.strftime('%Y%m%d-%H%M%S')TODAYBACKUPPATH = BACKUP_PATH + '/' + DATETIME # Checking if backup folder already exists or not. If not exists will create it.try: os.stat(TODAYBACKUPPATH)except: os.mkdir(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() # reading database name from file db = db[:-1] # deletes extra line dumpcmd = "mysqldump -h " + DB_HOST + " -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql" os.system(dumpcmd) gzipcmd = "gzip " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql" os.system(gzipcmd) p = p + 1 dbfile.close()else: db = DB_NAME dumpcmd = "mysqldump -h " + DB_HOST + " -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql" os.system(dumpcmd) gzipcmd = "gzip " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql" os.system(gzipcmd) print ("")print ("Backup script completed")print ("Your backups have been created in '" + TODAYBACKUPPATH + "' directory")

Execute Python Script

After downloading script make the script executable using following command

chmod +x dbbackup.py

and execute this script like below

python2 dbbackup.py

You can also schedule this script to run daily on regular interval using crontab. Add below command in crontab.

0 2 * * * /usr/bin/python dbbackup.py

Read more about crontab at 20 Useful Examples to Schedule Jobs.

Thank You! for using this script. If you have any suggestion for my script, feel free to email me or put a comment below. Your feedback will help me to make it better.