Close Menu

    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.

    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.

    Advertisement

    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

    View 29 Comments

    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
    Leave A Reply