Shulou(Shulou.com)06/01 Report--

Question:

At around 09:15 every day, operators will receive a large number of zabbix_server alarm emails prompting "PROBLEM: Zabbix agent on XXX is unreachable for 5 minutes"

Troubleshooting process:

Start with the running status of the system before 9: 00.

1. Check the zabbix_server.log around 9: 00. No abnormality was found.

2. Write a planned task 568 * / bin/ping one_agent_IP > > / tmp/net_status.log on zabbix server

Through the ip on the ping agent side, we can determine whether the network jitter causes the agent unreachable.

However, the log of / tmp/net_status_log the next day shows that ping has no packet loss problem. The problem of network jitter is eliminated.

3. Check the scheduled tasks around 9: 00 and find that a backup.sh was executed at 9: 00. You can see that the backup parameters of mysqldump are as follows:

Mysqldump-force-opt-uxxx-pxxx zabbix | gzip > xxxx.sql.gz

At this point, we can basically judge that the zabbixserver alarm is caused by this backup database operation.

Reason:

Here, let's take a look at some parameters of mysqldump above.

-opt Same as-- add-drop-table,--add-locks,-- create-options

-quick,--extended-insert,-lock-tables,--set-charset

And-- disable-keys.Enabled by default, disable with

-- skip-opt.

-f,-- force Continue even if we get an SQL error.

Among them,-- opt contains a bunch of parameters, and the most important thing here is that the official manual of lock-tables,mysql5.6 explains as follows:

Lock-tables,-l

For eachdumped database, lock all tables to be dumped before dumping them. The tablesare

Lockedwith READ LOCAL to permit concurrent inserts in the case of MyISAM tables. For transactional

Tables such as InnoDB,--single-transaction is a much better option than-lock-tables

Because it does not need tolock the tables at all.

Because--lock-tables locks tables for each database separately, this option does notguarantee

That thetables in the dump file are logically consistent between databases. Tables indifferent

Databasesmay be dumped in completely different states.

Someoptions, such as-opt, automatically enable-lock-tables. If you want tooverride this

Use--skip-lock-tables at the end of the option list.

Officials recommend using-- single-transaction instead of-- lock-tables when using the InnoDB storage engine, because-- lock-tables can cause table locking problems.

After matching the-single-transaction parameter, a START TRANSACTION statement is added after the FLUSH TABLES WITH READ LOCK, and the isolation level of the transaction is set to REPEATABLE READ. At this time, the lock is added only to determine the specific location of the binlog in the master-data and to open the transaction. After the transaction is opened, the read lock has been released, and as can be seen from the log, during the rollback of the log, it is rolled back to a single TRANSACTION, which is also the sp point. Each time you make changes to the table and parameters, the transaction is rolled back. Through the analysis of the backup log, we can find that after all the backup phases are completed, they are from rollback to sp. That is, when you return to the SAVEPOINT sp point in time, that is, after the backup is completed, the backup library is still at the sp point, and this so-called sp rollback is actually realized by the data snapshot in the called undo. (that is to say, when the backup is performed at 9 o'clock, the final backup data is the 9: 00 data, and the changes in the data during the backup will not be written into the backup file.

The figure below is the general log log of mysql when mysqldump-Q-single-transaction-B zabbix > test.sql is executed.

During backup, the-- opt parameter is used to temporarily lock the table, and the data collected by zabbix_agent cannot be written to the database in time. If zabbix_server does not find the data collected by agent for a long time, an alarm will be triggered.

All we have to do is change the backup parameters: mysqldump-uxx-pxx-f-q--single-transaction-B zabbix | gzip > xxxx.sql.gz

In addition, for tables of the Innodb storage engine, it is recommended that you add the-Q-- single-transaction parameter when using mysqldump backups.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.