Shulou(Shulou.com)06/01 Report--

MYSQL version:

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.6.27-log |

+-+

1 row in set (0.00 sec)

1. Enable query log

Mysql > SET GLOBAL general_log=1

Query OK, 0 rows affected (0.02 sec)

Mysql > show variables like 'general_log%'

+-+

| | Variable_name | Value |

+-+

| | general_log | ON |

| | general_log_file | / app/oracle/oradata2/mysql-5.5.37/data/oradb.log |

+-+

2 rows in set (0.00 sec)

two。 View MYSQL thread information

-- MYSQL Thread 1

Mysql > show full processlist

+-+-

| | Id | User | Host | db | Command | Time | State | Info |

+-+-

| | 1 | root | localhost | NULL | Query | 0 | init | show full processlist | |

+-+-

1 row in set (0.00 sec)

-- MYSQL Thread 2

Mysql > show full processlist

+-+-

| | Id | User | Host | db | Command | Time | State | Info |

+-+-

| | 1 | root | localhost | NULL | Sleep | 145 | | NULL |

| | 2 | root | localhost | NULL | Query | 0 | init | show full processlist | |

+-+-

2 rows in set (0.00 sec)

-- MYSQL Thread 3

Mysql > show full processlist

+-+-

| | Id | User | Host | db | Command | Time | State | Info |

+-+-

| | 1 | root | localhost | NULL | Sleep | 189 | | NULL |

| | 2 | root | localhost | NULL | Sleep | 44 | | NULL |

| | 3 | root | localhost | NULL | Query | 0 | init | show full processlist | |

+-+-

3 rows in set (0.00 sec)

-- MSYQL Thread 4

Mysql > show full processlist

+-+-

| | Id | User | Host | db | Command | Time | State | Info |

+-+-

| | 1 | root | localhost | NULL | Sleep | 238 | | NULL |

| | 2 | root | localhost | NULL | Sleep | 93 | | NULL |

| | 3 | root | localhost | NULL | Sleep | 49 | | NULL |

| | 4 | root | localhost | NULL | Query | 0 | init | show full processlist | |

+-+-

4 rows in set (0.00 sec)

-- MYSQL query log

[root@oradb data] # tail-f oradb.log

/ app/oracle/oradata2/mysql-5.6.27/bin/mysqld, Version: 5.6.27-log (MySQL Community Server (GPL)). Started with:

Tcp port: 3306 Unix socket: / tmp/mysql.sock

Time Id Command Argument

161116 10:48:57 1 Query show variables like 'general_log%'

161116 10:50:07 1 Query show full processlist

161116 10:52:24 2 Connect root@localhost on

2 Query select @ @ version_comment limit 1

161116 10:52:32 2 Query show full processlist

161116 10:53:09 3 Connect root@localhost on

3 Query select @ @ version_comment limit 1

161116 10:53:16 3 Query show full processlist

161116 10:53:57 4 Connect root@localhost on

4 Query select @ @ version_comment limit 1

161116 10:54:05 4 Query show full processlist

ID: represents the MYSQL thread ID

3. Use the query log to find out the blocking SQL caused by holding the lock

-- MYSQL thread 1 performs updates, and the uncommitted transaction persists in holding the lock

Use test

START TRANSACTION

UPDATE t1 SET NAME='test11' WHERE id

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.