MySQL

Monitoring MySQL

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
$ps 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:

/usr/local/etc/rc.d/mysql-server status

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%";

 

Related Articles

  • How can I convert my MyISAM tables to InnoDB?

    If you want to convert a MyISAM table to InnoDB, the process is fairly easy, but you can do something extra to speed things up. Before converting the table, adjust...
  • Reset MySQL Password

    Resetting a MySQL Root Password is one of the easiest things to do. However, finding documentation on how to do so can be challenging.   We as system administrators, have...
  • Installing phpMyAdmin and connecting to remote database server

    phpMyAdmin is one of the many tools that allows administrators the ability to effectively and efficiently work with their databases. This can can either be a local database or on...
  • How to Display MySQL Storage Engines

    How to Display MySQL Storage Engines From the MySQL command line, issue the following command: SHOW ENGINES; You should receive a result similar to:   Typically the default storage engine...