[PYTHON] Skript an mysqldump an alle MySQL-DBs

Es scheint, dass häufig ein Programm benötigt wird, das "mysqldump" für alle DBs ausführt, und es scheint, dass es jedes Mal erstellt wird. Ich kann mich jedoch nicht erinnern, einen von ihnen geschrieben zu haben.

Bash

Wie benutzt man:

$ ./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

Wie benutzt man

% ./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()