Shrink the Database

Summary/Problem

Your MySQL database is larger than expected, and you need steps to reduce the size of the database.

Cause

As the Printer Installer database grows, MySQL increases the size of the file that stores the database to accommodate the larger database. If the database later becomes smaller (for example, by deleting unused drivers), the database file will remain at its larger size. This is normal behavior, but if there was a dramatic reduction in the database size, and expect it to stay small, it can be nice to reclaim the unused hard drive space.

Additionally, there was an issue in certain versions of Printer Installer that caused one of the database tables to rapidly grow. This issue was resolved in version 16.1.5.94, and your Printer Installer server should be updated to this version or newer before shrinking the database. Otherwise, the database will just grow again.

Solution

  1. Export the database to a file.
    1. Open Command Prompt as Administrator and run the following two commands:

      cd "C:\program files\mysql\MySQL Server 5.5\bin"

      mysqldump --user=[DBUser] --password=[DBPassword] --single-transaction --add-drop-database --databases [DBName] > C:\backup.sql

      NOTE: The [DBUser], [DBPassword], and [DBName] values can be found in C:\inetpub\wwwroot\config\settings.php.

  2. Open Services and stop the MySQL5.5 service.
  3. Delete the ibdata1 file in C:\Program Files\MySQL\MySQL Server 5.5\data.

    Alternatively, you can move the file to a different folder if you don't have a server snapshot, in case something goes wrong.

  4. Start the MySQL55 service.

    Wait until the service is fully running.

  5. Import the database backup.

    1. Open Command Prompt as Administrator and run the following two commands:

      cd "C:\program files\mysql\MySQL Server 5.5\bin"

      mysql --user=[DBUser] --password=[DBPassword] [DBName] < C:\backup.sq

      NOTE: The [DBUser], [DBPassword], and [DBName] values can be found in C:\inetpub\wwwroot\config\settings.php.

Attribution:

Last updated:

Author:

Contributions by:

August 23, 2017

Dustin Larsen

Sean Bangerter