我描述下整個程序：
剛開始告警是主從延時，上從庫看了下
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住。
錯誤日志沒有資訊
重啟大法解決，但原因不明
求解求解
uj5u.com熱心網友回復：
無人回復嗎
uj5u.com熱心網友回復：
查了一下，確認你的 mysqld 有沒有共用表檔案，如果有，出現這種情況很正常，如果沒有，那么打開 skip_external_locking 選項uj5u.com熱心網友回復：
公用表檔案是什么？skip_external_locking 是打開的uj5u.com熱心網友回復：
你說的是共享表空間嗎？打開的每個表獨享表空間
用的云產品，出現過兩次了，真蛋疼
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/116697.html
標籤：MySQL
上一篇：org.springframework.jdbc.BadSqlGrammarException:
下一篇：超出系統資源