Skip to content

Instantly share code, notes, and snippets.

@sfinktah
Created September 14, 2023 04:17
Show Gist options
  • Save sfinktah/3b1ae7c51424490ea76940c976bf54c1 to your computer and use it in GitHub Desktop.
Save sfinktah/3b1ae7c51424490ea76940c976bf54c1 to your computer and use it in GitHub Desktop.
modular mysql backup & restore
#!/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
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment