Posted on by Glenn

MySQL I : Maintenance and Patching

Basic maintenance

1. Back up all data, in case something goes wrong during the repair and optimization process. To do this, stop MySQL, create a fold named <todays_date>, and manually copy the data directory there. Restart MySQL.
2. Determine which tables you want to analyze and repair.
3. cd to the bin directory: cd c:\mysql\bin
4. Run an analysis: myisamchk -a c:\mysql\data\accounting\expenses.myi
5. To repair a table: myisamchk -r c:\mysql\data\accounting\expenses.myi
6. Repeat steps for other tables.
7. Reload the tables into MySQL, so that MySQL sees the changes:
mysqladmin –u root –p reload
8. You can use wildcards. For example:
myisamchk –r c:\mysql\data\accounting\*.myi
9. Optimally, database maintenance is scripted and run from “Cron” or “Task Manager”. You might want to script this nightly or weekly. The larger the table, the longer the maintenance can take!
10. To optimize an individual table, log into the MySQL Monitor client:
mysql –u root –p
11. Select the database you want to work on. Type: USE accounting;
12. Optimize, a table at a time:
OPTIMIZE TABLE clients;
OPTIMIZE TABLE expense_categories;
OPTIMIZE TABLE expenses;
OPTIMIZE TABLE invoices;
OPTIMIZE TABLE users;
13. Such repair and optimization is limited to the ISAM table type.

Patching

Linux

  1. First, you’re going to patch manually only if you installed from a .tar.gz and compiled mysql yourself.
  2. Download the patch, place it in your source directory, and move to that directory.
  3. Command: gunzip patch-name.tar.gz
  4. Command: rm config.cache
  5. Command: make clean
  6. Now run config, make, and make install again.