现象：Seconds_Behind_Master的值一直在73358徘徊。且 Slave_SQL_Running_State的状态位System lock
1.查看从库复制状态
igoodful@glc (my_table) > show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.10 Master_User: mysqlsync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.020097 Read_Master_Log_Pos: 147987586 Relay_Log_File: relay-bin.000166 Relay_Log_Pos: 17867111 Relay_Master_Log_File: mysql-bin.020032 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: 17866946 Relay_Log_Space: 69941284661 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: 73358 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: 177104900 Master_UUID: 0af9b8b6-ad03-11e9-abca-e4434b034bf8 Master_Info_File: mysql.slave_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: 0af9b8b6-ad03-11e9-abca-e4434b034bf8:942236365-1165362599 Executed_Gtid_Set: 0af9b8b6-ad03-11e9-abca-e4434b034bf8:1-1043789379, bd3f2d47-ac5c-11e9-a378-e4434b21bc70:1-39550055933 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified Thu Mar 19 10:32:13 2020
2.查看活跃线程：
igoodful@glc (my_table) > select name,PROCESSLIST_STATE,PROCESSLIST_INFO from performance_schema.threads; +----------------------------------------+----------------------------------+--------------------------------------------------------------------------------+ | name | PROCESSLIST_STATE | PROCESSLIST_INFO | +----------------------------------------+----------------------------------+--------------------------------------------------------------------------------+ | thread/sql/main | NULL | NULL | | thread/threadpool/timer_thread | NULL | NULL | | thread/sql/thread_timer_notifier | NULL | NULL | | thread/innodb/io_ibuf_thread | NULL | NULL | | thread/innodb/io_log_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_read_thread | NULL | NULL | | thread/innodb/io_write_thread | NULL | NULL | | thread/innodb/io_write_thread | NULL | NULL | | thread/innodb/io_write_thread | NULL | NULL | | thread/innodb/io_write_thread | NULL | NULL | | thread/innodb/io_write_thread | NULL | NULL | | thread/innodb/io_write_thread | NULL | NULL | | thread/innodb/io_write_thread | NULL | NULL | | thread/innodb/io_write_thread | NULL | NULL | | thread/innodb/page_cleaner_thread | NULL | NULL | | thread/innodb/page_cleaner_thread | NULL | NULL | | thread/innodb/page_cleaner_thread | NULL | NULL | | thread/innodb/page_cleaner_thread | NULL | NULL | | thread/innodb/buf_lru_manager_thread | NULL | NULL | | thread/innodb/buf_lru_manager_thread | NULL | NULL | | thread/innodb/buf_lru_manager_thread | NULL | NULL | | thread/innodb/buf_lru_manager_thread | NULL | NULL | | thread/innodb/buf_lru_manager_thread | NULL | NULL | | thread/innodb/buf_lru_manager_thread | NULL | NULL | | thread/innodb/buf_lru_manager_thread | NULL | NULL | | thread/innodb/buf_lru_manager_thread | NULL | NULL | | thread/innodb/srv_monitor_thread | NULL | NULL | | thread/innodb/srv_lock_timeout_thread | NULL | NULL | | thread/innodb/srv_error_monitor_thread | NULL | NULL | | thread/innodb/srv_master_thread | NULL | NULL | | thread/innodb/srv_worker_thread | NULL | NULL | | thread/innodb/srv_worker_thread | NULL | NULL | | thread/innodb/srv_purge_thread | NULL | NULL | | thread/innodb/srv_worker_thread | NULL | NULL | | thread/innodb/buf_dump_thread | NULL | NULL | | thread/innodb/dict_stats_thread | NULL | NULL | | thread/sql/signal_handler | NULL | NULL | | thread/sql/compress_gtid_table | Suspending | NULL | | thread/sql/one_connection | NULL | NULL | | thread/sql/one_connection | NULL | NULL | | thread/sql/one_connection | NULL | NULL | | thread/sql/one_connection | NULL | NULL | | thread/sql/one_connection | NULL | NULL | | thread/sql/one_connection | NULL | NULL | | thread/sql/one_connection | NULL | NULL | | thread/sql/one_connection | NULL | NULL | | thread/sql/one_connection | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/sql/one_connection | Sending data | select name,PROCESSLIST_STATE,PROCESSLIST_INFO from performance_schema.threads | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/sql/slave_io | Waiting for master to send event | NULL | | thread/sql/slave_sql | System lock | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | | thread/threadpool/worker_thread | NULL | NULL | +----------------------------------------+----------------------------------+--------------------------------------------------------------------------------+ 114 rows in set (0.00 sec) Thu Mar 19 10:34:25 2020
3.查看当前事务
igoodful@glc (my_table) > select * from information_schema.innodb_trx\G; *************************** 1. row *************************** trx_id: 80927010513 trx_state: RUNNING trx_started: 2020-03-19 10:46:45 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 6 trx_mysql_thread_id: 54 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 2 trx_lock_structs: 4 trx_lock_memory_bytes: 1136 trx_rows_locked: 2 trx_rows_modified: 2 trx_concurrency_tickets: 0 trx_isolation_level: READ COMMITTED trx_unique_checks: 1 trx_foreign_key_checks: 1 trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0 trx_adaptive_hash_timeout: 0 trx_is_read_only: 0 trx_autocommit_non_locking: 0 1 row in set (0.00 sec) ERROR: No query specified Thu Mar 19 10:46:45 2020
4.验证是否有表锁
igoodful@glc (my_table) > show open tables where in_use >=1; Empty set (0.00 sec) Thu Mar 19 12:28:30 2020
####################################
京公网安备 11010502036488号