Skip to main content

Backup all mysql databases to separate archives, each table in separate file.

# 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: /output/directory

# 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

    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

    for routine in $routines; do
        echo "routine-$db.$routine"
    $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

echo -------------------------------------
echo   Finished!