Python script: MySQL backup to local folder

13 January, 2011

The following script allows a compressing MySQL databases to a local folder. In the field variables must change with “___ & ___”. The most common username is “root” and the hostname “localhost” but you can use any other option. The output will be in dest_folder/YEARMONTHDAY-databasename.sql.gz

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

# START: configuration
username = '___&___'
password = '___&___'
hostname = '___&___'
dest_folder = '___&___'
# END: configuration

# timestamp
filestamp = time.strftime('%Y%m%d')
# database list
database_list_command="mysql -u%s -p%s -h %s --silent -N -e 'show databases'" % (username, password, hostname)
# iterate database list
for database in os.popen(database_list_command).readlines():
        database = database.strip()
        # databases not to backup
        if database == 'information_schema' or database == 'test' or database == 'mysql' or database == 'performance_schema':
                continue
        # destination and filename
        filename = "%s/%s-%s.sql" % (dest_folder, filestamp, database)
        # dump + gzip command
        os.popen("mysqldump -u%s -p%s -h %s -e --opt -c %s | gzip -c > %s.gz" % (username, password, hostname, database, filename))

We can add the following line to /etc/crontab for the backup to take effect every day at 0:00:

0 0 * * * -username- python /-folder-/mysql2folder.py

Regards!

    Tags: , , , ,

    4 Comments

    1. Carles says:

      he creado un github para el script e instrucciones: https://github.com/carlessanagustin/mysql2folder
      saludos!

    2. Carles says:

      también encontré un script más avanzado que permite subir los ficheros a un ftp:
      https://github.com/s7anley/MySQL-backup
      contiene las instrucciones de configuración y uso.

    3. victor says:

      Gracias por la ayuda, solo una cosina, ¿si solo quieres hacer el backup de una única base de datos, como la eliges?

    Leave a Comment