View Source

h3. Problem:

When you delete data from MySQL the ibdata1 file never shrinks. This can be a problem if you delay in implementing a data retention policy.

The ibdata1 file grows to a very large size causing performance and potentially other database problems in GroundWork. To deal with these problems you must implement a data retention policy and trim a lot of records from the database, but the filesize of the ibdata1 file does not shrink.

h3. Solution:

The procedure to shrink the ibdata1 file is to stop groundwork, dump the databases, stop mysql, remove the ib\* files and associated database files, restart mysql, reload the dumped databases, and restart groundwork.

Attached is a script that automates this process.

Download and make the script executable:
chmod +x
{note} This script will cause a service outage for all groundwork services (including monitoring), and should only be run during a maintenance window. Depending on the size of the databases, this process may take a while to complete.

usage: options

This script works with GroundWork Monitor.

It stops groundwork, dumps the dbs, stops mysql, removes the ib* files
and associated db files, restarts mysql, reloads the db dump, and restarts groundwork.

-h Show this message
-c Cleanup. Delete db dump when finished
-b {path} Set backup directory, make sure there is enough diskspace
-l Force legacy variable. Use this flag if GroundWork
is older than version 5.3 and the script doesn't detect this.
-u {user} Set the mysql user, default=root
-p {password} Set the mysql password, default=""
-a Prompt for mysql password, overrides '-p' setting