Page 1 of 1
Backup Failure
Posted: Fri Jul 13, 2018 2:38 am
by 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
Re: Backup Failure
Posted: Fri Jul 13, 2018 10:12 am
by 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
Re: Backup Failure
Posted: Mon Jul 16, 2018 2:39 am
by 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,
Re: Backup Failure
Posted: Mon Jul 16, 2018 3:27 am
by 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
Re: Backup Failure
Posted: Mon Jul 16, 2018 12:25 pm
by 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.