Opsview - Why are my backups failing?

Symptoms

• Backups seem to fail and affect all versions of Opsview
• 

Cause: No Permissions Given

Known issue OP-57691

This issue occurs when a DB has granted the Opsview user only the PROCESS privilege on all databases (*.*), and all privileges on the opsview and runtime databases. This means that the Opsview user does not have the necessary RELOAD or FLUSH_TABLES privileges required for the backup process to work.

Below are the differences you can see in the working server against the one not working:

################################## 
The working output:
##################################

MariaDB [(none)]> SHOW GRANTS FOR 'opsview'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for opsview@localhost |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO 'opsview'@'localhost' IDENTIFIED BY PASSWORD '*09090EFE8112563605D4A16979079CD4F36418E0' |
| GRANT ALL PRIVILEGES ON `runtime`.* TO 'opsview'@'localhost' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `opsview`.* TO 'opsview'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------+

################################## 
The one non-working server output:
##################################

mysql> SHOW GRANTS FOR 'opsview'@'localhost';

+--------------------------------------------------------------------------------+

| Grants for opsview@localhost |

+--------------------------------------------------------------------------------+

| GRANT PROCESS ON *.* TO `opsview`@`localhost` |

| GRANT ALL PRIVILEGES ON `opsview`.* TO `opsview`@`localhost` WITH GRANT OPTION |

| GRANT ALL PRIVILEGES ON `runtime`.* TO `opsview`@`localhost` WITH GRANT OPTION |

+--------------------------------------------------------------------------------+

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)

There is no sign that the backup has failed.

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

Solution: Grant Permissions

This has happened because MySQL 5.7.41 and 8.0.32, released in late January 2023, introduced a change to mysqldump which meant that --single-transaction needs to run a FLUSH TABLES at the beginning to get a consistent snapshot. However, this flush statement requires the RELOAD permission.

To fix, set the opsview user for the Opsview database to have the 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

If you have any issues

• Please contact our team via the chat service box available on any of our websites or via email to support@itrsgroup.com
  

Make sure you provide us with:

• ANY LOG FILE OR DIAGNOSTICS DATA
• ANY SCREENSHOT
• ANY VENDOR/VERSION etc