Configure dedicated database server

I have setup test environment with 2 Master’s writing to their local databases and had load balanced between them using HAproxy. I can see on icinga web interface that the active endpoint changes when one of the master is down. So far its good but now I want both the master to write or use one dedicated database server but when i update the ido-mysql.conf with host as database name and enable_ha to true i could see an error in the web interface while i had changed the host in resource as well…this is the error i see
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘icinga2.icinga_dbversion’ doesn’t exist, query was: SELECT icinga_dbversion.version FROM icinga_dbversion
SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘icinga2.icinga_dbversion’ doesn’t exist
Zend_Db_Statement_Exception in /usr/share/icingaweb2/library/vendor/Zend/Db/Statement/Pdo.php:225 with message: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘icinga2.icinga_dbversion’ doesn’t exist, query was: SELECT icinga_dbversion.version FROM icinga_dbversion

So, you started with two separate masters, each with their own local database,
and now you want them to use a single shared database.

How have you created that single shared database? Have you simply pointed one
of the existing masters at the other one’s database?

Have you combined the two database servers into a cluster or implemented
master-master replication mode?

Have you created a new database server and pointed both Icinga masters at that
new database?

Did you follow any specific documentation about setting up Icinga2 HA?

Thanks,

Antony.

@Pooh

I created a server which is running mariadb and I have created user (2 masters)which can connect to the database server using mysql -h xxxxxxx -u icinga2 -p from respective master node

Initially I combined but due to some reason i had deleted those and now currently i have a single database but i will form a cluster after some times.

yes i have pointed those masters to new database by updating the ido-mysql conf file

So, you set up a new database server, and pointed the two Icinga2 masters at
that.

How did you set up the database tables on the new server?

Antony.

@Pooh

Database |
±-------------------+
| icinga2 |
| information_schema |
| mysql |
| performance_schema |

created a database with name icinga2 with grant privileges and also created user icinga2 for both masters with grant privileges

I asked “How did you set up the database tables on the new server?”

You said “created a database with name icinga2 with grant privileges and also
created user icinga2 for both masters with grant privileges”.

That tells me you have created a database, but did you create any tables in
that database?

I think you need to apply a schema definition to create the tables which Icinga
expects to find - it’s not just going to create them for you.

The simplest method might be to do a dump from one of your current working
databases and feed that into the new server.

Somebody else here might be able to point to the documentation on creating the
database schema manually (it’s normally done as part of a packaged
installation).

Antony.

@Pooh
yeah your right…I misunderstood your question…I don’t see any tables at the moment…
I tried to mysql -u root -p icinga2 </usr/share/icinga2/schema/mysql/schema.sql but this didn’t work for me…may be i am doing something wrong