MySQL Linux Backup Script

I’ve written the following script to backup the last closed historical partition and assosiated data tables for the Ignition historian on Linux. You can schedule this with cron tab and store the backups on a remote windows share or something pretty easily.

#!/usr/bin/python
import sys
import os
import datetime
import mysql.connector
from subprocess import call
from optparse import OptionParser

def main(argv):
	#---------------------------------------------------------------------------------------
	#	Setup command line argument parsing
	#---------------------------------------------------------------------------------------
	usage = "usage: %prog [options] directory"
	parser = OptionParser(usage=usage)
	parser.add_option("-u", "--user", dest="username", help="MySQL Username")
	parser.add_option("-p", "--password", dest="password", help="MySQL Password")
	parser.add_option("--host", dest="host", help="MySQL Host Address")
	parser.add_option("-d", "--database", dest="database", help="MySQL Database Name")
	
	(options, args) = parser.parse_args()
	if(len(args) != 1):
		parser.error("Error: Incorrect number of arguments")
	
	#---------------------------------------------------------------------------------------
	#	Read in arguments 
	#---------------------------------------------------------------------------------------
	if(options.host is None):
		host = "127.0.0.1"
	else:
		host = options.host
	
	if(options.username is None):
		username = "root"
	else:
		username = options.username
		
	if(options.password is None):
		password = ""
	else:
		password = options.password

	if(options.database is None):
		database = "Historian"
	else:
		database = options.database
	
	basedir = args[0]
	backupdir = basedir + 'mysql.' + database + "." + str(datetime.date.today())
	
	#---------------------------------------------------------------------------------------
	#	Connect to MySQL
	#---------------------------------------------------------------------------------------
	db = mysql.connector.connect(
			host=host,
			user = username,
			passwd = password,
			database = database              
	)

	cursor = db.cursor()

	
	#---------------------------------------------------------------------------------------
	#	Check if the backups table exists.  
	#---------------------------------------------------------------------------------------
	query = """
			SHOW TABLES WHERE tables_in_%s = 'sqlth_backups';
			""" % database
			
	cursor.execute(query)
	results = cursor.fetchall()
	
	# If there isn't a backups table, create it.
	if(len(results) < 1):
	
		print "Creating backups table."
		
		query = """
				CREATE TABLE `sqlth_backups` (
				  `backupid` int(11) NOT NULL AUTO_INCREMENT,
				  `target` varchar(255) DEFAULT NULL,
				  `actiontime` datetime NOT NULL,
				  `expires` datetime NOT NULL,
				  PRIMARY KEY (`backupid`)
				) ENGINE=InnoDB;
				"""
				
		cursor.execute(query)
		db.commit()
	# end if
	
	#---------------------------------------------------------------------------------------
	#	Select the current history partition to avoid backing that up.
	#---------------------------------------------------------------------------------------
	query = """
			SELECT
				pname
			FROM sqlth_partitions
			WHERE 
				end_time = (SELECT MAX(end_time) FROM sqlth_partitions);
			"""
	
	cursor.execute(query)
	latestPartition = cursor.fetchall()[0][0]

	#---------------------------------------------------------------------------------------
	#	Select misc history tables and history partitions that haven't been backed up yet
	#---------------------------------------------------------------------------------------
	query = """
			SHOW TABLES
			WHERE
				tables_in_{db} LIKE 'sqlth%'
			OR (
				tables_in_{db} LIKE 'sqlt_data_%'
			AND
				tables_in_{db} not in (select distinct target from sqlth_backups)
			);
			""".format(db=database)
			
	cursor.execute(query)
	tableList = cursor.fetchall()
	
	# create a new directory for this set of backups
	os.system("mkdir %s" % (backupdir))
	
	#---------------------------------------------------------------------------------------
	#	For each table, back it up to it's own SQL file, then insert a record into the backups
	#	table to record the backup.
	#---------------------------------------------------------------------------------------
	queries = []
	for row in tableList:
		if(row[0] != latestPartition):
		
			print "Backing up " + row[0]
		
			cmd =  "mysqldump -h %s -u %s -p%s %s %s > %s/%s.sql" % (host, username, password, database, row[0], backupdir, row[0])
			os.system(cmd)
			
			query = "INSERT INTO sqlth_backups(target, actiontime) VALUES('%s', CURRENT_TIMESTAMP);" % row[0]
			queries.append(query)
	# end for
	
	print "Recording Backups in SQL"
	
	for query in queries:
		cursor.execute(query)
	# end for
	
	#---------------------------------------------------------------------------------------
	#	Commit and close the database connection
	#---------------------------------------------------------------------------------------
	db.commit()
	cursor.close()
	db.close()	
	
	#---------------------------------------------------------------------------------------
	#	Archive the backup to a .tar.gz and remove the temporary backup folder.
	#---------------------------------------------------------------------------------------
	print "Zipping Archives"
		
	os.system("tar -cvzf %s.tar.gz %s" % (backupdir, backupdir))
	os.system("rm -r %s" % (backupdir))
	
# end def


if __name__ == "__main__":
        main(sys.argv[1:])
2 Likes

Thanks for posting this Cody, this looks really useful :thumb_left: