Dumping drupal DB without cache tables

If you want to dump DB from production or develop servers and not include cache table values for faster importing you can use below script. You need to change site_name variable and DUMPDIR to match what you have and also user/password of DB

#!/bin/bash

DATABASE="$1"

if [ -z "$DATABASE" ]; then
    echo "Missing database as first argument."
    echo "$0 DBNAME DESTDIR"
    exit 1
fi

echo "Building table ignore list..."
for TABLE in $(mysql -u root --password="some_pass" -Bsce "use $DATABASE; show tables;" | grep "^cache"); do
    IGNORED_TABLES="$IGNORED_TABLES --ignore-table=$DATABASE.$TABLE"
done
echo "Done."

echo "Building table definition only list..."
for TABLE in $(mysql -u root --password="some_pass" -Bsce "use $DATABASE; show tables;" | grep "^cache"); do
        DEFINITION_TABLES="$DEFINITION_TABLES $TABLE"
done
echo "Done."

DUMPDIR="$2"

if [ -z "$DUMPDIR" ]; then
    case "$DATABASE" in
        site_name*)
            DUMPDIR="/var/www/html-dev/site_name/bup"
        ;;
        site_name*)
            DUMPDIR="/var/www/html-dev/site_name/bup"
        ;;
        *)
            echo "Could not determine a destination directory."
            exit 1
    esac
fi

if [ ! -d "$DUMPDIR" ]; then
    echo "Non-existing destination directory $DUMPDIR"
    exit 1
fi

FILENAME="$DUMPDIR/$1.sql.gz"
TMPFILENAME=$(mktemp)

echo "Dumping DB $DATABASE to $FILENAME file..."
(mysqldump -u root --password="some_pass" --set-gtid-purged=OFF --single-transaction --quick "$DATABASE" $IGNORED_TABLES; mysqldump -u root --password="some_pass" --set-gtid-purged=OFF --single-transaction --quick --no-data "$DATABASE" $DEFINITION_TABLES) | mbuffer | nice gzip -c > "$TMPFILENAME"
mv "$TMPFILENAME" "$FILENAME"
touch "$FILENAME"
chown git:git "$FILENAME" >/dev/null 2>&1
chmod ugo+r "$FILENAME"
rm -f "$TMPFILENAME"
echo "Done."

exit 0