Python MySQL Automated Database Backup and Restore Utility

Interactive Google Colab Practice Notebook

Practice automating MySQL database backups via mysqldump, simulating table drops, and restoring database dumps interactively inside Google Colab.

View on GitHub

Automating database backups is a critical administrative task for any production web application or development workflow. In this tutorial, we will explore how to build a robust Python script to back up and restore MySQL databases using subprocess and system utilities like mysqldump.

1. Creating an Automated MySQL Backup Script

Using Python's built-in subprocess module, we can execute native MySQL command-line utilities to export schema structures and data into timestamped .sql backup files [cite: 21].

import subprocess
import os
from datetime import datetime

# Database configuration
DB_HOST = "localhost"
DB_USER = "root"
DB_PASSWORD = ""
DB_NAME = "my_tutorial"

# Generate timestamped backup filename
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
backup_filename = f"backup_{DB_NAME}_{timestamp}.sql"

# Construct mysqldump command
dump_cmd = [
    "mysqldump",
    f"-h{DB_HOST}",
    f"-u{DB_USER}",
    f"-p{DB_PASSWORD}" if DB_PASSWORD else "",
    DB_NAME
]
# Remove empty password argument if password is blank
dump_cmd = [arg for arg in dump_cmd if arg]

try:
    with open(backup_filename, "w") as f:
        process = subprocess.run(dump_cmd, stdout=f, stderr=subprocess.PIPE, text=True, check=True)
    print(f"Database backup created successfully: {backup_filename}")
except subprocess.CalledProcessError as e:
    print(f"Backup failed: {e.stderr}")

2. Restoring a Database from a Backup File

To restore a MySQL database from an existing .sql backup dump file, we pipe the file contents back into the MySQL client via subprocess [cite: 21]:

import subprocess

backup_file = "backup_my_tutorial_20260607_120000.sql"
restore_cmd = [
    "mysql",
    f"-h{DB_HOST}",
    f"-u{DB_USER}",
    DB_NAME
]

try:
    with open(backup_file, "r") as f:
        process = subprocess.run(restore_cmd, stdin=f, stderr=subprocess.PIPE, text=True, check=True)
    print("Database restored successfully from backup!")
except subprocess.CalledProcessError as e:
    print(f"Restore failed: {e.stderr}")

Frequently Asked Questions & Common Errors

Ensure there is no space between the -p flag and your password in the command list (e.g., use "-pMyPassword" instead of "-p", "MyPassword"). Putting a space causes the system to treat the password argument as the database name.

Yes! Simply append individual table names at the end of your mysqldump command array after specifying the database name (e.g., ["mysqldump", "-u", "root", "my_tutorial", "student"]).

You can schedule your Python backup script using operating system schedulers like Linux cron jobs or Windows Task Scheduler to execute daily or weekly.

The MySQL client utilities (specifically mysqldump and mysql binaries) must be installed on the host machine and available in the system's execution PATH.

Related Tutorials & Next Steps

Python MySQL Connection Guide
Learn how to configure SQLAlchemy engines and connection strings [cite: 21].
Python Pickle MySQL Data
Serialize database tables and Pandas DataFrames into binary storage [cite: 21].
Python MySQL DROP Table Guide
Learn how to drop tables and manage database structures safely [cite: 21].
Student Table SQL Dump File
Download the complete student.sql database dump file featuring sample records with the gender column schema [cite: 21].

Python functions for MySQL Collect records Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: