Backup Failure

[nms]

Hi,

Starting from today, I observed that the full Nagios XI backup failed with the following error:

Code: Select all

Backing up MySQL databases...
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `nagios_logentries` at row: 10108915

I re-tried to run the script "backup_xi.sh" but I received a similar error:

Code: Select all

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `nagios_statehistory` at row: 446596

Note that MySQL is not offloaded to another server.

I also observed the Nagios XI Jobs is reporting:

Code: Select all

Database Maintenance (dbmaint) stale (2260 seconds old), Database Maintenance (dbmaint) stale (2260 seconds old)

Is there anything I can try to solve this issue?

With Thanks,

Matthew

[jomann]

Those seem like fairly large tables, it's possible that it is taking long enough to hit a timeout in MySQL. In the MySQL config (should be somewhere like /etc/my.cnf) under the [mysqld] section, you should be able to use the connect_timeout option. More information here: https://dev.mysql.com/doc/refman/5.7/en ... ct_timeout

[nms]

Hi,

i tried to include the timeout in my.cnf file

Code: Select all

[mysqld]
connect_timeout=300
query_cache_size=16M
query_cache_limit=4M
tmp_table_size=64M
max_heap_table_size=64M
key_buffer_size=32M
table_open_cache=32
bind-address=10.192.254.55

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Restarted mysqld, ran the backup but got the same issue. Are there any recommended settings I can include here?

Rgds,

[nms]

I have now modified my.cnf file again with the following entries (first four entries):

Code: Select all

[mysqld]
connect_timeout=300
max_allowed_packet=1024M
net_read_timeout=7200
net_write_timeout=7200
query_cache_size=16M
query_cache_limit=4M
tmp_table_size=64M
max_heap_table_size=64M
key_buffer_size=32M
table_open_cache=32
bind-address=10.192.254.55

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

After which the backup was fine. Not sure if the settings are an overkill though. But I did them high on purpose to eliminate any timeouts

[jomann]

I'm not a DBA so I can't tell you more about optimization but it does look okay. Each installation is different so I wouldn't have any baseline to give you but if it's working for you and not causing any slowdown then it should be okay.