# Database Backup and Restore This page provides information on backing up and restoring the Squirro MySQL/MariaDB Database. ## Requirements The `.my.cnf` file is set up so that the mysql cli tools can connect to the databases server. If this is not given, then all commands need to be adjusted to provide the required information. ## Identify the Databases to Back Up ```bash echo "show databases;" | mysql ``` Result with Squirro 3.5.5 ```text information_schema configuration datasource emailsender filtering machinelearning mysql performance_schema plumber scheduler topic user ``` Out of these databases, `information_schema`, `performance_schema` and `mysql` are system databases. You may or may not be able to back those up. Especially with managed services such as AWS RDS, you might not get access to these. If you can’t back them up, you should create a SQL script that can recreate the required users & permissions as part of the recovery action. ## Backup ### Single File, All Databases Create a time-stamped, compressed backup of **all databases** into a single file. ```bash TIMESTAMP=$(date +"%Y_%m_%d_%H_%M_%S") mysqldump --single-transaction --all-databases | gzip > "squirro_mysql_${TIMESTAMP}.sql.gz" ``` ### Single File, All Squirro Databases Create a time-stamped, compressed backup of **all Squirro databases** into a single file. See the section above to ensure you get all databases, as this can differ across releases. ```bash TIMESTAMP=$(date +"%Y_%m_%d_%H_%M_%S") mysqldump --single-transaction --databases configuration datasource emailsender filtering machinelearning plumber scheduler topic user | gzip > "squirro_mysql_${TIMESTAMP}.sql.gz" ``` ### Individual Files, All Databases: ```bash TIMESTAMP=$(date +"%Y_%m_%d_%H_%M_%S") DATABASES=`echo 'SHOW DATABASES;' | mysql -N` for DATABASE in $DATABASES do echo "Backup ${DATABASE}" mysqldump --single-transaction --database "${DATABASE}" | gzip > "squirro_mysql_${DATABASE}_${TIMESTAMP}.sql.gz" done ``` The result is then a list of files like so: ```text squirro_mysql_configuration_2022_06_24_16_09_13.sql.gz squirro_mysql_datasource_2022_06_24_16_09_13.sql.gz squirro_mysql_emailsender_2022_06_24_16_09_13.sql.gz ... ``` ### Individual Files, All Squirro Databases Same as above, avoiding all system databases: ```bash TIMESTAMP=$(date +"%Y_%m_%d_%H_%M_%S") DATABASES=`echo 'SHOW DATABASES WHERE \`Database\` NOT IN ("information_schema", "mysql", "performance_schema", "sys")' | mysql -N` for DATABASE in $DATABASES do echo "Backup ${DATABASE}" mysqldump --single-transaction --database "${DATABASE}" | gzip > "squirro_mysql_${DATABASE}_${TIMESTAMP}.sql.gz" done ``` ## Cleaning Up Old Files If you don’t purge old files, the disk will eventually fill up. You can use this method to keep files for a specified time increment. For example, backups of the last 7 days as shown below: ```python find . -type f -mtime +7 -iname squirro_mysql\*.sql.gz -delete ``` ## Restore Before a restore can happen, all corresponding Squirro services must be stopped. Otherwise, the table locks will prevent the process from succeeding. The best course of action is to use: ```python squirro_stop ``` Restoring any of the above examples works like so: ```python gunzip | mysql ``` Note that the restore will of course first drop the database, then re-create it. Hence it might be advisable to take another backup, before doing a restore ## Storing the Backups safely A backup is no good if it’s lost with the server or disk that it’s on. Hence, we highly recommend moving the files off your Squirro server. In cloud environments, moving the files onto S3/S3 equivalent storage with versioning enabled will give you a lot of security. (e.g. using the AWS s3 `mv` command) We also recommend that you don’t leave the files sitting on the local disk, as the ability to inspect the files could give a bad actor a lot of information.