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:
{code}
chmod +x reload-db.sh
{code}
{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.
{note}
{code}
usage: reload-db.sh 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.
OPTIONS:
-h Show this message
-c Cleanup. Delete db dump when finished
-b {path} Set backup directory, make sure there is enough diskspace
default=/tmp
-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
{code}
{attachments}
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:
{code}
chmod +x reload-db.sh
{code}
{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.
{note}
{code}
usage: reload-db.sh 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.
OPTIONS:
-h Show this message
-c Cleanup. Delete db dump when finished
-b {path} Set backup directory, make sure there is enough diskspace
default=/tmp
-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
{code}
{attachments}