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.

Creating the Jamf Pro Database

Overview

This article explains how to create the Jamf Pro database which is required by the Jamf Pro web application to store its data and credentials for a user that has full access to that database.

MySQL is used as the database subsystem for Jamf Pro and can be a local instance (on the same server as Jamf Pro) or can be a MySQL instance on another 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

Summary of Steps

The following steps are described in detail below:

  1. Connect to MySQL
  2. Understand MySQL passwords
  3. Create the Jamf Pro database
  4. Create the Jamf Pro database user
  5. Grant access to the database
  6. Exit MySQL
  7. Configure MySQL

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.

Understanding 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 and later

ALTER USER 'root'@'localhost' IDENTIFIED BY 'mYub3rS3cuUr$p@sswRD';

MySQL 5.7.5 and 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 Plugin 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%';

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.

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.

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.

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.

Configure MySQL

The default MySQL configuration has historically been an acceptable starting point when installing Jamf Pro. Starting with Jamf Pro 10.6.0 and later, 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 5.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/5.7/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. 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. Typically this should be approximately 70% of the server's total available memory. At a minimum, it is recommended that this value is larger than the largest table size in the database. Table sizes will vary based on the number of enrolled devices and log flushing frequency. This value for this variable uses a single character to specify the unit of measure for the numeric value. Decimal values are not supported. Examples: "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.

  4. Change the value of innodb_file_per_table to 1.

  5. Save the modified file and then restart MySQL.

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 MySQL57
    net start MySQL57
    Note: The service name "MySQL57" 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 and above, 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 and below, type the following command followed by pressing the Return key:
    sudo /usr/local/mysql/support-files/mysql.server restart

Jamf Pro Startup

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 in light of new GDPR regulations if you are doing things like restoring a production database backup to a "testing" Jamf Pro instance.

Like Comment
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