Python script to backup mysql databases on Debian

Here’s a short python script I must have knocked up some time ago – and totally forgotten – hopefully it’ll be of some use to others….

Purpose: backup all MySQL databases, one in each file with a timestamp on the end. You’ll probably want to have a secondary cron job which does something like :

find /backups/mysql -mtime +5 -print | xargs -r rm

to delete old copies… changing +5 to how ever many days history you wish to have.

Method: Read /etc/mysql/debian.cnf to get login details for MySQL, connect to MySQL and ask it for a list of all databases, go through this list calling mysqldump on each one.

Code:

(Last updated: 2012/10/10 – skip trying to backup the performance_schema).

#!/usr/bin/env python
import ConfigParser
import os
import time

# On Debian, /etc/mysql/debian.cnf contains 'root' a like login and password.
config = ConfigParser.ConfigParser()
config.read("/etc/mysql/debian.cnf")
username = config.get('client', 'user')
password = config.get('client', 'password')
hostname = config.get('client', 'host')

filestamp = time.strftime('%Y-%m-%d')

# Get a list of databases with :
database_list_command="mysql -u %s -p%s -h %s --silent -N -e 'show databases'" % (username, password, hostname)
for database in os.popen(database_list_command).readlines():
    database = database.strip()
    if database == 'information_schema':
        continue
    if database == 'performance_schema':
        continue
    filename = "/backups/mysql/%s-%s.sql" % (database, filestamp)
    os.popen("mysqldump -u %s -p%s -h %s -e --opt -c %s | gzip -c > %s.gz" % (username, password, hostname, database, filename))


Posted

in

by

Tags:

Comments

13 responses to “Python script to backup mysql databases on Debian”

  1. […] Python script to backup mysql databases on Debian | David Goodwin […]

  2. […] Here’s a short python script I must have knocked up some time ago – and totally forgotten – hopefully it’ll be of some use to others….Purpose: backup all MySQL databases, one in each file with a timestamp on the end More here […]

  3. Mutaz Avatar
    Mutaz

    Thanks a lot! Very helpful script!

  4. )a((o Avatar
    )a((o

    you rock! thanks, it works like a charm.

  5. Alan Avatar

    Thanks, this is much easier than bash hacks. 🙂

  6. manolo Avatar
    manolo

    Excellent and Functional, Gracias Amigo

  7. […] The script itself is an adaptation from an example on David Goodwin’s site, which you can see here. […]

  8. shiva krishna Avatar
    shiva krishna

    I tried the exact code by giving the access details like username,password,localhost. Then its displaying me below error

    ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

    how to solve this error from the above code

  9. SamukaSmk Avatar
    SamukaSmk

    Ooww!!! Very! Very! Excellent! Mann!!…
    Thanks for the simplicity and astuteness

  10. David Goodwin Avatar

    Of course, a better approach would be to just use :

    mysqldump/mysql’s –defaults-extra-file=/etc/mysql/debian.cnf option

    to handle the authentication bit – rather than parsing the /etc/mysql/debian.cnf file and then using the extracted username/password.

Leave a Reply

Your email address will not be published. Required fields are marked *