Dump MySQL database and grants.

git-svn-id: svn+ssh://scm.narf.ssji.net/svn/shtrom/scripts@1903 1991c358-8f32-0410-a49a-990740bdf4c2

1 files changed, 24 insertions, 0 deletions

diff --git a/openbsd/backup.sh b/openbsd/backup.sh
index 519661d..f43a577 100755
--- a/openbsd/backup.sh
+++ b/openbsd/backup.sh
@@ -7,8 +7,21 @@ BACKUPSERVER=backup.narf.ssji.net
 #REMOTEBACKUPDIR="`hostname -s`@${BACKUPSERVER}::${BACKUPDIR}"
 REMOTEBACKUPDIR="`hostname -s`@${BACKUPSERVER}::/share/homes/`hostname -s`"
 EXCLUDEALWAYS="${BACKUPROOT} /srv/backup/ /srv/www/sites/offlines.fr/"
+MYSQLDUMPDIR="/srv/backup/"
+MYSQLUSER="dumper"
 TMPSUBDIR=/tmp
 
+# Dump privilege information  [0]
+# [0] http://serverfault.com/questions/8860/how-can-i-export-the-privileges-from-mysql-and-then-import-to-a-new-server/13050#13050
+mysqldump-grants()
+{
+	mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
+		'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
+		) AS query FROM mysql.user" | \
+		mysql $@ | \
+		sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
+}
+
 if [ "$1" = "remote" ]; then
 	EFFECTIVEBACKUPDIR=${REMOTEBACKUPDIR}
 	REMOTESCHEMA="ssh -qCT -i /etc/ssh/ssh_host_rsa_key %s"
@@ -17,6 +30,17 @@ else
 	REMOTESCHEMA=""
 fi
 
+echo -n "Dumping databases:"
+if [ ! -z "$MYSQLDUMPDIR" ]; then
+	MYSQLDUMP="${MYSQLDUMPDIR}/mysql.$(date +%Y-%m-%d_%H:%M).dump.sql"
+	echo -n " mysql (${MYSQLDUMP})"
+	# GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO 'dumper'@'localhost';
+	mysqldump -u ${MYSQLUSER} -A -E > ${MYSQLDUMP}
+	mysqldump-grants -u ${MYSQLUSER} >> ${MYSQLDUMP}
+	
+fi
+echo "."
+
 echo -n "Backing up to ${EFFECTIVEBACKUPDIR}:"
 for DIR in ${DIRS}; do
 	echo -n " ${DIR}"