Getting Started with MySQL Proxy
Pages: 1, 2, 3, 4
Let's go though it, using the tutorial from MySQL Forge.
The shell tutorial script implements a simple syntax to ask for shell commands:
SHELL commandFor example:
SHELL ls -lh /usr/local/mysql/data- Get the shell tutorial script. Save it as shell.lua.
- Launch the proxy.
- Connect to the proxy.
$ /usr/local/sbin/mysql-proxy --proxy-lua-script=shell.lua -D
# from a different console
$ mysql -U USERNAME -pPASSWORD -h 127.0.0.1 -P 4040Make sure that it works as a normal proxy to the database server.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.0.37-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)Good. The normal operations work as expected. Now we test the enhanced features.
mysql> shell df -h;
+--------------------------------------------------------+
| df -h |
+--------------------------------------------------------+
| Filesystem Size Used Avail Use% Mounted on |
| /dev/md1 15G 3.9G 9.7G 29% / |
| /dev/md4 452G 116G 313G 27% /app |
| tmpfs 1.7G 0 1.7G 0% /dev/shm |
| /dev/md3 253G 159G 82G 67% /home |
| /dev/md0 15G 710M 13G 6% /var |
+--------------------------------------------------------+
6 rows in set (0.00 sec)Hello shell! This is really a treat for advanced users. Once you have a way of accessing external commands, you can become quite creative.
mysql> shell grep key_buffer /usr/local/mysql/my.cnf;
+-----------------------------------------+
| grep key_buffer /usr/local/mysql/my.cnf |
+-----------------------------------------+
| key_buffer=2000M |
+-----------------------------------------+
1 row in set (0.00 sec)I know that I could check the same with SHOW VARIABLES, but since this is a value that can be set online, I just wanted to make sure that it was also in the configuration file. And how is our memory situation?
mysql> shell free -m;
+---------------------------------------------------------------------------+
| free -m |
+---------------------------------------------------------------------------+
| total used free shared buffers cached |
| Mem: 3280 1720 1560 0 9 1006 |
| -/+ buffers/cache: 704 2575 |
| Swap: 8189 2 8186 |
+---------------------------------------------------------------------------+
4 rows in set (0.08 sec)That's not bad. Now that we are content with the status of the server, what about some fun? We could, for example, check the last entries on Planet MySQL. Do you think I am babbling? Not at all. The command is quite long, but it works.
wget -q -O - http://www.planetmysql.org/rss20.xml \
| perl -nle 'print $1 if m{<title>(.*)</title>}' \
|head -n 21 | tail -n 20;However, because the listing is so large, and nobody will remember that anyway, you should paste it into a shell script, and call it, for instance, last_planet.sh. And, here you are!
mysql> shell last_planet.sh;
+-------------------------------------------------------------------------------------+
| last_planet.sh |
+-------------------------------------------------------------------------------------+
| Top 5 Wishes for MySQL |
| Open Source ETL tools. |
| MySQL Congratulates FSF on GPLv3 |
| Query cache is slow to the point of being unusable - what is being done about that. |
| About 'semi-unicode' And 'quasi Moon Stone' |
| My top 5 MySQL wishes |
| Four more open source startups to watch |
| More on queue... Possible Solution... |
| MySQL as universal server |
| MySQL Proxy. Playing with the tutorials |
| Open source @ Oracle: Mike Olson speaks |
| Quick musing on the "Queue" engine. |
| Distributed business organization |
| Ideas for a MySQL queuing storage engine |
| MySQL Test Creation Tool Design Change |
| Queue Engine, and why this won' likely happen... |
| What?s your disk I/O thoughtput? |
| My 5+ Wish List? |
| Top 5 best MySql practices |
| Packaging and Installing the MySQL Proxy with RPM |
+-------------------------------------------------------------------------------------+
20 rows in set (1.48 sec)Shell access, and web content from MySQL client! Wow!
A Word of Caution
Having shown that you can access the shell from a MySQL connection does not automatically imply that you should always do it. Shell access is a security vulnerability, and if you want to use this feature in your server, do it for internal purposes only. Do not allow shell access to applications open to normal users. That would be asking for trouble (and finding it really fast).
You can use the shell to view things, but you could also use it to erase items.
mysql> shell ls *.lua*;
+---------------------+
| ls *.lua* |
+---------------------+
| first_example.lua |
| first_example.lua~ |
| second_example.lua |
| second_example.lua~ |
+---------------------+
4 rows in set (0.03 sec)
mysql> shell rm *~;
Empty set (0.00 sec)
mysql> shell ls *.lua*;
+--------------------+
| ls *.lua* |
+--------------------+
| first_example.lua |
| second_example.lua |
+--------------------+
2 rows in set (0.01 sec)Be very careful with shell access!
Be aware that the shell access you get through the Proxy is referred to the host where the Proxy is running. If you install the Proxy on the same host, it will coincide with the database server, but don't take it for granted.
Customized Logging
I left this example for the end because, in my experience, this is the most interesting one and it has a practical, immediate use. Logs on demand are available in MySQL 5.1. But if you are stuck with MySQL 5.0, then the Proxy can give you a hand.
Simple Logging
To enable logging of queries into something that looks like a general log, the task is easy. Write this small portion of code into a simple_logs.lua file (or download the snippet from MySQL Forge).
local log_file = 'mysql.log'
local fh = io.open(log_file, "a+")
function read_query( packet )
if string.byte(packet) == proxy.COM_QUERY then
local query = string.sub(packet, 2)
fh:write( string.format("%s %6d -- %s \n",
os.date('%Y-%m-%d %H:%M:%S'),
proxy.connection["thread_id"],
query))
fh:flush()
end
endThen start the Proxy with it, and connect to the Proxy from some concurrent sessions. This script will log all queries to a text file named mysql.log. After a few sessions, the logfile would look like this:
2007-06-29 11:04:28 50 -- select @@version_comment limit 1
2007-06-29 11:04:31 50 -- SELECT DATABASE()
2007-06-29 11:04:35 51 -- select @@version_comment limit 1
2007-06-29 11:04:42 51 -- select USER()
2007-06-29 11:05:03 51 -- SELECT DATABASE()
2007-06-29 11:05:08 50 -- show tables
2007-06-29 11:05:22 50 -- select * from t1
2007-06-29 11:05:30 51 -- show databases
2007-06-29 11:05:30 51 -- show tables
2007-06-29 11:05:33 52 -- select count(*) from user
2007-06-29 11:05:39 51 -- select count(*) from columnsThe log contains date, time, connection ID, and query. Simple and effective for such a short script. Notice that there are three sessions, and their commands are not sorted by session, but by the time they were executed.
The pleasant aspect is that you don't need to restart the server to activate the general log. All you need to do is to point your applications to the port 4040 instead of 3306, and you have enabled a simple but functional logging. Come to think of it, you don't need to modify or restart your applications either. You can achieve the same result without touching server or applications. Simply start the Proxy on the same box where the server is located, and activate an iptables rule to redirect traffic from port 3306 to 4040 (courtesy of Patrizio Tassone).
sudo iptables -t nat -I PREROUTING \
-s ! 127.0.0.1 -p tcp \
--dport 3306 -j \
REDIRECT --to-ports 4040
Figure 5. Redirecting traffic from port 3306 to 4040
Now you have logging enabled, and you don't have to restart the server or to touch your applications! When you are done, and you don't need logs anymore, remove the rule (-D instead of -I) and kill the proxy.
sudo iptables -t nat -D PREROUTING \
-s ! 127.0.0.1 -p tcp \
--dport 3306 -j \
REDIRECT --to-ports 4040
