You are happy, relaxing on the weekend with your family and friends. You reach out for a margarita and then BUM! Your website/APP/service stops working, your database has fallen ☹
To avoid this scenario and keep your thousands (millions?) of users happy, and your life easy, nothing is better than health check your MySQL. Here is a list of 10 things you should check to make sure everything is OK.
1. MySQL availability
If the database is not even available the rest of this list is useless. Check availability by running
-mysqladmin -h 192.168.1.95 -u root -p status
If you are on Red Hat Linux you can simply ping MySQL using service mysqld status.
2. Insecure users and databases
Do you have any users with more privileges than needed? Your users should have explicitly named servers instead of %. % as host allows one to connect from anywhere in the world, this is really bad for security, if the server is named localhost, for example, an attacker first has to gain access to localhost. The root user can be changed to any other name you want, this makes harder for an aggressor to guess the admin access, here is how to create a new user and replace root
CREATE USER ‘obsure_name’@’%’ IDENTIFIED BY ‘password’;
GRANT ALL ON *.* TO ‘obsure_name’@’%’ WITH GRANT OPTION;
DROP USER ‘root’@‘%’;
FLUSH PRIVILEGES;
It goes without saying but all your users should have passwords!
Also, all MySQL instances come with a database called “test” that can be accessed by all users, it is a good idea to remove the test DB from all production instances.
3. Aborted connects can point to possible attacks and application errors
The aborted_connects command gives you the total number of aborted connection tries on the database. A connection is aborted for things like the user having insufficient privilegies, wrong password, or plain old hack attempts.
Here is the full command
SHOW GLOBAL STATUS LIKE ‘aborted_connects’;
4. Keep an eye on error logs
You should check your error logs frequently, it not only contains data on start and stop time but also any errors that happened while running. Search for the tag [error] to find anything wrong.
5. Deadlocks on InnoDB
InnoDB is the default database engine used by MySQL. If a deadlock happens InnoDB will rollback the transaction. Use this command to identify if any deadlock happened
SHOW ENGINE INNODB STATUS;
Trace to the deadlock to find ways to fix them and remember that the application should be handling deadlocks as well.
6. Search for any server configuration changes
If your server’s performance has diminished, try looking at the configurations. Even small changes can have a huge impact on database performance.
7. Find the slow queries on the Slow Queries Log
Any query that surpasses the long_query_time (number of seconds to consider a query time to be long) is logged on the Slow Queries Log. Slow queries make the database slower by consuming more CPU, making more disk reads and using more memory to run.
8. Slaves are important too
In production you will most likely be using slaves to replicate the master database. You can check the replica’s health running
SHOW SLAVE STATUS;
If slave_sql_running, slave_io_running is NO, your slave has stopped. The seconds_behind_master shows how much laggy your slaves are, keep in mind that if the master stops, the slaves take charge and if the lag is too big important information may be lost.
9. Used connections/maximum allowed connections
If you divide the number of used connections by the maximum allowed connections you can get the percentage of connections used. A higher connection usage is dangerous because if all allowed connections are used every new attempt will be rejected. Run these commands to see used and maximum allowed connections on MySQL
SHOW GLOBAL VARIABLES LIKE ‘max_connections’;
SHOW GLOBAL STATUS LIKE ‘max_used_connections’;
10. Full table scans
It takes a really long time to scan entire tables, so full tables scans should be kept to a minimum, this percentage can be represented by
((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)).
To retrieve metrics on queries and schemas causing full table scans and correct the problem simply run
SHOW GLOBAL STATUS LIKE “Handler_read%”;
Conclusion
This is a quick rundown of the main things you should regularly check on your MySQL database. You can also use tools to make these checks, as Percona Toolkit and MONyog, but is important that you know how to perform these checks if needed.
Is there anything I left off? Which tools you use? Plase leave in the comments.