Dump and compress all MySQL databases

Leave a reply

This is a little variation of a script I used in the past, what will do is find all databases, and export them compressed, with piping to gzip.

#!/bin/bash
MYSQLDUMP="$(which mysqldump)"
DIR="/root/db_dumps"
DBS=`mysql --defaults-extra-file=.my.cnf -e"show databases"`

#Remove	old dumps
rm -f $DIR/*.sql*

for DATABASE in $DBS
do
if [ $DATABASE != "Database" ]; then
FILENAME=$DATABASE
$MYSQLDUMP --defaults-extra-file=.my.cnf $DATABASE --single-transaction | gzip > $DIR/$FILENAME.sql.gz
fi
done

rm -f $DIR/information_schema.sql*
rm -f $DIR/performance_schema.sql*

.my.cnf file contents:

[client]
user=root
password=your_password

.my.cnf file should be in the user directory owned by him with permissions 600 or even 400