Automatically Dump MySQL Tables to Separate Files

The built-in mysqldump utility allows for selective table dumps, but requires pre-specified table names. For a dynamic approach that handles new table additions, an automated solution is necessary.

One such solution is a shell script that dynamically queries the database for all table names and dumps each table into a separate compressed file. This eliminates the need for manual table name maintenance in the dump script.

Here's a sample script that achieves this functionality:

#!/bin/bash

# Descr: Dump MySQL table data into separate SQL files for a specified database.
# Usage: Run without args for usage info.
# Author: @Trutane

[ $# -lt 3 ] && echo "Usage: $(basename $0) <DB_HOST> <DB_USER> <DB_NAME> [<DIR>]" && exit 1

DB_host=$1
DB_user=$2
DB=$3
DIR=${4:-.}

[ -d $DIR ] || mkdir -p $DIR

echo -n "DB password: "
read -s DB_pass

tbl_count=0

for t in $(mysql -NBA -h $DB_host -u $DB_user -p$DB_pass -D $DB -e 'show tables') 
do 
    echo "DUMPING TABLE: $DB.$t"
    mysqldump -h $DB_host -u $DB_user -p$DB_pass $DB $t | gzip > "$DIR/$DB.$t.sql.gz"
    tbl_count=$(( tbl_count   1 ))
done

echo "$tbl_count tables dumped from database '$DB' into dir=$DIR"

This script prompts for the database password, queries the specified database for table names, and dumps each table's data as SQL commands into individual compressed files within the specified directory (or the current working directory by default).