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 [email protected]
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 :::* LISTENIn 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:33066or
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) :) :
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.
