Error while restoring the data using backup

[rtsupport]

Hi,

While restoring the Nagios XI using backup we are getting below error..

ERROR 1010 (HY000) at line 22: Error dropping database (can't rmdir './nagios/', errno: 17)
Error restoring MySQL database 'nagios' - check the password in this script!

Backup taken on server using /usr/local/nagiosxi/scripts/backup_xi.sh
after that on same server using same backup created by above script when restoring it /usr/local/nagiosxi/scripts/restore_xi.sh /store/backups/nagiosxi/1529444444.tar.gz we are getting above error.

backup and restore procedure are done by root user.

Able to login in mysql using default pass.

Code: Select all

[nagios@usa0300lv725 ~]$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57492446
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

OS - Red Hat Enterprise Linux Server release 6.9 (Santiago)
Nagios XI - 2014R2.7

[lmiltchev]

Is the DB local or offloaded to a remote server? Can you run the following commands, and show the output?

Code: Select all

grep dbserver /usr/local/nagiosxi/html/config.inc.php
echo 'show databases;' | mysql -t -uroot -pnagiosxi
grep PASSWORD= /root/scripts/automysqlbackup
ls -lad /usr/local /usr/local/nagios
tail -20 /var/log/mysqld.log

Note: If the DB is offloaded, run:

Code: Select all

echo 'show databases;' | mysql -t -h <offloaded server's IP> -uroot -pnagiosxi

Make sure are you are root:

Code: Select all

whoami

then run:

Code: Select all

 bash -x /usr/local/nagiosxi/scripts/restore_xi.sh /store/backups/nagiosxi/1531749546.tar.gz | tee /tmp/debug.txt

and upload the "debug.txt" file that was generated in the "/tmp" directory by running the above command to the forum. Thank you!

[rtsupport]

DB is configured on same server and please find details below as requested.

Code: Select all

[root@usa tmp]# grep dbserver /usr/local/nagiosxi/html/config.inc.php
$cfg['dbserver'] = 'localhost'; // this setting is no longer used - use settings below
        "dbserver" => '',
        "dbserver" => 'localhost',
        "dbserver" => 'localhost',


[root@usa tmp]# echo 'show databases;' | mysql -t -uroot -pnagiosxi
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aivector           |
| mysql              |
| nagios             |
| nagiosql           |
| snmptt             |
| test               |
+--------------------+


[root@usa tmp]# grep PASSWORD= /root/scripts/automysqlbackup
PASSWORD=nagiosxi


[root@usa tmp]# ls -lad /usr/local /usr/local/nagios
drwxr-xr-x. 19 root root 4096 May 18  2015 /usr/local
drwxr-xr-x   9 root root 4096 May  4  2015 /usr/local/nagios


[root@usa0 tmp]# tail -20 /var/log/mysqld.log
InnoDB: Database physically writes the file full: wait...
150504 15:52:18  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
150504 15:52:18  InnoDB: Started; log sequence number 0 0
150504 15:52:18 [Note] Event Scheduler: Loaded 0 events
150504 15:52:18 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.73'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
150504 16:06:03 [Note] /usr/libexec/mysqld: Normal shutdown

150504 16:06:03 [Note] Event Scheduler: Purging the queue. 0 events
150504 16:06:05  InnoDB: Starting shutdown...
150504 16:06:09  InnoDB: Shutdown completed; log sequence number 0 44233
150504 16:06:09 [Note] /usr/libexec/mysqld: Shutdown complete

150504 16:06:09 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

debug file is also attached.

[lmiltchev]

[root@usa tmp]# echo 'show databases;' | mysql -t -uroot -pnagiosxi
+--------------------+
| Database |
+--------------------+
| information_schema |
| aivector |
| mysql |
| nagios |
| nagiosql |
| snmptt |
| test |
+--------------------+

It seems like you are missing the nagiosxi db... You would need to have it in order to restore...

What is the aivector db? This is not something that comes with XI.

Lastly, have you changed the password in the "restore_xi.sh" script?

Code: Select all

grep themysqlpass= /usr/local/nagiosxi/scripts/restore_xi.sh

[rtsupport]

ok, for missing DB what is the next step to restore it. and AI Vector was the vendor who architect the Nagios XI in our environment and they was the one who supporting us in terms on any Nagios issue.

for password in script nothing result is coming..
[nagios@usa03 ~]$ grep themysqlpass= /usr/local/nagiosxi/scripts/restore_xi.sh
[nagios@usa030 ~]$

have attached script as well for your reference.

[rtsupport]

just to updated, as earlier script was not containing mysql password in script, i have used another copy of ./restore script having mysqlpass in it but this time also i got the same error which was posted in first post.

Have attached that script as well.

[root@usa0 scripts]# grep themysqlpass= /usr/local/nagiosxi/scripts/restore_xi.sh
themysqlpass="nagiosxi"

[root@usa0 scripts]# echo 'show databases;' | mysql -t -uroot -pnagiosxi
+--------------------+
| Database |
+--------------------+
| information_schema |
| aivector |
| mysql |
| nagios |
| nagiosql |
| snmptt |
| test |
+--------------------+

[lmiltchev]

1. Try creating the nagiosxi db by running:

Code: Select all

mysql -u root -pnagiosxi
create database nagiosxi;
GRANT ALL ON nagiosxi.* TO nagiosxi@'<IP_OF_NAGIOS_XI_SERVER>' IDENTIFIED BY 'n@gweb';
quit;

where you substitute '<IP_OF_NAGIOS_XI_SERVER>' with the actual IP of your Nagios XI server, e.g. '192.168.0.10'.

2. Next, try restoring from the autobackup.

Code: Select all

/usr/local/nagiosxi/scripts/restore_xi.sh /store/backups/nagiosxi/1529444444.tar.gz

Note: If you are missing the nagiosxi db data (in the backup you are restoring from that is), you can try restoring the nagiosxi db from daily backups (if you have any).

Example:

Code: Select all

cd /store/backups/mysql/daily/nagiosxi
gunzip xxx.sql.gz
mysql -u root -p'nagiosxi' nagiosxi < xxx.sql

where you substitute "xxx" with the actual filename.

Let us know if this helped.

[rtsupport]

database create but while restoring the data we are still getting error.

Code: Select all

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aivector           |
| mysql              |
| nagios             |
| nagiosql           |
| nagiosxi           |
| snmptt             |
| test               |
+--------------------+
8 rows in set (0.00 sec)

Code: Select all

[root@usa03 scripts]# ./restore_xi.sh /store/backups/nagiosxi/1531920216.tar.gz
TS=1532003376
Extracting backup to /store/backups/nagiosxi/1532003376-restore...
In /store/backups/nagiosxi/1532003376-restore/1531920216...
Backup files look okay.  Preparing to restore...
Shutting down services...
Stopping nagios:/etc/init.d/nagios: line 150: kill: (17529) - No such process
 done.
ndo2db was not running... could not stop
NPCD was not running.
Restoring directories to /...
Restoring Nagios Core...
rm: cannot remove `/usr/local/nagios/share/perfdata': Device or resource busy
Restoring Nagios XI...
Restoring NagiosQL...
Restoring NagiosQL backups...
Restoring NRDP backups...
Restoring MRTG...
Restoring MySQL databases...
ERROR 1010 (HY000) at line 22: Error dropping database (can't rmdir './nagios/', errno: 17)
Error restoring MySQL database 'nagios' - check the password in this script!

checked daily backup but dont have nagiosxi on it.

Code: Select all

[root@usa0 daily]# cd /store/backups/mysql/daily/
[root@usa0300lv725 daily]# ll
total 24
drwxr-xr-x 2 root root 4096 Jul 19 07:00 aivector
drwxr-xr-x 2 root root 4096 Jul 19 07:00 mysql
drwxr-xr-x 2 root root 4096 Jul 19 07:00 nagios
drwxr-xr-x 2 root root 4096 Jul 19 07:00 nagiosql
drwxr-xr-x 2 root root 4096 Jul 19 07:00 snmptt
drwxr-xr-x 2 root root 4096 Jul 19 07:00 test

[lmiltchev]

Try moving the items in the /var/lib/mysql/nagios/ directory to a different location:

Code: Select all

mkdir /tmp/nagios
mv /var/lib/mysql/nagios/* /tmp/nagios

then try to restore again. Did it work this time?

[rtsupport]

Hi lmiltchev,

Sorry for delay in response and thanks for your last suggestion which resolved our issue.

After removing/MV files from "mv /var/lib/mysql/nagios/* /tmp/nagios" we are able to restore the data. As we were trying with multiple attempts due to which it took long to reply you back.

Thank you! again.