Nagios Xi restore from Backups

[Johnsmit]

Hi,

i have Nagios XI running on RHEL 7.3 Linux server where DB is offloaded to a remote server. As a part of DR plan we are building secondary server Nagios Xi installed and recovered with the backups of primary server. But i ran in to multiple errors.

before running script secondary server database has been moved to a remote server.


/usr/local/nagiosxi/scripts/restore_xi.sh /tmp/nagiosxi.1541790002.tar.gz
TS=1541790371
Extracting backup to /store/backups/nagiosxi/1541790371-restore...
In /store/backups/nagiosxi/1541790371-restore/nagiosxi.1541790002...
Backup files look okay. Preparing to restore...
Shutting down services...
Restoring directories to /...
Restoring Nagios Core...
Restoring Nagios XI...
Restoring NRDP backups...
Restoring MRTG...
Restoring Nagvis backups...
Restoring MySQL databases...
ERROR 1130 (HY000): Host 'secondary server' is not allowed to connect to this MariaDB server
Error restoring MySQL database 'nagios' - check the password in this script!
Then changed password in the script.

Ran the restore script but ran i to same error again.

what is the sequence to be followed to restore server if database resides on a remote server?

Thanks,

[npolovenko]

Hello, @Johnsmit. Are you trying to have the secondary XI server to use the same offloaded Database as the original XI? That's not a good idea. If two XI servers start communicating with the same DB that may cause corruption and other various interferences.
If you're planning on having two XI servers to be online on the same network at the same time consider creating another offloaded DB for the second XI. You'd need to use the same user credentials and passwords as the original XI. Then in the restore script, you'd override/hardcode the target offloaded DB address.
On top of that, you'd need to run the following commands on the new DB server to grant access to the secondary XI server:

GRANT ALL ON nagios.* TO nagios@'<IP_OF_NAGIOS_XI_SERVER>' IDENTIFIED BY 'nagios';
GRANT ALL ON nagiosql.* TO nagiosql@'<IP_OF_NAGIOS_XI_SERVER>' IDENTIFIED BY 'nagiosql';
GRANT ALL ON nagiosxi.* TO nagiosxi@'<IP_OF_NAGIOS_XI_SERVER>' IDENTIFIED BY 'nagiosxi';

What version of XI are you running?

[Johnsmit]

HI,

currently we had a plan to have 2 nagios xi 5.5.6 servers online with 2 offloaded databases. I have used the same credentials for secondary servers that i used on primary server. I have hardcoded Offloaded DB server address on secondary server. i gave DB permissions you specified while offloading database.

Currently i am using Nagios 5.5.6.

I followed Offloading database to a remote server and restore XI from backups documents

steps i did till now:
primary site:
Installed Nagios Xi on primary app server.
Offloaded database to primary database server.
ran backups and moved to secondary app server.

Secondary site:
Installed Nagios Xi on secondary app server.
Offloaded database to secondary database server.
tried to restore nagiosxi from primary backups
but ran into errors, verified restore script and changed target DB server and ran script again but still no use.

I ran database repair script and saw couple of errors.
/usr/local/nagiosxi/scripts/repair_databases.sh
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
DATABASE: nagios
TABLE:
mysqlcheck: Got error: 1130: Host 'secondary app server' is not allowed to connect to this MariaDB server when trying to connect
Issued remote command 'mysqlcheck -f -r -u nagios -pnagios -h primaryt DB server --port=3306 --databases nagios'
SOMETHING WENT WRONG. ATTEMPT MANUAL REPAIR
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
DATABASE: nagiosql
TABLE:
mysqlcheck: Got error: 1130: Host 'secondary app server' is not allowed to connect to this MariaDB server when trying to connect
Issued remote command 'mysqlcheck -f -r -u nagiosql -pnagiosql -h Primary DB Server --port=3306 --databases nagiosql'
SOMETHING WENT WRONG. ATTEMPT MANUAL REPAIR
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
DATABASE: nagiosxi
TABLE:
mysqlcheck: Got error: 1130: Host 'secondary app server' is not allowed to connect to this MariaDB server when trying to connect
Issued remote command 'mysqlcheck -f -r -u nagiosxi -pnagiosxi -h Primary DB Server --port=3306 --databases nagiosxi'
SOMETHING WENT WRONG. ATTEMPT MANUAL REPAIR
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory

=======================
nagios database repair FAILED, please check output above!
nagiosql database repair FAILED, please check output above!
nagiosxi database repair FAILED, please check output above!
I am not sure how the primary database ip address came in to place.

[npolovenko]

@Johnsmit, Please revert the restore script to its original state. Then open the backup archive that you're trying to restore from and navigate to nagiosxi.tar.gz -> nagiosxi.tar -> usr -> local -> nagiosxi -> html -> 'open the config.inc.php file and change the IP address of the DB'. Save the archive and try restoring from it. Let me know if it works for you!

[Johnsmit]

@Johnsmit, Please revert the restore script to its original state. Then open the backup archive that you're trying to restore from and navigate to nagiosxi.tar.gz -> nagiosxi.tar -> usr -> local -> nagiosxi -> html -> 'open the config.inc.php file and change the IP address of the DB'. Save the archive and try restoring from it. Let me know if it works for you!

