Database Backup and Restore

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#

echo "show databases;" | mysql

Result with Squirro 3.5.5

information_schema
configuration
datasource
emailsender
filtering
fingerprint
machinelearning
mysql
performance_schema
plumber
scheduler
topic
trends
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.

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.

TIMESTAMP=$(date +"%Y_%m_%d_%H_%M_%S")
mysqldump --single-transaction --databases configuration datasource emailsender filtering fingerprint machinelearning plumber scheduler topic trends user    | gzip > "squirro_mysql_${TIMESTAMP}.sql.gz"

Individual Files, All Databases:#

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:

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:

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:

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:

squirro_stop

Restoring any of the above examples works like so:

gunzip <file>    | 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.

Contents