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
-
Tags:
- Opsview
- Answerbot
- opsview
Comments
0 comments
Please sign in to leave a comment.