Skip to content

Instantly share code, notes, and snippets.

@pstaender

pstaender/mysqlbackup_complete.sh

Created April 28, 2015 16:41
Download ZIP

Revisions

  1. Philipp Staender created this gist Apr 28, 2015.
    27 changes: 27 additions & 0 deletions mysqlbackup_complete.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,27 @@
    #!/bin/bash

    FREQUENCY=${1:-daily}
    DB_BACKUP_DIR_ROOT="/home/backup/mysql/$FREQUENCY"
    DB_BACKUP_DIR_TODAY="$DB_BACKUP_DIR_ROOT/`date +%Y-%m-%d`"
    DATESTRING=$(date +%Y.%m.%dT%H-%M-%S)

    [[ $FREQUENCY = 'weekly' ]] && holdback="+182" || holdback="+31"

    # Create the backup directory
    mkdir -p $DB_BACKUP_DIR_ROOT && mkdir -p $DB_BACKUP_DIR_TODAY

    # Remove backups older than 1 day
    find $DB_BACKUP_DIR_ROOT/ -maxdepth 1 -type d -mtime $holdback -exec rm -rf {} \;

    # Backup each db in the server, skip schema dbs though
    # Note, no username or password in this script, you should use mysql_config_editor to store is securely
    # See http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html
    for db in $(mysql -Bse 'show databases'|egrep -vi 'information_schema|performance_schema')
    do
    echo "Dump '$db' to '$DB_BACKUP_DIR_TODAY/$db-$DATESTRING.sql.gz'";
    mysqldump $db | gzip > "$DB_BACKUP_DIR_TODAY/$db-$DATESTRING.sql.gz";
    done

    # Export privileges
    echo "Exporting db privileges to '$DB_BACKUP_DIR_TODAY/privileges_global_$DATESTRING.sql.gz'"
    mysql -Ne "select distinct concat( \"SHOW GRANTS FOR '\",user,\"'@'\",host,\"';\" ) from user;" mysql | mysql | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}' | gzip > "$DB_BACKUP_DIR_TODAY/privileges_global_$DATESTRING.sql.gz"