Manually Creating the Jamf Pro Database

Updated: 01 July 2021
Product: Jamf Pro

This article explains how to manually create the Jamf Pro database. The Jamf Pro web application requires the Jamf Pro database for storing its data and credentials for a user that has full access to that database.

MySQL is the database subsystem used by Jamf Pro. MySQL can be a local instance, residing on the same server as Jamf Pro, or MySQL can reside on a different server. While there are various methods for connecting to MySQL to perform the following tasks, the process described here is for connecting locally to the mysql command line on the same server on which MySQL is installed.

Creating the Jamf Pro database involves the following steps:

  1. Configuring MySQL 8.0 for Jamf Pro

  2. Connecting to MySQL

  3. Understanding MySQL Passwords

  4. Creating the Jamf Pro Database

  5. Creating the Jamf Pro Database User

  6. Granting Access to the Database

  7. Exiting MySQL

  8. Configuring MySQL

  9. Restarting MySQL

  10. Stopping Tomcat

  11. Configuring the Connection Between Jamf Pro and the Database

  12. Starting Tomcat and Accessing Jamf Pro (new Jamf Pro installations only)

Note:

You can also use the Jamf Pro Server Tools command-line interface to create the Jamf Pro database. For more information, see the Creating the Jamf Pro Database Using the Jamf Pro Server Tools Command-Line Interface article.

General Requirements

MySQL should already be installed and running on the target server.

Step 2: Connecting to MySQL

Requirements

Depending on the MySQL version you installed, the installation process may have created a randomly generated password for the "root" MySQL user (instead of the older strategy of creating the "root" user with no password).

You should temporarily copy this password somewhere (you will need it to complete the steps below), and plan to change the "root" user password.

  1. Do one of the following, depending on your platform:
    • Linux: From a command prompt, access the MySQL command line as the "root" MySQL user by typing:
      mysql -u root -p
      Note:

      If you have not added a path to mysql to the system path, you may need to enter the full path to MySQL in place of mysql in the above command. The path to MySQL can be determined with the command which mysql, but on Linux, it is usually:

      /usr/bin/mysql

    • Windows: Launch the "MySQL Command Line Client" or "MySQL Command Line Client - Unicode".

    • Mac: Open the Terminal application and access the MySQL command line as "root" MySQL user by typing: 
      mysql -u root -p
      Note:

      If you have not added a path to mysql to the system path, you may need to enter the full path to MySQL in place of mysql in the above command. The default path for MySQL on macOS is:

      /usr/local/mysql/bin/mysql
  2. If prompted, enter the password for the "root" MySQL user or simply press the Return key if the user has no password.
On any of the platforms, you should now see the MySQL command prompt:
mysql>

The remainder of the process is the same on all platforms.

Note:

All MySQL commands must end with a semicolon (;) and, although MySQL reserved words are presented below in uppercase, they are case-insensitive and may be typed in all lowercase.

Step 3: Understanding MySQL Passwords

Changing the "root" User Password
Use the following command at the MySQL command prompt (after logging in to MySQL) to change the "root" user password on MySQL 5.7.6 or later: 
ALTER USER 'root'@'localhost' IDENTIFIED BY 'mYub3rS3cuUr$p@sswRD';
Checking the MySQL Password Validation Level

The MySQL Password Validation Plugin is used to validate that any MySQL user passwords are of acceptable strength. The MySQL Reference Manual and specifically the Password Validation Options and Variables section provides more detailed information about password policy validation.

For production servers, it is recommended that at least the "MEDIUM" password policy validation level be enforced.

Note that on Red Hat Enterprise Linux, it is set to the "MEDIUM" password policy by default. This means all MySQL user passwords must contain at least one uppercase letter, at least one lowercase letter, at least one number, at least one special character, and be at least eight characters long.

Use the following command at the MySQL command prompt (after logging in to MySQL) to return the currently set password policy validation level and requirements:

SHOW VARIABLES LIKE 'validate_password%';

Step 4: Creating the Jamf Pro Database

To create the Jamf Pro database, type the following at the MySQL command prompt followed by pressing the Return key:
CREATE DATABASE jamfsoftware;
The database name can be customized by replacing jamfsoftware in the command above with the desired name as shown in the following example:
CREATE DATABASE myDatabaseName;
Note:

While this customization may increase security, it is generally not necessary.

Step 5: Creating the Jamf Pro Database User

Creating secure credentials (a combination of username and password) is of paramount importance in protecting production systems. It is imperative that you choose a secure password for the database user and it is recommended that you use a unique username. The password used will also have to conform to the MySQL password validation policy described above.

To create a new MySQL user with a username of uniquename and a password of Z9hfB#qta8YfUB{va6K (a long, randomly generated password or pass phrase is recommended), type the following at the MySQL command prompt followed by pressing the Return key: 
CREATE USER 'uniquename'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Z9hfB#qta8YfUB{va6K';
Note:

  • The WITH mysql_native_password portion of the command may not be supported on earlier versions of MySQL. If an error occurs, remove WITH mysql_native_password and reissue the command.

  • On non-production (i.e., "testing" or "staging") systems, the default username of jamfsoftware and the default password of jamfsw03 may be used to avoid having to provide custom values when the Jamf Pro web application first launches, but these should not be used for production systems.

If MySQL is installed on a server different from where the Jamf Pro web application will be installed, it is important to replace localhost with the IP address of the remote server that will be trying to communicate with MySQL (i.e., the Jamf Pro web application server), as in this example:
CREATE USER 'uniquename'@192.168.22.22 IDENTIFIED WITH mysql_native_password BY 'Z9hfB#qta8YfUB{va6K';
Note:

