Backup all mysql databases to separate archives, each table in separate file.
#!/bin/bash
#
# Backup all databases to separate archives.
# 1. Each table is dumped into separate *.sql file.
# 2. Each view is dumped into separate file with DEFINER=CURRENT_USER.
# 3. All routines are dumped into routines-dbname.sql,
# 4. All files are added to archive dbname.7z, compressed with 7-zip, max compression.
#
# Usage: mysqlback.sh /output/directory
#
#USER="root"
#PASSWORD=""
OUTPUTDIR="$1"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD \
-e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
# backup all databases
for db in $databases; do
# get list of items in this database
tables=`$MYSQL --batch -N -e "select table_name from information_schema.tables where table_type='BASE TABLE' and table_schema='$db';"`
views=`$MYSQL --batch -N -e "select table_name from information_schema.tables where table_type='VIEW' and table_schema='$db';"`
routines=`$MYSQL --batch -N -e "select routine_name from information_schema.routines where routine_schema='$db';"`
# backup all items as separate files
for table in $tables; do
echo "table-$db.$table"
$MYSQLDUMP -Q -f --add-drop-table $db $table | \
7z u -bd -ms=off -si"table-$db.$table.sql" -mx=9 "$OUTPUTDIR/$db.7z" >/dev/null
done
for view in $views; do
echo "view-$db.$view"
$MYSQLDUMP -Q -f --add-drop-table --no-data $db $view | \
sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | \
7z u -bd -ms=off -si"view-$db.$view.sql" -mx=9 "$OUTPUTDIR/$db.7z" >/dev/null
done
for routine in $routines; do
echo "routine-$db.$routine"
done
$MYSQLDUMP --routines --no-data --no-create-info --no-create-db $db | \
sed -r 's/DEFINER=`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g' | \
7z u -bd -ms=off -si"routines-$db.sql" -mx=9 "$OUTPUTDIR/$db.7z" >/dev/null
done
echo -------------------------------------
echo Finished!