How to Automatically Backup MySQL Database Using Python

With the excess of data nowadays, databases are becoming massive and crucial to bear any data loss. So MySQL, which is a popular RDBMS, provides a mysqldump client utility to create a logical backup of databases. The user can use the mysqldump command in any script to create an automatic backup of MySQL databases. As MySQL supports many languages, this post will discuss how to automatically back up a MySQL database using Python, which is a popular in-demand language.

Make sure that Python is installed in your system and MySQL server has a database that you want to backup.

How to Automatically Backup MySQL Database Using Python?

Head towards any code editor to write the python code to automatically backup MySQL database. For this post, “Visual Studio Code” is being used:

Create a python file named “backup.py”:

Import these required libraries at the beginning of the python file:

import os

import time

Enter your MySQL database credentials:

DB_HOST = 'your_mysql_host'

DB_USER = 'your_mysql_username'

DB_PASS = 'your_mysql_password'

DB_NAME = 'your_database_name'

Provide the backup directory, where the MySQL database backup should be saved. Make sure to replace the path according to your system, “D:\\MySQL_Data_backup_files” is the path for this post:

BACKUP_DIR = 'D:\\MySQL_Data_backup_files'

Enter the “date_format” to define the data format for the backup files:

date_format = '%Y-%m-%d_%H-%M-%S'

Set the “BACKUP_INTERVAL”, to define the time after which a backup should automatically create:

BACKUP_INTERVAL = 20

Note: In this post, the backup interval is 20 seconds, you can set any, but it should be defined in seconds.

Type this piece of code to format the current time in the “date_format”:

while True:

current_time = time.strftime(date_format)

Define the backup file name:

backup_file = f'{DB_NAME}-{current_time}.sql'

Define the full path of the backup file by typing the following code:

backup_file_path = os.path.join(BACKUP_DIR, backup_file)

Use the mysqldump command to create a backup file by providing the required parameters according to your MySQL database:

mysqldump_cmd = f'mysqldump -h {DB_HOST} -u {DB_USER} -p{DB_PASS} {DB_NAME} > {backup_file_path}'

os.system(mysqldump_cmd)

To save space on the disk, compress the backup file using the gzip tool by typing:

gzip_cmd = f'gzip {backup_file_path}'

os.system(gzip_cmd)

To remove the old backup files, use the find command and delete them. For this post, files that are old than “7” days will be deleted:

find_cmd = f'find {BACKUP_DIR} -type f -name "*.gz" -mtime +7 -delete'

os.system(find_cmd)

Lastly, type this to wait for the backup interval:

time.sleep(BACKUP_INTERVAL)

Review your file and ensure everything is according to your system and MySQL database. Save the file by pressing “CTRL + S”:

Run the file by opening the terminal or the Command prompt in this directory and typing:

python.exe backup.py

Head to the directory to confirm that backup files are created after each “20 Seconds” as it was the backup interval:

It is visible in the output that backup files are created automatically.

Conclusion

To automatically back up MySQL databases using Python. Create a python file, and provide MySQL database details, backup interval, date, and file name format. Use the mysqldump command to create backup files and gzip to compress them. Execute the file to start the backup process. This guide demonstrates the process of creating backup files of MySQL database automatically.

How to Automatically Backup MySQL Database Using Python

How to Automatically Backup MySQL Database Using Python?

Conclusion

About the author

Abdul Mannan