Script to mysqldump to all MySQL DBs

It seems that a program that does mysqldump for all DBs is often needed, and it seems that it is created each time. However, I have no memory of writing any of them.

Bash

How to use:

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

How to use

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