 | 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. |
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.
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 reload-db.sh
| 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: 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