The best approach to troubleshooting is to prevent the situation in the first place. The only way to eliminate all possible problems is to completely block access to the MySQL server, which means it wouldn’t do any useful work. So we can speak only about compromises.
One important aspect of prevention is privileges. Although
many test cases in this book have used the root user, this is acceptable
only in test environments where you can’t harm anything. When running on
a production server, each person should have as few privileges as
possible. Ideally, a user would have only the privileges for those
objects she is going to use. For example, if a web application only
selects data from a few databases, don’t give write access and read
access to the
mysql database to the
web application’s user account. If you need to do a maintenance task on
that database, create a separate user for it.
Such precautions will protect you from bad interventions and can even mitigate against SQL injection attacks. A successful attack will be limited in the damage it can cause.
Another important aspect is the environment for running the server: the MySQL server options and the external environment in which it runs.
When adjusting options, analyze their effects. Start with only the options whose effects you are sure of, then add others one by one and analyze how they change things. In such a scenario, if something goes wrong, you will be able to restore an acceptable work environment ...