MySQL Troubleshooting

Chapter 7. Best Practices

This chapter summarizes some best practices that can help you troubleshoot MySQL effectively and safely. They aren’t troubleshooting methods or tools in themselves, but they can dramatically affect troubleshooting. These practices were discussed throughout the book but are combined here to emphasize their value.

Backups

Many of the procedures in this book can make changes to databases, which is why I’ve encouraged you to run tests in a sandbox. You can create your own disaster, however, by making such changes in a production database. Backups can save you in such cases.

A backup is a state to which you can return at any step. If something is damaged during tests or an application failure, you can restore all but the most recent changes to data. Backups are also useful because you can load data from them into a sandbox for testing. If you make a backup at a convenient time, you don’t have to wait until load is low on the main server and you can take a new snapshot of a running instance. Without interrupting normal operation, you can just copy the backup to the sandbox and start testing.

Of course, a backup is useful only if it’s recent. A backup made a month ago of an application with extensive write operations won’t help much. So it’s a good idea to make periodical full backups and frequent incremental backups. It’s also good to have the binary log turned on so that you have all the changes made at the time a problem occurred.

The books High Performance MySQL ...