Backup all mysql tables

mysql-backup-tables.sh

#!/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