MySQL Troubleshooting

Prevention

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.

Privileges

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.

Environment

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 ...