I’m not sure I understood but icingaweb2 needs to be installed on both masters and points to it’s local DB but I’m not sure, how you need to resolve the split brain scenario of the two DBs.
Ok to make it easier…can you guide me with setting up just 1 database for both masters. In this case i have galeratest01 as a database.
You need to update the connection information in /etc/icinga2/features-enabled/ido-mysql.conf
and set enable_ha to true on both masters and than a icinga2 service restart is needed.
Have a look at Object Types - Icinga 2 and Distributed Monitoring - Icinga 2
yes i have updated the ido-mysql.conf with ip of database server and enable_ha to true and restarted also…
@rivad @log1c
Can you guide me with schema definition to create the tables on how i can dump from one of the current working databases and feed that into the new server. or if there is any documentation on creating the database schema manually?
Thanks
You export a database including its schema with:
mysqldump --flush-privileges --routines --single-transaction --triggers -u < username > -p >dbname.dump
And then create an empty database (previously granted rights were keep):
mysql -u < username > -p -e “CREATE DATABASE < dbname >”
Finally, this will create schema and database:
mysql -u < username > -p <dbname.dump
Hi @rsx
Where should this commands to be run? is it on icinga master or the database server?
I tried on both but none worked…it says unknown database when i run the mysqldump query
you need to run the dump command on the server where your existing database is.
Please show the output of show databases on the existing db server as well as the command you have run.
| Database |
±-------------------+
| icinga2 |
| icingadb |
| information_schema |
| mysql |
| performance_schema
– MySQL dump 10.19 Distrib 10.3.36-MariaDB, for debian-linux-gnu (x86_64)
– Host: localhost Database: dbname.dump
– Server version 10.3.36-MariaDB-0+deb10u2
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8mb4 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE=‘+00:00’ /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Got error: 1049: “Unknown database ‘dbname.dump’” when selecting the database
I think this should be:
mysqldump --flush-privileges --routines --single-transaction --triggers -u USERNAME -p DBNAME > DBNAME.dump
Please consult man mysqldump for the right syntax and options.
USERNAME and DBNAME needs to be replaced with actual values.
1 Like
@rivad
Thanks…this command worked but i don’t see anything under the database…
mysqldump --flush-privileges --routines --single-transaction --triggers -u root -p icingadb > icingadb.dump
this is what i ran and it didn’t threw any error…also icingadb database is created …
Did it result in SQL statements to recreate the database in icingadb.dump? Check with less icingadb.dump - it can be large!
icingadb is not the correct database when you are still unsing the IDO MySQL feature and database schema. I guess that would be your icinga2 named database.
IcingaDB is the new default database setup for Icinga2.
Older setups will still use the IDO DB, as the productive go-live for IcingaDB was just 2-3 months ago.
Docs about the IDO DB:
https://icinga.com/docs/icinga-2/latest/doc/14-features/#ido-database-db-ido
– MySQL dump 10.19 Distrib 10.3.36-MariaDB, for debian-linux-gnu (x86_64)
– Host: localhost Database: icinga2
– Server version 10.3.36-MariaDB-0+deb10u2
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8mb4 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE=‘+00:00’ /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO’ /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
–
– Dumping routines for database ‘icinga2’
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE /;
/!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS /;
/!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS /;
/!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION /;
/!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
– Dump completed on 2022-10-21 12:47:02
I tried with icinga2 and you can see the less icinga2.dump
So i would have to install icinga2-ido-mysql on database server as well???
No.
This is only needed on the Icinga master(s), which are connecting to the database.
After having created the dump, copy the resulting file to the new system.
Login to the database, create a new empty database and then import the dump into the database.
After that change the ido-mysql.conf to your new database server and restart icinga2. To reflect the changes in the webinterface change the corresponding resource used in the “Monitoring backend” of the monitoring module: https://yourmonitoring.domain.whatever/config/modules#!/monitoring/config
example:
@log1c
Thanks ,.these steps helped me finally have single database for 2 master’s…Now the next challenge i have is to integrate the new master server to the already running master server and all this via puppet.
The setup of a second master and how to add it into the cluster is described in detail in the docs:
Distributed Monitoring - Icinga 2
How you then “translate” it in to Puppet, I don’t know. Tbh I’m not sure why you would want to do that via Puppet. This should be a one-time task.
I don’t use Puppet but Ansible but the benefit is the same. I documented all the steps in “code” and if SHTF you can rebuild very quickly. One more benefit of Puppet is that it will fight with manual changes on the target and can thus “heal” config errors.
@rivad
Can you share document please. Also the initial node wizard setup should be done manually or even this will be managed by puppet or ansible? I want a clarity on what steps should be done manually