Created
May 13, 2016 14:16
-
-
Save tacone/cac7002375d5d952af27c2e8706fd998 to your computer and use it in GitHub Desktop.
Backup all mysql tables
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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