i did everything you said, changed the IP address in the backup archive. Firstly, i unzipped the tarball and edited the files and then zipped it again and tried to restore from backups, but i ran into errors saying the zip file is corrupted.
Thanks.

[npolovenko]

@Johnsmit, I used 7-zip to open the config.inc.php file in a text editor without extracting it from the archive. Then I saved the file and it asked me if I want to modify the archive. I answered yes, and the program changed the archive on the fly. Then I was able to restore from that backup just fine.
https://www.7-zip.org/

[Johnsmit]

@Johnsmit, I used 7-zip to open the config.inc.php file in a text editor without extracting it from the archive. Then I saved the file and it asked me if I want to modify the archive. I answered yes, and the program changed the archive on the fly. Then I was able to restore from that backup just fine.
https://www.7-zip.org/

Hi i have done everything you said but still i ended up with the errors in the beginning.

/usr/local/nagiosxi/scripts/restore_xi.sh /tmp/nagiosxi.1541790002.tar.gz
TS=1542234548
Extracting backup to /store/backups/nagiosxi/1542234548-restore...
In /store/backups/nagiosxi/1542234548-restore/nagiosxi.1541790002...
Backup files look okay. Preparing to restore...
Shutting down services...
Restoring directories to /...
Restoring Nagios Core...
Restoring Nagios XI...
Restoring NRDP backups...
Restoring MRTG...
Restoring Nagvis backups...
Restoring MySQL databases...
ERROR 1045 (28000): Access denied for user 'root'@'server_name' (using password: YES)
Error restoring MySQL database 'nagios' - check the password in this script!
[root@servername tmp]# vi /usr/local/nagiosxi/scripts/restore_xi.sh

I checked the password in the script. its correct and i can login to Db server database from App server remotely with that password.I checked all the passwords, ip addresses in config.inc.php, ndo2b.cfg, restore_xi.sh scripts. everything is good. But still am running into error.

Thanks,

[npolovenko]

@Johnsmit, From the new servers command line please run the following commands. Replace the <IP_ADDRESS_OF_MYSQL_OR_MARIADB_SERVER> with the new offloaded DB IP address. Let me know if all three commands work for you.

echo 'STATUS;' | mysql -u nagios -p'nagios' -h <IP_ADDRESS_OF_MYSQL_OR_MARIADB_SERVER>
echo 'STATUS;' | mysql -u nagiosql -p'nagiosql' -h <IP_ADDRESS_OF_MYSQL_OR_MARIADB_SERVER>
echo 'STATUS;' | mysql -u nagiosxi -p'nagiosxi' -h <IP_ADDRESS_OF_MYSQL_OR_MARIADB _SERVER>

[Johnsmit]

@Johnsmit, From the new servers command line please run the following commands. Replace the <IP_ADDRESS_OF_MYSQL_OR_MARIADB_SERVER> with the new offloaded DB IP address. Let me know if all three commands work for you.

echo 'STATUS;' | mysql -u nagios -p'nagios' -h <IP_ADDRESS_OF_MYSQL_OR_MARIADB_SERVER>
echo 'STATUS;' | mysql -u nagiosql -p'nagiosql' -h <IP_ADDRESS_OF_MYSQL_OR_MARIADB_SERVER>
echo 'STATUS;' | mysql -u nagiosxi -p'nagiosxi' -h <IP_ADDRESS_OF_MYSQL_OR_MARIADB _SERVER>

Please see the below results.

echo 'STATUS;' | mysql -u nagios -p'nagios' -h <IP_ADDRESS_OF_MYSQL_OR_MARIADB_SERVER>
--------------
mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 46061
Current database:
Current user: nagios@APP_Server
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.52-MariaDB MariaDB Server
Protocol version: 10
Connection: DB Server_IP via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 6 days 1 hour 44 min 27 sec

Threads: 1 Questions: 936019 Slow queries: 0 Opens: 4423 Flush tables: 2 Open tables: 229 Queries per second avg: 1.784
-------------

echo 'STATUS;' | mysql -u nagiosql -p'nagiosql' -h <IP_ADDRESS_OF_MYSQL_OR_MARIADB_SERVER>
--------------
mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 46062
Current database:
Current user: nagiosql@APP_server
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.52-MariaDB MariaDB Server
Protocol version: 10
Connection: DB_Server_IP via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 6 days 1 hour 46 min 36 sec

Threads: 1 Questions: 936023 Slow queries: 0 Opens: 4423 Flush tables: 2 Open tables: 229 Queries per second avg: 1.783
--------------

echo 'STATUS;' | mysql -u nagiosxi -p'nagiosxi' -h <IP_ADDRESS_OF_MYSQL_OR_MARIADB _SERVER>
--------------
mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1

Connection id: 46063
Current database:
Current user: nagiosxi@APP_server
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.52-MariaDB MariaDB Server
Protocol version: 10
Connection: DB_Server_IP via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 6 days 1 hour 47 min 40 sec

Threads: 1 Questions: 936027 Slow queries: 0 Opens: 4423 Flush tables: 2 Open tables: 229 Queries per second avg: 1.783
--------------

Thanks,

[npolovenko]

@Johnsmit, I'm going to create a custom restore script for you. Please send me the new offloaded DB IP address, user and password for each database -> nagios, nagiosql, nagiosxi. You can send it in a private message. I will hardcode these values in the restore_xi.sh script and send it to you.