MySQLの全DBに対してmysqldumpするスクリプト

全DBに対して mysqldump をするプログラムが必要になることがしばしばあるらしく、その都度作っているらしい。しかし、どれも書いた記憶が全くない。

Bash

使い方:

$ ./mysqldump-all </path/to/dump/dir>

mysqldump-all

#!/usr/bin/env bash

MYSQL_USER=root
MYSQL_PASS=root

if [ $# -ne 1 ]
then
        echo "$(basename $0) </path/to/dump/dir>"
        exit 1
fi

dump_dir=$1

if [ ! -d $dump_dir ]
then
        echo "$dump_dir is not a directory."
        exit 1
fi

databases=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e 'SHOW DATABASES;' --silent)

for database in $databases
do
        if [ "$database" != "information_schema" ]
        then
                echo "Dumping $database..."
                filename=$dump_dir/mysql-$database.sql.gz
                mysqldump -u${MYSQL_USER} -p${MYSQL_PASS} $database | gzip > $filename
                echo $filename
        fi
done

Python

つかいかた

% ./mysqldump-all.py -h
usage: mysqldump-all.py [-h] [-u USERNAME] [-p PASSWORD] [-x PREFIX] outdir

Dump all mysql databases

positional arguments:
  outdir       directory to make dump files

optional arguments:
  -h, --help   show this help message and exit
  -u USERNAME  mysql username
  -p PASSWORD  mysql password
  -x PREFIX    file prefix

mysqldump-all.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import os
import argparse
import commands
import sys

class MySQLDump:

    def __init__(self, options):
        self.username = options.username
        self.password = options.password
        self.outdir   = options.outdir
        self.prefix   = options.prefix
        self.ignore_databases = ['information_schema', 'performance_schema', 'mysql', 'test']

    def dump(self):
        self._check_outdir()

        databases = self._get_databases()

        for database in databases:
            if database not in self.ignore_databases:
                self._dump_database(database)

    def _check_outdir(self):
        if os.path.isdir(self.outdir) == False:
            raise Exception("Out directory '%s' not found." % self.outdir)

    def _get_databases(self):
        command = "mysql --silent %s -e 'SHOW DATABASES'" % self._get_options()
        result = commands.getstatusoutput(command)
        if result[0] > 0:
            raise Exception('Failed to fetch databases: ' + result[1]);

        return result[1].split("\n")

    def _dump_database(self, database):
        print "Dumping database %s..." % database 
        filename = self.outdir + '/' + self.prefix + database + '.sql.gz'
        command = "mysqldump %s %s | gzip > %s" % (self._get_options(), database, filename)
        result = commands.getstatusoutput(command)
        if result[0] > 0:
            raise Exception(result[1])

        print filename

    def _get_options(self):
        options = []

        if self.username:
            options.append("-u %s" % self.username)

        if self.password:
            options.append("-p %s" % self.password)

        return ' '.join(options)


def main():
    parser = argparse.ArgumentParser(description='Dump all mysql databases')
    parser.add_argument('-u', type=str, help='mysql username', default='', dest='username')
    parser.add_argument('-p', type=str, help='mysql password', default='', dest='password')
    parser.add_argument('-x', type=str, help='file prefix', default='mysql-', dest='prefix')
    parser.add_argument('outdir', type=str, help='directory to make dump files')

    args = parser.parse_args()

    try:
        mysqldump = MySQLDump(args)
        mysqldump.dump()
    except Exception, e:
        print e
        exit(1)

if __name__ == "__main__":
    main()