How do you stay on top of your Oracle MySQL monitoring? Here are five ways to do just that.
1. Determine if you have rogue users or databases that are not secure.
If you have users that are able to connect from anywhere in the world by using % as hosts, then you should be doubly careful because your MySQL server is prone to hacking and other attacks. Do not use %. Instead, give literal values for your host. This way the hacker would need to gain access to that host first before being able to gain access to your servers. Also, check out users who have a lot of privileges. For instance, a root user that has super privileges by default. In this case, rename your root
CREATE USER ‘another_name’@’%’ IDENTIFIED BY ‘password’;
GRANT ALL ON *.* TO ‘another_name’@’%’ WITH GRANT OPTION;
DROP USER ‘root’@‘%’;
And while we are on the topic of security, you should also require all MySQL users to use passwords.
Another thing to watch out for is the test database that comes with all MySQL installations by default. This database is accessible to everyone even those users without explicit access to the database. So if you are ready to get into production, do not use “test” as names for your databases.
[expand title=”Click here to read more about this article”]
2. Check for availability.
When you have MySQL used in your production databases and systems, a little downtime is simply not acceptable. In fact, most guides would tell you that availability should be on top of your list. There are two ways to check on your MySQL availability.
a.) Execute -mysqladmin -h 192.168.x.xx -u root -p status in order to find out if your MySQL is running as it should.
b.) Use service mysqld status to ping your MySQL (only applicable in RedHat Linux environments).
3. Inspect recent server configuration changes.
If there is a noticeable change in the way your server is performing, especially if your server is suddenly doing very poorly when it was fine just a week or two ago, then check for any recent changes in your server configuration.
4. See your error log.
MySQL provides you with an error log that contains information on the start and stop time of your servers, as well ass critical errors that happen while your MySQL is running. Be sure to pore over the log and pay attention to all entries that has [error] in it.
5. Look for excessive aborted connects.
Failed attempts to connect to your server are easily determined by aborted_connects. Excessive number of failed attempts could mean that whoever is trying to access your MySQL may not have the necessary privileges or might have an incorrect password. Worst, it could indicate a hacking attempt.
To see failed attempts, execute SHOW GLOBAL STATUS LIKE ‘aborted_connects’.
Then there are others, such as checking on slave lags, slow query log, maximum allowed connections, and the number of full table scans.
Contact Four Cornerstone now if you need assistance with Oracle MySQL monitoring.[/expand]