Monitoring MySQL servers can be difficult in a way that a novice won’t be able to monitor MySQL that easily. MySQL gives a comprehensive list of variables to check your server’s health and performance.
1. Check MySQL Status
Yes, this is the first thing you should be looking at! It would not make sense to monitor your MySQL if its not even available. MySQL downtime is simply not acceptable in production. At the same time ensuring zero downtime does not guarantee maximum performance.
You can execute
$ service mysql-server status
mysql is running as pid 0000
PID TT STAT TIME COMMAND
0000 ?? I 0:00.50 [mysqld]
There is also another way for you to check if MYSQL is running on your server is be execute this:
Once you have executed this you will get an Output of:
mysql is running as pid 1259.
2. Insecure Users ad Database
For MySQL users can have a “%” as host. This meas that the user has the ability to connect to MySQL from anywhere. If users contain a % for host, then you are vulnerable to attack on your server. To decrease the chance for attacks and increase your security, I would recommend you give literal host values instead of %.
If for instance, your host is localhost then the hacker will have to gain access to localhost first before touching your servers. Localhost is too common and needs to be changed.
Are there any users for MySQL that contains high privleges? MySQL by default comes with a user called root, who has super privileges. Super privileges can get access to pretty much anything. The word is too well known. You can simply give any other name instead of root.
CREATE USER 'obsure_name'@'%’ IDENTIFIED BY 'password'; GRANT ALL ON *.* TO 'obsure_name'@'%' WITH GRANT OPTION; DROP USER 'root'@‘%’; FLUSH PRIVILEGES;
In addition after this, I highly recommend that you ensure that all your MySQL users have passwords.
The default MySQL installation comes with a database named ‘test’ that anyone can access. This database is intended only for testing or tutorial purpose. The ‘test’ can be accessed by users who do not have explicit privileges granted to that database. Therefore databases named ‘test’ should be avoided on production servers.
3. Aborted connects
aborted_connects gives the total number of failed attempts to connect to MySQL. Excess aborted_connects indicate that the client does not have enough privileges, or the client uses an incorrect password, or someone is trying to hack into your server.
aborted_connects is a global status value and can be retrieved using-
SHOW GLOBAL STATUS LIKE 'aborted_connects';
4. Error log
MySQL Error log not only contains information on server start and stop time but also critical errors that occur while the server is running. On some operating systems, the error log also contains stack trace of MySQL before it crashed. Any entry of type [error] obviously needs your attention.
5. Innodb Deadlocks
Innodb rolls back transactions if there is a deadlock. Knowing its occurrence is important to trace back the root cause of deadlock. You should know what deadlocks occured and verify that applications have handled them properly or take proper action.
To detect deadlocks, use
SHOW ENGINE INNODB STATUS;
6. Change in server configuration
Ever wondered why your server was performing perfectly a few weeks ago but isn’t now? Then you should be looking at the MySQL configuration changes that were made in the recent past. MySQL configuration plays a critical role in your server’s performance.
7. Slow Query log
Consists of all the queries that exceed long_query_time seconds to execute. More slow queries would mean more disk reads, more memory usage, more CPU usage which ultimately just slows down your servers, causes bottlenecks and hence results in poor performance. Slow Query log is where you find queries that are potential targets for optimization.
8. Slave lag
Most production servers have one or more replication slaves. Monitoring slaves are equally important. If slave_sql_running, slave_io_running is NO, then your slave has stopped replicating and should be fixed first. Higher seconds_behind_master is, the slower is your slave. By executing –
SHOW SLAVE STATUS;
you can track your replicas.
9. Percentage of maximum allowed connections
A high value of percentage of maximum allowed connections(max_used_connections / max_connections) tells that you could soon run out of client connections. In other words, new connection requests will simply be refused. So make sure your max_connections is large enough to suite your application. By executing-
SHOW GLOBAL VARIABLES LIKE 'max_connections'; SHOW GLOBAL STATUS LIKE 'max_used_connections';
you can track maximum allowed connections.
10. Percentage of full table scans
Percentage of full table scans is best represented using
((Handler_read_rnd_next + Handler_read_rnd) / (Handler_read_rnd_next + Handler_read_rnd + Handler_read_first + Handler_read_next + Handler_read_key + Handler_read_prev)).
An increase in full table scans (or in creation of temporary tables or similar) may turn satisfactory performance into non-satisfactory. You should understand the queries causing this and consider changes to schemas or queries if needed. You can retrieve these metric by executing-
SHOW GLOBAL STATUS LIKE "Handler_read%";