I’ve been working on various different MySQL related issues and maintenance procedures some of which have not gone according to plan. Here is a recipe that may help you avoid wasting a lot of time, especially if your database is large.
In order to do some of these tests make tests against a server configured identically to the one you plan to work on but instead which has no data. That is the mysql database needs to be complete but the other databases need to be dumped with the –no-data or -d options. Don’t forget to also include any triggers or stored routines.
Now run the “procedure” on this “emtpy instance”. As it has no data most things run very quickly. So if you have issues you can repeat the procedure in no time. Restoring the instance too is easy as it’s tiny. This makes the whole procedure scriptable and you can be confident in the results.
Once you are …[Read more]