MySQL Forge

Resources for the MySQL Community

Lua Scripts For MySQL Proxy Examples

Contents 1 Lua scripts examples 1.1 All hooks 1.2 Session bandwidth measurement 1.3 User bandwidth measurement 1.4 Blocking unwanted queries 1.5 Track open transactions

[edit] Lua scripts examples

Examples for Lua scripts for Proxy MySQL University session.

Each script must be loaded with

mysql-proxy --proxy-lua-script=/path/to/script/script_name.lua

[edit] All hooks

all-hooks.lua

Sample output

/usr/local/sbin/mysql-proxy --proxy-lua-script=all-hooks.lua
 1 inside connect_server         
 2 inside read_handshake         
 3 inside read_auth              
 4 inside read_auth_result       
 5 inside read_query 	           select @@version_comment limit 1
 6 inside read_query_result       select @@version_comment limit 1
 7 inside read_query 	           select now()
 8 inside read_query_result 	   select now()
 9 inside read_query 	           
10 inside disconnect_client

Client called as

mysql -h 127.0.0.1 -P 4040 -e 'select now()'

[edit] Session bandwidth measurement

This script measures the amount of traffic per session

bandwidth-by-session.lua

Sample output:

/usr/local/sbin/mysql-proxy --proxy-lua-script=bandwidth.lua
64	78
64	115
65	78
65	115
66	78
66	1118

[edit] User bandwidth measurement

An improvement on the previous script. This one measures the amount of traffic by user

bandwidth-by-user.lua

Sample output:

/usr/local/sbin/mysql-proxy --proxy-lua-script=bandwidth2.lua
gmax -> 78
gmax -> 115
gmax -> 194
gmax -> 231
gmax -> 310
gmax -> 347
gmax -> 426
gmax -> 1466
root -> 78
root -> 112
root -> 350

[edit] Blocking unwanted queries

A script that blocks the execution of SHOW TABLES and corresponding commands from INFORMATION_SCHEMA

command-block.lua

Sample client output:

show tables;
ERROR 7777 (X7777): command <SHOW TABLES> is  not allowed

select count(*) from information_schema.schemata;
+----------+
| count(*) |
+----------+
|       22 | 
+----------+
1 row in set (0.01 sec) 

select count(*) from information_schema.tables;
ERROR 7777 (X7777): command <SELECT FROM INFORMATION_SCHEMA TABLES> is  not allowed

[edit] Track open transactions

transactions.lua

sample output (proxy side):

/usr/local/sbin/mysql-proxy --proxy-lua-script=transactions.lua
session_id: 74
74  insert into t1 values (10)

session_id: 74
    1 insert into t1 values (10)
74  insert into t1 values (20)

session_id: 74
    1 insert into t1 values (10)
    2 insert into t1 values (20)
74  select * from t1

session_id: 74
   1 insert into t1 values (10)
   2 insert into t1 values (20)
   3 select * from t1
Disconnecting - implicit rollback -  So delete from transactions where same thread_id = 74

session_id: 74

Sample output (client side)

set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

insert into t1 values(10);
Query OK, 1 row affected (0.01 sec)

insert into t1 values(20);
Query OK, 1 row affected (0.00 sec)

select help;
+-----------------------------------+---------------------------------------------+
| command                           | description                                 |
+-----------------------------------+---------------------------------------------+
| select help                       | this command                                | 
| select transactions summary       | list how many queries for each session      | 
| select transactions status        | shows the current monitoring status         | 
| set global transactions.log={1|0} | enables/disables logging to file            | 
| select transactions               | list all queries in each active transaction | 
+-----------------------------------+---------------------------------------------+
5 rows in set (0.00 sec)

select transactions status;
+----------------------+-----------------------+
| feature              | status                |
+----------------------+-----------------------+
| active transactions  | 1 (2 queries)         | 
| transactions logging | OFF                   | 
| transactions logfile | /tmp/transactions.log | 
+----------------------+-----------------------+
3 rows in set (0.00 sec)

select transactions summary;
+------------+---------+--------------------------+--------------------------+
| session_id | queries | min_time                 | max_time                 |
+------------+---------+--------------------------+--------------------------+
| 75         | 2       | Sun Dec  2 14:26:45 2007 | Sun Dec  2 14:26:52 2007 | 
+------------+---------+--------------------------+--------------------------+
1 row in set (0.00 sec)

exit