Mysql应用通过案例分析MySQL中令人头疼的Aborted告警

MYSQL实例
[Warning] Aborted connection 305628 to db: 'db' user: 'dbuser' host: 'hostname' (Got an error reading communication packets)
[Warning] Aborted connection 81 to db:'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication
packets)
[Warning] Aborted connection 109 to db:'helei1' user: 'sys_admin' host: '192.168.1.1' (Got an error writing communication packets)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password: YES)
[Warning] Got an error writing communication packets

MYSQL实例
mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name    | Value |
+----------------------------+----------+
| connect_timeout   | 10  |
| delayed_insert_timeout  | 300  |
| innodb_lock_wait_timeout | 50  |
| innodb_rollback_on_timeout | OFF  |
|interactive_timeout  | 28800 |
| lock_wait_timeout   | 31536000 |
| net_read_timeout   | 30  |
| net_write_timeout   | 60  |
| slave_net_timeout   | 3600  |
|wait_timeout    | 28800 |
+----------------------------+----------+
10 rows in set (0.01 sec)

MYSQL实例
mysql>show global status like 'aborted%';
+------------------+-------+
|Variable_name | Value |
+------------------+-------+
|Aborted_clients | 19 |
|Aborted_connects | 0  |
+------------------+-------+
2 rows inset (0.00 sec)

MYSQL实例
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

MYSQL实例
mysql>show global status like 'aborted%';
+------------------+-------+
|Variable_name | Value |
+------------------+-------+
|Aborted_clients | 19 |
|Aborted_connects | 5  |
+------------------+-------+
2 rows inset (0.00 sec)

MYSQL实例
[Warning] Access denied for user'root'@'127.0.0.1' (using password: YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)

MYSQL实例
mysql>set global wait_timeout=10;
Query OK,0 rows affected (0.00 sec)
 
mysql>set global interactive_timeout=10;
Query OK,0 rows affected (0.00 sec)
mysql>show processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect... Connection id: 79 Current database: *** NONE ***
 
+----+------+-----------------+------+---------+------+-------+------------------+
| Id |User | Host   | db | Command | Time | State | Info    |
+----+------+-----------------+------+---------+------+-------+------------------+
| 79 |root | 127.0.0.1:42016 | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

MYSQL实例
mysql>show global status like 'aborted%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect... Connection id: 81 Current database: *** NONE ***
 
+------------------+-------+
|Variable_name | Value |
+------------------+-------+
|Aborted_clients | 22 |
|Aborted_connects | 5  |
+------------------+-------+
2 rows in set (0.01 sec)

MYSQL实例
[Warning] Aborted connection 81 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets)
[Warning] Aborted connection 78 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets) 
[Warning] Aborted connection 79 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets)

MYSQL实例
mysql>show global variables like 'max_conn%';
+--------------------+-------+
|Variable_name  | Value |
+--------------------+-------+
|max_connect_errors | 1000 |
|max_connections | 1024 |
+--------------------+-------+
2 rows in set (0.00 sec)
 
 
mysql>set global max_connections=2;
Query OK,0 rows affected (0.00 sec)

MYSQL实例
[root@HE3~]# mysql -uroot -pMANAGER -h127.0.0.1
ERROR 1040 (HY000): Too many connections

MYSQL实例
mysql>show global status like 'aborted%';
+------------------+-------+
|Variable_name | Value |
+------------------+-------+
|Aborted_clients | 28 |
|Aborted_connects | 10 |
+------------------+-------+
2 rows in set (0.00 sec)

MYSQL实例
170626 16:26:56 [Warning] Aborted connection 109 to db: 'helei1' user: 'sys_admin' host: '192.168.1.1' (Got an error writing communication packets)