Skip to content

Instantly share code, notes, and snippets.

@davigmacode

davigmacode/mysql-backup-all.sh

Forked from Thinkscape/mysql-backup-all.sh
Created April 28, 2016 00:30
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save davigmacode/62727dd1e1e8084121c0fabe4146d6c0 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save davigmacode/62727dd1e1e8084121c0fabe4146d6c0 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @Thinkscape Thinkscape revised this gist Mar 27, 2012. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion mysql-backup-all.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -9,6 +9,7 @@
    # Usage: mysqlback.sh /output/directory
    #
    # Author: Artur Bodera <[email protected]>
    # Latest version of this script at: https://gist.github.com/2215200
    #
    #USER="root"
    #PASSWORD=""
    @@ -52,4 +53,4 @@ for db in $databases; do

    done
    echo -------------------------------------
    echo Finished!
    echo Finished!
  2. @Thinkscape Thinkscape created this gist Mar 27, 2012.
    55 changes: 55 additions & 0 deletions mysql-backup-all.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,55 @@
    #!/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
    #
    # Author: Artur Bodera <[email protected]>
    #
    #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!