點擊上方藍字關注我

MySQL中經常遇到事務中的SQL正在執(zhí)行或執(zhí)行完成后未提交，如何找出對應的SQL？

1. 查看正在執(zhí)行的SQL

查看事務中正在執(zhí)行的SQL方式有多種，例如

(資料圖片僅供參考)

1.1 通過processlist查看

會話1：執(zhí)行1個SQL

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql>selectsleep(20),now(),idfromtest1;

會話2：開啟另一個會話，查看對應的SQL

mysql> select  id ,info  from information_schema.processlist where info is not null;+----+------------------------------------------------------------------------------+| id | info                                                                         |+----+------------------------------------------------------------------------------+| 36 | select sleep(20),now() ,id from test1                                        || 37 | select  id ,info  from information_schema.processlist where info is not null |+----+------------------------------------------------------------------------------+2rowsinset(0.00sec)

可以看到正在執(zhí)行的SQL，包括自己的SQL的id及內容

1.2通過events_statements_current查看

會話1：執(zhí)行1個SQL

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql>selectsleep(20),now(),idfromtest1;

會話2：查看對應的SQL

mysql> select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id\G*************************** 1. row ***************************id: 36info: select sleep(20),now() ,id from test1thread_id: 76sql_text: select sleep(20),now() ,id from test1*************************** 2. row ***************************id: 37info: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_idthread_id: 77sql_text: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id2 rows in set (0.01 sec)

2.方式對比

通過processlist和通過events_statements_current區(qū)別在于，processlist中能查到的SQL是正在運行的SQL，而運行結束的SQL是看不到的。

會話1：執(zhí)行1個SQL

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select sleep(2),now() ,id from test1;+----------+---------------------+----+| sleep(2) | now()               | id |+----------+---------------------+----+|        0 | 2023-01-03 22:01:09 |  1 |+----------+---------------------+----+1 row in set (2.00 sec)

此時查看事務情況

mysql> select  * from information_schema.innodb_trx\G*************************** 1. row ***************************trx_id: 421227264232664trx_state: RUNNINGtrx_started: 2023-01-03 22:01:09trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 0trx_mysql_thread_id: 36trx_query: NULLtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 0trx_lock_structs: 0trx_lock_memory_bytes: 1128trx_rows_locked: 0trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0trx_schedule_weight: NULL1rowinset(0.00sec)

其中trx_mysql_thread_id=36的會話正是我們會話1的線程id,但是我們看不到具體的SQL

mysql> select *  from information_schema.processlist where id=36;+----+------+-----------+--------+---------+------+-------+------+| ID | USER | HOST      | DB     | COMMAND | TIME | STATE | INFO |+----+------+-----------+--------+---------+------+-------+------+| 36 | root | localhost | testdb | Sleep   |  177 |       | NULL |+----+------+-----------+--------+---------+------+-------+------+1 row in set (0.00 sec)

但是此時通過方式2就可以查到

mysql> select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id\G*************************** 1. row ***************************id: 36info: NULLthread_id: 76sql_text: select sleep(2),now() ,id from test1*************************** 2. row ***************************id: 37info: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_idthread_id: 77sql_text: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id2 rows in set (0.00 sec)

注意：此時只能查到一個事務中的多條SQL的最后一個

例如：

mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select sleep(2),now() ,id from test1;+----------+---------------------+----+| sleep(2) | now()               | id |+----------+---------------------+----+|        0 | 2023-01-03 22:01:09 |  1 |+----------+---------------------+----+1 row in set (2.00 sec)mysql> select sleep(1),now() ,id from test1;+----------+---------------------+----+| sleep(1) | now()               | id |+----------+---------------------+----+|        0 | 2023-01-03 22:06:35 |  1 |+----------+---------------------+----+

會話2查看結果

mysql> select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id\G*************************** 1. row ***************************id: 36info: NULLthread_id: 76sql_text: select sleep(1),now() ,id from test1*************************** 2. row ***************************id: 37info: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_idthread_id: 77sql_text: select a.id,a.info, b.thread_id, c.sql_text  from information_schema.processlist  a, performance_schema.threads b, performance_schema.events_statements_current c  where  a.id=b.processlist_id  and b.thread_id = c.thread_id2rowsinset(0.00sec)

可見，查到的是最后一個SQL了，如果事務手動commit提交了，則顯示的是commit

往期精彩回顧

1.MySQL高可用之MHA集群部署

2.mysql8.0新增用戶及加密規(guī)則修改的那些事3. 比hive快10倍的大數(shù)據(jù)查詢利器-- presto

4. 監(jiān)控利器出鞘：Prometheus+Grafana監(jiān)控MySQL、Redis數(shù)據(jù)庫

5. PostgreSQL主從復制--物理復制

6.MySQL傳統(tǒng)點位復制在線轉為GTID模式復制

7.MySQL敏感數(shù)據(jù)加密及解密

8.MySQL數(shù)據(jù)備份及還原（一）

9.MySQL數(shù)據(jù)備份及還原（二）

掃碼關注

關鍵詞： 正在執(zhí)行 數(shù)據(jù)備份 看不到的