how to take backup of mysql database using python

Solutions on MaxInterview for how to take backup of mysql database using python by the best coders in the world

showing results for - "how to take backup of mysql database using python"
Jaden
11 May 2018
1#!/usr/bin/python
2 
3###########################################################
4#
5# This python script is used for mysql database backup
6# using mysqldump and tar utility.
7#
8# Written by : Rahul Kumar
9# Website: http://tecadmin.net
10# Created date: Dec 03, 2013
11# Last modified: Aug 17, 2018 
12# Tested with : Python 2.7.15 & Python 3.5
13# Script Revision: 1.4
14#
15##########################################################
16 
17# Import required python libraries
18 
19import os
20import time
21import datetime
22import pipes
23 
24# MySQL database details to which backup to be done. Make sure below user having enough privileges to take databases backup.
25# 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.
26 
27DB_HOST = 'localhost' 
28DB_USER = 'root'
29DB_USER_PASSWORD = '_mysql_user_password_'
30#DB_NAME = '/backup/dbnameslist.txt'
31DB_NAME = 'db_name_to_backup'
32BACKUP_PATH = '/backup/dbbackup'
33 
34# Getting current DateTime to create the separate backup folder like "20180817-123433".
35DATETIME = time.strftime('%Y%m%d-%H%M%S')
36TODAYBACKUPPATH = BACKUP_PATH + '/' + DATETIME
37 
38# Checking if backup folder already exists or not. If not exists will create it.
39try:
40    os.stat(TODAYBACKUPPATH)
41except:
42    os.mkdir(TODAYBACKUPPATH)
43 
44# Code for checking if you want to take single database backup or assinged multiple backups in DB_NAME.
45print ("checking for databases names file.")
46if os.path.exists(DB_NAME):
47    file1 = open(DB_NAME)
48    multi = 1
49    print ("Databases file found...")
50    print ("Starting backup of all dbs listed in file " + DB_NAME)
51else:
52    print ("Databases file not found...")
53    print ("Starting backup of database " + DB_NAME)
54    multi = 0
55 
56# Starting actual database backup process.
57if multi:
58   in_file = open(DB_NAME,"r")
59   flength = len(in_file.readlines())
60   in_file.close()
61   p = 1
62   dbfile = open(DB_NAME,"r")
63 
64   while p <= flength:
65       db = dbfile.readline()   # reading database name from file
66       db = db[:-1]         # deletes extra line
67       dumpcmd = "mysqldump -h " + DB_HOST + " -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql"
68       os.system(dumpcmd)
69       gzipcmd = "gzip " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql"
70       os.system(gzipcmd)
71       p = p + 1
72   dbfile.close()
73else:
74   db = DB_NAME
75   dumpcmd = "mysqldump -h " + DB_HOST + " -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql"
76   os.system(dumpcmd)
77   gzipcmd = "gzip " + pipes.quote(TODAYBACKUPPATH) + "/" + db + ".sql"
78   os.system(gzipcmd)
79 
80print ("")
81print ("Backup script completed")
82print ("Your backups have been created in '" + TODAYBACKUPPATH + "' directory")
83