Script pour mysqldump vers toutes les bases de données MySQL

Il semble qu'un programme qui fait mysqldump pour toutes les bases de données soit souvent nécessaire, et il semble qu'il soit créé à chaque fois. Cependant, je n'ai aucun souvenir d'en avoir écrit.

Bash

Comment utiliser:

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

Comment utiliser

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