现象: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

 

 

####################################