可能是复制线程system lock引起的大量unauthenticated user

[gmgmyxx]

我描述下整个过程：
刚开始告警是主从延时，上从库看了下
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.18
Master_User: qc_repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000039
Read_Master_Log_Pos: 460090672
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 444770992
Relay_Master_Log_File: mysql-bin.000039
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 444770902
Relay_Log_Space: 460090966
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 671
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 71008354
Master_UUID: 3e547b24-135d-11e7-9676-525411d50ff7
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3e547b24-135d-11e7-9676-525411d50ff7:36407029-37060714
Executed_Gtid_Set: 3e547b24-135d-11e7-9676-525411d50ff7:1-37031363,
d4c68f4e-f18a-11e5-9127-0cc47ab72b72:1-30282323,
d5628a48-f18a-11e5-aea6-0cc47ab727cf:1-5194
Auto_Position: 1
1 row in set (0.00 sec)



processlists没什么异常
mysql> show processlist;
+-------+-------------+---------------------+------+---------+-------+----------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+---------------------+------+---------+-------+----------------------------------+------------------+
| 147 | system user | | NULL | Connect | 54015 | Waiting for master to send event | NULL |
| 148 | system user | | NULL | Connect | 708 | System lock | NULL |
| 65675 | kanban | 192.168.0.31:47232 | zkyq | Sleep | 32 | | NULL |
| 71602 | kanban | 192.168.0.31:47312 | vms | Sleep | 20 | | NULL |
| 72255 | kanban | 192.168.0.31:47322 | vms | Sleep | 753 | | NULL |
| 72256 | kanban | 192.168.0.31:47323 | vms | Sleep | 753 | | NULL |
| 76261 | root | 192.168.55.16:37618 | NULL | Query | 0 | init | show processlist |
+-------+-------------+---------------------+------+---------+-------+----------------------------------+------------------+


但是INNODB_TRX 表里有一行，当时差的时候已经快14：50分了


mysql> SELECT * FROM information_schema.INNODB_TRX\G;
*************************** 1. row ***************************
trx_id: 147178299
trx_state: RUNNING
trx_started: 2017-08-09 14:38:21
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 1
trx_mysql_thread_id: 148
trx_query: NULL
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 1
trx_lock_memory_bytes: 360
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.01 sec)


后来就是突然出现大量的未验证连接
show processlists

mysql> show processlist;
+-------+----------------------+---------------------+--------------------+---------+-------+----------------------------------+---------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------------------+---------------------+--------------------+---------+-------+----------------------------------+---------------------------+
| 147 | system user | | NULL | Connect | 54268 | Waiting for master to send event | NULL |
| 148 | system user | | NULL | Connect | 961 | System lock | NULL |
| 65675 | kanban | 192.168.0.31:47232 | zkyq | Sleep | 285 | | NULL |
| 71602 | kanban | 192.168.0.31:47312 | vms | Sleep | 10 | | NULL |
| 76537 | root | 192.168.55.16:37636 | NULL | Query | 33 | init | show engine innodb status |
| 76628 | qc_master | localhost | NULL | Query | 33 | executing | SHOW GLOBAL STATUS |
| 76629 | unauthenticated user | connecting host | NULL | Killed | NULL | login | NULL |
| 76630 | unauthenticated user | connecting host | NULL | Killed | NULL | login | NULL |
| 76631 | qc_master | localhost | NULL | Killed | 31 | cleaning up | NULL |
| 76632 | unauthenticated user | connecting host | NULL | Killed | NULL | login | NULL |
| 76633 | unauthenticated user | connecting host | NULL | Killed | NULL | login | NULL |
| 76634 | unauthenticated user | connecting host | NULL | Killed | NULL | login | NULL |
| 76635 | qc_repl | localhost | NULL | Killed | 26 | cleaning up | NULL |
| 76636 | unauthenticated user | connecting host | NULL | Killed | NULL | login | NULL |
| 76637 | unauthenticated user | connecting host | NULL | Killed | NULL | login | NULL |
| 76638 | unauthenticated user | connecting host | NULL | Killed | NULL | login | NULL |
| 76639 | unauthenticated user | connecting host | NULL | Killed | NULL | login | NULL |
| 76640 | qc_master | localhost | NULL | Query | 24 | executing | SHOW GLOBAL STATUS
。。。。。略
53 rows in set (0.00 sec)


然后一直涨啊涨，涨到我怀疑人生
开始出现这个连接的时候，数据库就访问不了了，执行sql就一直hang住。
错误日志没有信息

重启大法解决，但原因不明

求解求解

[gmgmyxx]

引用 2 楼 zjcxc 的回复:

查了一下，确认你的 mysqld 有没有共用表文件，如果有，出现这种情况很正常，如果没有，那么打开 skip_external_locking 选项

公用表文件是什么？skip_external_locking 是打开的

[gmgmyxx]

引用 2 楼 zjcxc 的回复:

查了一下，确认你的 mysqld 有没有共用表文件，如果有，出现这种情况很正常，如果没有，那么打开 skip_external_locking 选项

你说的是共享表空间吗？打开的每个表独享表空间 用的云产品，出现过两次了，真蛋疼

[gmgmyxx]

无人回复吗

[zjcxc]

查了一下，确认你的 mysqld 有没有共用表文件，如果有，出现这种情况很正常，如果没有，那么打开 skip_external_locking 选项