Skip to content

Instantly share code, notes, and snippets.

@nickjj
Last active December 27, 2023 07:16
Show Gist options
  • Save nickjj/00b07e522caee02e37951ec6de2a9c95 to your computer and use it in GitHub Desktop.
Save nickjj/00b07e522caee02e37951ec6de2a9c95 to your computer and use it in GitHub Desktop.
An example of creating a database backup and restore script for MySQL, MariaDB and PostgreSQL. It is focused on backing up and restoring a Sendy database (working example from https://www.youtube.com/watch?v=kbCytSYPh0E) but you can apply it for any database with slight modifications.
30 0 * * * root /usr/local/bin/sendy-backup > /dev/null 2>&1
0 0 1 * * root find /var/lib/sendy-backup -type f -mtime +90 -delete > /dev/null 2>&1
#!/usr/bin/env bash
# Backup your local Sendy database to a compressed file.
set -e
set -o pipefail
sendy_config="/var/www/html/includes/config.php"
sendy_db_user="$(grep -o "dbUser = '[^']*[^']'" "${sendy_config}" | cut -d "=" -f 2 | sed "s/'//g;s/ //g")"
sendy_db_password="$(grep -o "dbPass = '[^']*[^']'" "${sendy_config}" | cut -d "=" -f 2 | sed "s/'//g;s/ //g")"
sendy_db_name="$(grep -o "dbName = '[^']*[^']'" "${sendy_config}" | cut -d "=" -f 2 | sed "s/'//g;s/ //g")"
# MySQL / MariaDB.
/usr/bin/mysqldump -u "${sendy_db_user}" \
-p"${sendy_db_password}" \
"${sendy_db_name}" \
| gzip > "/var/lib/sendy-backup/$(date +%F)_sendy.sql.gz"
# PostgreSQL (not using Sendy's values since it's just an example).
#
# Option 1: Use the .pgpass file
# - sudo touch /root/.pgpass && sudo chmod 600 /root/.pgpass
# - Add your DB connection info on line 1 of that file like this:
# localhost:5432:mydatabase:foouser:mysecurepassword
# - Use cut to parse out your user / host / db to avoid duplication when calling pg_dump
# - When you connect with pg_dump below it will find a matching connection and use that pw
#
# Option 2: Use the PGPASSWORD env variable in this script
# - Add this line right before calling pg_dump below:
# export PGPASSWORD=mysecurepassword
# - Feel free to parse your password out from a different file to avoid hard coding it
#
# Option 2 is a bit less secure but in a controlled environment it's not unreasonable.
# /usr/bin/pg_dump -Fc -U foouser -h localhost -d mydatabase > mydatabase.sql.dump
#!/usr/bin/env bash
# Restore your Sendy database.
set -e
set -o pipefail
backup_path="/var/lib/sendy-backup"
file_name="${1}"
restore_path="${backup_path}/${file_name}"
sendy_config="/var/www/html/includes/config.php"
sendy_db_user="$(grep -o "dbUser = '[^']*[^']'" "${sendy_config}" | cut -d "=" -f 2 | sed "s/'//g;s/ //g")"
sendy_db_password="$(grep -o "dbPass = '[^']*[^']'" "${sendy_config}" | cut -d "=" -f 2 | sed "s/'//g;s/ //g")"
sendy_db_name="$(grep -o "dbName = '[^']*[^']'" "${sendy_config}" | cut -d "=" -f 2 | sed "s/'//g;s/ //g")"
if [ -z "${file_name}" ]; then
echo "You must supply a file name, example: ${0} $(date +%F)_sendy.sql.gz"
exit 1
fi
if [ ! -f "${restore_path}" ]; then
echo "'${restore_path}' not found"
exit 1
fi
read -p "Restoring is going to wipe your current database, are you sure (y/n)? " -n 1 -r
echo
if [[ ! "${REPLY}" =~ ^[yY]$ ]]; then
echo "The '${sendy_db_name}' database was not restored because you didn't type y or Y."
exit 1
fi
# MySQL / MariaDB.
zcat "${restore_path}" | mysql -u "${sendy_db_user}" -p"${sendy_db_password}" "${sendy_db_name}"
# PostgreSQL (not using Sendy's values since it's just an example).
#
# Repeat either option 1 or option 2 from the backup script.
# psql -U foouser -h localhost -d mydatabase < mydatabase.sql.dump
echo "The '${sendy_db_name}' database was successfully restored from '${file_name}'"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment