Converting the MySQL Database Storage Engine from MyISAM to InnoDB Using the Jamf Pro Server Tools Command-Line Interface

Updated: 22 March 2022
Product: Jamf Pro
This article describes how to convert the MySQL database storage engine from MyISAM to InnoDB using the Jamf Pro Server Tools command-line interface (CLI). The Jamf Pro database must be converted to InnoDB prior to upgrading to Jamf Pro 10.10.0.
Note:

The InnoDB conversion process may considerably increase the size of your MySQL database. Before converting the database, it is recommended that you have at least as much free disk space as the current database size, as well as adequate RAM (memory) for the server, as InnoDB will require some memory-intensive operations. If adequate system resources are not available on the server, either memory or storage, do not continue this process until resources have been addressed.

Converting the MySQL storage engine to InnoDB involves the following steps:

  1. Ensuring You Have the Latest Version of the Jamf Pro Server Tools CLI

  2. Confirming Database Information

  3. Configuring the Jamf Pro Server Tools CLI

  4. Configuring InnoDB Settings

  5. Stopping Apache Tomcat

  6. Backing Up the Database

  7. Converting to InnoDB

General Requirements

  • Local or remote access to the server hosting the Jamf Pro database

  • Local administrator credentials

Step 1: Ensuring You Have the Latest Version of the Jamf Pro Server Tools CLI

If you have already installed the Jamf Pro Server Tools CLI, ensure that you have the latest version by doing the following:

  1. To check which version you have installed, execute the following command:
    jamf-pro version
  2. Verify the version shown matches the latest version listed in the Changelog.
  3. If you do not have the latest version, use one of the following methods to download and install it.

Methods to Download Install Jamf Pro Server Tools CLI

Installing the Jamf Pro Server Tools CLI Included in the Jamf Pro Installer (Jamf Pro 10.7.0 or later)

Note:

If you are converting the MySQL storage engine to InnoDB and upgrading to the latest version of Jamf Pro, you may use the Jamf Pro installer to install the Jamf Pro Server Tools CLI. Be aware that the version of the Jamf Pro Server Tools CLI installed via a package manager will always be the latest and may be newer than the one included in the Jamf Pro installer. For more information, see "Downloading the Jamf Pro Server Tools CLI Using a Package Manager" below.

You can find the Jamf Pro Server Tools CLI in the following bin directory of the Jamf Pro installation for your platform:

  • Linux: /usr/local/jss/bin/jamf-pro

  • Windows: C:\Program Files\JSS\bin\jamf-pro.exe

Downloading the Jamf Pro Server Tools CLI Using a Package Manager

Red Hat Enterprise Linux
rpm --import https://archive.services.jamfcloud.com/gpg-public-key.asc
yum-config-manager --add-repo=https://archive.services.jamfcloud.com/rpm/
yum install jamf-pro-server-tools
Downloading the Jamf Pro Server Tools CLI Using a Browser
To manually download the Jamf Pro Server Tools CLI binary for your platform from a browser, go to the following webpage: https://archive.services.jamfcloud.com/#jamf-pro-server-tools/release/latest/
Other Downloading Options
For more information and options for installing the Jamf Pro Server Tools CLI, see the The Jamf Pro Server Tools Command-Line Interface article.
Note:

You can copy the jamf-pro binary to other servers in your environment if you want to use the CLI on a different server. There are no additional requirements or dependencies.

Step 2: Confirming Database Information

Gather the following database information, which will be needed in an upcoming step:

  • Database name

  • Database user

  • Database user's password

The database name, database user, and database user's password are needed to allow both:

  • the Jamf Pro Apache Tomcat web application to connect to the MySQL database, and

  • the Jamf Pro Server Tools utility to connect to and manage the MySQL database

Note:

When using the Jamf Pro Server Tools CLI to set the values for these connections, a different command must be executed for each connection.

For an existing Jamf Pro installation, this information should already be set in the DataBase.xml file (for the connection between the Jamf Pro web application and the MySQL database). You will need these same values to configure the connection between Jamf Pro Server Tools and the MySQL database (see below). If these values are unknown to you, you may obtain them from the DataBase.xml file.

The DataBase.xml file resides in the following locations:

  • Linux servers: /usr/local/jss/tomcat/webapps/ROOT/WEB-INF/xml

  • Windows servers: C:\Program Files\JSS\Tomcat\webapps\ROOT\WEB-INF\xml

Step 3: Configuring the Jamf Pro Server Tools CLI

In a default installation of the Jamf Pro Server Tools, the database information from Step 3 is already set to a default value in the CLI configuration.

  1. For Linux servers, execute the following command to return the current configuration:
    jamf-pro config list
  2. For Windows, all the jamf-pro commands must be executed from inside the directory where the executable is stored. To navigate to the correct directory, execute the following command in Command Prompt:
    cd C:\Program Files\JSS\bin
  3. In the returned configuration example above, the default database name is jamfsoftware, and the default database user is jamfsoftware as well. If the database name and database user information returned do not match what was displayed in the DataBase.xml file in Step 3, change the needed setting by executing commands similar to the following examples:
    jamf-pro config set --database-name db_name_from_database_xml
    jamf-pro config set --database-user db_username_from_database_xml
    Note:

    Replace the example text in the above commands, db_name_from_database and db_username_from_database, with the actual name and username from your database.

  4. In the returned configuration example above, the database user's password was not defined, instead showing as <not configured>. Specify the database user's password before continuing. This password was identified in Step 3 via the DataBase.xml. Configure the password by executing the following command:
    jamf-pro config set --database-password
  5. When prompted, enter your database password.
  6. Now that the password is set, execute the following command to make sure everything is configured correctly:
    jamf-pro config list

    The value for the database password should now show as <configured>. The database name and database user should match what is found in the DataBase.xml file. If what is returned in the list does not match the current system settings from the DataBase.xml file in Step 3, adjust the needed settings so that the tool will operate correctly and perform the conversion.

Configuring InnoDB Settings

Before converting the database engine from MyISAM to InnoDB, configure two InnoDB-specific MySQL settings. The two settings are innodb_buffer_pool_size and innodb_file_per_table.

innodb_buffer_pool_size

This setting controls how much RAM is available for the InnoDB engine to cache table and index data and must be configured before attempting the conversion. The recommended InnoDB buffer pool size is around 50 to 70% of the total system memory for dedicated database servers. However, if the Jamf Pro database server is also hosting Tomcat, calculate this value to be 50 to 70% of the remaining system memory, after accounting for how much is already allocated to the Tomcat service, the operating system, and any other running services on the server.

For example, a server running both Tomcat and MySQL with 16 GB of RAM total has 8 GB allocated to the Tomcat service and 4 GB has been set aside for the system and other processes. 4 GB remains from the original total, so the calculation would be to allocate anywhere from 2 to 3 GB to the InnoDB buffer pool size setting.

To set the innodb_buffer_pool_size, execute the following command:
jamf-pro database config set --innodb-buffer-pool-size <value>
This command will accept values in megabytes (MB) or gigabytes (GB). For example:
jamf-pro database config set --innodb-buffer-pool-size 2048MB
jamf-pro database config set --innodb-buffer-pool-size 2GB

innodb_file_per_table

This setting allows for data and indexes to be stored in separate .ibd files, allowing for compression and performance enhancement. This setting must be set to true (boolean, 1) before attempting the conversion.
  1. To set the innodb_file_per_table to true, execute the following command:
    jamf-pro database config set --innodb-file-per-table true
  2. Execute the following command to restart the MySQL service: 
    jamf-pro database restart

Step 4: Stopping Tomcat on the Jamf Pro Server

Requirements

For Linux servers, sudo privileges are required.

For Windows servers, the jamf-pro commands must be run from the directory where the executable is stored. Reference Step 4 for instructions on navigating to the correct directory.

Stop Apache Tomcat on the server hosting the Tomcat web application by executing a command similar to the following:
jamf-pro server stop

If Tomcat is running on a different server or this is a clustered environment, Tomcat must be stopped on all nodes. For information on starting and stopping Tomcat on the server hosting the Jamf Pro web app, see the Starting and Stopping Tomcat article.

Step 5: Backing Up the Database

  1. Perform a back up of the database using the CLI by executing the following command:
    jamf-pro database backup
    Note:

    For Windows, the jamf-pro commands must be run from the directory where the executable is stored. Reference Step 4 for instructions on navigating to the correct directory.

  2. For more details, execute the following command:
    jamf-pro database backup --help

Step 6: Converting the Database to InnoDB

  1. Start the conversion process by executing the following command:
    jamf-pro database convert-to-innodb
    Note:

    For Linux servers, sudo privileges will be needed. For Windows, the jamf-pro commands must be run from the directory where the executable is stored. Reference Step 4 for instructions on navigating to the correct directory.

  2. When prompted, enter Y to confirm the following prerequisites:

    • The database is running locally

    • Sufficient free disk space and system memory are available

    • The database has been backed up

    • The Tomcat server is stopped on all nodes

    • The InnoDB Buffer Pool Size setting has been configured and that the MySQL service has been restarted

  3. After the prerequisites are confirmed, a pre-conversion process occurs and perform the following actions:

    • Repair and optimize the database

    • Verify the database settings

After the pre-conversion process is complete, all database tables will be converted from MyISAM to InnoDB. This process may take some time.

A message stating that the conversion is complete will be displayed when the process has finished. At this point it is recommended to create a new database backup (see Step 7 above) prior to starting Tomcat again on the server.

Step 7: Getting Help and Troubleshooting

You can get additional usage information about the CLI by executing the following command:
jamf-pro help

Similarly, using the --help flag with any jamf-pro subcommand will display usage information about that command:

jamf-pro database --help

On Linux, some CLI commands may require sudo. If you receive an error similar to "jamf-pro: command not found", the sudopath may not be configured properly. To resolve this issue, run sudo using the full path to the jamf-pro binary. For example:

sudo /usr/local/jss/bin/jamf-pro database convert-to-innodb

If you receive an error similar to "Error: database config file not found", the my.cnf or my.ini file has not been found by the CLI tool. You will need to verify that it is present on the system per Step 2 above.

If you receive an error similar to "Error: unrecognized memory format", then the string syntax used to set the memory value is not recognized. The tool will accept "M" or "MB" for megabytes, and "G" or "GB" for gigabytes and is case-sensitive. For example: 
jamf-pro database config set --innodb-buffer-pool-size 1024M