Remote MySQL Performance And Query Monitoring

There may be the situation that you have to monitor a MySQL server remotely. There are some linux tools to do performance and query monitoring locally, and these tools can also used to monitor remotely - but only unencrypted ! Also often MySQL is only listening on the loopback interface, so it is even not reachable remotely over the net (which is very good seen from the security viewpoint). But there is an easy solution in the Linux world.

 

Preliminary Note + Disclaimer

The following article describes the way I installed and used the software, I do not issue any guarantee that the same way works for you. You should have some basic knowledge doing things in a shell.

 

SSH

Once SSH was called the poor mans VPN, but it is very versatile and and has a lot of handy features, and it is very easy to set up, and when you are adminstrating a Linux box chances are high, that you already use it. So why not use it to our advantage when the task is to administrate a remote MySQL database ?!

 

MySQL Tools

mysqlreport

mysqlreport was introduced on Howtoforge in the artice Getting MySQL Status Values With mysqlreport, also the guide to understand this report was mentioned there, the mysqlreportguide.

mysqlreport could be used to contact remote sites with command line parameters in a style like

mysqlreport --host 127.0.0.1 --port 3306 --user root --password ...

to contact remote MySQL servers, but only unencrypted.

 

mytop

mytop is a top like application, which gives various status pages every few seconds. it has also basic remote features, but unencrypted, by using command line parameters like

mytop --host 127.0.0.1 --port 3306 --user root --password ...

 

innotop

innotop is kind of focused on the Inno-DB engine of MySQL. It works also in a top like fashion as mytop does, also has basic networking capabilities in the style of

innotop -u root -p ... -h 127.0.0.1

For all this tools you should have to have a look at the manpages for deeper knowledge regarding their possibilites.

 

SSH

These tools, and I can imagine there are more tools for MySQL administratings and monitoring that have basic network capabilities, could easily be combined with the power of SSH which encrypts connections to remote sites on the fly and makes them safe from eavesdropping valuable informations such as passwords. An SSH tunnel might also be a method to safely contact a service only listening on localhost.

• There should be an SSH daemon running on the server
• Preferably you might have exported your public key to the server for a password-less login.
• You should establish a safe, encrypted tunnel with the means of SSH to the server, in the style of

ssh -v -L33066:localhost:3306 -N root@1.2.3.4

to perform the local portforwarding. You can verify this by issuing a

netstat -an | grep 33066

and get an output like

tcp        0      0 127.0.0.1:33066         0.0.0.0:*               LISTEN
tcp6       0      0 ::1:33066               :::*                    LISTEN

In this case there are two listening ports, one IPv4 and one IPv6, but the ::1 is localhost in IPv6 as is 127.0.0.1 in IPv4. 4. Then you can safely connect with your your local installed clientsoftware to the MySQL target over an encrypted tunnel, in the style of

mysqlreport --host 127.0.0.1 --port 33066 --user root --password ...

or

innotop -u root -p ... -h 127.0.0.1:33066

or

mytop --host 127.0.0.1 --port 33066 --user root --password ...

I'm sure this kind of connection also could be established with the famous PuTTY or with other SSH clients, and also with more MySQL administration tools that allow remote administration.

 

Further Reading

As I'm not the german Ex-Minister and Ex-Doctor von Guttenberg, who has not correctly cited the sources of information in his dissertation, and his Doctors degree therefore has been dropped, here are my sources of information, or to say, the footnotes (where he has stumbled upon) :) :

• Getting MySQL Status Values With mysqlreport
• mysqlreportguide
• Password-less logins with OpenSSH

And, again regarding to the Causa "von Guttenberg", the above is no Dissertation, only a kind of technical Howto or Cooking Recipe. Just to clarify.