It is also necessary to configure the bind-address in the MySQL configuration file so that MySQL is "listening" for communication coming from this IP address.

In a clustered environment (more than one Jamf Pro web application server communicating with MySQL), it is necessary to create a user for each of the remote server IP addresses.
Note:

In a clustered environment, there are many other settings that will need to be addressed.

Step 6: Granting Access to the Database

To allow the newly created MySQL user full access to the Jamf Pro database, type the following at the MySQL command prompt followed by pressing the Return key:
GRANT ALL ON jamfsoftware.* TO 'uniquename'@'localhost';
Or, in the case of MySQL and Jamf Pro installed on different servers, type something similar to:
GRANT ALL ON jamfsoftware.* TO 'uniquename'@192.168.22.22;

This will grant ALL access to every table (*) of the database named jamfsoftware. Adjust jamfsoftware as needed, if you named the database something else.

Step 7: Exiting MySQL

To exit the MySQL command prompt, type the following at the MySQL command prompt followed by pressing the Return key:
exit
Note:

The semicolon is not used at the end of this command.

Step 8: Configuring MySQL

The default MySQL configuration has historically been an acceptable starting point when installing Jamf Pro. Starting with Jamf Pro 10.6.0, the default storage engine for the database is InnoDB.

Note:

For installations and upgrades to Jamf Pro 10.6.0 or later, the following one-time change to the MySQL configuration is required.

Configuration changes are made in the MySQL configuration file: my.cnf (Linux and macOS) or my.ini (Windows). The location of this file may vary depending on the MySQL version and operating system, but these are the most common locations:

  • Linux: /etc/my.cnf

  • Windows: C:\ProgramData\MySQL Server 8.x\my.ini

  • macOS: /etc/my.cnf

Note:

For a list of all possible locations for the my.cnf/my.ini file, see the the following webpage: https://dev.mysql.com/doc/refman/8.0/en/option-files.html.

Requirements
Creating the my.cnf File on macOS

On macOS with MySQL 5.7.18 or later, the MySQL configuration file does not get created by default. For a new installation, you can assume the file does not exist. For existing installations, it is recommended that you search for the my.cnf file in the MySQL-supported locations to ensure that it does not already exist before you create a new one.

Common locations for the my.cnf file include:

  • /etc/my.cnf

  • /etc/mysql/my.cnf

  • /usr/local/mysql/my.cnf

  • /usr/local/mysql/etc/my.cnf

For a complete list of MySQL-supported paths, see the following webpage: https://dev.mysql.com/doc/refman/8.0/en/option-files.html

To create a my.cnf file on macOS, execute a command similar to the following using your preferred MySQL-supported path:
sudo touch /etc/my.cnf
  1. Create a backup of the my.cnf (Linux or Mac) or my.ini (Windows) file.
  2. Open the MySQL configuration file (my.cnf or my.ini) in your preferred text editor.
  3. Look for the [mysqld] line. If it does not exist, add [mysqld] to the file. Ensure it appears above the innodb_buffer_pool_size and innodb_file_per_table lines if they are present.
  4. Change or set the value of the innodb_buffer_pool_size to an appropriate value (in bytes) for your server.
    Note:

    The innodb_buffer_pool_size 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.

    Note:

    Ensure that enough memory is left available for the operating system of the server. Different operating systems have different memory requirements. For more information, see the following webpage: https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-resize.html

  5. Change or set the value of innodb_file_per_table to ON.
    Note:
    The final relevant entries in the my.cnf file should look similar to the following example (using 1024M as the size):
    [mysqld]
innodb_buffer_pool_size = 1073741824
innodb_file_per_table = ON
  6. Save the modified file and then restart MySQL.

Step 9: Restarting MySQL

Restart MySQL following the steps below for your platform.

Linux
Type one of the following at the command prompt followed by pressing the Return key:
/etc/init.d/mysqld restart
service mysqld restart
service mysql restart
Windows

  1. Launch Command Prompt.

  2. Type both of the following commands, each followed by pressing the Return key:
    net stop MySQL80
net start MySQL80
    Note:

    The service name MySQL80 may vary.

Mac

  1. Open System Preferences.

  2. Click on the MySQL preference pane.

  3. Click the button to stop and start MySQL.

Alternatively, for MySQL 5.7 or later, type both of the following commands, each followed by pressing the Return key: 
sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

Step 11: Configuring the Connection Between Jamf Pro and the Database

After installing Jamf Pro on the server, you must configure the connection between Jamf Pro and the database using one of the following methods before the Jamf Pro server will start successfully:

Method 1: Manually Edit the DataBase.xml File
The database connection settings are stored in the DataBase.xml file. For instructions on editing the DataBase.xml file, see the Manually Editing the Database Connection article.
Method 2: Configure the Connection Using Jamf Pro Server Tools
For instructions, see the Editing the Database Connection Using Jamf Pro Server Tools article.

Step 12: Starting Tomcat and Accessing Jamf Pro (new Jamf Pro installations only)

Perform the following steps for new Jamf Pro installations only. Skip these steps if you are upgrading to MySQL 8.0.

  1. Start Tomcat. For instructions, see the Starting and Stopping Tomcat article.
  2. Access Jamf Pro by opening a web browser and typing the protocol, IP address or hostname of the server, and port. For example: https://jamf.mycompany.com:8443/