Page Comparison

...

The users .my.cnf file is setup 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.

Caveats

In some setups, e.g. with managed instances of MariaDB/MySQL it might not be possible to restore system databases. In local setup the option --all-databases is the way to go, for managed services the selective approach of --databases is usually better.

Identify the databases to back up

...

Out of these database, information_schema, performance_schema and mysql are system database.

You want to backup these if you also want to be able to recover the servers system configurationmay or may not be able to back those up. Especially with managed services, e.g. database permissions etc.Take note of all database you want to back upAWS 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

...

TIMESTAMP=$(date +"%Y_%m_%d_%H_%M_%S")
mysqldump --single-transaction --all-databases | gzip > "squirro_mysql_${TIMESTAMP}.sql.gz"

...

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 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

...

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
squirro_mysql_filtering_2022_06_24_16_09_13.sql.gz
squirro_mysql_fingerprint_2022_06_24_16_09_13.sql.gz
squirro_mysql_machinelearning_2022_06_24_16_09_13.sql.gz
squirro_mysql_plumber_2022_06_24_16_09_13.sql.gz
squirro_mysql_scheduler_2022_06_24_16_09_13.sql.gz
squirro_mysql_topic_2022_06_24_16_09_13.sql.gz
squirro_mysql_trends_2022_06_24_16_09_13.sql.gz
squirro_mysql_user_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 e.g only backups of the last 7 days

...

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.

...