The query below captures mysql traffic for 10 seconds and then reports:
- SELECTs per second during each one second interval
- INSERTs …
- UPDATEs …
- COMMITs …
- ROLLBACKs …
- CREATE TABLEs …
- DESCRIBEs …
- ALTER TABLEs …
- TRUNCATE TABLEs …
- DROP TABLEs …
- USEs …
- CREATE DATABASEs …
- DROP DATABASEs …
- CREATE INDEXes …
- DROP INDEXes …
Note that the schema is usually not shown, as it was probably selected at the beginning of the mysql session.
tshark -a duration:10 -qz 'io,stat,1,COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?SELECT",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?INSERT",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?UPDATE",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?COMMIT",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?ROLLBACK",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?CREATE TABLE",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?DESC",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?ALTER TABLE",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?TRUNCATE TABLE",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?DROP TABLE",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?USE",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?CREATE DATABASE",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?DROP DATABASE",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?CREATE (UNIQUE )?INDEX",COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?DROP INDEX"' -d tcp.port==3306,mysql -R mysql.query port 3306
After 10 seconds, you'll see output:
84 packets captured ===================================================================================================================================================== | IO Statistics | | | | Interval size: 1 secs | | Col 1: Frames and bytes | | 2: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?SELECT" | | 3: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?INSERT" | | 4: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?UPDATE" | | 5: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?COMMIT" | | 6: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?ROLLBACK" | | 7: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?CREATE TABLE" | | 8: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?DESC" | | 9: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?ALTER TABLE" | | 10: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?TRUNCATE TABLE" | | 11: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?DROP TABLE" | | 12: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?USE" | | 13: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?CREATE DATABASE" | | 14: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?DROP DATABASE" | | 15: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?CREATE (UNIQUE )?INDEX" | | 16: COUNT(mysql.query)upper(mysql.query) matches "^[\\n\\s]*?DROP INDEX" | |---------------------------------------------------------------------------------------------------------------------------------------------------| | |1 |2 |3 |4 |5 |6 |7 |8 |9 |10 |11 |12 |13 |14 |15 |16 | | Interval | Frames | Bytes | COUNT | COUNT | COUNT | COUNT | COUNT | COUNT | COUNT | COUNT | COUNT | COUNT | COUNT | COUNT | COUNT | COUNT | COUNT | |---------------------------------------------------------------------------------------------------------------------------------------------------| | 0 <> 1 | 51 | 11782 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 1 <> 2 | 56 | 9930 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 2 <> 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 3 <> 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 4 <> 5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 5 <> 6 | 66 | 8802 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 6 <> 7 | 158 | 59540 | 14 | 0 | 8 | 4 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | =====================================================================================================================================================