Reloading MySQL databases

You are viewing an old version of this page. View the current version. Compare with Current  |   View Page History

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

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 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

There are currently no attachments on this page.

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.