Offloading MySQL to Remote DB server

[jacek]

Hi,

I've migrated the 3 DB's (nagios, nagiosql, nagiosxi) to our DB server according to this instruction:
https://assets.nagios.com/downloads/nag ... Server.pdf

At this step:
d) Restart Nagios and ndo2d
instead of Nagios XI normal logging page I get the following installation screen and if I try to create the admin account SQL problems pop up on the top of the page:

screen.png

When I open up the MySQL console and lookup the nagiosql DB there are no "xi_" starting tables. The "xi_" named tables are in the nagiosxi DB.

What is wrong? I went exactly as the instruction says changing only the address, user name and password (one, the same for all 3 DB's).
This is a freshly (last week) deployed NagiosXI server on the Amazon's AWS.

[lmiltchev]

Run the following command on the Nagios XI server, and show the output:

Code: Select all

echo 'select now();'|mysql -t -u root -pnagiosxi -h <remote server>

where you substitute the "<remote server>" with the IP address of the server with the offloaded db.

Also, run:

Code: Select all

echo 'show tables;'|mysql -t -u root -pnagiosxi nagios -h <remote server> > /tmp/nagios.txt
echo 'show tables;'|mysql -t -u root -pnagiosxi nagiosql -h <remote server> > /tmp/nagiosql.txt
echo 'show tables;'|mysql -t -u root -pnagiosxi nagiosxi -h <remote server> > /tmp/nagiosxi.txt

and post the nagios.txt, nagiosql.txt, and nagiosxi.txt files that were created in the /tmp directory.

[jacek]

Hi,

As You wrote I had already all DB's purged.
I reverted the config files to the backups I created and it worked without problems, so it seems that nothing happened to my original DB's.

So I re-did all the steps and the effect is pretty much the same - the Nagios XI installation page pops-up already with the SQL errors all around - screen attached, before I needed to click that I want to create the user to get the errors from the post before.

Capture.PNG

Please find attached 2 files, one with the time (it seems to be correct):

time.txt

and the 3 DB tables dump in zip (3 attachment's limit):

DBs.zip

[lmiltchev]

I am not sure how the "xi_incidents" table ended up in the "nagiosql" database... Do you have it in the "local" db?

Run:

Code: Select all

echo 'show tables;'|mysql -t -u root -pnagiosxi nagiosql  > /tmp/nagiosql_local.txt

and show the "nagiosql_local.txt" file.

Do you see any errors/crashed tables in the mysqld.log on the remote server with the offloaded mysql db?

Code: Select all

tail -50 /var/log/mysqld.log

If opening a new ticket in our email ticketing system is an option for you, send the profile.zip to [email protected].

Admin->System Profile->Download Profile

[jacek]

Attaching the local DB tables list.

nagiosql_local.txt

The xi_incidents table pops up by itself, even if I delete it - I have no access to the shell of the remote DB server, so I asked the admin to look into it.
But personally I think this is not the problem.

I'm worried that we are looking in the wrong place, the error is saying:

Code: Select all

SQL: SQL Error [nagiosxi] : Table 'nagiosql.xi_users' doesn't exist

I understand, that Nagios is trying to look in the nagiosxi DataBase for xi_users table but is failing due to the nagiosql prefix (nagiosql.xi_users) which is forcing the SQL to look in the nagiosql DB instead of nagiosxi.
The same applies to the nagiosql.xi_usermeta table...
Am I right here?

I will send an e-mail to the nagios customer support today, but I need to revert the changes first, to get the system working so I can export the profile first

[rkennedy]

I see we have received a ticket for this, going to lock this thread up so we can continue there.