Why are my backups failing?

Symptoms Copied

• Backups seem to fail and affect all versions of Opsview

• Note, the following self-monitoring checks display the status of the latest backups:

  • Opsview - Backups Daily
  • Opsview - Backups Apply Changes

Cause: Change in MySQL 5.7.41 and 8.0.32 only Copied

Known issue OP-57646

This issue occurs because the MySQL 5.7.41 and 8.0.32 versions introduced an update requiring the RELOAD privilege when using mysqldump with the --single-transaction option, which is used in Opsview backups. However, the opsview user which runs the backups does not have this permission.

Running db_opsview db_backup will throw an error:

/opt/opsview/coreutils/bin/db_opsview db_backup > /dev/null
mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227)

This affects MySQL 5.7.41 (Ubuntu18) and MySQL 8.0.32 (Ubuntu20) client binaries on the orchestrator. MariaDB is not affected.

Solution: Upgrade MySQL Copied

Upgrade to MySQL 5.7.42 or above, or MySQL 8.0.33 or above.

If it is not possible to upgrade, you can temporarily work around it by adding these permissions below: Copied

To fix this, set the opsview user for the Opsview database to have RELOAD permission.

Login to MySQL as root:

GRANT RELOAD ON *.* TO 'opsview'@'%';
GRANT RELOAD ON *.* TO 'opsview'@localhost;

Take a snapshot/backup prior to doing these changes, as this is always a good idea, good luck.

To manually kick off a backup simply do the below:

/opt/opsview/coreutils/bin/db_opsview db_backup > /dev/null

/opt/opsview/coreutils/var/backups# /opt/opsview/coreutils/bin/rc.opsview backup
Backup finished successfully