Skip to content

Instantly share code, notes, and snippets.

@tacone
Created May 13, 2016 14:16
Show Gist options
  • Save tacone/cac7002375d5d952af27c2e8706fd998 to your computer and use it in GitHub Desktop.
Save tacone/cac7002375d5d952af27c2e8706fd998 to your computer and use it in GitHub Desktop.
Backup all mysql tables
#!/bin/sh
#
# Adapted from: https://www.howtoforge.com/shell-script-to-back-up-all-mysql-databases-each-table-in-an-individual-file-and-upload-to-remote-ftp
#
# System + MySQL backup script
# Copyright (c) 2008 Marchost
# This script is licensed under GNU GPL version 2.0 or above
# ---------------------------------------------------------------------
#########################
######TO BE MODIFIED#####
### System Setup ###
BACKUP=/root/backups/mysql
### MySQL Setup ###
MUSER="root"
MPASS=""
MHOST="localhost"
######DO NOT MAKE MODIFICATION BELOW#####
#########################################
### Binaries ###
GZIP="$(which gzip)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
### Runtime variables ###
if [ -z "$MPASS" ]; then
PASSFLAG="";
else
PASSFLAG="-p$MPASS";
fi;
### Create hourly dir ###
mkdir -p $BACKUP
### Get all databases name ###
DBS="$($MYSQL -u $MUSER -h $MHOST $PASSFLAG -Bse 'show databases')"
for db in $DBS
do
### Create dir for each databases, backup tables in individual files ###
rm -f $BACKUP/$db/*.gz
mkdir -p $BACKUP/$db
for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST $PASSFLAG $db|grep -v Tables_in_`;
do
FILE=$BACKUP/$db/$i.sql.gz
echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST $PASSFLAG $db $i | $GZIP -9 > $FILE
done
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment