modular mysql backup & restore

mysql-restore-tables.sh

#!/usr/bin/env bash

## For speedy use:
## mysql-restore-tables *.schema.*
## parallel -j 8 mysql-restore-table *.data.*

# MySQL server credentials
DB_USER="your_username"
DB_PASS="your_password"
DB_HOST="localhost"
DB_NAME=fluffyd_turn14

# Function to restore a MySQL dump file
restore_dump() {
    local dump_file="$1"

    if [[ "$dump_file" == *.gz ]]; then
        echo -n "$dump_file..."
        # Use zcat to decompress gzipped file and pipe it to mysql
        zcat "$dump_file" | mysql -u"$DB_USER" -p"$DB_PASS" -h"$DB_HOST" "$DB_NAME"
        # zcat "$dump_file" | sudo mysql "$DB_NAME"
        echo "$dump_file restored."
    else
        echo -n "$dump_file..."
        # Use mysql directly for non-gzipped files
        mysql -u"$DB_USER" -p"$DB_PASS" -h"$DB_HOST" "$DB_NAME" < "$dump_file"
        # sudo mysql "$DB_NAME" < "$dump_file"
        echo "$dump_file restored."
    fi
}

for fn in "${@}"; do
        restore_dump "$fn"
done

mysql-table-dump.sh

#!/usr/bin/env bash

DatabaseType='mysql'
Hostname='localhost'
DatabaseName='fluffyd_turn14'
Username='your_username'
Password='your_password'

MysqlBatchExecute() {
    $DatabaseType -h "$Hostname" -u "$Username" -p"$Password" --batch --execute "$@"
}

MysqlDump() {
    ${DatabaseType}dump -h "$Hostname" -u "$Username" -p"$Password" --compress "$@"
}


timestamp=$( date +"%y-%m-%d-%H.%M" )
backupdir="mysql-backup-$timestamp"
pushd .
mkdir -p "$backupdir" && cd "$backupdir" && {
    for db in $( MysqlBatchExecute "show databases" ); do
        echo "Database: $db"
        pushd .
        mkdir -p "$db" && cd "$db"
        for table in $( MysqlBatchExecute "show tables" $db ); do
           echo "Backing up $db.$table"
           MysqlDump -d "$db" "$table" | gzip > "$db.$table.$$.schema.sql"
           MysqlDump -t "$db" "$table" | gzip > "$db.$table.$$.data.sql.gz"
        done
        popd
    done
    popd
    # rsync/gzip/ftp "$backupdir/"
}


# Remove backupss
# rm *.$$.sql.gz