Skip to main content
Jamf Nation, hosted by Jamf, is the largest Apple IT management community in the world. Dialog with your fellow IT professionals, gain insight about Apple device deployments, share best practices and bounce ideas off each other. Join the conversation.

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.

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

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

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.

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 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 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. This is the total amount of memory that MySQL will use on the server. This should be approximately 70% of the server's total available memory. At a minimum, a value larger than the largest table size in the database is recommended. Table sizes will vary based on the number of enrolled devices and log flushing frequency. The value for this variable uses a single character to specify the unit of measure for the numeric value. Decimal values are not supported. For example:
    • "16G" is equal to 16 Gigabytes
    • "4096M" is equal to 4096 Megabytes or 4 Gigabytes. 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.

Additional Information

For for information, see the Creating the Jamf Pro Database Using the Jamf Pro Server Tools Command-Line Interface Knowledge Base article.

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