How to Build a Python‑Powered MySQL Backup Tool with GUI and Scheduler

Introduction

This document describes a Python‑based utility for backing up MySQL databases. It supports multiple database instances, provides a Tkinter graphical interface, schedules recurring backups with APScheduler, logs each operation, and stores connection credentials in an external INI configuration file.

Key Features

Manage any number of MySQL instances from a single GUI.

Select one or more databases for immediate or scheduled backup.

Define backup frequency using standard cron expressions.

Automatic logging of success and error messages to backup.log.

Credentials are read from config.ini, avoiding hard‑coded secrets.

Technical Stack & Installation

Required components:

Python (cross‑platform)

Tkinter (standard GUI library)

mysqldump (MySQL command‑line dump tool)

APScheduler (background job scheduler)

logging (standard logging module)

configparser (INI file handling)

Install the Python dependencies with:

pip install mysql-connector-python apscheduler configparser

Configuration File

Create a config.ini file that defines each database and the default backup directory. Example:

[database1]
host=localhost
user=root
password=your_password
db_name=test_db1

[database2]
host=localhost
user=root
password=another_password
db_name=test_db2

[backup]
path=/path/to/backup

Code Overview

The application consists of three main classes:

BackupConfig : Loads config.ini, provides get_db_params() to retrieve all database sections prefixed with database, and updates the backup path.

MySQLBackup : Executes mysqldump for a given database name, writes the dump to {db_name}_{timestamp}.sql in the configured directory, and logs the outcome.

BackupApp (Tkinter Tk subclass): Builds the GUI, handles user actions (selecting backup path, choosing databases, setting cron expression), and interacts with APScheduler to run MySQLBackup.backup() either immediately or on schedule.

import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import os
from datetime import datetime
import configparser
import subprocess
from apscheduler.schedulers.background import BackgroundScheduler
import logging

logging.basicConfig(filename='backup.log', level=logging.INFO,
                    format='%(asctime)s - %(levelname)s - %(message)s')

class BackupConfig:
    def __init__(self, config_path='config.ini'):
        self.config = configparser.ConfigParser()
        self.config.read(config_path)
    def get_db_params(self):
        return {section: dict(self.config.items(section))
                for section in self.config.sections()
                if section.startswith('database')}
    def set_backup_path(self, path):
        self.config.set('backup', 'path', path)
        with open('config.ini', 'w') as f:
            self.config.write(f)

class MySQLBackup:
    def __init__(self, db_params, backup_path):
        self.db_params = db_params
        self.backup_path = backup_path
    def backup(self, db_name):
        ts = datetime.now().strftime('%Y%m%d%H%M%S')
        file_name = f"{db_name}_{ts}.sql"
        file_path = os.path.join(self.backup_path, file_name)
        cmd = [
            'mysqldump',
            '-h', self.db_params[db_name]['host'],
            '-u', self.db_params[db_name]['user'],
            '--password=' + self.db_params[db_name]['password'],
            db_name
        ]
        try:
            with open(file_path, 'w') as f:
                subprocess.run(cmd, stdout=f, check=True)
            logging.info(f"Backup succeeded: {file_path}")
            return True, file_path
        except subprocess.CalledProcessError as e:
            logging.error(f"Backup failed for {db_name}: {e}")
            return False, str(e)

class BackupApp(tk.Tk):
    def __init__(self):
        super().__init__()
        self.title('MySQL Database Backup Tool')
        self.geometry('600x400')
        self.config_obj = BackupConfig()
        self.db_params = self.config_obj.get_db_params()
        self.scheduler = BackgroundScheduler()
        self.scheduler.start()
        self._build_ui()
    def _build_ui(self):
        ttk.Button(self, text='Select Backup Path', command=self._select_path).pack(pady=10)
        self.lb = tk.Listbox(self, selectmode=tk.MULTIPLE)
        for db in self.db_params:
            self.lb.insert(tk.END, db)
        self.lb.pack(pady=10)
        self.cron_entry = ttk.Entry(self)
        self.cron_entry.insert(0, '0 2 * * *')
        self.cron_entry.pack(pady=10)
        ttk.Button(self, text='Set Scheduled Backup', command=self._schedule).pack(pady=10)
        ttk.Button(self, text='Backup Now', command=self._backup_now).pack(pady=10)
        self.status = ttk.Label(self, text='')
        self.status.pack(pady=10)
    def _select_path(self):
        path = filedialog.askdirectory()
        if path:
            self.config_obj.set_backup_path(path)
            self.status.config(text=f'Backup path set: {path}')
    def _backup_now(self):
        path = self.config_obj.config.get('backup', 'path', fallback=None)
        if not path:
            messagebox.showerror('Error', 'Select a backup path first.')
            return
        selected = [self.lb.get(i) for i in self.lb.curselection()]
        if not selected:
            messagebox.showwarning('Warning', 'No database selected.')
            return
        backup = MySQLBackup(self.db_params, path)
        for db in selected:
            success, msg = backup.backup(db)
            if not success:
                messagebox.showerror('Error', msg)
    def _schedule(self):
        cron = self.cron_entry.get().split()
        if len(cron) != 5:
            messagebox.showerror('Error', 'Invalid cron expression.')
            return
        selected = [self.lb.get(i) for i in self.lb.curselection()]
        path = self.config_obj.config.get('backup', 'path', fallback=None)
        if not path or not selected:
            messagebox.showerror('Error', 'Select path and at least one database.')
            return
        def job():
            backup = MySQLBackup(self.db_params, path)
            for db in selected:
                backup.backup(db)
        self.scheduler.add_job(job, 'cron', second=cron[0], minute=cron[1], hour=cron[2],
                               day=cron[3], month=cron[4])
        self.status.config(text='Scheduled backup configured.')

if __name__ == '__main__':
    app = BackupApp()
    app.mainloop()

Usage Guide

Edit config.ini to add each database’s host, user, password, and db_name. Define the default backup directory under the [backup] section.

Run the script ( python backup_tool.py). The GUI loads the configured databases.

Select one or more databases from the list box.

Click “Select Backup Path” to choose a directory where dump files will be stored.

For an immediate backup, click “Backup Now”.

To automate, enter a cron expression (e.g., 0 2 * * * for daily 02:00) and click “Set Scheduled Backup”.

Monitor progress via the status label and review backup.log for detailed results.

Automation & Security Considerations

APScheduler runs the backup job in a background thread, allowing the GUI to remain responsive. All credentials are read from config.ini at runtime; they are never embedded in source code, reducing the risk of accidental exposure. Ensure the configuration file has appropriate filesystem permissions to limit access.

Conclusion

The tool provides a lightweight, cross‑platform solution for routine MySQL backups, combining multi‑database handling, a simple Tkinter interface, cron‑style scheduling, and structured logging. It can be extended with additional features such as compression, remote storage, or email notifications as needed.