SQL Backup
Abstract
My way of making SQL dumps and restore them if needed.
Initially designed to work on containerized environnement, the script "sql_dump_and_restore" should work in classical environments.
Goals
Dump and restore the databases of the most used SQL engines :
- MySQL (5 and 8)
- MariaDB (10)
- Postgres (10+)
Operating mode
Specify the expected variables and the script should do what you need.
Mandatories :
- DB_HOST : hostname or @IP of the database server
- DB_USER : user to use for the operation
- DB_PASSWORD : password of the choosen user
- DB_NAME : name of the database to dump or restore
- DB_TYPE : MySQL, MariaDB or PostgreSQL
- ACTION : Backup (default) of Restore
- BACKUP_DESTINATION (dump) or BACKUP_SOURCE (restore) : Local or S3
In case of backup in Local mode :
- BACKUP_PATH : folder where the dump will be stored
- DUMPFILE_NAME : name of the dump file (default is "dump-${DB_NAME}-$(date +%Y-%m-%d_%s).sql"
In case of restoration in Local mode :
- DUMPFILE_PATH : file path of the dump file to use
In case of S3 backup or restore :
- S3_PROVIDER : Minio only for the moment
- S3_ENDPOINT : URL of the S3 endpoint
- S3_USERNAME : username with the proper permissions to download or upload the dump
- S3_PASSWORD : password of the username
- S3_BUCKET : name of the bucket to use
- DUMPFILE_NAME : name of the dump to restore
Optionnals
- PORT : port number in case of non standard (3306 for MySQL and MariaDB and 5432 for PostgreSQL)
- GZIP : Yes (default) or No to know if we need to gzip the dumpfile after the dump or before the restore
- DB_NAME_DUMP : override the name of the database for the dump file (dump-${DB_NAME_DUMP}-$(date +%Y-%m-%d_%s).sql)
- S3_SUBPATH : path after the bucket if we want to store the dump in a subfolder or if the dump to restore is inside a subfolder
Examples
Check the examples folder to see some uses cases.