Python Script for MySQL Database Backup

By 3 Mins Read

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.

Advertisement

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.

Prerequisites

Before diving into the code, ensure you have the following:

  1. Python – An installation of Python 3 is needed. You can download the latest version from the official website.
  2. MySQL Server – You should have MySQL server installed and running. Download it from the official MySQL website.
  3. 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 = 'mydb'

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.txt
database1
mydb

And add this file to script like below.

  DB_NAME = '/backup/dbnames.txt'

Change Backup Location: You can change below variable to change the location of backup path.

  BACKUP_PATH = '/backup/dbbackup/'

Execute Python Script

After downloading script make the script executable using following command

chmod +x dbbackup.py

and execute this script like below

python2 dbbackup.py

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.

Important Note

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.

Conclusion

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.

Share.

Related Posts

29 Comments

  1. Parth on

    Thanks For the code,
    I am facing this issue and not able to resolve it,kindly help

    Traceback (most recent call last):
    File “/home/Dev/sqldump/databasebckup”, line 14, in
    os.stat(TODAYBACKUP)
    FileNotFoundError: [Errno 2] No such file or directory: ‘backup/2022-09-29-184801’

    During handling of the above exception, another exception occurred:

    Traceback (most recent call last):
    File “/home/Dev/sqldump/databasebckup”, line 16, in
    os.mkdir(TODAYBACKUP)
    FileNotFoundError: [Errno 2] No such file or directory: ‘backup/2022-09-29-184801’

    Reply
    • Rahul on

      Hi Parth,

      It looks like you have modified the script on your system. Can you send me the full script, so I can check? Also, which Python version are you running?

      Reply
  5. Nick on

    I have a problem:
    mysqldump: Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ‘$.”number-of-buckets-specified”‘) FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = ‘–My DB name —‘ AND TABLE_NAME = ‘category’;’: Unknown table ‘COLUMN_STATISTICS’ in information_schema (1109)

    Reply
  6. Mahmood Ali Rajput on

    Hi Rahul,

    Hope you are doing great. Brother can you guide me is it possible to after taking backup, script send us an email that backup has been taking. Please add in the script if possible.

    Thank you.

    Reply
  7. Neeraj Paliwal on

    Hi Rahul,
    Great script. But when I run to get back up from my aws server I get connection lost error. How do I fix this? The Database is large in size.

    Reply
  9. Monendra Singh on

    Hi Rahul,

    I have below requirement from my client:
    1. Take backup of postgresql DB using python
    2. Drop postgresql DB using python
    3. Create new postgresql DB using python
    4. Restore DB bacup file to newly created postgresql DB using python

    Please help me in this. How we can perform above steps using python?

    Thanks,
    Monendra Singh

    Reply
  10. Wil on

    Hi Rahul,
    I like this program and it works great on my computer. However, when I run it on my server it outputs blank SQL files. How do I fix this?

    Reply
  11. Wil on

    Hi Rahul,
    Great code and very simple. However, I ran into a problem. I ran it on my PC and it worked great however when I ran it on my DB server it outputs blank databases. How can I solve this issue?

    Reply
  13. Carlo on

    Hello guys,

    Made some improvements to the script and fixed the issues below.

    When accessing to a remote database it returns error (Only works for localhost).
    It returns errors when the path contain blank spaces and special characters.

    Added extra instructions to compress the sql file result into a tar.gz file.

    The source code can be found on https://github.com/carlouni/python-mysql-backup

    Reply
  14. Japonix on

    This is very good. Only change I would make is to implement ‘–login-path’ instead of using visible user credentials.

    Reply
  17. Ahmed Sameh on

    Hello again, found the fix.

    dumpcmd = “%s -u “%MysqldumpPath + DB_USER + ” -p” + DB_USER_PASSWORD + ” -h ” + DB_HOST + ” ” + db + ” > ” + TODAYBACKUPPATH + “/” + db + “.sql”

    Just use this line instead.

    Reply
  18. Ahmed Sameh on

    Hi,

    great idea, but I noticed you don’t actually use the DB_HOST variable, so when I tried running this code on another host it kept updating my local one, any idea?

    Reply
  19. setevoy on

    Thanks. Very simple and useful.

    I just made little changes – use logging:

    logging.info(‘Backup process started at %s.’ % DATETIME)
    logging.info(‘creating backup folder’)

    instead of:

    print “creating backup folder”

    Reply
  21. Rameez on

    Hello,

    Its showing “”Databases file not found…””. But its already have the DB and backed up to the specified directory and still shows this msg.
    My suggestion : Its not an incremental backup and its waste of space. If you can write a script for incremental backup,.

    root@system1:~# python dbbackup.py
    creating backup folder
    checking for databases names file.
    Databases file not found…
    Starting backup of database db_nagiosql_v32
    Backup script completed
    Your backups has been created in ‘/backup/dbbackup/12262013-125317’ directory
    root@system1:~#

    Regards
    Rameez

    Reply
    • Rahul Kumar on

      Hi Rameez,

      Thanks for you suggestion.. I will modify script to take incremental backup.

      Regarding message “Databases file not found”, we will get this msg if we are not using multiple databases backup option…. We will update messages with next update on script.

      Reply

Exit mobile version