Database Backup Automation
This utility automates the backup of a MySQL or MariaDB database. It uses the subprocess module to execute the system's mysqldump command and pipes the output directly into a gzip-compressed file, creating a timestamped archive.
Modules Used:
- subprocess: To execute the
mysqldumpcommand. gzip: To compress the SQL dump on the fly.- argparse: To handle command-line arguments.
datetime: To generate unique filenames based on the current time.
Prerequisites
- MySQL/MariaDB Server: You must have a database server running.
- mysqldump: The
mysqldumpcommand-line utility must be installed and available in your system's PATH.
The Code
Save this as db_backup.py.
import subprocess
import gzip
import argparse
import os
from datetime import datetime
import sys
def backup_database(host, user, password, database, output_dir):
# Ensure output directory exists
if not os.path.exists(output_dir):
try:
os.makedirs(output_dir)
except OSError as e:
print(f"Error creating directory: {e}")
return
# Generate filename: dbname_YYYY-MM-DD_HH-MM-SS.sql.gz
timestamp = datetime.now().strftime("%Y-%m-%d_%H-%M-%S")
filename = f"{database}_{timestamp}.sql.gz"
filepath = os.path.join(output_dir, filename)
print(f"Backing up database '{database}' to '{filepath}'...")
# Construct command
# Note: -p{password} must not have a space
cmd = [
'mysqldump',
f'-h{host}',
f'-u{user}',
f'-p{password}',
database
]
try:
# Open the output file in binary write mode for gzip
with gzip.open(filepath, 'wb') as f:
# Run mysqldump and pipe stdout to the gzip file
# stderr is captured to print errors if they occur
process = subprocess.Popen(cmd, stdout=f, stderr=subprocess.PIPE)
# Wait for process to finish
_, stderr = process.communicate()
if process.returncode != 0:
print(f"Error during backup: {stderr.decode('utf-8')}")
else:
size_mb = os.path.getsize(filepath) / (1024 * 1024)
print(f"Backup successful! Size: {size_mb:.2f} MB")
except FileNotFoundError:
print("Error: 'mysqldump' command not found. Is MySQL installed and in your PATH?")
except Exception as e:
print(f"An error occurred: {e}")
if __name__ == "__main__":
parser = argparse.ArgumentParser(description="MySQL Database Backup Tool")
parser.add_argument("database", help="Database name")
parser.add_argument("-u", "--user", required=True, help="MySQL Username")
parser.add_argument("-p", "--password", required=True, help="MySQL Password")
parser.add_argument("-h", "--host", default="localhost", help="MySQL Host (default: localhost)")
parser.add_argument("-o", "--output", default="./backups", help="Output directory (default: ./backups)")
args = parser.parse_args()
backup_database(args.host, args.user, args.password, args.database, args.output)Usage
# Backup 'my_app_db' using root user
python db_backup.py my_app_db -u root -p mysecretpassword
# Backup to a specific folder
python db_backup.py my_app_db -u root -p mysecretpassword -o /var/backups/sql