Skip to main content
Jamf Nation, hosted by Jamf, is a knowledgeable community of Apple-focused admins and Jamf users. Join us in person at the ninth annual Jamf Nation User Conference (JNUC) this November for three days of learning, laughter and IT love.

Manually Creating the Jamf Pro Database

Overview

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.

Note: You can also use the Jamf Pro Server Tools CLI 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 Knowledge Base article.

Requirements

MySQL should already be installed and running on the target server. For more information, see the Installing Java and MySQL Knowledge Base article.

For more guidance on how to handle the root MySQL user password during installation and setup of MySQL, see the "Understanding MySQL Passwords" section below.

Procedure

Creating the Jamf Pro database involves the following steps:

  1. (MySQL 8.0 only) Configure MySQL for Jamf Pro
  2. Connect to MySQL
  3. Understand MySQL passwords
  4. Create the Jamf Pro database
  5. Create the Jamf Pro database user
  6. Grant access to the database
  7. Exit MySQL
  8. Configure MySQL
  9. Restart MySQL
  10. Start Up Jamf Pro

Step 1. (MySQL 8.0 only) Configure MySQL 8.0 for Jamf Pro

Follow the instructions in the Configuring MySQL 8.0 for Jamf Pro Knowledge Base article.

Step 2. Connect to MySQL

Default MySQL password

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.

Linux

  1. 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
  2. If prompted, enter the password for the “root” MySQL user or simply press the Return key if the user has no password.

Windows

  1. Launch the "MySQL Command Line Client" or "MySQL Command Line Client - Unicode".
  2. If prompted, enter the password for the “root” MySQL user or simply press the Return key if the user has no password.

Mac

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

MySQL Command Line Prompt

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. Understand MySQL Passwords

Use one of the following commands at the MySQL command prompt (after logging in to MySQL) to change the "root" user password:

MySQL 5.7.6 or Later
ALTER USER 'root'@'localhost' IDENTIFIED BY 'mYub3rS3cuUr$p@sswRD';
MySQL 5.7.5 or Earlier
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mYub3rS3cuUr$p@sswRD');

MySQL Password Validation Policy

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. Create 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. Create 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: 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. Grant 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. Exit 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. Configure 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: /usr/local/mysql/my.cnf

Note: For a list of locations of the my.cnf/my.ini file, see the the following webpage: https://dev.mysql.com/doc/refman/8.0/en/option-files.html. On macOS, you may not find a my.cnf file and it may be necessary to create the file using default example files. You should be able to use the following command: sudo cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

  1. Create a back up the my.cnf/my.ini 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] above the innodb_buffer_pool_size and innodb_file_per_table lines.
  4. Change the value of the innodb_buffer_pool_size to an appropriate value 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.
  5. Change the value of innodb_file_per_table to 1.
  6. Save the modified file and then restart MySQL.

Step 9. Restart 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,

  1. 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
    or
  2. For MySQL 5.6 or earlier, type the following command followed by pressing the Return key:
    sudo /usr/local/mysql/support-files/mysql.server restart

Step 10. Start Up Jamf Pro

After installing Jamf Pro on the server, your first navigation to the Jamf Pro web application should display the "Database Connection Error" dialog.

Click the Edit Connection button and change any of the default information to the custom values that you used during the creation of the Jamf Pro database and database access user. This should, at least, include the password.

Note: As mentioned earlier, you may avoid this step on testing or staging systems by using the default jamfsoftware database name, the default jamfsoftware@localhost username and the default jamfsw03 password, but these should not be used for production systems containing user data.

Warning: Be especially cognizant of the need to use secure credentials to meet GDPR regulations if you are doing things like restoring a production database backup to a "testing" Jamf Pro instance.

Like Comment
Order by:
SOLVED Posted: by ryan.ball

For those that want to do this on Ubuntu 16.04 you can use this file:

/etc/mysql/mysql.conf.d/mysqld.cnf

In my case I added the lines at the bottom like so:

# Added by blah blah blah on blah blah date
# As mentioned here: https://www.jamf.com/jamf-nation/articles/542/creating-the-jamf-pro-database
innodb_buffer_pool_size=11468M
innodb_file_per_table=1

Then run:

sudo /etc/init.d/mysql restart

If you do want to use the default my.cnf file make sure you precede your lines with "[mysqld]" or mysql might not start back up.

Like
SOLVED Posted: by andrewtadkins5

Testing Mac Pro (64G Memory), macOS 10.13.6, Server 5.6.3 with MySQL 5.7.24-community: noted that when a sample my.cnf was added, MySQL would no longer startup. There was no sample file in /usr/local/mysql/support-files/ available, so wrote one:

[mysqld]
innodb_buffer_pool_size = 16G
innodb_file_per_table = 1

MySQL would not start. When I renamed /etc/my.cnf to /etc/my.not, MySQL started up on its own. BTW, this test is borne of issues with a new JSS running CentOS 7.8.1804 that is being investigated separately.
Just curious if there is a recommended format for directing MySQL on macOS to use innodb.

Like
SOLVED Posted: by brian.stewart

@andrewtadkins5 MySQL uses some defaults when there is no my.cnf file. On my macOS machine, MySQL would not start without having a datadir variable present. My minimal my.cnf looks like this:

[client]

[mysqld]
datadir = /usr/local/mysql/data
innodb_buffer_pool_size = 1G
innodb_file_per_table = 1
Like
SOLVED Posted: by ron.heck

This article has been updated to add a new Step 1 for configuring MySQL 8.0 for Jamf Pro.

Like
SOLVED Posted: by ron.heck

This article has been updated as follows:

  • Moved the link to the "Creating the Jamf Pro Database Using the Jamf Pro Server Tools Command-Line Interface" Knowledge Base article to the Overview section
  • Added information in Step 2 about the default MySQL password
  • Updated the commands in Step 5 for creating the MySQL database user
Like
SOLVED Posted: by ron.heck

This article has been updated to provide more details about the innodb_buffer_pool_size setting.

Like