In the digital world, data is valuable. Consequently, ensuring data’s safety and availability is of paramount importance. That’s where backups come into the picture. This article will explain how to create a Python program to back up MySQL databases.
For this tutorial, we’ll use the `mysqldump` utility – a powerful tool that generates a SQL script from existing database content. We’ll execute this utility using Python’s `subprocess` module.
Before diving into the code, ensure you have the following:
- Python – An installation of Python 3 is needed. You can download the latest version from the official website.
- MySQL Server – You should have MySQL server installed and running. Download it from the official MySQL website.
- mysqldump – This tool comes pre-installed with the MySQL server.
The Python Program
Click here or use below command to download script from Github or you can simply copy below script.
How to Use Script
This script is very easy to use, Download or copy this script on your local system and execute it with python. This script is capable to take multiple databases backup
Single Database Backup: If you want to use this script for taking single database backup, edit script as below. For example database name is mydb.
DB_NAME = '
Multiple Databases Backup: To take multiple databases backup, create an text file like /backup/dbnames.txt and add databases names one per line like below
cat /backup/dbnames.txtdatabase1 mydb
And add this file to script like below.
DB_NAME = '
Change Backup Location: You can change below variable to change the location of backup path.
BACKUP_PATH = '
Execute Python Script
After downloading script make the script executable using following command
chmod +x dbbackup.py
and execute this script like below
You can also schedule this script to run daily on regular interval using crontab. Add below command in crontab.
0 2 * * * /usr/bin/python dbbackup.py
Read more about crontab at 20 Useful Examples to Schedule Jobs.
This program writes the password directly into the command string, which can expose sensitive information to system logs or to anyone who can view the command line history. A safer alternative is to use the MySQL options file (`~/.my.cnf`) to store the user credentials. This would look something like:
Then, you can remove the `-p` option from the `mysqldump` command in your Python script.
Creating backups of your database is an essential routine to prevent data loss. Python, in combination with the mysqldump utility, offers a flexible and programmable way to automate this task. This guide provided a robust script to backup MySQL databases using Python. However, in a production environment, it’s recommended to implement more sophisticated backup strategies, including error checking, logging, notification systems, and regular backup verifications.